...
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 |
| Included in digital sales | ||||||||||||||||||||||||
Restaurants Reporting Any Menu Item (“RRAMI“) |
| |||||||||||||||||||||||||
Scan & Pay (“S&P”) Transactions where Scan & Pay feature was used |
Catering Catering sales
| 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 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 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
*,
WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START,
regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid
From
prodrt.curated_points_events as points
where
partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'
AND '${myapplication.Offer_End_Date}'
and tag = 'PRODUCT_CHALLENGED_COMPLETED'
),
completed as(
Select
a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
a.WEEK_START_MAPPING as WEEK_START_MAPPING,
a.PERIOD_DT as PERIOD_DT,
DATE_FORMAT(
CAST(
UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
),
'yyyyMMdd'
) as transaction_dt,
a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,
a.loyaltyCustomerId as loyaltyCustomerId,
a.TRANSACTIONID as TRANSACTIONID,
a.REST_TYP_NM as REST_TYP_NM,
a.OPS_DIV_NM as OPS_DIV_NM,
a.DESCRIPTION as DESCRIPTION,
a.REDEMPTIONS as REDEMPTIONS,
b.transactionID as completion_transactionID,
b.pointsEarned as pointsEarned,
b.restaurant as restaurant,
b.partition_date_key as offer_completion_dt,
b.WEEK_START as offer_completion_week,
b.lid as lid
from
TRXN_OFFER_CHALLENGES_REDEMPTIONS a
left join points_issued b on a.loyaltyCustomerId = b.lid
and b.WEEK_START = a.TH_FISCAL_WEEK
)
Select
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
WEEK_START_MAPPING,
REGISTEREDACCOUNTID,
loyaltyCustomerId,
offer_completion_week,
count(
distinct(
case
when offer_completion_dt is not NULL
and transaction_dt <= offer_completion_dt then TRANSACTIONID
end
)
) as trnx_before_completion,
count(distinct(TRANSACTIONID)) as total_trnx,
count(
distinct (
case
when lid is not Null then lid
end
)
) as completed
from
completed
group by 1, 2, 3, 4, 5, 6 |
Count of guests who completed a specific Offer Challenge
Games
NHL Hockey Challenge & Tims Word Challenge
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 _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
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 _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
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 THTENDER_FISCAL_YEARNAME, TICKET_ID TH_FISCAL_WEEK, FROM loyalty.tlog.tlog_sale_ticket_tenders DATE_FORMAT( WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731 CAST( AND LEFT(REST_NO,2) = 10 ), UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') LOYALTY AS TIMESTAMP ( SELECT ), TICKET_ID AS TICKET_ID 'yyyyMMdd' FROM STG.DERIVED_MASTER_TABLE_NEW ) ASWHERE WEEKPARTITION_STARTDATE_MAPPING,KEY BETWEEN 20230101 AND 20230731 CAST(PERIOD_DT AS DATE) AS PERIOD_DT, AND COUNTRY_NM = 'CANADA' REGISTEREDACCOUNTID, AND IS_PASS_THROUGH = 0 loyaltyCustomerId, AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east' ) TRANSACTIONID, SELECT A.* EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,FROM TENDERS A INNER JOIN REST_TYP_NM,LOYALTY B ON A.TICKET_ID = B.TICKET_ID OPS_DIV_NM ) SELECT CASE FROM WHEN UPPER(TENDER_NAME) IN PRODRT.CURATED_TRANS_EVENTS_NEW -- 1. Challenge duration: 26/09 to 02/10 WHERE('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH' WHEN DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}' AND '${myapplication.Offer_End_Date}UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT' AND COUNTRY_NM = 'CANADA' AND REGISTEREDACCOUNTID IS NOT NULL AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD" ) 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' SELECT WHEN UPPER(TENDER_NAME) IN ('TIM CARD', 'DIGITAL TIM CARD', DISTINCT A.TH_FISCAL_YEAR'CARTE TIM', 'MOBILE TIM CARD', 'DIGITAL CARTE TIM', A.TH_FISCAL_WEEK'TIM CARTE', 'TIMS GIFT CARD', 'DIGITAL TIMS GIFT CARD', A.WEEK_START_MAPPING, 'CARTE-CADEAU TIM', 'DIGITAL CARTE-CADEAU TIM') THEN 'TIMCARD' A.PERIOD_DT, WHEN A.REGISTEREDACCOUNTID, UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP' A.loyaltyCustomerId, WHEN UPPER(TENDER_NAME) A.TRANSACTIONID, LIKE '%UBER%' THEN 'UBER' A.REST_TYP_NM, WHEN UPPER(TENDER_NAME) LIKE A.OPS_DIV_NM, '%DOOR%' THEN 'DOORDASH' B.DESCRIPTION, WHEN UPPER(TENDER_NAME) IN ('SCAN AND 1PAY 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 *,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 WEEKOFYEARUPPER(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}'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 and tag = 'PRODUCT_CHALLENGED_COMPLETED' ), ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP' completed as( SelectELSE 'OTHER' END AS TENDER, COUNT(DISTINCT TICKET_ID) AS TRXNS a.TH_FISCAL_YEAR as TH_FISCAL_YEAR, a.TH_FISCAL_WEEK as TH_FISCAL_WEEK, a.WEEK_START_MAPPING as WEEK_START_MAPPING, a.PERIOD_DT as PERIOD_DT, DATE_FORMAT( CAST( UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP ), 'yyyyMMdd' ) as transaction_dt, a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID, a.loyaltyCustomerId as loyaltyCustomerId, a.TRANSACTIONID as TRANSACTIONID, a.REST_TYP_NM as REST_TYP_NM, a.OPS_DIV_NM as OPS_DIV_NM, a.DESCRIPTION as DESCRIPTION, a.REDEMPTIONS as REDEMPTIONS, b.transactionID as completion_transactionID, b.pointsEarned as pointsEarned, b.restaurant as restaurant, b.partition_date_key as offer_completion_dt, b.WEEK_START as offer_completion_week, b.lid as lid from TRXN_OFFER_CHALLENGES_REDEMPTIONS a left join points_issued b on a.loyaltyCustomerId = b.lid and b.WEEK_START = a.TH_FISCAL_WEEK ) Select TH_FISCAL_YEAR, TH_FISCAL_WEEK, WEEK_START_MAPPING, REGISTEREDACCOUNTID, loyaltyCustomerId, offer_completion_week, count( distinct( case when offer_completion_dt is not NULL and transaction_dt <= offer_completion_dt then TRANSACTIONID end ) ) as trnx_before_completion, count(distinct(TRANSACTIONID)) as total_trnx, count( distinct ( case when lid is not Null then lid end ) ) as completed from completed group by 1, 2, 3, 4, 5, 6 |
Count of guests who completed a specific Offer Challenge
Games
NHL Hockey Challenge & Tims Word Challenge
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 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 WHERE= 'element_clicked' AND TRIM(DATE(TIMESTAMP) >= DATE '2023-11-01' AND _TIMHORTONS.INTERACTION.PATH) IN= ('/timswordchallenge') AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play' AND _TIMHORTONS.LOYALTY.PLATFORMID 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 FROMSELECT 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 ( loyalty.events.adobe_app_events WHERE SELECT TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge' AND TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks') ANDMONTH_DT, _TIMHORTONS.PLATFORM IN ('app') AND SUM(CASE WHEN A.TAG IS NULL AND LEFT(_TIMHORTONSA.LOYALTYEXPLODED.IDOFFERID,711) = 'us-eastEARN_POINTS' 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 (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 SELECT REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME, FROM (SELECT TICKET_ID FROM loyalty.tlog.tlog_sale_ticket_tenders WHERE LAST_DAY(TO_DATE(PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731, 'yyyyMMdd')) AS MONTH_DT, AND LEFT(REST_NO,2) = 10 )TAG, LOYALTY AS ( SELECTTRANSACTIONID, TICKET_ID AS TICKET_ID FROM STG.DERIVED_MASTER_TABLE_NEW POINTSEARNED, WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731 AND COUNTRY_NM = 'CANADA' AND IS_PASS_THROUGH = 0coalesce(cast(isCustomerServiceVisit as string), '') as ISCUSTOMERSERVICEVISIT, AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east' EXPLODE(APPLIEDOFFERDETAILS) AS EXPLODED SELECT A.* FROM TENDERS A INNER JOIN LOYALTY B ONFROM APRODRT.TICKETCURATED_POINTS_IDEVENTS =A B.TICKET_ID ) SELECT CASE WHENWHERE UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH' TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS WHEN UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT' AND LEFT(BARCODE, 4) = '0463' WHEN UPPER(TENDER_NAME) IN ('VISA', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS'AND COALESCE(PARTNERID, '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'AND ISCUSTOMERSERVICEVISIT IS NOT TRUE WHEN UPPER(TENDER_NAME) IN ('TIM CARD', 'DIGITAL TIM CARD', 'CARTE TIM', 'MOBILE TIM) CARD',A 'DIGITAL CARTE TIM', 'TIM CARTE', 'TIMS GIFT CARD', 'DIGITAL TIMS GIFT CARD', 'CARTE-CADEAU TIM', 'DIGITAL CARTE-CADEAU TIM') THEN 'TIMCARD' GROUP BY 1 ), ALL_OTHER AS ( WHEN UPPER(TENDER_NAME) LIKE '%SKIP%'SELECT THEN 'SKIP' WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER'LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT, SUM(CASE WHEN B.TAG IN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'('DAYPART_CHALLENGED_COMPLETED','PRODUCT_CHALLENGED_COMPLETED','FREQUENCY_CHALLENGED_COMPLETED') THEN B.POINTSEARNED ELSE 0 END) AS CHALLENGES, SUM(CASE WHEN UPPER(TENDER_NAMELEFT(TAG, 4) IN= ('RUTR'SCAN ANDTHEN PAYPOINTSEARNED 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'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, WHEN UPPER(TENDER_NAMESUM(B.POINTSEARNED) 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'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' WHENAND ((UPPER(TENDER_NAMECOALESCE(PARTNERID, '') LIKE= '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP' GROUP BY 1 ) SELECT ELSE 'OTHER' END AS TENDER, COUNT(DISTINCT TICKET_ID) AS TRXNS FROM LOYALTY_TENDERS GROUP BY 1 |
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 |