Metric and definition | Query | Comments |
|---|
Known Diner Sales (KDS) - all sales that are made by recognizable guests, i.e we have loyalty card tagged to the guest | | Code Block |
|---|
SELECT A.TH_FISCAL_YEAR,
A.TH_FISCAL_WEEK,
SUM(A.AMOUNT) AS SALES,
COUNT(DISTINCT A.TICKET_ID) AS TRNXS,
COUNT(DISTINCT A.REGISTERED_ACCOUNT_ID) AS GUESTS
FROM STG.DERIVED_MASTER_TABLE_NEW A
WHERE
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND PERIOD_DT BETWEEN '<START DATE>' AND '<END DATE>'
AND A.IS_PASS_THROUGH = 0
AND A.COUNTRY_NM = 'CANADA'
AND LEFT(A.REGISTERED_ACCOUNT_ID,7) = 'us-east'
GROUP BY 1 |
| 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 | | Code Block |
|---|
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 PERIOD_DT 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 | | Code Block |
|---|
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 PERIOD_DT 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 guest visits in a time period | | Code Block |
|---|
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 PERIOD_DT 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. |
Digital Sales - known diner sales along with 3P delivery, non-registered catering & kiosk | | |
White Label Delivery (WL) | | Code Block |
|---|
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN '$1_START_DATE' AND '$2_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 |
| |
Mobile Order & Pay (MO&P) | | Code Block |
|---|
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN '$1_START_DATE' AND '$2_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 |
| |
Loyalty Scans - this includes eat in, takeout & drive thru | | Code Block |
|---|
SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES
FROM STG.DERIVED_MASTER_TABLE_NEW
WHERE PARTITION_DATE_KEY BETWEEN '$1_START_DATE' AND '$2_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 |
| |
| | |