...
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 :
and to filter for Canada only accounts use : |
transactionID | Same as transaction ID in | |
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 |
|
...
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 : |
| ||||||
RUTW BONUS | These are for the RUTW bonus - Historically these were roll up MO&P only offers | |||||||
Hockey | Tag exists : | 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 : |
| ||||||
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 | 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
...