Occasionally, the data flow from the Jumbo Lottery Platform (JLP) to downstream apps like Segment and Braze can be disrupted due to the sheer volume of events being consumed globally by these applications. Although the event pipeline is 99.99% accurate, when incongruences occur it can result in inaccurate communication preferences along the MarTech stack.
This guide outlines the process to identify out-of-sync customers and re-sync them using JLP Admin.
Prerequisites
- Access to the Jumbo Lottery Platform (JLP) Admin with customer edit permission.
- Access to Braze with permissions to create and export segments.
- Access to Metrics data (via Looker or your BI tool of choice).
- Spreadsheet software (e.g., Google Sheets).
Step 1: Pull data from Braze
1.1 Create a segment
Log in to Braze and navigate to Audience -> Segments.
Create a segment that contains all customers with communication preferences:
Click Create Segment in the top-right corner.
Enter a name (e.g., 'Customers With Communication Preferences') and click Create Segment.
Under Segment Builder -> Filter Group -> Click Search filter -> Click Custom attributes -> Click Custom attributes.
Select the 'communication_preferences' custom attribute and set Comparison to has a value.
Check the Estimated Reachable Users for the Segment:
- If the segment has fewer than 330k users, proceed to the Export Segment Data (1.3) section.
- If the segment exceeds 330k users, follow the steps in Partition the Data into Groups of <330k Users (1.2).
1.2 Partition the data into groups of less than 330k
Google Sheets imposes a 100MB file import limit, which restricts CSVs to ~330k rows.
To achieve this, create one segment for each group of 330k users. We'll use Braze's random bucket number to split users into groups. Each user in Braze is assigned a random bucket number between 0 and 9999 inclusive.
For example, if there are 1.2 million users on Braze, we would require four segments of <330k users. Divide the random bucket number into four ranges:
- Group 1: Random Bucket # from 0 to 2499
- Group 2: Random Bucket # from 2500 to 4999
- Group 3: Random Bucket # from 5000 to 7499
- Group 4: Random Bucket # from 7500 to 9999
By doing this, each group will contain roughly 330k users, ensuring all CSV files stay within the size limit.
Navigate to your segment ("Customers With Communication Preferences"). Rename it to "Customers With Communication Preferences Group 1".
Under Segment Builder -> Filter Group -> Search Filter, search for Random Bucket #.
Click the OR drop down next to Random Bucket # and select AND.
In the Comparison drop down, select at least and set the Random Bucket # to the lower limit (e.g. 0).
In the Search Filter, add another Random Bucket # filter.
In the Comparison drop down, select at most and set the Random Bucket # to your upper limit (e.g. 2499).
Click Save.
Repeat this process to create a new segment for each additional group.
Group 2 example:
1.3 Export segment data
Open a segment in Braze -> click User Data -> CSV Export User Data.
Under Custom Attributes select the attribute to resync (e.g. communication_preferences).
Click Export. Braze will email the CSV file shortly. Repeat the export for each segment.
Step 2: Remove personally identifiable information (PII) from Braze data
By default, Braze exports include PII. Since this task doesn’t require PII and to avoid uploading sensitive data to Google’s servers, it’s recommended to remove PII locally using spreadsheet software. This also helps keep CSVs under Google Sheets' 100MB import limit (~330,000 rows).
Open your spreadsheet software of choice and import your Braze data.
Select all columns, excluding user_id and communication_preferences. Right click a highlighted column and select 'Delete columns'.
Your data should look similar to this:
Save the CSV file. Repeat this process for all exported Braze CSV files.
Step 3: Pull data from Metrics
Use your BI tool of choice (Looker, Power BI, etc) and the following query to extract the Metrics data:
SELECT
C.customer_encoded_id,
CASE
WHEN L.lottery_names = '{ALL}' THEN 'all_lotteries'
ELSE REPLACE(LOWER((L.lottery_names::text[])[1]), ' ', '_')
END AS lottery_name,
customer_communication_preferences->>'sms' AS sms,
customer_communication_preferences->>'push' AS push,
customer_communication_preferences->>'email' AS email,
customer_communication_preferences->>'phone' AS phone,
customer_communication_preferences->>'direct_mail' AS direct_mail
FROM customer_communication_preference CCP
JOIN customer C ON C.customer_id = CCP.customer_id
JOIN dim_lottery L ON L.dim_lottery_id = CCP.dim_lottery_id
WHERE customer_communication_preference_timestamp >= '2024-12-01'
AND customer_communication_preference_timestamp <= '2024-12-31';
Note: Update the date range (2024-12-01 to 2024-12-31) as required.
Step 4: Set up data in Google Sheets
This guide uses Google Sheets as it is easily accessible by anyone on any operating system. Feel free to use your spreadsheet software of choice, but be aware that the spreadsheet formula provided have been designed and tested for Google Sheets.
4.1 Import Braze Data
Create a new Google Sheets document. Click File -> Import -> Upload your first Braze CSV. Under Import Location select Replace current sheet.
Click Import data.
If you removed PII from your Braze data, it should look similar to this:
Highlight all empty columns -> Right click on a selected column -> Delete columns
To add the rest of your Braze CSVs, repeat the process above for each CSV, but instead of selecting 'Replace current sheet', select 'Append to current sheet'.
Repeat this process for each CSV that you exported from Braze.
Double click on the sheet's name and rename it Braze.
4.2 Import Metrics Data
Click File -> Import -> Upload your Metrics data CSV. Under Import Location select Insert new sheet(s).
Click Import data.
Double click on the sheet's name and rename it Metrics.
4.3 Compare Data
Create a new column for each communication preference method (e.g., SMS Match, Push Match, etc).
In cell H2 paste this formula:
=C2 = REGEXMATCH(FILTER(Braze!$B:$B, Braze!$A:$A = $A2), "\b" & $B2 & "_" & C$1 & "\b")
Where Braze is the name of the sheet with your Braze data.
In cell C2 we can see SMS preferences for all_lotteries is set to TRUE in the Metrics data. So this formula will return TRUE if the customers communication_preferences data in the Braze sheet contains all_lotteries_sms.
Select the cell with the formula and hover over the circle in the bottom right corner. Double click to populate the formula for the entire column.
Select all the cells in the column containing the formula. Then click and drag the blue circle in the bottom right hand corner of the cell to the right to apply it to the other four columns.
4.4 Identify Out-Of-Sync Customers
Create a final column called All Match that will tell us whether each customers communication preferences are synced correctly.
Modify the below formula based on what communication methods your site sends to Braze.
E.g., if your site only sends SMS and email preferences to Braze then the below formula should only check H2 and J2.
=AND(H2, I2, J2, K2, L2)
Copy paste the formula into cell M2.
Double click the blue circle in the bottom right corner of the cell to apply the formula to the whole column.
Click on the M column -> Click 'Create a filter' in the tool bar.
Click the three horizontal lines icon next to All Match -> deselect all values apart from FALSE to view customers who are out of sync between Metrics and Braze.
This gives you a list of customers to manually sync via the admin site.
Step 5: Sync Customers
Log in to JLP Admin -> navigate to the customer profile.
Click Sync account. Repeat this process for each customer to be synced.
Note: Allow up to one hour for the sync to reach Braze.