Weekly Execution - Loyalty Slides

Weekly Execution 2024 Databricks Notebook

https://timhortons-dev.cloud.databricks.com/?o=2205369574251959#notebook/388648600066931

Weekly Execution Excel files

All files used for Loyalty Weekly Execution slides can be found in the Marketing Sharepoint

KDS, Known Diner Spend Deciles Points Issued/Redeemed: “KDS & Points Wk __”

Comping Guests & Known Diner Segment Sales: “Comping Guest View Week __”

Offer AUVs by Category (& YoY): “Offer AUVs Wk __”

Offer Sales & Incrementality: “Offers Performance Wk __”

Loyalty Discounting (& YoY): “Discounting Wk __”

Loyalty Section Slide 1 & 2 - Known Diner Sales Penetration, Known Diner Penetration YoY Growth

1. Open the Weekly Execution 2024 notebook and “KDS & Points Wk __” file

2. In the Weekly Execution 2024 notebook, update the date widgets at the top left to reflect the current reporting week, in the date format “YYYY-MM-DD” (e.g. Week 37 should have START_DATE as 2024-09-09 and END_DATE as 2024-09-15)
   
   

   Open

   

3. Run the following cells:

   1. SWS

   2. RRAMI

   3. KDS

4. In the KDS & Points file “SWS & RRAMI” tab, add a new row for the new week. Update the columns that are in red text (K, M,N) using Databricks output. Drag down the other columns so that the formulas flow into the new row that you made.

   1. Column K: SWS (Databricks) → Update using Databricks “SALES” output from “SWS” cell

   2. Column M: Tickets → Update using Databricks “TRXNS” output from “SWS” cell

   3. Column N: RRAMI → Update using Databricks “RRAMI” output from “RRAMI” cell

5. In the KDS & Points file “KD YoY” tab, update the columns that are in red text (C, D, E) using Databricks output. Drag down the other columns so that the formulas flow into the new row.

   1. Column C: KDS → Update using Databricks “SALES” output from “KDS” cell

   2. Column D: KD Tickets → Update using Databricks “TICKETS” output from “KDS” cell

   3. Column E: Known Diners→ Update using Databricks “GUESTS” output from “KDS” cell

6. In the KDS% Chart tab, the graph should now be updated for the new week. Fix formatting and data label as needed. Copy & paste graph into the Known Diner Sales Penetration slide.

7. In the KDS% Growth YoY Chart tab, click “Select Data” and select only the data for the most recent 12 weeks. Then click “OK”, the chart should now be updated. Fix formatting as needed. Copy & paste graph into the Known Diner Penetration YoY Growth slide.

Loyalty Slide 3 - Average Guest Spend Lift YoY by Guest Spend Tiers

1. In the Weekly Execution 2024 notebook, go to “New Loyalty Views” and run the “Guest Spend Tiers” cell. The output should look like:
   
   

   Open

   

2. In the “KDS & Points Wk __” file, go to the “Data” tab in the “Deciles --->” section. Paste the Databricks “Guest Spent Tiers” output into columns B to F, in the bottom-most rows.

3. Go to the “KDS” tab in the “Deciles --->” section and drag the data down one row. Input the reporting week number into column C. You will see Column A update accordingly (e.g. if you put in “38” in column C, column A should update to 202438). You will also see Column D update with the reporting week’s Known Diner Sales (referencing from the most recent row in the “KD YoY” tab).

4. Return to the “Data” tab and drag down all the columns (with formulas) in black text to align with your inputted data from earlier, all data should populate automatically.

5. Go to the “Deciles Chart” tab and refresh the pivot table at the top left. Click into the “TH_FISCAL_WEEK” filter and select the current reporting week; ensure that when selected, columns for 2023 and 2024 both appear. This will populate the table below, which will reflect in the deciles graph on the right. Copy & paste graph into the Average Guest Spend Lift YoY by Guest Spend Tiers slide.

Loyalty Slides 4 & 5 - Average Guest Spend Lift YoY by Guest Spend Tiers

1. In the Weekly Execution 2024 notebook, go to “Comping Guests YoY”. In the “RRAMI YTD” and “ Known Diners YTD” cells, in the brackets preceded by “ AND TH_FISCAL_WEEK IN , add the number of the current reporting week. E.g., If it is currently week 14:

   WHERE TO_DATE(PARTITION_DATE_KEY, "yyyyMMdd") BETWEEN '2018-12-31' AND '$2_END_DATE' AND TH_FISCAL_WEEK IN (1,2,3,4,5,6,7,8,9,10,11,12,13) <-- ADD HERE AND IS_PASS_THROUGH = 0 AND COUNTRY_NM = 'CANADA'
   WHERE TO_DATE(PARTITION_DATE_KEY, "yyyyMMdd") BETWEEN '2018-12-31' AND '$2_END_DATE' AND TH_FISCAL_WEEK IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14) AND IS_PASS_THROUGH = 0 AND COUNTRY_NM = 'CANADA'

2. After updating the week, run both the cells.

3. In the “Guest Segments YTD” cell, update the portion of the WHERE statement for each year that pertains to PARTITION_DATE_KEY. If the current reporting week is week 38, in 2024, the last day of that week (in yyyyMMdd format) is 20240915. In 2020, the end of week 38 is 20200913; update for each year accordingly.

4. In the Weekly Execution 2024 notebook, go to “KDS$ by Guest Segments YoY”. Similarly to how you updated the “Guest Segments YTD” cell, update the dates in the “Guest List” cell. Run this cell.

5. In the “Comping, New, & Re-Engaged Sales”, “Churned Sales”, Total KDS $ YoY YTD”, and “Total SWS $ YoY YTD” cells, add the current reporting week into the fiscal week line, similarly to how you updated the RRAMI YTD and Known Diners YTD cells.

6. Open the “Comping Guest View Week __” file. In the “RRAMI for Segments” tab, paste in the outputs for RRAMI YTD, Known Diners YTD, SWS YTD, and KDS YTD for each year accordingly.

7. In the “KD Segments” tab,