...
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’) |
| This metric is used as a baseline to see loyalty sales penetration against system wide sales. | ||||||||||||||||||||||||||||||||
System Wide Sales (“SWS”) Total sales across TH |
| 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 |
| 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 |
| Usually look at this metric for a week, month, or year. | ||||||||||||||||||||||||||||||||
White Label Delivery (“WL”) Delivery sales initiated from the TH mobile app |
| Included in known diner sales & digital sales. Sales of our internal app delivery platform. | ||||||||||||||||||||||||||||||||
Mobile Order & Pay (“MO&P”) |
| Included in known diner sales & digital sales. | ||||||||||||||||||||||||||||||||
Loyalty Scans Sales made by known diners and includes eat-in, takeout & drive thru |
| 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 |
| 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’ |
| Included in digital sales
| Included in digital sales
| Catering Catering sales |
| |||||||||||||||||||||||||||||
Catering Catering sales |
| Included in digital sales | ||||||||||||||||||||||||||||||||
Restaurants Reporting Any Menu Item (“RRAMI“) |
| |||||||||||||||||||||||||||||||||
Scan & Pay (“S&P”) Transactions where Scan & Pay feature was used |
| 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 |
| 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
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
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 DTEPERIOD_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 _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
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, COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER FROM TRANSACTIONID, 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 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 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, TH_FISCAL_WEEKb.partition_date_key as offer_completion_dt, CAST(b.WEEK_START_DT AS DATE) AS WEEK_START_DT, as offer_completion_week, b.lid as lid DATE_FORMAT( from CAST( TRXN_OFFER_CHALLENGES_REDEMPTIONS a UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP left join points_issued b on a.loyaltyCustomerId = b.lid ), and b.WEEK_START = a.TH_FISCAL_WEEK ) 'yyyyMMdd' Select ) AS WEEK_START TH_FISCAL_YEAR, TH_FISCAL_WEEK, CAST(PERIOD_DT AS DATE) AS PERIODWEEK_START_DTMAPPING, REGISTEREDACCOUNTID, REGISTEREDACCOUNTID, loyaltyCustomerId, TRANSACTIONIDoffer_completion_week, count( EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS distinct( FROM PRODRT.CURATED_TRANS_EVENTS_NEWcase WHERE when offer_completion_dt is PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'not NULL and AND REGISTEREDACCOUNTID LIKE 'us-east%' transaction_dt <= offer_completion_dt then TRANSACTIONID AND COUNTRY_NM = 'CANADA'end ) ) SELECT ) DISTINCTas A.THtrnx_FISCALbefore_YEARcompletion, A.TH_FISCAL_WEEK,count(distinct(TRANSACTIONID)) as total_trnx, count( A.WEEK_START_DT, A.PERIOD_DT, distinct ( A.REGISTEREDACCOUNTID, case A.TRANSACTIONID, C.NAME, when lid is C.DESCRIPTION,not Null then lid A.EXPLODED_OFFERS, 1end AS VOLUME FROM ) TRANSACTIONS_WITH_OFFER_ID A ) as completed INNER JOINfrom DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID completed group LEFTby JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID ) SELECT1, 2, 3, 4, 5, 6 |
Count of guests who completed a specific Offer Challenge
Games
NHL Hockey Challenge & Tims Word Challenge
Code Block | ||
---|---|---|
| ||
SELECT YEAR(TIMESTAMP) AS YR WEEK_START_DT, A.DESCRIPTION, MONTH(TIMESTAMP) AS OFFER_DESCRIPTION,MTH A.EXPLODED_OFFERS AS OFFERID, , SUMCOUNT(VOLUMEDISTINCT GUESTS) AS REDEMPTION_VOLUME GUESTS FROM ( -- WORD CHALLENEGE OFFER_REDEMPTION_TRXNSPLAYERS A SELECT GROUP BY DISTINCT 1TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS FROM 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 TRXN_OFFER_CHALLENGES_REDEMPTIONS as (loyalty.events.adobe_app_events WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge') AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play' AND _TIMHORTONS.PLATFORM WITH EXPLODED AS (IN ('app') AND LEFT(_TIMHORTONS.LOYALTY.ID,7) = SELECT 'us-east' AND TH_FISCAL_YEAR, TH_FISCAL_WEEK,_TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND DATE >= DATE DATE_FORMAT('2023-11-01' UNION -- HOCKEY PLAYERS SELECT DISTINCT CAST( TIMESTAMP,_TIMHORTONS.LOYALTY.ID AS GUESTS FROM loyalty.events.adobe_app_events WHERE UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMPTRIM(_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) = 'yyyyMMddus-east' AND _TIMHORTONS.LOYALTY.ID IN (SELECT )DISTINCT registeredAccountId AS WEEK_START_MAPPING, FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND CAST(PERIOD_DT AS DATE) AS PERIOD_DT, REGISTEREDACCOUNTID, >= 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 LOYALTY_TENDERS AS ( WITH loyaltyCustomerId,TENDERS AS ( TRANSACTIONID, SELECT REPLACE(UPPER(TRIM(TENDER_NAME)), EXPLODE(APPLIEDOFFERS'.', '') AS EXPLODEDTENDER_OFFERSNAME, REST_TYP_NM,TICKET_ID OPS_DIV_NMFROM loyalty.tlog.tlog_sale_ticket_tenders WHERE PARTITION_DATE_KEY FROMBETWEEN 20230101 AND 20230731 PRODRT.CURATED_TRANS_EVENTS_NEW -- 1. Challenge duration: 26/09 to 02/10 AND LEFT(REST_NO,2) = 10 ), WHERE LOYALTY AS ( DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}' SELECT AND '${myapplication.Offer_End_Date}' TICKET_ID AS TICKET_ID AND COUNTRY_NM = 'CANADA'FROM STG.DERIVED_MASTER_TABLE_NEW WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND REGISTEREDACCOUNTID20230731 IS NOT NULL AND COUNTRY_NM = 'CANADA' AND TRANSACTIONID IS NOT NULL -- AND RESTIS_TYP_NM = "STANDARD" )PASS_THROUGH = 0 SELECT AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east' ) DISTINCT A.TH_FISCAL_YEAR, SELECT A.TH_FISCAL_WEEK,* FROM TENDERS A.WEEK_START_MAPPING, INNER JOIN A.PERIOD_DT,LOYALTY B ON A.TICKET_ID = A.REGISTEREDACCOUNTID,B.TICKET_ID ) SELECT A.loyaltyCustomerId,CASE A.TRANSACTIONID, WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', A.REST_TYP_NM'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED A.OPS_DIV_NM, COMPTANT') THEN 'CASH' B.DESCRIPTION, WHEN UPPER(TENDER_NAME) IN ('DEBIT 1 AS REDEMPTIONS FROMCARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT' 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,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 regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lidUPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP' From WHEN UPPER(TENDER_NAME) LIKE '%UBER%' prodrt.curated_points_events as pointsTHEN 'UBER' where WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN partition_date_key BETWEEN '${myapplication.Offer_Start_Date}''DOORDASH' 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(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', 'NUMERISEZ ET PAYEZ CARTE-CADEAU TIM') THEN 'SCANANDPAY' WHEN 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,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 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
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 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'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
Code Block | ||
---|---|---|
| ||
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 |
Code Block |
---|
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 updated in March 2024; any unmapped fields would show up as null and would be considered as “Other”
Discounts on offerids
Code Block | ||
---|---|---|
| ||
-- Total Summary
with clean_loyalty_transactions as(
with FILTERED as(
WITH SEPERATED AS (
-- Filter for Exploded offers for offer ids and explode the disc amt with corresponding PLU
with zipped as (
-- explode offers and combine the Discount amount with offer
SELECT
*,
arrays_zip(discountAmounts, appliedOffers) as comb -- EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
FROM
PRODRT.CURATED_TRANS_EVENTS_NEW
WHERE
partition_date_key >= 20201201
-- to_date(partition_date_key, "yyyyMMdd") BETWEEN DATE '$Start_Date' AND DATE '$End_Date'
AND LEFT(REGISTEREDACCOUNTID, 7) = 'us-east'
AND APPLIEDOFFERS IS NOT NULL
AND COUNTRY_NM = 'CANADA'
)
select
DISTINCT transactionId,
loyaltyCustomerId,
registeredAccountId,
state_nm,
ticketId,
period_dt,
th_fiscal_week,
th_fiscal_year,
week_start_dt,
explode(Comb) as test
from
zipped
)
SELECT
*,
test ['discountAmounts'] AS DISC_AMT,
test ['appliedOffers'] AS APPLIED_OFFER
FROM
SEPERATED
)
select
FILTERED.*,
O.NAME,
O.DESCRIPTION,
1 as VOLUME
From
FILTERED
inner JOIN DYDB.OFFERS O ON O.OFFERID = FILTERED.APPLIED_OFFER
)
SELECT
TH_FISCAL_YEAR,
th_fiscal_week,
date(week_start_dt) as wk_start_dt,
state_nm,
APPLIED_OFFER,
DESCRIPTION,
SUM(DISC_AMT) AS DISCOUNT_DOLLARS,
SUM(VOLUME) AS REDEMPTION_VOLUME
FROM
clean_loyalty_transactions
GROUP BY
1,2,3,4,5,6 |
Tims Word Challenge Player Levels
Code Block | ||
---|---|---|
| ||
SELECT LEVEL, COUNT(DISTINCT ID) AS USERS
FROM (
SELECT _TIMHORTONS.LOYALTY.ID, MAX(DATE(TIMESTAMP)) AS DTE
, COALESCE(MAX(CAST(SUBSTRING(_TIMHORTONS.INTERACTION.ELEMENT.VALUE,18,CHARINDEX('-',REPLACE(_TIMHORTONS.INTERACTION.ELEMENT.VALUE,'"','-'),18)-18) AS INT)),0) LEVEL
FROM loyalty.events.adobe_app_events
WHERE EVENTTYPE = 'element_clicked'
AND DATE(TIMESTAMP) >= DATE '2023-11-01'
AND _TIMHORTONS.INTERACTION.PATH = '/timswordchallenge'
AND _TIMHORTONS.INTERACTION.ELEMENT.NAME = 'play'
AND _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
GROUP BY 1
)
GROUP BY 1
ORDER BY 1 |
Points Issued by Channel
Code Block | ||
---|---|---|
| ||
WITH OFFER_BASE AS ( SELECT MONTH_DT, SUM(CASE WHEN A.TAG IS NULL AND LEFT(A.EXPLODED.OFFERID,11) = 'EARN_POINTS' THEN A.EXPLODED.TIMSPOINTSEARNED ELSE 0 END) AS BASE, SUM(CASE WHEN LEFT(A.EXPLODED.OFFERID,11) <> 'EARN_POINTS' AND A.EXPLODED.OFFERID IS NOT NULL AND A.TAG IS NULL THEN A.EXPLODED.TIMSPOINTSEARNED ELSE 0 END) AS OFFERS WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'FROM (SELECT WHEN UPPERLAST_DAY(TENDER_NAME) IN ('DEBIT CARD'TO_DATE(PARTITION_DATE_KEY, 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'yyyyMMdd')) AS MONTH_DT, TAG, WHEN UPPER(TENDER_NAME) IN ('VISA', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS', 'DISCOVER' TRANSACTIONID, 'M/C', 'DISCOVER CARD', 'DIGITAL AMEX', 'DIGITAL DISCOVER', 'DIGITAL MASTER CARD', 'DIGITAL MASTERCARD', 'DIGITAL AMERICAN EXPRESS', 'DIGITAL VISA', 'DO VISA'POINTSEARNED, 'DO MASTERCARD') THEN 'CREDIT' WHEN UPPER(TENDER_NAME) IN ('TIM CARD'coalesce(cast(isCustomerServiceVisit as string), ''DIGITAL) TIMas CARD'ISCUSTOMERSERVICEVISIT, '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' EXPLODE(APPLIEDOFFERDETAILS) AS EXPLODED WHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP' FROM PRODRT.CURATED_POINTS_EVENTS A WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER' WHENWHERE UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH' TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS 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' LEFT(BARCODE, 4) = '0463' AND COALESCE(PARTNERID, '') = '' AND ISCUSTOMERSERVICEVISIT IS NOT TRUE ) A 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' GROUP BY 1 ), ALL_OTHER AS ( SELECT LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT, SUM(CASE WHEN B.TAG IN ('DAYPART_CHALLENGED_COMPLETED','PRODUCT_CHALLENGED_COMPLETED','FREQUENCY_CHALLENGED_COMPLETED') THEN B.POINTSEARNED ELSE 0 END) AS CHALLENGES, WHEN ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP' SUM(CASE WHEN LEFT(TAG, 4) = 'RUTR' THEN POINTSEARNED ELSE 0 END) AS RUTW, ELSE 'OTHER' END AS TENDER, COUNTSUM(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
Code Block |
---|
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 |
Code Block |
---|
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”CASE WHEN LEFT(B.TAG, 6) = 'HOCKEY' THEN B.POINTSEARNED ELSE 0 END) AS HOCKEY,
SUM(CASE WHEN B.ISCUSTOMERSERVICEVISIT = 'true' THEN B.POINTSEARNED ELSE 0 END) AS GUEST_CARE,
SUM(CASE WHEN UPPER(B.TAG) = 'WORD_CHALLENGE_LEVEL' THEN B.POINTSEARNED ELSE 0 END) AS WORD_CHALLENGE,
SUM(B.POINTSEARNED) AS TOTAL_POINTS
FROM PRODRT.CURATED_POINTS_EVENTS B
WHERE TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS
AND LEFT(BARCODE, 4) = '0463'
AND COALESCE(PARTNERID, '') = ''
GROUP BY 1
)
SELECT
A.MONTH_DT,
BASE,
OFFERS,
CHALLENGES,
RUTW,
HOCKEY,
GUEST_CARE,
WORD_CHALLENGE,
TOTAL_POINTS
FROM OFFER_BASE A
LEFT JOIN ALL_OTHER B
ON A.MONTH_DT = B.MONTH_DT |