...
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 | ||
---|---|---|
| ||
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 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 _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,
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, 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 *, 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 FROMwhere PRODRT.CURATEDpartition_TRANSdate_EVENTS_NEWkey BETWEEN '${myapplication.Offer_Start_Date}' WHERE PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'AND '${myapplication.Offer_End_Date}' and tag AND REGISTEREDACCOUNTID LIKE 'us-east%= 'PRODUCT_CHALLENGED_COMPLETED' ), AND COUNTRY_NM = 'CANADA'completed as( ) Select SELECT DISTINCT A.a.TH_FISCAL_YEAR as TH_FISCAL_YEAR, A.a.TH_FISCAL_WEEK as TH_FISCAL_WEEK, A a.WEEK_START_DT,MAPPING A.PERIOD_DTas WEEK_START_MAPPING, A.REGISTEREDACCOUNTID, A.TRANSACTIONID, C.NAMEa.PERIOD_DT as PERIOD_DT, C.DESCRIPTION, DATE_FORMAT( A.EXPLODED_OFFERS, CAST( 1 AS VOLUME FROM TRANSACTIONS_WITH_OFFER_ID A INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP ), LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID 'yyyyMMdd' ) SELECT ) as WEEK_START_DTtransaction_dt, A.DESCRIPTION AS OFFER_DESCRIPTION, A.EXPLODED_OFFERS AS OFFERIDa.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID, SUM(VOLUME) AS REDEMPTION_VOLUME a.loyaltyCustomerId FROMas OFFER_REDEMPTION_TRXNS AloyaltyCustomerId, GROUP BY a.TRANSACTIONID as 1,TRANSACTIONID, a.REST_TYP_NM as 2REST_TYP_NM, 3 ) SELECT a.WEEKOPS_STARTDIV_DTNM AS WKDTas OPS_DIV_NM, OFFER_DESCRIPTION, OFFERID, SUM(REDEMPTION_VOLUME) FROMa.DESCRIPTION as DESCRIPTION, REDEMPTION_SUMMARY A GROUP BY 1,2,3 |
a |
. |
Offer Challenge
Code Block | ||
---|---|---|
| ||
with TRXN_OFFER_CHALLENGES_REDEMPTIONS as REDEMPTIONS, ( WITHb.transactionID EXPLODED AS (as completion_transactionID, b.pointsEarned SELECTas pointsEarned, TH_FISCAL_YEARb.restaurant as restaurant, b.partition_date_key as THoffer_FISCALcompletion_WEEKdt, b.WEEK_START as DATE_FORMAT(offer_completion_week, b.lid as lid CAST( from UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP ),TRXN_OFFER_CHALLENGES_REDEMPTIONS a left join points_issued b on a.loyaltyCustomerId = b.lid and b.WEEK_START = a.TH_FISCAL_WEEK 'yyyyMMdd' ) Select ) AS WEEKTH_STARTFISCAL_MAPPINGYEAR, TH_FISCAL_WEEK, CAST(PERIOD_DT AS DATE) AS PERIOD_DT, WEEK_START_MAPPING, REGISTEREDACCOUNTID, loyaltyCustomerId, offer_completion_week, TRANSACTIONID, count( EXPLODE distinct(APPLIEDOFFERS) AS EXPLODED_OFFERS, case REST_TYP_NM, when OPSoffer_DIV_NM FROM completion_dt is not NULL PRODRT.CURATED_TRANS_EVENTS_NEW -- 1. Challenge duration: 26/09 to 02/10 and transaction_dt <= offer_completion_dt then TRANSACTIONID WHERE end DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}' ) ) AND '${myapplication.Offer_End_Date}'as trnx_before_completion, count(distinct(TRANSACTIONID)) as total_trnx, AND COUNTRY_NM = 'CANADA' count( ANDdistinct REGISTEREDACCOUNTID IS( NOT NULL case AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD" when lid is not Null )then lid SELECT end DISTINCT A.TH_FISCAL_YEAR, ) A.TH_FISCAL_WEEK, ) as completed A.WEEK_START_MAPPING,from completed A.PERIOD_DT, group by 1, 2, 3, 4, A.REGISTEREDACCOUNTID, 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 A.loyaltyCustomerId, A.TRANSACTIONID, MONTH(TIMESTAMP) AS MTH A.REST_TYP_NM, A.OPS_DIV_NM, COUNT(DISTINCT GUESTS) AS GUESTS FROM ( -- WORD B.DESCRIPTION,CHALLENEGE PLAYERS SELECT DISTINCT 1TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS REDEMPTIONS GUESTS FROM loyalty.events.adobe_app_events WHERE EXPLODED AS A TRIM(_TIMHORTONS.INTERACTION.PATH) IN LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERID('/timswordchallenge') AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play' AND _TIMHORTONS.PLATFORM WHEREIN ('app') AND EXPLODED_OFFERSLEFT(_TIMHORTONS.LOYALTY.ID,7) = '76290e2e-1783-442f-8ff6-a2b16900a34e'us-east' AND ), points_issued as ( _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND Select DATE >= DATE '2023-11-01' UNION -- *,HOCKEY PLAYERS SELECT WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START, DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID AS GUESTS FROM regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid Fromloyalty.events.adobe_app_events WHERE TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge' AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) prodrt.curated_points_events as pointsIN ('submit_picks') AND _TIMHORTONS.PLATFORM whereIN ('app') AND partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east' AND _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId AND '${myapplication.Offer_End_Date}' FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473')) AND and tagDATE >= DATE 'PRODUCT_CHALLENGED_COMPLETED2023-11-01' ) GROUP BY )1,2 ORDER BY 1 completed as( 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 ( Select WITH TENDERS AS ( a.TH_FISCAL_YEAR as TH_FISCAL_YEAR, SELECT a.TH_FISCAL_WEEK as TH_FISCAL_WEEK, REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME, a.WEEK_START_MAPPING as WEEK_START_MAPPING,TICKET_ID FROM a.PERIOD_DT as PERIOD_DT,loyalty.tlog.tlog_sale_ticket_tenders WHERE PARTITION_DATE_FORMAT(KEY BETWEEN 20230101 AND 20230731 AND CAST( LEFT(REST_NO,2) = 10 ), UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') LOYALTY AS TIMESTAMP ( SELECT ), TICKET_ID AS TICKET_ID 'yyyyMMdd' FROM STG.DERIVED_MASTER_TABLE_NEW ) asWHERE transaction_dt, PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731 a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,AND COUNTRY_NM = 'CANADA' a.loyaltyCustomerId as loyaltyCustomerId, AND IS_PASS_THROUGH = 0 a.TRANSACTIONID as TRANSACTIONID, AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east' a.REST_TYP_NM as REST_TYP_NM,) SELECT a.OPS_DIV_NM as OPS_DIV_NM,A.* FROM TENDERS A a.DESCRIPTION as DESCRIPTION,INNER JOIN LOYALTY B ON aA.REDEMPTIONSTICKET_ID as REDEMPTIONS,= B.TICKET_ID ) SELECT CASE b.transactionID as completion_transactionID, b.pointsEarned as pointsEarned, b.restaurant as restaurant, b.partition_date_key as offer_completion_dt,WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH' WHEN b.WEEK_START as offer_completion_week, b.lid as lidUPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT' from WHEN UPPER(TENDER_NAME) TRXN_OFFER_CHALLENGES_REDEMPTIONS a left join points_issued b on a.loyaltyCustomerId = b.lid and b.WEEK_START = a.TH_FISCAL_WEEK ) SelectIN ('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 TH_FISCAL_YEAR, TH_FISCAL_WEEK, WEEK_START_MAPPING, REGISTEREDACCOUNTID, loyaltyCustomerId, 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' offer_completion_week, WHEN countUPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP' distinct( WHEN UPPER(TENDER_NAME) LIKE '%UBER%' caseTHEN 'UBER' whenWHEN offer_completion_dt is not NULL UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH' andWHEN 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 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 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
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', 'NUMERISEZ ET PAYEZ CARTE-CADEAU TIM') THEN 'SCANANDPAY' 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, 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'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, TAG, 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
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 TRANSACTIONID, 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(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' ANDPARTNERID, '') = '' DATE(TIMESTAMP) >= DATE '2023-11-01' AND _TIMHORTONS.INTERACTION.PATH = '/timswordchallenge' AND _TIMHORTONS.INTERACTION.ELEMENT.NAME = 'play' GROUP BY 1 ) GROUP BY 1 ORDER BY 1GROUP 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 |