Offer Measurements Requirements 2024

Overview

This is a document to highlight all the data enhancements we can do to achieve automated offer measurements

📸 Current State

We use the following tables in data to use toward offer measurement.

Table	Use	Comments
dydb.offers	Has all of the metadata for all digital offers in the system	We do not have complete data to determine the following things : What AUV Categories does the offer drive If Definitions are changed then log of different names and rules How much Discount and the tag that differentiates between points and $ and % and nX is incomplete Objective of this offer - sanity field that tells us what this offers id for .. eg- lapsing , trial , cross sell etc.. Mechanism - also comes from sanity and tells us if the mechanism is only applicable on a specific action ( eg - applicable to only scan and pay / or automatically aplies for birthday etc. ) We have observed that there are some missing offers in the dydb.offers table that exist in the sysb.weeklyoffers table ( checking this and making sure its not a timing issue when new offers are created )
dydb.weeklyoffers	Offers list that is sent out by the weekly targeted offer models	We do not have a log of when an offer is sent out Vs not , we rely on redemptions to gather that information we have a snapshot of current week, where a flag set to 1 signifies that the offer is currently active
dydb.new_offer_product_mapping_added_l3_new_names	Created manually by the offer teams and not maintained	this table is manually created and fulfils some of the requirements of metadata, but ideally , we would need not to rely on manual tables and rely only on the offer table for metadata
prodrt.curated_points_events	Table of all points transactions	Apart from its primary purpose of being the points transactional table, This is the only table that determines if a guest has completed a challenge (i.e points were issued under the “challenge complete” tag )
prodrt.curated_trans_events_new	Loyalty Transactions table	We use this for getting transactions attached to an offers , associated discounts and sales, guest and transaction counts
analytics.base_offer_log	Log table created to be a table to determine a log of all offers sent to a guest	This table is unusable at this stage as there are missing fields and there is no way to easily distinguish between campaigns
analytics.ca_challenge_campaign_log	Log table created to be a table to determine a log of all challenges sent to a guest	We use this table and should be used as a guide on how to scale this table to include all offers being sent out and not just the challenges
`offers.historical_tiered_approach_final_output` `offers.historical_tiered_approach_final_output_dynamic` `ds.incentive_tool_offers_send_table_historical` `ds.incentive_tool_offers_send_table_historical` ANALYTICS.scan_and_pay_offer_20230703 ANALYTICS.scan_and_pay_offer_20230717 ANALYTICS.scan_and_pay_offer_20230731 analytics.scan_and_pay_offer_20230814_400_points analytics.scan_and_pay_offer_20230814_600_points analytics.scan_and_pay_offer_20230828_200_points analytics.scan_and_pay_offer_20230828_400_points analytics.scan_and_pay_800_points_20230925 analytics.scan_and_pay_free_coffee_group2_20231016 analytics.scan_and_pay_free_coffee_group2_20231023 analytics.scan_and_pay_free_coffee_group2_20231030 analytics.scan_and_pay_free_coffee_20231016 analytics.scan_and_pay_free_coffee_20231023 analytics.scan_and_pay_free_coffee_20231030 analytics.scan_and_pay_free_coffee_20231030 analytics.guest_scan_and_pay_retention_guests_offer_20231016 analytics.guest_scan_and_pay_retention_guests_offer_20231023 analytics.guest_scan_and_pay_retention_guests_offer_20231030 `ds.high_churn_risk_user_offer_history` `analytics.trigger_offer_30_60` `analytics.trigger_offer_90plus` + more for each campaign send	Send Tables - These are table that have information on who were sent which offer	The list is not exhaustive and there are many more tables that are created each week Most of these tables only have registered_accoun_ids and rely on the table name to determine which offer/campaign it relates to The Goal would be clean up all these tables and only have one table that automatically logs each send.

😇 Future State

Table	Definition	Comments
Offers Metadata	its a Fact table on each offer	Enhancing our existing offer tablet to have fields flow in from sanity to have a full view on each offer
Sends	Combined table and a single source to get who was sent which offer , when and why	Combined table ( use cleanup on existing log table ) to get a view on who was sent what each week. This should ideally have what a guest would be seeing in their tray each week
Universal Control	each week a holdback group that will not receive any offer and can be used as a baseline control	Table that contains the guest list who are in control in that week.

🏦 Offers Table fields we need :

Column	Eg. Data	Definition
name	$9.99 for Iced Coffee, Loaded Wrap and a Donut
offerId	xxxxxx-xxxx-xxxx-xxxx-xxxxxxx
description	CA-VALUE-$9.99 ICED COFFEE AND LOADED WRAP AND DONUT-SINGLEUSE-ALL-OFFER
stackable	False	if the offer can be used with another discount/offer in the same ticket
type	WeeklyOffer	Which Camapain is supposed to trigger this offer ?
offerPlu	7xxxxx2	ID to match to transactions
Objective	Crossell	From Sanity what is the primary objective of this offers eg. trial , lapsing , birthday etc..
Products	COLDBEVERAGES,MAINFOODS	Product categories that the offer is designed to dive
TotalQTY	3	How many total quantity of items do you need to qualify for this offer
SNACK/BAKED	1	How many of snack/baked quantities are required to be added to qualify for this offer
BREAKFAST FOODS	0	How many of Breakfast Foods quantities are required to be added to qualify for this offer
COLD BEVERAGES	1	How many of Cold Beverage quantities are required to be added to qualify for this offer
HOT BEVERAGES	0	How many of Hot Beverage quantities are required to be added to qualify for this offer
MAIN FOODS	1	How many of Main Foods quantities are required to be added to qualify for this offer
Last Changed at	<Date>	If the offer definitions have ever changed than when ? NOTE: if we are going to create new offerids when the definitions change this wont be necessary
Discount Type	$ Value Reduced	if its $ , bonus points , 2X or % off , Bogo , Buy 2 get $ off , but 2 get one free .. etc
Discount Value	Avg Discount of $2	it can be net $ value or point value in discount that a guest would receive when this offer is applied
Mechanism	Qualifying Items	If the offer has any special trigger mechanism .. eg. only applicable if you use scan and pay , if you add all of the qualifying items , only for white label deliveries, only MO&P

🥅 Sends Table example fields :

Column	Eg. Data	Definition
campaign_id	ca_challenge_20231211	Unique name for each manually triggered campaign or autogenerated <campaign name ><date> for automated campaigns
offerStartDate	20231211	This should be the data when the offer would start appearing in the guest tray
offerEndDate	20231224	This should be the data when the offer would start expire in the guest tray
offerId	76290e2e-1783-442f-8ff6-a2b16900a34e	The offer which was sent
registered_account_id	us-east-1:00001188-42ca-4547-9b0b-xxxxxxxxxx	Guest identifier
test_control_flag	test	This would always be test (except for historical campaigns)
segment_name	notActive	segment name given by team creating the send
Campaign_type	Lapsed	What mechanism/rules triggered this offer ?

Universal Control :

Column	Eg. Data	Definition
Control Generation Date	1/22/2024	When was this control group generated ?
Start Date	1/22/2024	when is this control in affect , any campaign running between the start and end date need to exclude these guests as control
End Date	1/29/2024	when does the control period end for these guests
Registered_Account_Id	xxxxxx-xxxxx-xxxx-xxxxxxxxxx	Account Id
IsBirthday?	FALSE	True or false flag on the birthday as that offer will be sent even if you are in control
Pre_period_weekly_Cheque	$5	prior week cheque
Pre_period_weekly_Freq	2	prior week frequency
pre_period_weekly_Spend	$10	prior week spend