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.
...
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 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. |
...