Versions Compared

Old Version 10

changes.mady.by.user Rayand Ramlal

Saved on

compared with

New Version Current

changes.mady.by.user Lavanya Davluri

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Jira Initiative

Jira Legacy
serverSystem Jira
serverId255417eb-03fa-3e2f-a6ba-05d325fec50d
keyAA-3364

Jira Legacy
serverSystem JIRAJira
serverId255417eb-03fa-3e2f-a6ba-05d325fec50d
keyAA-3378

Key Stakeholders

Lavanya Davluri Linsey Liao

Status

Status
colourYellow
titleDRAFTin progress

🎯 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

  • 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 :

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 ?

...