Team | |
|---|---|
Reviewer | |
Links | Excel File: Databricks Notebook: |
Metrics List
Metric and Definition | Query | Comments | |
|---|---|---|---|
Mobile Order & Pay (“MO&P”) | 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 | 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 | 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’ | 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 ('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 | 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 | 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 | 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 | 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 | 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 | 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 |