Page Comparison

...

Expand

title	Known Diner Sales (KDS) - all sales that are made by recognizable guests, i.e we have lotalty card tagged to the guest

Table

Code Block
STG.DERIVED_MASTER_TABLE_NEW

Query

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

Expand

title	Known Diner System Wide Sales (KDSSWS) - all sales that are made by recognizable guests, i.e we have lotalty card tagged to the guestacross the system

Table

Code Block
STG.DERIVED_MASTER_TABLE_NEW

Query

Code Block

SELECT
AT1.TH_FISCAL_YEAR,
AT1.TH_FISCAL_WEEK,
SUM(A.AMOUNTCAST(WEEK_START_DT AS DATE) AS SALESWEEK_DT,
COUNT(DISTINCT A.TICKETREGISTERED_ACCOUNT_ID) AS TRNXSGUESTS,
COUNT(DISTINCT AT1.REGISTEREDTICKET_ACCOUNT_ID) AS GUESTSTRXNS,
SUM(T1.AMOUNT) AS SWS

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

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

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

Versions Compared

Old Version 1

New Version 2

Key