Page Comparison

Has all of the metadata for all digital offers in the system

• We do not have complete data to determine the following things :

  • What AUV Categories does the offer drive

  • If Definitions are changed then log of different names and rules

  • How much Discount and the tag that differentiates between points and $ and % and nX is incomplete

  • Objective of this offer - sanity field that tells us what this offers id for .. eg- lapsing , trial , cross sell etc..

  • Secondary Category of Offer Mechanism - also comes from sanity and tells us if the mechanism is only applicable on a specific action ( eg - applicable to only scan and pay / or automatically aplies for birthday etc. )

  • We have observed that there are some missing offers in the dydb.offers table that exist in the sysb.weeklyoffers table ( checking this and making sure its not a timing issue when new offers are created )

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 offer teams and not maintained

• this table is manually created and fulfils some of the requirements of metadata, but ideally , 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 )

Loyalty Transactions table

We use this for getting transactions attached to an offers , 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 toeasily 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_accoun_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

its a Fact table on each offer

Enhancing our existing offer tablt tablet 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

offerId

xxxxxx-xxxx-xxxx-xxxx-xxxxxxx

description

CA-VALUE-$9.99 ICED COFFEE AND LOADED WRAP AND DONUT-SINGLEUSE-ALL-OFFER

stackable

False

if the offer can be used with another discount/offer in the same ticket

type

WeeklyOffer

Which Camapain is supposed to trigger this offer ?

offerPlu

7xxxxx2

ID to match to transactions

Objective

Crossell

From Sanity what is the primary objective of this offers eg. trial , lapsing , birthday etc..

Products

COLDBEVERAGES,MAINFOODS

Product categories that the offer is designed to dive

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

Discount Type

$ Value Reduced

if its $ , bonus points , 2X or % off , Bogo , Buy 2 get $ off , but 2 get one free .. etc

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

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

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 ?