Offer Measurements Requirements 2024

Jira Initiative	AA-3378 - Getting issue details... STATUS
Key Stakeholders	Lavanya Davluri Linsey Liao
Status	DRAFT

🎯 Overview & Objective

This is a document to highlight all the data enhancements we can do to achieve automated offer measurements. The intent of this requirement is to facilitate the accurate and simplified reporting on Offers with minimal user intervention, or bespoke Offers knowledge. Currently, only a few users are familiar with the conventions, layout, implicit rules & limitations and sources of the Offers data. This requirement will allow Loyalty, as well as TH-wide users, the ability to accurately identify and measure Offer sends, activations and redemptions, and further allow users to supplement this data seamlessly with loyalty and system transactional data to create meaningful and insights-driven analyses in keeping with Tim Hortons' data democratization mandate.

📸 Current State

We use the following tables to facilitate offer measurement:

Table	Use Case	Comments
dydb.offers	Has limited metadata for all digital offers in the system	The following limitations / shortcomings exist on this table: What AUV’s and AUV Categories does the offer drive? Key field of Offer Type is not well-maintained If Definitions are changed, then we log of updated names and rules How much Discount and the tag that differentiates between points, $-off and %-off and nX is incomplete Objective of this offer - sanity field that tells us what this offer id is for e.g., lapsing, trial, cross-sell, etc. Mechanism - also comes from sanity and tells us if the mechanism is only applicable on a specific action (e.g., applicable to only Scan & Pay, MO&P, or automatically applies for birthday, etc.) We have observed that there are some missing offers in the dydb.offers table that exist in the dydb.weeklyoffers table. Offers metadata should have one single table as the source of truth
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 offers team and not maintained	This table is manually created and fulfils some of the requirements of metadata, but 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) This represents an inadequate way of determining challenging completes and is hyper-prone to erroneous reporting
prodrt.curated_trans_events_new	Loyalty Transactions table	We use this for getting transactions attached to an offer, 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_account_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	Full, detailed and well-maintained metadata on all digital offers	Enhancing our existing offer table 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 Campaign 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

🖥️ Technical Specification

To meet the objective of the initiative, Data Engineering and Advanced Analytics will support with the following:

Translate requirements into a plan with engineering activities to meet due date.
Engage with project team to clarify any assumptions or initiative objectives, as well as to provide the project team with regular updates on progress.
Complete the data governance requirements for the project.
Conduct the appropriate quality assurance (“QA”) activities to ensure data fields and definitions are accurate and comprehensive.
Provide support and ongoing maintenance of the data models.