Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

Glossary of Digital & Loyalty Metrics

\uD83D\uDCD8 Metrics List

Metric and Definition

Query

Comments and Business Use

Known Diner Sales (“KDS”)

All sales that are made by recognizable guests, i.e. guests that have registered through the app (identified by their registered account id beginning with ‘us-east’)

SELECT TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS

FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

This metric is used as a baseline to see loyalty sales penetration against system wide sales.

System Wide Sales (“SWS”)

Total sales across TH

SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS

FROM STG.DERIVED_MASTER_TABLE_NEW T1
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0 
AND T1.COUNTRY_NM = 'CANADA'

GROUP BY 1,2,3

Note: Some forecasts may use Hyperion sales interchangeable in lieu of SWS sourced from STG.DERIVED_MASTER_TABLE_NEW.

Cheque

Average sales value ($) of an individual transaction

SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS,
SUM(T1.AMOUNT)/COUNT(DISTINCT T1.TICKET_ID) AS CHEQUE

FROM STG.DERIVED_MASTER_TABLE_NEW T1
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0 
AND T1.COUNTRY_NM = 'CANADA'

GROUP BY 1,2,3

Can calculate this on a system level or down to an individual guest level. Can filter on loyalty or non-loyalty.

Frequency

Average loyalty guest visits in a given time period

SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.TICKET_ID)/COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS FREQUENCY

FROM STG.DERIVED_MASTER_TABLE_NEW T1
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND T1.IS_PASS_THROUGH = 0 
AND T1.COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2,3

Usually look at this metric for a week, month, or year.

White Label Delivery (“WL”)

Delivery sales initiated from the TH mobile app

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN '$START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('WHITE LABEL DELIVERY','DELIVERY')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Included in known diner sales & digital sales.

Sales of our internal app delivery platform.

Mobile Order & Pay (“MO&P”)

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('MOBILE ORDER DRIVE THRU', 'MOBILE ORDER EAT IN', 'MOBILE ORDER TAKE OUT')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1

Included in known diner sales & digital sales.

Loyalty Scans

Sales made by known diners and includes eat-in, takeout & drive thru

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('TAKEOUT', 'EATIN','DRIVETHRU')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Included in known diner sales & digital sales.

3P Delivery

Delivery sales initiated and fulfilled by third-party delivery providers, such as UberEats, SkipTheDishes and DoorDash

SELECT 
a.PARTITION_DATE_KEY,
SUM(A.AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW A 

INNER JOIN TLOG.TLOG_SALE_TICKET_TENDERS B 
ON A.TICKET_ID = B.TICKET_ID

WHERE A.PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND TENDER_NAME IN ("UBER EATS CREDIT","Credit Uber Eats","Uber Eats Credit","CRÉDIT UBER EATS","SKIP CREDIT","Skip Credit","CRÉDIT de DoorDash","CRÉDIT DE DOORDASH","DoorDash Credit","DOORDASH CREDIT","Crédit de DoorDash","Doordash")

GROUP BY 1

Included in digital sales

Kiosk

Sales via Kiosk; can be split into registered and un-registered Kiosk sales using

SELECT
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT', 'KIOSK EATIN')
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA" 

GROUP BY 1

Included in digital sales

-- Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) = 'us-east'
-- Un-Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) != 'us-east'

Catering

Catering sales

SELECT 
PARTITION_DATE_KEY,
SUM(ITEMTOTALPRICE)AS SALES,

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0 
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND DININGTYPE = 'CT'

GROUP BY 1

Included in digital sales

Restaurants Reporting Any Menu Item (“RRAMI“)

WITH RESTAURANTS AS (

SELECT 

TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
PARTITION_DATE_KEY,
COUNT(DISTINCT REST_NO) AS RESTAURANTS

FROM STG.DERIVED_MASTER_TABLE_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA' 

GROUP BY 1,2,3
ORDER BY 1)

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(RESTAURANTS) AS RRAMI

FROM RESTAURANTS

GROUP BY 1,2

Scan & Pay (“S&P”)

Transactions where Scan & Pay feature was used

SELECT 
PARTITION_DATE_KEY,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES 

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM IN ('CANADA')
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND SCANANDPAY IS TRUE

GROUP BY 1

Often tracked as a % of registered transactions

Scan & Pay Penetration Formula: (Scan & Pay Transactions) / (Registered Transactions)

Loyalty Redemptions

Products that were redeemed using loyalty points

CACHE TABLE DAILY_REDEMPTIONS AS

WITH TRANSACTIONS_WITH_OFFER_ID AS (

SELECT 
LEFT(PERIOD_DT, 10) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID, 
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE DATE_KEY >= '20230606'
AND COALESCE(REGISTEREDACCOUNTID,'') IS NOT NULL
AND APPLIEDOFFERS IS NOT NULL
AND COUNTRY_NM = 'CANADA')

SELECT 
PERIOD_DT,
COUNT(DISTINCT TRANSACTIONID) AS TRXNS

FROM TRANSACTIONS_WITH_OFFER_ID AS A

INNER JOIN (SELECT DISTINCT OFFERID FROM DYDB.WEEKLYOFFERS
             
            UNION ALL
            
            SELECT DISTINCT OFFERID
            FROM DYDB.OFFERS
            WHERE CONTAINS(description, 'LOYALTY')
            AND DESCRIPTION LIKE 'CA L%'
            AND DESCRIPTION NOT IN ('CA LR Registered Default (same as L1)-LOYALTY', 'CA LU Unregistered (same as 102) ONE-TIME-LOYALTY') ) B
            
ON A.EXPLODED_OFFERS = B.OFFERID 

LEFT JOIN DYDB.OFFERS AS C 
ON A.EXPLODED_OFFERS=C.OFFERID

WHERE EXPLODED_OFFERS IS NOT NULL 
AND C.OFFERID IS NOT NULL

GROUP BY 1

Used for tracking how many free items we’re giving away and the value of them.

Often viewed as a percentage of SWS or KDS.

Loyalty drag formula = $ value of redeemed items / SWS $

Monthly Active Users (“MAU”)

Number of guests that visited the app each month

WITH EVENTS AS (
SELECT DATE
       , _TIMHORTONS.INTERACTION.ELEMENT.NAME
       , _TIMHORTONS.LOYALTY.ID AS GUEST_ID
FROM   loyalty.events.adobe_app_events
WHERE  EVENTTYPE IN ('app_open','app_launch')    
AND    _TIMHORTONS.PLATFORM IN ('app') 
AND    LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND    DATE <= CURRENT_DATE )

SELECT     LAST_DAY(EVENTS.DATE) AS DTE
          , COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER
FROM      EVENTS  
GROUP BY  1
ORDER BY  1 DESC

Used to track the unique number of guests who opened or launched the app per month. Limitation of the dataset is: (1) data only exists from Nov 1st, 2023, and (2) Prior to mm/dd/yyyy (TBD), dataset did not contain guests using app versions prior to 7.1.187.

Digital Sales

Pulled using the individual queries listed above.

Consists of WL, MO&P, Loyalty Scans, 3P Delivery, Kiosk & Catering.

Weekly Offer Redemptions

WITH REDEMPTION_SUMMARY AS (
  WITH OFFER_REDEMPTION_TRXNS AS (
    WITH TRANSACTIONS_WITH_OFFER_ID AS (
      SELECT
        TH_FISCAL_YEAR,
        TH_FISCAL_WEEK,
        CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,
        DATE_FORMAT(
          CAST(
            UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
        ) AS WEEK_START,
        CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
        REGISTEREDACCOUNTID,
        TRANSACTIONID,
        EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
      FROM
        PRODRT.CURATED_TRANS_EVENTS_NEW
      WHERE
        PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
        AND REGISTEREDACCOUNTID LIKE 'us-east%'
        AND COUNTRY_NM = 'CANADA'
    )
    SELECT
      DISTINCT A.TH_FISCAL_YEAR,
      A.TH_FISCAL_WEEK,
      A.WEEK_START_DT,
      A.PERIOD_DT,
      A.REGISTEREDACCOUNTID,
      A.TRANSACTIONID,
      C.NAME,
      C.DESCRIPTION,
      A.EXPLODED_OFFERS,
      1 AS VOLUME
    FROM
      TRANSACTIONS_WITH_OFFER_ID A
      INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
      LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
  )
  SELECT
    WEEK_START_DT,
    A.DESCRIPTION AS OFFER_DESCRIPTION,
    A.EXPLODED_OFFERS AS OFFERID,
    SUM(VOLUME) AS REDEMPTION_VOLUME
  FROM
    OFFER_REDEMPTION_TRXNS A
  GROUP BY
    1,
    2,
    3
)
SELECT
  a.WEEK_START_DT AS WKDT,
  OFFER_DESCRIPTION,
  OFFERID,
  SUM(REDEMPTION_VOLUME)
FROM
  REDEMPTION_SUMMARY A
  GROUP BY 1,2,3

Used to track the volume of redemptions for each offer in a given week.

Offer Challenge

WITH 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 >= <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
    <this is where you select the offers you want>
    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, 
  COUNT(DISTINCT REGISTEREDACCOUNTID) AS GUESTS
FROM
  OFFER_REDEMPTION_TRXNS a
GROUP BY
  1,
  2,
  3

Count of guests who completed a specific Offer Challenge

Games

NHL Hockey Challenge & Tims Word Challenge

SELECT    YEAR(TIMESTAMP) AS YR
          , MONTH(TIMESTAMP) AS MTH 
          , COUNT(DISTINCT GUESTS) AS GUESTS FROM (
-- WORD CHALLENEGE PLAYERS

SELECT    DISTINCT TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM      loyalty.events.adobe_app_events 
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 

UNION
-- HOCKEY PLAYERS

SELECT    DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID  AS GUESTS
FROM      loyalty.events.adobe_app_events
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 
)
GROUP BY 1,2
ORDER BY 1 DESC, 2

Count of guests who played Games (NHL Hockey Challenge and Tims Word Challenge).

Limitation: Data only available from Nov 1, 2023.

  • No labels