...
Jira Initiative |
| ||||||||||||||||
Key Stakeholders | |||||||||||||||||
Status |
|
🎯 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 in data to use toward facilitate offer measurement.:
Table | Use Case | Comments |
---|---|---|
dydb.offers | Has |
limited metadata for all digital offers in the system |
|
|
|
|
|
|
|
|
|
|
|
| ||
dydb.weeklyoffers | Offers list that is sent out by the weekly targeted offer models |
|
---|
|
| |
dydb.new_offer_product_mapping_added_l3_new_names | Created manually by the |
---|
offers team and not maintained |
|
| ||
prodrt.curated_points_events | Table of all points transactions |
|
---|
| ||
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 |
|
---|
| ||
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 |
---|---|---|
ANALYTICS.scan_and_pay_offer_20230703
| 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 Metadataits | a Fact table on each offerFull, detailed and well-maintained metadata on all digital offers | Enhancing our existing offer tablt 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 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 :
Note |
---|
Team deferred the request stating that, there would be no further development in sanity, and this would be possible once we migrate the offer creation process from Sanity to Adobe in Q2 of 2025. |
Column | Eg. Data | Definition | Sanity |
---|---|---|---|
name | $9.99 for Iced Coffee, Loaded Wrap and a Donut | Name | |
offerId | xxxxxx-xxxx-xxxx-xxxx-xxxxxxx | Header | |
description | CA-VALUE-$9.99 ICED COFFEE AND LOADED WRAP AND DONUT-SINGLEUSE-ALL-OFFER | Description/Internal Name | |
stackable | False | if the offer can be used with another discount/offer in the same ticket | Offer Type > <rules from https://rbioffice-my.sharepoint.com/:x:/g/personal/jwang_timhortons_com/EeJ8PSruV0pLiAXYhp-ZI7EBFg0mbMRjyS0ioOe1zD_nGg?e=8ScDTn > cannot include as a flag |
type | WeeklyOffer | Which Campaign is supposed to trigger this offer ? | only can distinguish between global and acquisition |
offerPlu | 7xxxxx2 | ID to match to transactions | TPN/PLU - there are duplicates , only in future state we will have unique PLU for each offer, 90% of active offers have unique PLU |
Objective | Crossell | From Sanity what is the primary objective of this offers eg. trial, lapsing, birthday, etc. | We do not have this information |
Products | COLDBEVERAGES, MAINFOODS | Product categories that the offer is designed to dive | Ruleset > main item > QST PLU and Combo Slot > QST PLU this might be the feild to get and then get the lvl 3 from a mapping table |
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 | can get it from last_published_date |
Discount Type | $ Value Reduced | if its $ , bonus points , 2X or % off , Bogo , Buy 2 get $ off , but 2 get one free .. etc | incentives>action, discount_type |
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 | incentives>action, discount_value |
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 | redemption methods if no method is defined , its available for all channels |
🥅 Sends Table example fields : [DONE]
Tip |
---|
Offer Send Logging Table Schema - Evan Lin - Confluence (atlassian.net) |
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.