Offer Measurements Requirements 2024

Jira Initiative

https://rbictg.atlassian.net/browse/AA-3364

https://rbictg.atlassian.net/browse/AA-3378

Key Stakeholders

@Lavanya Davluri @Linsey Liao

Status

in progress

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

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

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

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

Loyalty Transactions table

We use this for getting transactions attached to an offer, associated discounts and sales, guest and transaction counts

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

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

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.

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.

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

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 ?

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