Metric and Definition | Query | Comments and Business Use |
---|
Known Diner Sales (“KDS”) All sales that are made by recognizable guests, i.e. guests that have registered through the app (identified by their registered account id beginning with ‘us-east’) | Code Block |
---|
SELECT TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS
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 LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2 |
| This metric is used as a baseline to see loyalty sales penetration against system wide sales. |
System Wide Sales (“SWS”) Total sales across TH | Code Block |
---|
SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS
FROM STG.DERIVED_MASTER_TABLE_NEW T1
WHERE
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0
AND T1.COUNTRY_NM = 'CANADA'
GROUP BY 1,2,3 |
| Note: Some forecasts may use Hyperion sales interchangeable in lieu of SWS sourced from STG.DERIVED_MASTER_TABLE_NEW. |
Cheque Average sales value ($) of an individual transaction | Code Block |
---|
SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS,
SUM(T1.AMOUNT)/COUNT(DISTINCT T1.TICKET_ID) AS CHEQUE
FROM STG.DERIVED_MASTER_TABLE_NEW T1
WHERE
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0
AND T1.COUNTRY_NM = 'CANADA'
GROUP BY 1,2,3 |
| Can calculate this on a system level or down to an individual guest level. Can filter on loyalty or non-loyalty. |
Frequency Average loyalty guest visits in a given time period | Code Block |
---|
SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.TICKET_ID)/COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS FREQUENCY
FROM STG.DERIVED_MASTER_TABLE_NEW T1
WHERE
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0
AND T1.COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2,3 |
| Usually look at this metric for a week, month, or year. |
White Label Delivery (“WL”) Delivery sales initiated from the TH mobile app | Code Block |
---|
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 |
| Included in known diner sales & digital sales. Sales of our internal app delivery platform. |
Mobile Order & Pay (“MO&P”) | Code Block |
---|
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 |
| Included in known diner sales & digital sales. |
Loyalty Scans Sales made by known diners and includes eat-in, takeout & drive thru | Code Block |
---|
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 ('TAKEOUT', 'EATIN','DRIVETHRU')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2 |
| Included in known diner sales & digital sales. |
3P Delivery Delivery sales initiated and fulfilled by third-party delivery providers, such as UberEats, SkipTheDishes and DoorDash | Code Block |
---|
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 |
| Included in digital sales |
Kiosk Sales via Kiosk; can be split into registered and un-registered Kiosk sales using | Code Block |
---|
SELECT
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT', 'KIOSK EATIN')
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = "CANADA"
GROUP BY 1 |
| Included in digital sales Code Block |
---|
-- Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) = 'us-east'
-- Un-Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) != 'us-east' |
|
Catering Catering sales | Code Block |
---|
SELECT
PARTITION_DATE_KEY,
SUM(ITEMTOTALPRICE)AS SALES,
FROM PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND DININGTYPE = 'CT'
GROUP BY 1 |
| Included in digital sales |
Restaurants Reporting Any Menu Item (“RRAMI“) | Code Block |
---|
WITH RESTAURANTS AS (
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
PARTITION_DATE_KEY,
COUNT(DISTINCT REST_NO) AS RESTAURANTS
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'
GROUP BY 1,2,3
ORDER BY 1)
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(RESTAURANTS) AS RRAMI
FROM RESTAURANTS
GROUP BY 1,2 |
| |
Scan & Pay (“S&P”) Transactions where Scan & Pay feature was used | Code Block |
---|
SELECT
PARTITION_DATE_KEY,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES
FROM PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM IN ('CANADA')
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND SCANANDPAY IS TRUE
GROUP BY 1 |
| Often tracked as a % of registered transactions Scan & Pay Penetration Formula: (Scan & Pay Transactions) / (Registered Transactions) |
Loyalty Redemptions Products that were redeemed using loyalty points | Code Block |
---|
CACHE TABLE DAILY_REDEMPTIONS AS
WITH TRANSACTIONS_WITH_OFFER_ID AS (
SELECT
LEFT(PERIOD_DT, 10) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID,
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
FROM PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE DATE_KEY >= '20230606'
AND COALESCE(REGISTEREDACCOUNTID,'') IS NOT NULL
AND APPLIEDOFFERS IS NOT NULL
AND COUNTRY_NM = 'CANADA')
SELECT
PERIOD_DT,
COUNT(DISTINCT TRANSACTIONID) AS TRXNS
FROM TRANSACTIONS_WITH_OFFER_ID AS A
INNER JOIN (SELECT DISTINCT OFFERID FROM DYDB.WEEKLYOFFERS
UNION ALL
SELECT DISTINCT OFFERID
FROM DYDB.OFFERS
WHERE CONTAINS(description, 'LOYALTY')
AND DESCRIPTION LIKE 'CA L%'
AND DESCRIPTION NOT IN ('CA LR Registered Default (same as L1)-LOYALTY', 'CA LU Unregistered (same as 102) ONE-TIME-LOYALTY') ) B
ON A.EXPLODED_OFFERS = B.OFFERID
LEFT JOIN DYDB.OFFERS AS C
ON A.EXPLODED_OFFERS=C.OFFERID
WHERE EXPLODED_OFFERS IS NOT NULL
AND C.OFFERID IS NOT NULL
GROUP BY 1 |
| Used for tracking how many free items we’re giving away and the value of them. Often viewed as a percentage of SWS or KDS. Loyalty drag formula = $ value of redeemed items / SWS $ |
Monthly Active Users (“MAU”) Number of guests that visited the app each month | Code Block |
---|
| WITH EVENTS AS (
SELECT DATE
, _TIMHORTONS.INTERACTION.ELEMENT.NAME
, _TIMHORTONS.LOYALTY.ID AS GUEST_ID
FROM loyalty.events.adobe_app_events
WHERE EVENTTYPE IN ('app_open','app_launch')
AND _TIMHORTONS.PLATFORM IN ('app')
AND LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND DATE <= CURRENT_DATE )
SELECT LAST_DAY(EVENTS.DATE) AS DTE
, COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER
FROM EVENTS
GROUP BY 1
ORDER BY 1 DESC |
| Used to track the unique number of guests who opened or launched the app per month. Limitation of the dataset is: (1) data only exists from Nov 1st, 2023, and (2) Prior to mm/dd/yyyy (TBD), dataset did not contain guests using app versions prior to 7.1.187. |
Digital Sales | Pulled using the individual queries listed above. | Consists of WL, MO&P, Loyalty Scans, 3P Delivery, Kiosk & Catering. |
Weekly Offer Redemptions | Code Block |
---|
WITH REDEMPTION_SUMMARY AS (
WITH OFFER_REDEMPTION_TRXNS AS (
WITH TRANSACTIONS_WITH_OFFER_ID AS (
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,
DATE_FORMAT(
CAST(
UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
),
'yyyyMMdd'
) AS WEEK_START,
CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID,
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
FROM
PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE
PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
AND REGISTEREDACCOUNTID LIKE 'us-east%'
AND COUNTRY_NM = 'CANADA'
)
SELECT
DISTINCT A.TH_FISCAL_YEAR,
A.TH_FISCAL_WEEK,
A.WEEK_START_DT,
A.PERIOD_DT,
A.REGISTEREDACCOUNTID,
A.TRANSACTIONID,
C.NAME,
C.DESCRIPTION,
A.EXPLODED_OFFERS,
1 AS VOLUME
FROM
TRANSACTIONS_WITH_OFFER_ID A
INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
)
SELECT
WEEK_START_DT,
A.DESCRIPTION AS OFFER_DESCRIPTION,
A.EXPLODED_OFFERS AS OFFERID,
SUM(VOLUME) AS REDEMPTION_VOLUME
FROM
OFFER_REDEMPTION_TRXNS A
GROUP BY
1,
2,
3
)
SELECT
a.WEEK_START_DT AS WKDT,
OFFER_DESCRIPTION,
OFFERID,
SUM(REDEMPTION_VOLUME)
FROM
REDEMPTION_SUMMARY A
GROUP BY 1,2,3 |
| Used to track the volume of redemptions for each offer in a given week. |
Offer Challenge | Code Block |
---|
WITH OFFER_REDEMPTION_TRXNS AS (
WITH TRANSACTIONS_WITH_OFFER_ID AS (
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,
DATE_FORMAT(
CAST(
UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
),
'yyyyMMdd'
) AS WEEK_START,
CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID,
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
FROM
PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE
partition_date_key >= <DATE>
AND REGISTEREDACCOUNTID LIKE 'us-east%'
AND COUNTRY_NM = 'CANADA'
)
SELECT
DISTINCT A.TH_FISCAL_YEAR,
A.TH_FISCAL_WEEK,
A.WEEK_START_DT,
A.PERIOD_DT,
A.REGISTEREDACCOUNTID,
A.TRANSACTIONID,
C.NAME,
C.DESCRIPTION,
a.EXPLODED_OFFERS,
1 AS VOLUME
FROM
TRANSACTIONS_WITH_OFFER_ID A
-- INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
<this is where you select the offers you want>
LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
)
SELECT
WEEK_START_DT,
a.DESCRIPTION as Offer_Description,
a.EXPLODED_OFFERS as offerid,
SUM(VOLUME) AS REDEMPTION_VOLUME,
COUNT(DISTINCT REGISTEREDACCOUNTID) AS GUESTS
FROM
OFFER_REDEMPTION_TRXNS a
GROUP BY
1,
2,
3 |
| Count of guests who completed a specific Offer Challenge |
Games NHL Hockey Challenge & Tims Word Challenge | Code Block |
---|
| SELECT YEAR(TIMESTAMP) AS YR
, MONTH(TIMESTAMP) AS MTH
, COUNT(DISTINCT GUESTS) AS GUESTS FROM (
-- WORD CHALLENEGE PLAYERS
SELECT DISTINCT TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM loyalty.events.adobe_app_events
WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND _TIMHORTONS.PLATFORM IN ('app')
AND LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND DATE >= DATE '2023-11-01'
UNION
-- HOCKEY PLAYERS
SELECT DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID AS GUESTS
FROM loyalty.events.adobe_app_events
WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
AND _TIMHORTONS.PLATFORM IN ('app')
AND LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND DATE >= DATE '2023-11-01'
)
GROUP BY 1,2
ORDER BY 1 DESC, 2 |
| Count of guests who played Games (NHL Hockey Challenge and Tims Word Challenge). Limitation: Data only available from Nov 1, 2023. |
Total Sales and Ticket Count by Tender Type | Code Block |
---|
WITH REST_TENDERS AS (
WITH TENDERS AS (
SELECT
REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
TICKET_ID,
REST_NO = "Restaurant Number"
FROM
loyalty.tlog.tlog_sale_ticket_tenders
WHERE
PARTITION_DATE_KEY BETWEEN "START DATE" AND "END DATE"
AND REST_NO = "Restaurant Number"
),
LOYALTY AS (
SELECT
REST_NO,
TICKET_ID AS TICKET_ID,
AMOUNT AS AMOUNT
FROM
STG.DERIVED_MASTER_TABLE_NEW
WHERE
PARTITION_DATE_KEY BETWEEN "START DATE" AND "END DATE"
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND REST_NO = "Restaurant Number"
SELECT
B.*,
TENDER_NAME,
B.REST_NO
FROM
TENDERS A
INNER JOIN LOYALTY B ON A.TICKET_ID = B.TICKET_ID
)
SELECT
CASE
WHEN UPPER(TENDER_NAME) IN (
'CASH',
'COMPTANT',
'EFECTIVO',
'US CASH',
'CANADIAN CASH',
'CDN CASH'
) THEN 'Cash'
WHEN UPPER(TENDER_NAME) IN (
'DEBIT CARD',
'DEBIT',
'CARTE DEBIT',
'DEBITO',
'DÉBIT',
'DO DEBIT'
) THEN 'Debit'
WHEN UPPER(TENDER_NAME) IN ('VISA', 'DIGITAL VISA', 'DO VISA') THEN 'Visa'
WHEN UPPER(TENDER_NAME) IN (
'MASTERCARD',
'MASTER CARD',
'M/C',
'DIGITAL MASTER CARD',
'DIGITAL MASTERCARD',
'DO MASTERCARD'
) THEN 'Mastercard'
WHEN UPPER(TENDER_NAME) IN (
'AMEX',
'AMERICAN EXPRESS',
'DIGITAL AMEX',
'DIGITAL AMERICAN EXPRESS'
) THEN 'AMEX'
WHEN UPPER(TENDER_NAME) IN (
'TIM CARD',
'DIGITAL TIM CARD',
'CARTE TIM',
'MOBILE TIM CARD',
'DIGITAL CARTE TIM',
'TIM CARTE'
) THEN 'Tim Card'
WHEN UPPER(TENDER_NAME) IN (
'SCAN AND PAY TIMCARD',
'SCAN AND PAY AMEX',
'SCAN AND PAY DISCOVER',
'SCAN AND PAY TIM CARD',
'NUMERISEZ ET PAYEZ – VISA',
'SCANTOPAY',
'NUMERISEZ ET PAYEZ – MASTERCARD',
'NUMERISEZ ET PAYEZ – CARTE TIM',
'NUMÉRISEZ ET PAYEZ – VISA',
'NUMÉRISEZ ET PAYEZ – MASTERCARD',
'NUMÉRISEZ ET PAYEZ – CARTE TIM',
'NUMERISEZ ET PAYEZ – AMEX',
'NUMÉRISEZ ET PAYEZ – AMEX'
) THEN 'SCANANDPAY'
WHEN UPPER(TENDER_NAME) IN (
'HST',
'HST1',
'TVQ',
'TPS',
'GST',
'TAX',
'PST',
'HST TAXABLE SALES',
'SALES TAX' 'HST 1',
'H.S.T.1',
'H.S.T',
'HST 13% TAXABLE SALES',
'GST TAXABLE SALES',
'TAX 1',
'HST 1',
'GST# 75696 6891 RT0001',
'HST # 897258141',
'HST5%',
'HST8%',
'QST',
'SALES TAX',
'MEAL PLAN CARD - PREPAID TAX',
'CARTE PLAN REPAS - TAX PREPAYEE',
'GST # 121071781RT0001'
) THEN 'TAX'
WHEN (
(UPPER(TENDER_NAME) LIKE '%ROUND%')
OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')
) THEN 'ROUND UP'
ELSE 'OTHER'
END AS TENDER,
A.REST_NO,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SALES
FROM
REST_TENDERS A
GROUP BY
1,
2 |
| Total sales $ and ticket count organized by tender type (debit, credit, cash, Tims Card, tax) for a given restaurant |