Glossary of Digital & Loyalty Metrics
\uD83D\uDCD8 Metrics List
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’) | 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 | SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS
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
| 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 | SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS,
SUM(AMOUNT)/COUNT(DISTINCT TICKET_ID) AS CHEQUE
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
| 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 | SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT 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 IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2
| Usually look at this metric for a week, month, or year. |
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
| Included in known diner sales & digital sales. Sales of our internal app delivery platform. |
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. |
Loyalty Scans Sales made by known diners and includes eat-in, takeout & drive thru | 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 | 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 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 (
101789,
101517,
108175,
101493,
102093,
101846,
109331,
102784,
109403,
108485,
100525,
106376,
105060,
101712,
103498,
109951,
103637,
102534,
100410,
100062,
104505,
104853,
103482,
103389,
103227,
103217,
102614,
100788,
108397,
100112,
105789,
100806,
102549,
107228,
101475,
101657,
100674,
102011,
101129,
104420,
104764,
106305,
101000,
109241,
108358,
103413,
100027,
100526,
104213,
103704,
105217,
104402,
104271,
102946,
102376,
102229,
103233,
100940,
104962,
101491,
102118,
102732,
107384,
101803,
100446,
109041,
109757,
101917,
109878,
102169,
104840,
104013,
108507,
101886,
100136,
100514,
102017,
102974,
101900,
105085,
100467,
100490,
103690,
102467,
102305,
104925,
103167,
101382,
108487,
103648,
101281,
102110,
100045,
102398,
103850,
107647,
102753,
103124,
102224,
100678,
106882,
101865,
100652,
100503,
100693,
102040,
109449,
108102,
105389,
103695,
101666,
101076,
100529,
100376,
100856,
102606,
101582,
106547,
107608,
100325,
100324,
103340,
102417,
103129,
100544,
101365,
105340,
102074,
100096,
108167,
108166,
108088,
107336,
104443,
103384,
103255,
103208,
103356,
101450,
103202,
104284,
104856,
101241,
102331,
104393,
101820,
104126,
101333,
102556,
102972,
104471,
103568,
105792,
103021,
103143,
102833,
103086,
101991,
108399,
109334,
109955,
101462,
100582,
100124,
108500,
107576,
101335,
109291,
103677,
107569,
102635,
105237,
100160,
103077,
102622,
101818,
105917,
101600,
102308,
103029,
101992,
102603,
103947,
103137,
108172,
101022,
103050,
106865,
103323,
106310,
103169,
102399,
102018,
101595,
103407,
101655,
100101,
100506,
100093,
100863,
103625,
103279,
102891,
101999,
101543,
101476,
102562,
100024,
104852,
104370,
109430,
108502,
102852,
103644,
101623,
109405,
101837,
100200,
101568,
100885,
100104,
101628,
102991,
102773,
102877,
103548,
102821,
102103,
103411,
104887,
102196,
102315,
109027,
100998,
102630,
102394,
108402,
101650,
109042,
101873,
103755,
102925,
103478,
109407,
109397,
109396,
109338,
109286,
109246,
107582,
103584,
101967,
104970,
106478,
104212,
102212,
101508,
101368,
100800,
100077,
103950,
103698,
102132,
101851,
105363,
100670,
102157,
108115,
103001,
103955,
102892,
103130,
101769,
101357,
101904,
100118,
101864,
101686,
101174,
101924,
109711,
104289,
103886,
102710,
101689,
101442,
109404,
109780,
109447,
103351,
100640,
104813,
102387,
104275,
109444,
101584,
109448,
108480,
108395,
105840,
104651,
103132,
102679,
107653,
106874,
105763,
102646,
101849,
100229,
108430,
101985,
102150,
101871,
102719,
102060,
120293,
100965,
109436,
108137,
100776,
108118,
100387,
102041,
103754,
102409,
102032,
101602,
108518,
100769,
101788,
109288,
103159,
101954,
109446,
103294,
103549,
104391,
102129,
102175,
100015,
101528,
102171,
100967,
103073,
101308,
101552,
100738,
101535,
103702,
101870,
103591,
102274,
101159,
102593,
101200,
100255,
103659,
104966,
105920,
100489,
100568,
100588,
101339,
102001,
103267,
107343,
103634
)
GROUP BY
1
| Included in digital sales -- 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 | 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“) | 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 | SELECT
STOREDATE,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES
FROM PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE STOREDATE 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 | 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 | 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 | 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 | with TRXN_OFFER_CHALLENGES_REDEMPTIONS as (
WITH EXPLODED AS (
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
DATE_FORMAT(
CAST(
UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
),
'yyyyMMdd'
) AS WEEK_START_MAPPING,
CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
REGISTEREDACCOUNTID,
loyaltyCustomerId,
TRANSACTIONID,
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,
REST_TYP_NM,
OPS_DIV_NM
FROM
PRODRT.CURATED_TRANS_EVENTS_NEW -- 1. Challenge duration: 26/09 to 02/10
WHERE
DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'
AND '${myapplication.Offer_End_Date}'
AND COUNTRY_NM = 'CANADA'
AND REGISTEREDACCOUNTID IS NOT NULL
AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD"
)
SELECT
DISTINCT A.TH_FISCAL_YEAR,
A.TH_FISCAL_WEEK,
A.WEEK_START_MAPPING,
A.PERIOD_DT,
A.REGISTEREDACCOUNTID,
A.loyaltyCustomerId,
A.TRANSACTIONID,
A.REST_TYP_NM,
A.OPS_DIV_NM,
B.DESCRIPTION,
1 AS REDEMPTIONS
FROM
EXPLODED AS A
LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERID
WHERE
EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e'
),
points_issued as (
Select
*,
WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START,
regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid
From
prodrt.curated_points_events as points
where
partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'
AND '${myapplication.Offer_End_Date}'
and tag = 'PRODUCT_CHALLENGED_COMPLETED'
),
completed as(
Select
a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
a.WEEK_START_MAPPING as WEEK_START_MAPPING,
a.PERIOD_DT as PERIOD_DT,
DATE_FORMAT(
CAST(
UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
),
'yyyyMMdd'
) as transaction_dt,
a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,
a.loyaltyCustomerId as loyaltyCustomerId,
a.TRANSACTIONID as TRANSACTIONID,
a.REST_TYP_NM as REST_TYP_NM,
a.OPS_DIV_NM as OPS_DIV_NM,
a.DESCRIPTION as DESCRIPTION,
a.REDEMPTIONS as REDEMPTIONS,
b.transactionID as completion_transactionID,
b.pointsEarned as pointsEarned,
b.restaurant as restaurant,
b.partition_date_key as offer_completion_dt,
b.WEEK_START as offer_completion_week,
b.lid as lid
from
TRXN_OFFER_CHALLENGES_REDEMPTIONS a
left join points_issued b on a.loyaltyCustomerId = b.lid
and b.WEEK_START = a.TH_FISCAL_WEEK
)
Select
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
WEEK_START_MAPPING,
REGISTEREDACCOUNTID,
loyaltyCustomerId,
offer_completion_week,
count(
distinct(
case
when offer_completion_dt is not NULL
and transaction_dt <= offer_completion_dt then TRANSACTIONID
end
)
) as trnx_before_completion,
count(distinct(TRANSACTIONID)) as total_trnx,
count(
distinct (
case
when lid is not Null then lid
end
)
) as completed
from
completed
group by 1, 2, 3, 4, 5, 6
| Count of guests who completed a specific Offer Challenge |
Games NHL Hockey Challenge & Tims Word Challenge | 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 | WITH LOYALTY_TENDERS AS
(
WITH TENDERS AS
(
SELECT
REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
TICKET_ID
FROM loyalty.tlog.tlog_sale_ticket_tenders
WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
AND LEFT(REST_NO,2) = 10
),
LOYALTY AS
(
SELECT
TICKET_ID AS TICKET_ID
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
)
SELECT
A.*
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', 'ROUNDED CASH', 'ROUNDED COMPTANT') 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', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS', 'DISCOVER', 'M/C', 'DISCOVER CARD', 'DIGITAL AMEX', 'DIGITAL DISCOVER', 'DIGITAL MASTER CARD', 'DIGITAL MASTERCARD', 'DIGITAL AMERICAN EXPRESS', 'DIGITAL VISA', 'DO VISA', 'DO MASTERCARD') THEN 'CREDIT'
WHEN UPPER(TENDER_NAME) IN ('TIM CARD', 'DIGITAL TIM CARD', 'CARTE TIM', 'MOBILE TIM CARD', 'DIGITAL CARTE TIM', 'TIM CARTE', 'TIMS GIFT CARD', 'DIGITAL TIMS GIFT CARD', 'CARTE-CADEAU TIM', 'DIGITAL CARTE-CADEAU TIM') THEN 'TIMCARD'
WHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP'
WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER'
WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'
WHEN UPPER(TENDER_NAME) IN ('SCAN AND PAY VISA', 'SCAN AND PAY MASTERCARD', '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', 'SCAN AND PAY TIMS GIFT CARD') 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', 'TVH', 'TVH1', 'VAF', 'H.S.T.') THEN 'TAX'
WHEN ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'
ELSE 'OTHER'
END AS TENDER,
COUNT(DISTINCT TICKET_ID) AS TRXNS
FROM LOYALTY_TENDERS
GROUP BY 1
| Total sales $ and ticket count organized by tender type (debit, credit, cash, Tims Card, tax) for a given restaurant |
Discounting | With base as( select period_dt, state_nm, detail_type, service_mode_cd, ticket_details_key, ticket_details_pos_no, a.ticket_details_pos_nm, B.discount_key, B.discount_cd, B.discount_nm, C.coupon_key, C.coupon_cd, C.coupon_offr_nm, D.Category, SUM(amount) as amt, count(distinct(ticket_id)) as TRXNS from STG.DERIVED_MASTER_TABLE_NEW a left join loyalty.tlog.dim_discount b on a.ticket_details_key = b.discount_key left join tlog.dim_coupon c on a.ticket_details_key = c.coupon_key left join loyalty.analytics.clearview_mapping_discount_types d on a.ticket_details_pos_nm = d.ticket_details_pos_nm where amount < 0 and partition_date_key between <Start_Date> and <End_Date> AND COUNTRY_NM = 'CANADA' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14 ) select last_Day(period_dt) as month_dt, detail_type, ticket_details_pos_nm, discount_nm, coupon_offr_nm, Category, sum(amt), sum(TRXNS) As trxns from base group by 1, 2, 3, 4, 5, 6 | loyalty.analytics.clearview_mapping_discount_types This table is manually mapped by grouping ticket_detail_POS_nm into categories Combo Discount, Combo Discount (BG Bundle), Tims Rewards, Targeted Offers, Campaign, In-restaurant, Other, Settlement, and Uncategorized This mapping was last update in March 2024. and any unmapped fields would show up as null and would be considered as “Other” |