Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

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

IN PROGRESS

šŸ™ Ā Current State

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

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

GOOD

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Ā 

GOOD

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

(blue star) Other Housekeeping


  • 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

  • No labels