\uD83D\uDCD8 KPI List - Expand for definitions and Code
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 | 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 |
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 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 | 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. |