Loyalty Points Liability data model
This data source brings in actions that result in the accrual or redemption of loyalty points.
Example Use-Cases
Loyalty points accrued/ redeemed by guest, center
Balance for a user as of a particular date
Cross center redemptions or cross center liability
Granularity of the data
At the most basic level, data is organized at an action level. Every action in an invoice that leads to the accrual or redemption of loyalty points is populated in a separate row. Further, if there are different types of tiers or programs through which loyalty points are accrued within each invoice, each row gets further split to accommodate this granularity. The total loyalty points get apportioned to the various rows. In the below example, Tom accrued 10 loyalty points as a welcome bonus on 5/21. After this action, he became part of the 'bronze' tier. The tier of the guest after the corresponding action gets populated in the 'accrual tier' field. On 5/30, he spent some money and accrued 11 more loyalty points. As a result, he was upgraded to the 'silver' tier. The 'previous tier name' field populates the tier of the guest at the beginning of the day and the 'current tier name' populates the tier of the guest at the end of the day. This does not change even if there are multiple tier changes within a day. Another guest, John accrued loyalty points through 'Promotion B' of the regular loyalty program.
 |
Group | Description | Field | Definition | Examples/Notes | Data Type |
|---|---|---|---|---|---|
Center, Organization | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | organization account name | The domain name of the organization in Zenoti. | If you use this URL to log in to Zenoti 'https://massagespa.zenoti.com', then "massagespa" is your account name. The formal name and domain name may be different. | varchar(256) |
Center, Organization | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | accrual center name | The center where loyalty points are accrued. This is populated only when 'record type name' is 'Accrual'. | varchar(256) | |
Center, Organization | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | payment center name | The center where the payment of the redeemed item is received. Usually, this is the center where the loyalty points are redeemed. This is populated only when 'record type name' is 'Redemption'. | varchar(256) | |
Center, Organization | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | accrual center minutes to add | The number of minutes that need to be added to UTC time to get the current time at the accrual center. Timestamps that are generated by Tableau [E.g. if you use filters such as Today, Tomorrow, etc.] are in UTC, hence this field is needed to arrive at the center time. This is not needed for timestamps that are in the datasource directly- these are already in the center time zone. | integer | |
Center, Organization | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | payment center minutes to add | The number of minutes that need to be added to UTC time to get the current time at the payment center. Timestamps that are generated by Tableau [E.g. if you use filters such as Today, Tomorrow, etc.] are in UTC, hence this field is needed to arrive at the center time. This is not needed for timestamps that are in the datasource directly- these are already in the center time zone. | integer | |
Guest | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | user id | A unique identifier for the guest who accrued or redeemed the loyalty points. This is a non-blank value that gets populated for every guest. | varchar(36) | |
Guest | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | guest first name | First name of the guest who accrued or redeemed the loyalty points. | varchar(160) | |
Guest | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | guest middle name | Middle name of the guest who accrued or redeemed the loyalty points. | varchar(128) | |
Guest | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | guest last name | Last name of the guest who accrued or redeemed the loyalty points. | varchar(256) | |
Guest | These fields refer to two types of centers. 1. Accrual Center: Center where the loyalty points are accrued. 2. Payment Center: Center where the payment of the redeemed item is received. The fields in this group detail the various attributes of the accrual center and payment center. | guest base center | Base center of the guest. This is where the guest's profile was created. | varchar(256) | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | record type name | Indicates whether the action led to an accrual or redemption of loyalty points. | varchar(64) | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | guestpointsid | This is a unique identifier generated for all actions which result in accrual of loyalty points. For redemption records, the corresponding accrual action's guestpointsid is populated. This field can be used to map redemption of loyalty points to the corresponding accrual record. It is particularly useful to calculate cross center redemptions. | User A has accrued 100 loyalty points for which "ab3cde" is assigned to guestpointsid. Say User A redeems 75 of these points for a service. For this redemption record- guestpointsid "ab3cde" is populated. | varchar(36) |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | action date | The date on which loyalty points are accrued or redeemed. | date | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | action name | The action using which loyalty points are accrued or redeemed. | Below are actions that can lead to accrual or redemption of loyalty points. These can be configured at organization level: 1. Purchase a service 2. Welcome bonus 3. Amount spent - Anniversary 4. Purchase a gift card 5. Purchase of membership by referred guest 6. Purchase a Series and Custom package 7. First visit and payment 8. Purchase a membership 9. Purchase of series and custom package by referred guest 10. Rebook a service 11. Online - Sign up for a Class 12. Provide feedback 13. Amount spent- Birthday 14. Purchase a day package 15. Online - Share feedback with other guests 16. Online - Signup 17. Purchase a class 18. Purchase a product 19. Amount spent 20. Online - Share feedback with friends 21. Referring a new guest 22. Employee Grooming Points 23. Online - Book an appointment 24. Imported Points | varchar(128) |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | isreferral | This flag is set to 1 when a guest accrues loyalty points because of the referral of another guest. For example, Guest A refers Guest B and as a result Guest A accrues loyalty points. For this action, this flag will be set to 1. Some programs are set up such that all purchases by Guest B lead to accrual of loyalty points for Guest A. For all these actions, this flag will be set to 1 again. The flag helps identify that Guest A has accrued loyalty points, but they were due to either referring a guest or an action by the referred guest. In this case, the action details and invoice details correspond to the action of the referred guest. | integer | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | Loyalty Points | Number of loyalty points accrued or redeemed with the particular action. The number is positive for accrual and negative for redemptions. | FLOAT | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | effective start date | The date from which the accrued loyalty points can be redeemed. If the value is null, the loyalty points can be used at any point in time. | timestamp | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | effective end date | The date on which the accrued loyalty points expire. If the value is null, the loyalty points don't have any expiry date. | timestamp | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | program name | Name of the program using which loyalty points are accrued or redeemed. | varchar(512) | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | previous tier name | Name of the tier that the user is associated to at the beginning of the day. | varchar(512) | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | current tier name | Name of the tier that the user is associated to at the end of the day. | varchar(512) | |
Loyalty Points | These fields detail the attributes of the guest who accrued or redeemed the loyalty points. | accrual tier name | Name of the tier that the user is associated to after the corresponding action. | varchar(512) | |
Invoice | This group of fields detail out the invoice associated with the items. | invoiceid | A unique identifier for the invoice. It is unique within the organization. | varchar(36) | |
Invoice | This group of fields detail out the invoice associated with the items. | invoiceno | A unique identifier for the invoice. It is unique within the center. | varchar(40) |