\uD83D\uDCD8 KPI 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 | 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 to our system wide sales |
System Wide Sales (SWS) - all sales across the system | 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
| Some forecasts may use hyperion sales interchangeable instead of SWS |
Cheque - average sales 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 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) | 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 = 'WHITE LABEL DELIVERY') OR (SERVICE_MODE_CD = 'DELIVERY' AND REGISTERED_ACCOUNT_ID LIKE 'us-east%')) 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 - this 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 |
Other Registered Sales - Catering, Kiosk & Curbside | 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 ('KIOSK', 'KIOSK TAKEOUT', 'CATERING', 'CURB SIDE PICK UP')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1,2
| Included in known diner sales & digital sales |
3P Delivery | 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 |
Unregistered Kiosk | SELECT
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN '$1_START_DATE' AND '$2_END_DATE'
AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT')
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = "CANADA"
AND LEFT(REGISTERED_ACCOUNT_ID,7) <> 'us-east'
GROUP BY 1
| Included in digital sales |
Unregistered Catering | 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 ('CATERING', 'CURB SIDE PICK UP')
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = "CANADA"
AND REGISTERED_ACCOUNT_ID NOT LIKE 'us-east%'
GROUP BY 1
| Included in digital sales |
RRAMI (Restaurants Reporting Any Menu Item) | 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 - 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 >= 20230801
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 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) - Amount of guests that visited the app each month | Pulled currently from Amplitude using unique guests that triggered the ‘Session Start’ event. Will switch to Adobe in October 2023 using the same event. | Used to track amount of guests who visit the app. |
Digital Sales | Pulled using the individual queries listed above. | Consists of all known diner sales (WL, MO&P, Loyalty Scans, Other Registered Sales) along with 3P Delivery, Unregistered Kiosk & Unregistered 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. |