• Done

    • Points Bank - Points Table [DONE]

    Owned by Lavanya Davluri, created
    Last updated: Jul 13, 2023
    3 min read

    As of may 29th 2023 , please use this link for Points table :

    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

    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

    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

    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

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

     

    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