Team

Reviewer

Links

Excel File:

2025 Earnings.xlsx

Databricks Notebook:
https://timhortons-dev.cloud.databricks.com/editor/notebooks/3855490699228903?o=2205369574251959#command/3855490699228987

Metrics List

Metric and Definition

Query

Comments

Mobile Order & Pay (“MO&P”)

Code Block

language	sql

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW 
WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('MOBILE ORDER DRIVE THRU', 'MOBILE ORDER EAT IN', 'MOBILE ORDER TAKE OUT')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2

Included in known diner sales & digital sales.

White Label Delivery (“WL”)

Delivery sales initiated from the TH mobile app

Code Block

language	sql

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW 
WHERE PARTITION_DATE_KEY BETWEEN '$START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('WHITE LABEL DELIVERY','DELIVERY')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2

3P Delivery

Delivery sales initiated and fulfilled by third-party delivery providers, such as UberEats, SkipTheDishes and DoorDash

Code Block

language	sql

SELECT 
A.PARTITION_DATE_KEY,
SUM(A.AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW A 
INNER JOIN TLOG.TLOG_SALE_TICKET_TENDERS B 
ON A.TICKET_ID = B.TICKET_ID
WHERE A.PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND TENDER_NAME IN ("UBER EATS CREDIT","Credit Uber Eats","Uber Eats Credit","CRÉDIT UBER EATS","SKIP CREDIT","Skip Credit","CRÉDIT de DoorDash","CRÉDIT DE DOORDASH","DoorDash Credit","DOORDASH CREDIT","Crédit de DoorDash","Doordash")
GROUP BY 1

Kiosk

Sales via Kiosk; can be split into registered and un-registered Kiosk sales using left(registered_account_id,7) = ‘us-east’

Code Block

language	sql

SELECT
    LAST_DAY(PERIOD_DT) AS MONTH,
    SUM(AMOUNT) AS KIOSK_SALES,
    COUNT(DISTINCT TICKET_ID) AS KIOSK_TRXNS,
    COUNT(DISTINCT LOYALTY_CUSTOMER_ID) AS KIOSK_GUESTS
  FROM
    STG.DERIVED_MASTER_TABLE_NEW
  WHERE
    PERIOD_DT BETWEEN '$Start_Date'
    AND '$End_Date'
    AND IS_PASS_THROUGH = 0
    AND COUNTRY_NM = 'CANADA'
    AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT', 'KIOSK EATIN')
    AND REST_NO IN (
    <list of restaurants with KIOSKS>
    )
  GROUP BY
    1

Restaurant list as of Jan 2025

Code Block

language	sql

('101491',	'104962',	'104013',	'104840',	'102169',	'100024',	'108480',	'103132',	'102679',	'109448',	'105840',	'108395',	'102011',	'101985',	'104651',	'100490',	'109405',	'103411',	'102132',	'102074',	'105340',	'104852',	'104370',	'102467',	'102877',	'102175',	'100863',	'107228',	'102549',	'100806',	'103351',	'102305',	'103690',	'100588',	'109955',	'109447',	'104212',	'102212',	'109780',	'100640',	'101335',	'103143',	'103389',	'102017',	'102614',	'104853',	'103217',	'100788',	'104970',	'103584',	'106478',	'102387',	'102892',	'104275',	'103021',	'102773',	'109397',	'109286',	'109407',	'109396',	'109338',	'105792',	'103482',	'103227',	'108397',	'104420',	'100503',	'101865',	'100652',	'106882',	'101864',	'100104',	'100045',	'103340',	'100324',	'101368',	'100800',	'100077',	'102635',	'108402',	'100856',	'101954',	'100738',	'102606',	'101200',	'108487',	'100526',	'104764',	'103029',	'102118',	'101623',	'101886',	'100582',	'101849',	'101900',	'108507',	'109878',	'109041',	'107569',	'103677',	'109027',	'108118',	'100998',	'100776',	'100387',	'108137',	'100965',	'101686',	'101129',	'102534',	'100410',	'103086',	'102274',	'102991',	'104925',	'106865',	'101820',	'108500',	'107576',	'100096',	'103850',	'103950',	'103698',	'101904',	'100118',	'109757',	'101851',	'101462',	'103001',	'108115',	'102196',	'102150',	'103625',	'103279',	'102315',	'101846',	'109446',	'101917',	'107647',	'103548',	'102821',	'102833',	'109331',	'103648',	'101281',	'102103',	'108502',	'106874',	'102974',	'102852',	'102646',	'100229',	'109404',	'104289',	'103886',	'102710',	'109711',	'101689',	'101442',	'101655',	'102925',	'105763',	'103634',	'103077',	'101382',	'107653',	'104813',	'105389',	'102040',	'101991',	'100529',	'101666',	'101076',	'108102',	'103695',	'100376',	'109449',	'108399',	'100200',	'104471',	'103549',	'109444',	'101584',	'101365',	'101022',	'100177',	'103702',	'101528',	'101967',	'101493',	'101552',	'101535',	'103478',	'101870',	'104271',	'109436',	'105217',	'109951',	'101543',	'101999',	'101476',	'106547',	'102891',	'102376',	'102478',	'101788',	'102562',	'101712',	'102398',	'101333',	'105060',	'102603',	'106376',	'100885',	'100693',	'101475',	'108518',	'101657',	'103591',	'100255',	'103498',	'104966',	'103202',	'100674',	'101650',	'103124',	'102224',	'101837',	'101873',	'109042',	'103955',	'103130',	'101769',	'101357',	'104213',	'103637',	'103704',	'102946',	'103129',	'100544',	'104393',	'103659',	'100506',	'100093',	'106305',	'100027',	'103189',	'101600',	'101556',	'100112',	'105789',	'103755',	'101789',	'101818',	'101517',	'105917',	'102732',	'102229',	'104402',	'102001',	'102308',	'101000',	'100568',	'103159',	'102972',	'102556',	'109288',	'102331',	'100489',	'100940',	'105085',	'100136',	'100514',	'100101',	'102129',	'101174',	'102630',	'100467',	'104887',	'102093',	'120293',	'103073',	'100678',	'100769',	'109430',	'100062',	'102110',	'107608',	'100325',	'101450',	'108167',	'103267',	'103255',	'103208',	'100446',	'107384',	'101803',	'108430',	'108166',	'103356',	'108175',	'104443',	'103384',	'108088',	'106310',	'103169',	'102018',	'102409',	'102032',	'102399',	'101628',	'101582',	'103137',	'101924',	'108172',	'105237',	'100160',	'108358',	'103413',	'109241',	'101595',	'103947',	'103323',	'100525',	'109403',	'108485',	'109246',	'102784',	'104126',	'105363',	'107582',	'101871',	'103644',	'102719',	'102060',	'103407',	'100537',	'106996',	'104779',	'121566',	'101080',	'103015',	'104971',	'101518',	'101752',	'101888',	'101889',	'103869',	'103870',	'103871',	'103872',	'103873',	'103874',	'103875',	'103978',	'103979',	'103980',	'103982',	'104100',	'104101',	'104102',	'104130',	'104281',	'104444',	'104459',	'104781',	'104782',	'100621',	'100884',	'104779',	'121566',	'100197',	'121144',	'100537',	'120190',	'102402',	'121546',	'121782',	'106996',	'100537')

Catering

Catering sales

Code Block

language	sql

SELECT
    LAST_DAY(PERIOD_DT) AS MONTH,
    SUM(AMOUNT) AS CATERING_SALES,
    COUNT(DISTINCT TICKET_ID) AS CATERING_TRXNS,
    COUNT(DISTINCT LOYALTY_CUSTOMER_ID) AS CATERING_GUESTS
  FROM
    STG.DERIVED_MASTER_TABLE_NEW
  WHERE
    PERIOD_DT BETWEEN '$Start_Date'
    AND '$End_Date'
    AND IS_PASS_THROUGH = 0
    AND COUNTRY_NM = 'CANADA'
    AND SERVICE_MODE_CD = 'CATERING'
    
  GROUP BY
    1

Includes all types of catering

Loyalty In-Store

Sales from regular loyalty scans in-restaurant. Excludes MO&P, WL delivery, catering, and kiosk

Code Block

language	sql

SELECT
    LAST_DAY(PERIOD_DT) AS MONTH,
    SUM(AMOUNT) AS LOYALTY_IN_STORE_SALES,
    COUNT(DISTINCT TICKET_ID) AS LOYALTY_IN_STORE_TRXNS,
    COUNT(DISTINCT LOYALTY_CUSTOMER_ID) AS LOYALTY_IN_STORE_GUESTS
  FROM
    STG.DERIVED_MASTER_TABLE_NEW
  WHERE
    PERIOD_DT BETWEEN '$Start_Date'
    AND '$End_Date'
    AND IS_PASS_THROUGH = 0
    AND COUNTRY_NM = 'CANADA'
    AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
    AND SERVICE_MODE_CD NOT IN ('MOBILE ORDER TAKE OUT', 'MOBILE ORDER EAT IN', 'MOBILE ORDER DRIVE THRU', 'DELIVERY', 'WHITE LABEL DELIVERY', 'KIOSK', 'KIOSK TAKEOUT', 'CATERING', 'THIRD PARTY DELIVERY')
    
  GROUP BY
    1

Mobile Order & Pay (“MO&P”) Weekly Offers

Total Mobile Order & Pay ticket sales with a weekly offer used on that transaction

Code Block

language	sql

SELECT 
LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
ROUND(SUM(ITEMTOTALPRICE),2) AS MOP_OFFER_SALES

FROM loyalty.PRODRT.CURATED_TRANS_EVENTS_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND MEDIATYPE IN ("MA","AW","GP")
AND DININGTYPE <> "WL" 
AND TRANSACTIONID IN (
                      WITH EXPLODED AS (
                                        SELECT 
                                        *,
                                        EXPLODE(APPLIEDOFFERS) AS EXPLODED

                                        FROM LOYALTY.PRODRT.CURATED_TRANS_EVENTS_NEW

                                        WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
                                        AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
                                        AND APPLIEDOFFERS IS NOT NULL
                                        AND COUNTRY_NM = 'CANADA'
                                        AND IS_PASS_THROUGH = 0
                                        )
                      SELECT 
                      DISTINCT TRANSACTIONID

                      FROM EXPLODED 

                      WHERE EXPLODED IN (SELECT DISTINCT OFFERID FROM loyalty.DYDB.WEEKLYOFFERS WHERE OFFERID NOT IN ('4292e4a3-bd68-43fc-bdb4-73fa30f1a6c9',
                                        'c4c5a3d0-27ec-4527-9fe4-66d6ae2fce20'))
                      )

GROUP BY 1

White Label Delivery (“WL”) Weekly Offers

Total White Label Delivery ticket sales with a weekly offer used on that transaction

Code Block

language	sql

SELECT 
LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
ROUND(SUM(ITEMTOTALPRICE),2) AS WL_OFFER_SALES

FROM loyalty.PRODRT.CURATED_TRANS_EVENTS_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND MEDIATYPE IN ("MA","AW","GP")
AND DININGTYPE = "WL" 
AND TRANSACTIONID IN (
                      WITH EXPLODED AS (
                                        SELECT 
                                        *,
                                        EXPLODE(APPLIEDOFFERS) AS EXPLODED

                                        FROM LOYALTY.PRODRT.CURATED_TRANS_EVENTS_NEW

                                        WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
                                        AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
                                        AND APPLIEDOFFERS IS NOT NULL
                                        AND COUNTRY_NM = 'CANADA'
                                        AND IS_PASS_THROUGH = 0
                                        )
                      SELECT 
                      DISTINCT TRANSACTIONID

                      FROM EXPLODED 

                      WHERE EXPLODED IN (SELECT DISTINCT OFFERID FROM loyalty.DYDB.WEEKLYOFFERS WHERE OFFERID NOT IN ('4292e4a3-bd68-43fc-bdb4-73fa30f1a6c9',
                                        'c4c5a3d0-27ec-4527-9fe4-66d6ae2fce20'))
                      )

GROUP BY 1

Loyalty In-Store Weekly Offers

Total Loyalty In-Store ticket sales with a weekly offer used on that transaction

Code Block

language	sql

SELECT 
LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
ROUND(SUM(ITEMTOTALPRICE),2) AS WL_OFFER_SALES

FROM loyalty.PRODRT.CURATED_TRANS_EVENTS_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND MEDIATYPE NOT IN ("MA","AW","GP")
AND TRANSACTIONID IN (
                      WITH EXPLODED AS (
                                        SELECT 
                                        *,
                                        EXPLODE(APPLIEDOFFERS) AS EXPLODED

                                        FROM LOYALTY.PRODRT.CURATED_TRANS_EVENTS_NEW

                                        WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
                                        AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
                                        AND APPLIEDOFFERS IS NOT NULL
                                        AND COUNTRY_NM = 'CANADA'
                                        AND IS_PASS_THROUGH = 0
                                        )
                      SELECT 
                      DISTINCT TRANSACTIONID

                      FROM EXPLODED 

                      WHERE EXPLODED IN (SELECT DISTINCT OFFERID FROM loyalty.DYDB.WEEKLYOFFERS WHERE OFFERID NOT IN ('4292e4a3-bd68-43fc-bdb4-73fa30f1a6c9',
                                        'c4c5a3d0-27ec-4527-9fe4-66d6ae2fce20'))
                      )

GROUP BY 1

Total Weekly Offers

Sum of MO&P weekly offers + WL weekly offers + Loyalty In-Store weekly offers

Code Block

language	sql

SELECT 
LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
ROUND(SUM(ITEMTOTALPRICE),2) AS WL_OFFER_SALES

FROM loyalty.PRODRT.CURATED_TRANS_EVENTS_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND TRANSACTIONID IN (
                      WITH EXPLODED AS (
                                        SELECT 
                                        *,
                                        EXPLODE(APPLIEDOFFERS) AS EXPLODED

                                        FROM LOYALTY.PRODRT.CURATED_TRANS_EVENTS_NEW

                                        WHERE PARTITION_DATE_KEY BETWEEN 20241001 AND 20241231
                                        AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
                                        AND APPLIEDOFFERS IS NOT NULL
                                        AND COUNTRY_NM = 'CANADA'
                                        AND IS_PASS_THROUGH = 0
                                        )
                      SELECT 
                      DISTINCT TRANSACTIONID

                      FROM EXPLODED 

                      WHERE EXPLODED IN (SELECT DISTINCT OFFERID FROM loyalty.DYDB.WEEKLYOFFERS WHERE OFFERID NOT IN ('4292e4a3-bd68-43fc-bdb4-73fa30f1a6c9',
                                        'c4c5a3d0-27ec-4527-9fe4-66d6ae2fce20'))
                      )

GROUP BY 1

Sum of MO&P weekly offers + WL weekly offers + Loyalty In-Store weekly offers

Version	Old Version 4	New Version Current
Changes made by	Adrian Monk	Adrian Monk
Saved on	Jan 09, 2025	Jan 09, 2025

Versions Compared

Key

Metrics List

Content Comparison

Versions Compared

Key

Metrics List