Jira Initiative |
| ||||||||||||||||
Key Stakeholders | |||||||||||||||||
Status |
|
...
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. |
...
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]
/wiki/spaces/~617476157/pages/4551214177
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 ? |
...