Guests Level Dataset

Owned by Lavanya Davluri, created
Last updated: Jun 27, 2023
2 min read

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