Versions Compared

Old Version 11

changes.mady.by.user Lavanya Davluri

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

As of may 29th 2023 , please use this link for Points table : /wiki/spaces/THAA/pages/4140073544

This document highlights and lists the ongoing process of points tracking, and points data table build.

Objective

Create a dataset to have line of sight on points issued

Driver

Lavanya Davluri

Approver

Igor Rahal Linkewitsch

Data Engineering

Anirudha Porwal

Contributors - Technology

Murilo Mello

Informed

Zahra Lakhani

Due Date

April 30, 2023

Key Outcomes

Created a dataset for full line of sigts on points issued

Status

Status
colourYellow
titleIn Progress

🐙  Current State

We have the following table that we currently use for points tracking: prodrt.curated_points_events

Columns in the table and column definitions

Column

Definition

Tips

barcode

this is the scanned barcode on a loyalty account

to join with loyalty Account ID, Use :

REGEXP_EXTRACT(BARCODE, '(\\d+)|(\\d+)', 0) as Loyalty_account_id

and to filter for Canada only accounts use : left(barcode,4) = '0463'

transactionID

Same as transaction ID in PRODRT.CURATED_TRANS_EVENTS_NEW

timestamp

unix timestamp

pointsUsed

This is the number of points used in a team transaction

pointsEarned

This is the number of points earned for that transaction

tag

See the distinct tags list below

restaurant

Restaurant number

pointsRemoved

these are removed points either because of expiry or testing

isPointsUsed

Flag for if the points were used in the transaction

Points can be earned and used in the same transaction on base points earned ,points offers , and challenges

isPointReceived

This would be false for when a transaction only has points used and no earns

partition_date_key

Date

TO_DATE(PARTITION_DATE_KEY, "yyyyMMdd") to cast it in the yyyy-mm-dd format

Tag

Comments

Points (as of 16th Jan 2023)

3.0 Changes

null 

This is a catchall tag

15,878,433,750

HOCKEY_1

Win 10 points in Hockey

 247,265,120

RUTR_CAN_22

Roll up 2022

44,762,830

HOCKEY_2 

Win 50 Points in Hockey

179,739,640

HOCKEY_3 

Win 70 Points in Hockey

44,842,230

Offer Share

Points Shared to other user - Would not be part of earn , but only removed ?

null

PRODUCT_CHALLENGED_COMPLETED 

Product Challenge

54,810,750

FREQUENCY_CHALLENGED_COMPLETED 

Frequency Challenge

70,219,639

REGISTRATION 

28,245,760

RUTR_CAN_21_2

RUTW 2021 - Fall

199,928,460

POINTS_EXPIRED 

null

MIGRATION_30 

RUTR_CAN_21

RUTW 2021 - Spring

405,589,640

DAYPART_CHALLENGED_COMPLETED 

FrequencyDaypart Challenge

34,632,305

REFUND_2021_10 

22,588,327 

  <BLANK>

1,888,810

CHALLENGE_COMPLETE 

Do not use

640

SURVEY 

35,220

THUSA_Unregistered_Sweepstake

136,006

SURVEY_POINTS

24,570

[AMIT TO PROVIDE]

Do not use

10

HOCKEY_0 

Hockey - Did not Win

10

TEST 

Do not use

10

PRODUCT_CHALLENGE_COMPLETED

Do not use

280 

🥹 Pain points

  1. Some tags are missing and getting captured in the null tag in the data

  2. No line of sight to support points being issued

  3. No differentiation between 2.0 Points and 3.0 points, as points are adjusted at different multipliers in offers, but the tags being used are the same

🎯 Future State

Listed below are the tags that would give us the line of sight for reporting on campaigns and keeping track of the points bank

💸 On Issuance :

2023 Campaigns

Definition

Comments

Base

this is all transaction base points being issued

This should only account for transaction points and not any bonus points on transactions

RUTW

Tag Exists : RUTR_CAN_23

Status
colourGreen
titleGood

RUTW BONUS

These are for the RUTW bonus - Historically these were roll up MO&P only offers

Hockey

Tag exists : HOCKEY_0, HOCKEY_1, HOCKEY_2, HOCKEY_3

There is no differentiation between 2.0 and 3.0 points and Hockey Points (X5) use a lower multiplier than base points(X6.2)

Challenges

Tags Exist : PRODUCT_CHALLENGED_COMPLETED ,FREQUENCY_CHALLENGED_COMPLETED ,DAYPART_CHALLENGED_COMPLETED 

Status
colourGreen
titleGood

Points Offers*

These are for any points offers in our points library gets sent either weekly or through triggers

Need tags for points offers , if we are going to use offerID to tag the table then we do not need definitions of weather whether a point offer is weekly or triggered, but if we make changes to the offer definitions then it would still be difficult to consolidate these offerID tags on offer changes

Support

This is what has been issued as a result of guest care team reimbursements

Need tags and more definitions of what are the channels for points being issued by Guest care VS in restaurant and other teams

2XGlobal

Not Tagged , this is a global offer and needs to be tagged as 2X_Global

Offer id : a0b7eca9-d979-44e7-8051-72a3f734d282

2X_HFLC

Not Tagged

Offer id : d05ee406-1650-4b97-926d-444d32012583

👛 On Redemption:

Can redemptions be tracked on the points table?

(blue star) Solutions - To be Discussed

  • Add more tags for better line of sight on point issuance

  • Create a way to differentiate between 2.0 points and 3.0 Points

  • Create a Process for new tag creation for points campaigns

  • Create BI to make points bank available for teams


🕵️‍♂️ Murilo Mello

  • Restaurant Purchase points:

    • Base points, global offers and points offers will be tagged using the offer id

    • i.e.: TBD

  • Support Points:

    • Tag should be SUPPORT_POINTS

📸 Snaphot of Points Table : April 11th , 2023

Points .xlsx

...