Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
import spark.implicits._
// Input data
val identity =
.select("id", "account_created", "last_active_date", "primary_email_address")
val consentSummary = spark
.select("identity_id", "jobs")
val brazeJobConsentUsersFull =
.option("header", true)
val brazeJobConsentUsers = brazeJobConsentUsersFull
.select($"user_id".as("external_id"), $"Appboy ID".as("braze_id"), $"email", $"consents_jobs_opted_in")
val dlJobConsentUsersToUploadQuery =
"""select *
|from clean.braze_users
|where consents_jobs_opted_in = true
val dlJobConsentUsersToUpload = spark.sql(dlJobConsentUsersToUploadQuery).cache()
// Diffs
val usersInBrazeNotDl = brazeJobConsentUsers
.join($"".as("external_id")), Seq("external_id"), "left_anti")
val usersInDlNotBraze =
.select($"".as("external_id"), $"email")
.join(brazeJobConsentUsers, Seq("external_id"), "left_anti")
// Investigating diffs.
// 1. Join extraneous users in Braze with identity to see how many match up
val brazeExtraUsersVsIdentity =
.join(identity, usersInBrazeNotDl("external_id") === identity("id"), "left_outer")
val numExtraBrazeUsersThatAreNotInIdentity = brazeExtraUsersVsIdentity.where("id is null").count()
// > 20
val numExtraBrazeUsersThatAreInIdentity = brazeExtraUsersVsIdentity.where("id is not null").count()
// > 1149
// Find why the 1149 that are still in 'identity' have job consents in Braze.
// First, check their job consent value in clean.consent_summary
val knownExtraBrazeUsersWithConsents =
.where("id is not null")
.join(consentSummary, brazeExtraUsersVsIdentity("id") === consentSummary("identity_id"), "left_outer")
val numExtraBrazeUsersWithAnyFormOfJobConsents =
knownExtraBrazeUsersWithConsents.where("jobs is not null")
// > 0
// So ALL of these users had 'null' in consent_summary.
// Had they given job consent before?
// Have a look in older versions of clean.consents to find out.
def consentsDataFrame(dateStr: String) = spark.sql(s"""
|select identity_id,
|from clean.consents
|where export_date = date '$dateStr'""".stripMargin)
// The first date we have braze_users data for is 2018-11-18, so try this first.
val oldConsents = consentsDataFrame("2018-11-18")
val knownExtraBrazeUsersWithOldConsents =
.where("id is not null")
.join(oldConsents, brazeExtraUsersVsIdentity("id") === oldConsents("identity_id"), "left_outer")
val numExtraBrazeUsersWithOldConsents = knownExtraBrazeUsersWithOldConsents.where("consented = true").count()
// > 1143
// Also: 6 nulls in at this date
// So, these extra users DID have job consents at some point but don't in the latest data.
// Now the question is: why did we not action deletions for the ones who removed their consent?
// To answer this, plot the number of consents present over the date range.
val dateRange = Stream
.range(0, 100)
.map(n => LocalDate.of(2018, 11, 18).plusDays(n))
.takeWhile(_ isBefore LocalDate.of(2018, 11, 27))
def numConsentsForDate(date: LocalDate): Long = {
val consentsForDate = consentsDataFrame(date.toString)
val usersWithConsentsForDate =
.where("id is not null")
.join(consentsForDate, brazeExtraUsersVsIdentity("id") === consentsForDate("identity_id"), "left_outer")
val numActualConsentsForDate = usersWithConsentsForDate.where("consented = true").count()
println(s"For date $date, $numActualConsentsForDate of the users had a value of 'true' for the job consent")
val numConsentsByDate = => date -> numConsentsForDate(date))
// Dump counts as CSV
numConsentsByDate.foreach {
case (date, count) =>
// date,count
// 2018-11-18,1143
// 2018-11-19,1146
// 2018-11-20,1146
// 2018-11-21,1148
// 2018-11-22,1148
// 2018-11-23,991
// 2018-11-24,700
// 2018-11-25,269
// 2018-11-26,3
// So we see that these users HAVE been removing their consent steadily over this date range.
// Yet somehow this has not been reflected in Braze.
// If all the removed consents had happened on a single date, we might suspect that the job had a one-off failure.
// But as there should have been deletions happening on many dates, there seems to be something systematic
// wrong with deleting attributes off users.
// A hypothesis for what went wrong:
// - We're not handling the case when a consent flag is 'null' (as opposed to false).
// There are two options for what to do in this case: send a 'null' value to Braze, which will delete the user property,
// or infer a 'false' value and send this to Braze. What does our code do in this case?
// First, check what the job consent values are in braze_users for these 1,143 users:
val interestingIdentityIds = brazeExtraUsersVsIdentity
.where("id is not null")
val brazeUsers = spark
.select($"".as("external_id"), $"consents_jobs_opted_in")
val brazeUsersContentForInterestingIds =
.join(interestingIdentityIds, interestingIdentityIds("id") === brazeUsers("external_id"))
brazeUsersContentForInterestingIds.where("consents_jobs_opted_in is null").count()
// > 0
// There are actually NO null values for consents_jobs_opted_in in clean.braze_users
// The values uploaded to braze are always Some(true) or Some(false), never None.
// (See BrazeUserAttributesSource.createUserAttributes())
// So that hypothesis can't hold.
// Some other candidate hypotheses:
// - Braze aren't correctly handling these updates
// - We didn't apply diffs for these users (the job failing to handle some diffs)
// To test these, it would be useful to know if there were any users that DID change their job consent
// from true to null/false in the same time period and see if ANY of these were updated correctly in Braze.
// First, find all users who DID give job consent on 2018-11-18 and DIDN'T give job consent by 2018-11-26
val consentsAtStart = consentsDataFrame("2018-11-18")
.withColumnRenamed("consented", "consented_at_start")
val consentsAtEnd = consentsDataFrame("2018-11-26")
.withColumnRenamed("consented", "consented_at_end")
val changedConsents = consentsAtStart
.join(consentsAtEnd, "identity_id")
.where("consented_at_start = true and (consented_at_end = false or consented_at_end is null)")
val numChangedConsents = changedConsents.count()
// > 1399
val changedConsentsToFalse = consentsAtStart
.join(consentsAtEnd, "identity_id")
.where("consented_at_start = true and consented_at_end = false")
val numChangedConsentsToFalse = changedConsentsToFalse.count()
// > 1399
val changedConsentsToNull = consentsAtStart
.join(consentsAtEnd, "identity_id")
.where("consented_at_start = true and consented_at_end is null")
val numChangedConsentsToNull = changedConsentsToNull.count()
// > 0
// Now for these users, see how many of them have the correct job consent setting in Braze.
val brazeStateForChangedConsents = brazeJobConsentUsers.join(
brazeJobConsentUsers("external_id") === changedConsents("identity_id"),
val numMatchedAtStart = brazeStateForChangedConsents.where("consents_jobs_opted_in = consented_at_start").count()
// > 1141
val numMatchingAtEnd = brazeStateForChangedConsents.where("consents_jobs_opted_in = consented_at_end")
// > 0
// This suggests that NONE of the cases where users change their consent to false have actually been picked up in Braze.
// Which suggests this is not an intermittent problem.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment