Weekly Execution Offers Reporting Process - 2021-2022
- Lavanya Davluri
Monday Offer Notebook Prep
A. Preparing list of guests who viewed the offer in app (takes the longest)
Download Offer User list from Amplitude link (
https://analytics.amplitude.com/restaurantbrandsintl/chart/2t360m2?source=dashboard )
2. Edit dates to most recent week-Monday to Sunday
3. Download Users in 5 cohorts (since csv can only hold 1m rows of data)
a. Region – Ontario / Platform – iOS
b. Region – Ontario / Platform – non iOS (Ontario has more than 1m rows of data)
c. Region – Quebec
d. Region – Alberta + British Columbia
e. Region – All other (non Ontario/Quebec/Alberta/BC)
4. Copy/paste an offer folder from the previous week ( Offers)
5. Rename the downloaded files as Opened_app_yyyymmdd_1 format (can copy the name from the files in “Raw Data” folder and change the date)
6. Open all downloaded and renamed files (takes time to load)
7. Once the new files are opened, one by one update the files in “Edited”
a. Copy user_id column only
8. Rename the files in Edited to the most recent date
10. Upload the files to databricks – Data – Create Table
a. Table settings:
i. Cluster – Any cluster can be used to create a table
ii. Table Name – remove “_csv” from the table name
iii. Create in Database – CA
iv. Column Delimiter – “,”
v. First Row Header checked
11. In the Canadian offer notebook : ( https://timhortons-dev.cloud.databricks.com/?o=2205369574251959#notebook/2951651830673629/command/2951651830673630 ), Change input table names from Command 36 “Viewed in App”
12. Run command 37 to check if the tables are properly uploaded
B. Update email sends
Change dates in the widgets from the measurement offer notebook (
2. Run command 20 “All Emails Sent this Week” to find the email code sent that week (campaign_id)
a. Offer emails subjects are formatted as “15Nov2021_Monday_Newsletter_CA
3. Add the campaign id in the list below (command 21, “List of Offer Emails to Filter for”)
Tuesday morning update
Master Offer Notebook Update
Run All Commands
above Command 77 – to check before appending to the master offers table
Run All commands after and including 77 – to append new weeks data to the master offers table
Offer related queries
Change Week Start / End dates
Run command 4 to update Offer Library
Run command 7 for the redemptions
Run command 9 for loyalty RRAMI
Run command 11 and 12 for Net loyalty impact
Run command 14 and 15 for Loyalty Discount Impact (don’t need Scott’s Discount file)
Run command 17 for offer funnel
Run command 19 and 20 for NoC Offer Impact Data
Run command 21 for Offer Transaction FnP
a. Change TH_FISCAL_WEEK to include the most recent week
Update Excel (1.Offer Impact_week xx)
Update “Net Loyalty SWS Impact” Tab
a. Columns E (CMD14 offer related query), F (CMD 16 offer related query)
b. Column H (CMD 14 offer related query)
c. Column I (CMD 12 offer related query)
Update “Offer Redemptions” Tab
a. Paste raw data in “Redemptions Data” tab
i. Get recent week data from “Offer Related Queries” notebook
b. Paste Loyalty RRAMI data in “National RRAMI” tab
c. Update pivot in “Offer Redemptions” tab
Update “Offer Funnel” Tab
a. Get raw data inputs from CMD18 from offer related query
b. 30 Day Active from “Master Offer Notebook” command 20 “Active Registered Users”
Update Powerpoint
Net Loyalty SWS Impact
a. Click on chart à Chart Design à Edit Data in Excel
i. Net SWS Impact