...
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
| 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 |
| Included in digital sales | Restaurants Reporting Any Menu Item (“RRAMI“) | 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 |
| 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 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 AND COUNTRY_NMGROUP = '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 monthBY 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 EVENTSREDEMPTION_SUMMARY AS ( SELECT DATEWITH OFFER_REDEMPTION_TRXNS AS ( ,WITH _TIMHORTONS.INTERACTION.ELEMENT.NAME TRANSACTIONS_WITH_OFFER_ID AS ( , _TIMHORTONS.LOYALTY.ID AS GUEST_ID FROM loyalty.events.adobe_app_events WHERE EVENTTYPE IN ('app_open','app_launch') SELECT TH_FISCAL_YEAR, AND _TIMHORTONS.PLATFORM IN ('app') ANDTH_FISCAL_WEEK, LEFTCAST(_TIMHORTONS.LOYALTY.ID,7) = 'us-east' ANDWEEK_START_DT AS DATE) AS WEEK_START_DT, DATE <= CURRENT_DATE ) SELECT DATE_FORMAT( CAST( LASTUNIX_DAY(EVENTS.DATETIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS DTETIMESTAMP , COUNT(DISTINCT EVENTS.GUEST_ID), 'yyyyMMdd' ) AS ACTIVEWEEK_USERSTART, FROM EVENTS CAST(PERIOD_DT GROUPAS BYDATE) AS 1PERIOD_DT, 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 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 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, THA.REST_FISCALTYP_YEARNM, THA.OPS_FISCALDIV_WEEKNM, CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,B.DESCRIPTION, 1 AS REDEMPTIONS FROM DATE_FORMAT( EXPLODED AS A CAST( LEFT JOIN DYDB.OFFERS AS B ON UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMPA.EXPLODED_OFFERS = B.OFFERID WHERE EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e' ), points_issued as ( 'yyyyMMdd' Select ) AS WEEK_START*, CAST(PERIOD_DT AS DATEWEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS PERIODWEEK_DTSTART, REGISTEREDACCOUNTID, regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid TRANSACTIONID, From EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERSprodrt.curated_points_events as points where FROM partition_date_key BETWEEN PRODRT.CURATED_TRANS_EVENTS_NEW'${myapplication.Offer_Start_Date}' WHERE AND '${myapplication.Offer_End_Date}' PARTITION_DATE_KEY BETWEEN 'STARTand DATE'tag AND= 'END DATEPRODUCT_CHALLENGED_COMPLETED' ), AND REGISTEREDACCOUNTIDcompleted LIKE 'us-east%'as( Select AND COUNTRY_NM = 'CANADA' ) a.TH_FISCAL_YEAR as TH_FISCAL_YEAR, SELECT DISTINCT A.a.TH_FISCAL_WEEK as TH_FISCAL_YEARWEEK, A a.THWEEK_FISCALSTART_MAPPING as WEEK_START_MAPPING, Aa.WEEK_STARTPERIOD_DT as PERIOD_DT, A.PERIOD_DT, DATE_FORMAT( A.REGISTEREDACCOUNTID,CAST( A.TRANSACTIONID, C.NAME, UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP C.DESCRIPTION, A.EXPLODED_OFFERS), 1 AS VOLUME 'yyyyMMdd' FROM ) TRANSACTIONS_WITH_OFFER_ID Aas transaction_dt, INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERIDa.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID, a.loyaltyCustomerId as LEFTloyaltyCustomerId, JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = Ca.OFFERIDTRANSACTIONID as TRANSACTIONID, ) SELECT WEEKa.REST_STARTTYP_DT,NM as REST_TYP_NM, A.DESCRIPTION AS OFFER_DESCRIPTION, A.EXPLODED_OFFERS AS OFFERIDa.OPS_DIV_NM as OPS_DIV_NM, SUM(VOLUME) AS REDEMPTION_VOLUME FROM a.DESCRIPTION as DESCRIPTION, OFFER_REDEMPTION_TRXNS A a.REDEMPTIONS as GROUPREDEMPTIONS, BY 1, b.transactionID as 2completion_transactionID, 3 ) SELECT a.WEEK_START_DT AS WKDT,b.pointsEarned as pointsEarned, OFFER_DESCRIPTION, OFFERID, SUM(REDEMPTION_VOLUME) FROMb.restaurant as restaurant, 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 ( b.partition_date_key as offer_completion_dt, b.WEEK_START as offer_completion_week, b.lid as lid WITH EXPLODED ASfrom ( SELECTTRXN_OFFER_CHALLENGES_REDEMPTIONS a left join TH_FISCAL_YEAR,points_issued b on a.loyaltyCustomerId = b.lid and b.WEEK_START = a.TH_FISCAL_WEEK, ) Select DATE_FORMAT( TH_FISCAL_YEAR, CAST(TH_FISCAL_WEEK, WEEK_START_MAPPING, UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP REGISTEREDACCOUNTID, loyaltyCustomerId, offer_completion_week, ), count( distinct( 'yyyyMMdd' case ) AS WEEK_START_MAPPING, CAST(PERIOD_DT AS DATE) AS PERIOD_DT,when offer_completion_dt is not NULL REGISTEREDACCOUNTID, and transaction_dt <= offer_completion_dt then TRANSACTIONID loyaltyCustomerId, end TRANSACTIONID, ) EXPLODE(APPLIEDOFFERS) ASas EXPLODEDtrnx_before_OFFERScompletion, count(distinct(TRANSACTIONID)) as total_trnx, REST_TYP_NM, count( OPS_DIV_NM distinct ( FROM case PRODRT.CURATED_TRANS_EVENTS_NEW -- 1. Challenge duration: 26/09 to 02/10when lid is not Null then lid WHERE end DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}' ) AND '${myapplication.Offer_End_Date}' ) as completed from AND COUNTRY_NM =completed 'CANADA' group by 1, 2, 3, 4, AND REGISTEREDACCOUNTID IS NOT NULL 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 AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD", MONTH(TIMESTAMP) AS MTH ), COUNT(DISTINCT GUESTS) AS GUESTS FROM ( SELECT-- WORD CHALLENEGE PLAYERS SELECT DISTINCT A.TH_FISCAL_YEAR, TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS FROM Aloyalty.events.THadobe_FISCALapp_WEEK,events WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) A.WEEK_START_MAPPING, IN ('/timswordchallenge') AND A.PERIOD_DT, TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play' AND A.REGISTEREDACCOUNTID, _TIMHORTONS.PLATFORM IN ('app') AND A.loyaltyCustomerId, LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east' AND A.TRANSACTIONID, A.REST_TYP_NM, _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND A.OPS_DIV_NM, DATE >= DATE '2023-11-01' UNION B.DESCRIPTION, -- HOCKEY PLAYERS SELECT DISTINCT 1TIMESTAMP,_TIMHORTONS.LOYALTY.ID AS REDEMPTIONSGUESTS FROM FROM loyalty.events.adobe_app_events WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) EXPLODED AS A = '/hockey_challenge' AND LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERIDTRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks') AND _TIMHORTONS.PLATFORM WHEREIN ('app') AND EXPLODED_OFFERSLEFT(_TIMHORTONS.LOYALTY.ID,7) = '76290e2e-1783-442f-8ff6-a2b16900a34eus-east' AND _TIMHORTONS.LOYALTY.ID IN (SELECT ),DISTINCT registeredAccountId FROM loyalty.users.customer_base points_issued as ( WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND Select DATE >= DATE '2023-11-01' ) GROUP BY *1,2 ORDER BY 1 WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START, 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 regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid TENDERS AS ( From SELECT prodrt.curated_points_events as points REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME, where TICKET_ID partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'FROM loyalty.tlog.tlog_sale_ticket_tenders AND '${myapplication.Offer_End_Date}' WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731 and tagAND LEFT(REST_NO,2) = 'PRODUCT_CHALLENGED_COMPLETED'10 ), completed as(LOYALTY AS ( Select SELECT a.TH_FISCAL_YEAR as TH_FISCAL_YEAR, a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,TICKET_ID AS TICKET_ID FROM aSTG.WEEKDERIVED_START_MAPPING as WEEK_START_MAPPING,MASTER_TABLE_NEW a.PERIOD_DT as PERIOD_DT, WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731 AND DATECOUNTRY_FORMAT(NM = 'CANADA' AND CAST( IS_PASS_THROUGH = 0 UNIX_TIMESTAMP(AND LEFT(PERIODREGISTERED_ACCOUNT_DTID,7) 10),= 'yyyyus-MM-dd'east' ) AS TIMESTAMP SELECT A.* ), FROM TENDERS A 'yyyyMMdd' INNER JOIN LOYALTY B ) as transaction_dt, ON A.TICKET_ID = B.TICKET_ID ) SELECT CASE a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID, WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', a.loyaltyCustomerId as loyaltyCustomerId, a.TRANSACTIONID as TRANSACTIONID,'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH' a.REST_TYP_NM as REST_TYP_NM, a.OPS_DIV_NM as OPS_DIV_NM, WHEN UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT' a.DESCRIPTION as DESCRIPTION, WHEN UPPER(TENDER_NAME) IN 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('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' left join points_issued b on a.loyaltyCustomerId = b.lidWHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP' and b.WEEK_START = a.TH_FISCAL_WEEK )WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER' Select WHEN TH_FISCAL_YEAR, UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH' TH_FISCAL_WEEK, WEEK_START_MAPPING, WHEN UPPER(TENDER_NAME) IN ('SCAN AND PAY REGISTEREDACCOUNTIDVISA', 'SCAN AND loyaltyCustomerIdPAY MASTERCARD', 'SCAN AND offer_completion_weekPAY TIMCARD', 'SCAN AND PAY AMEX', 'SCAN AND count(PAY DISCOVER', 'SCAN AND PAY TIM CARD', 'NUMERISEZ ET distinct(PAYEZ – VISA', 'SCANTOPAY', 'NUMERISEZ ET PAYEZ – MASTERCARD', 'NUMERISEZ ET casePAYEZ – CARTE TIM', 'NUMÉRISEZ ET PAYEZ – VISA', 'NUMÉRISEZ ET PAYEZ – when offer_completion_dt is not NULL and transaction_dt <= offer_completion_dt then TRANSACTIONID endMASTERCARD', '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 UPPER(TENDER_NAME) IN ('HST', 'HST1', 'TVQ', 'TPS', ) as trnx_before_completion, count(distinct(TRANSACTIONID)) as total_trnx, count( distinct ( case when lid is not Null then lid'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 end ) ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP' ) as completed ELSE from'OTHER' END AS TENDER, COUNT(DISTINCT TICKET_ID) AS completedTRXNS FROM LOYALTY_TENDERS groupGROUP byBY 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
|
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' ) 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' 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 FROM (SELECT WHEN LAST_DAY((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT, ELSE 'OTHER'TAG, 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_dtTRANSACTIONID, 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 POINTSEARNED, coalesce(cast(isCustomerServiceVisit as string), '') as ISCUSTOMERSERVICEVISIT, EXPLODE(APPLIEDOFFERDETAILS) AS EXPLODED FROM PRODRT.CURATED_POINTS_EVENTS A 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, '') = '' AND ISCUSTOMERSERVICEVISIT IS NOT TRUE ) A 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, SUM(CASE WHEN LEFT(TAG, 4) = 'RUTR' THEN POINTSEARNED ELSE 0 END) AS RUTW, SUM(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 |