Offer Measurements Requirements

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.. Secondary Category of Offer - 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 toeasily 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 tablt 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.