You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 2
Next »
\uD83D\uDCD8 KPI List - Expand for definitions and Code
Known Diner Sales (KDS) - all sales that are made by recognizable guests, i.e we have lotalty card tagged to the guest
Table |
STG.DERIVED_MASTER_TABLE_NEW
|
---|
Query |
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
|
System Wide Sales (SWS) - all sales across the system
Table |
STG.DERIVED_MASTER_TABLE_NEW
|
---|
Query |
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
|
Additional Comments | Some forecasts may use hyperion sales interchangeable instead of SWS |
Metric and definition | Query | Comments |
---|
Known Diner Sales (KDS) - all sales that are made by recognizable guests, i.e we have lotalty card tagged to the guest |
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 |
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 |