Guests Level Dataset
- Lavanya Davluri
As part of the BI initiative, we have created datasets that compile logic across our various datasets.
The dataset mentioned below is the guest-level dataset that would have transaction, loyalty, scan and pay and other metrics for each guest each week.
The goal is to map out any guest-level metric as a column in this dataset.
Data Dictionary
Here are the columns present in the data.
Databricks Table name :
loyalty.reportbi.weekly_execution_guest_level
Column | Type | Level Definitions | Data Source 1 | Comments |
TH_FISCAL_YEAR | string | EXISTING | temp.weekly_execution_trans | |
TH_FISCAL_WEEK | string | EXISTING | temp.weekly_execution_trans | |
REGISTERED_ACCOUNT_ID | string | EXISTING | temp.weekly_execution_trans | |
TRNXS_WHITELABEL_REG | long | Total volumne of distinct transaction happened in 'delivery' and 'white label delivery' (a full or partial fulfillment service offered by third parties) | temp.weekly_execution_trans | |
TRNXS_MOP_REG | long | Total volumne of distinct transaction happened in 'mobile order drive thru','mobile order eat in','mobilel order take out'. | temp.weekly_execution_trans | |
TRNXS_SCAN_REG | long | Total volumne of distinct transaction happened in 'take out','eatin','drive thru' | temp.weekly_execution_trans | |
TRNXS_CATERING_REG | long | Total volumne of distinct transaction happened in 'catering','curb side pick up' | temp.weekly_execution_trans | |
TRNXS_KIOSK_REG | long | Total volumne of distinct transaction happened in 'kiosk','kiosk takeout' | temp.weekly_execution_trans | |
SALE_WHITELABEL_REG | double | Total amount of sales happened in 'delivery' and 'white label delivery' (a full or partial fulfillment service offered by third parties) | temp.weekly_execution_trans | |
SALE_MOP_REG | double | Total amount of sales happened in mobile order drive thru','mobile order eat in','mobilel order take out'. | temp.weekly_execution_trans | |
SALE_SCAN_REG | double | Total amount of sales happened in 'take out','eatin','drive thru' | temp.weekly_execution_trans | |
SALE_CATERING_REG | double | Total amount of sales happened in 'catering','curb side pick up' | temp.weekly_execution_trans | |
SALE_KIOSK_REG | double | Total amount of sales happened in 'kiosk','kiosk takeout' | temp.weekly_execution_trans | |
CA_WEEKLY_SALE | double | sum total sales by user by week | temp.weekly_execution_trans | |
CA_WEEKLY_TRXN | long | count total transactions by user by week | temp.weekly_execution_trans | |
APP_WAU | integer | if user has Any Type Of Event on mobile app | MPARTICLE.APP_EVENTS | Mparticle data is not fully reliable and waiting to be updated |
APP_EVENTS_BASKET | string | types of events on mobile app | MPARTICLE.APP_EVENTS | Mparticle data is not fully reliable and waiting to be updated |
APP_EVENTS | long | number of events on mobile app | MPARTICLE.APP_EVENTS | Mparticle data is not fully reliable and waiting to be updated |
WAU_PURCHASING_FLAG | string | If APP_WAU IS 1, then WAU; otherwise PURCHASING | MPARTICLE.APP_EVENTS | Mparticle data is not fully reliable and waiting to be updated |
EMAIL_SUBSCRIBE_FLAG | string | subsdribed or not | loyalty.mparticle.user_attributes | data is only available till March 2023, therefore unable to filter for the most recent week |
PUSH_SUBSCRIBE_FLAG | string | subsdribed or not | loyalty.mparticle.user_attributes | data is only available till March 2023, therefore unable to filter for the most recent week |
EMAIL_EVENTS_BASKET | string | types of events on email events | loyalty.mparticle.email_events B | |
CA_WEEKLY_OFFER_RED | long | Total volumne of distinct transaction that has redeemed a weekly offer | loyalty.prodrt.curated_trans_events_new | list of weekly offers is collected from dydb.weeklyoffers table |
CA_POINTS_RED | long | Total volumne of distinct transaction that has redeemed points | loyalty.prodrt.curated_trans_events_new | list of loyalty offers is obtained using existing logic provided by businss team |
CA_WEEKLY_SP_TRXN | long | Total volumne of distinct transaction that is Scan and Pay (based on loyalty data as per business decision) | loyalty.prodrt.curated_trans_events_new | |
CA_WEEKLY_SP_SALE | double | Total amount of sales happened in Scan and Pay(based on loyalty data as per business decision) | loyalty.prodrt.curated_trans_events_new | |
PY_TH_FISCAL_YEAR | string | EXISTING | STG.DERIVED_MASTER_TABLE_NEW | |
PY_TH_FISCAL_WEEK | string | EXISTING | STG.DERIVED_MASTER_TABLE_NEW | |
CY_SALES | double | Total amount of sales happened in this week this year | STG.DERIVED_MASTER_TABLE_NEW | |
PY_SALES | double | Total amount of sales happened in this week last year | STG.DERIVED_MASTER_TABLE_NEW | |
CY_TRAFFIC | long | Total volumne of distinct transaction that happened this week this year | STG.DERIVED_MASTER_TABLE_NEW | |
PY_TRAFFIC | long | Total volumne of distinct transaction that happened this week last year | STG.DERIVED_MASTER_TABLE_NEW | |
COMPING_NONRETURNING_FLAG | string | NON-COMPING: has transaction this week this year but not this week last year; opposite to Non-returning | STG.DERIVED_MASTER_TABLE_NEW | |