Loyalty points fact table
Bi_FactLoyaltyPoints_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
AccrualCenterID | NVARCHAR(MAX) | The unique identifier of the centre where the points were accrued.(Bi_DimCenter) | Bi_DimCenter_s3 centerid | ||
AccrualTierID | NVARCHAR(MAX) | The unique identifier of the tier that the user is associated to after the corresponding action.(Bi_DimOrgLPTier) | Bi_DimOrgLPTier_s3 TierId | ||
ActionDate | DATE | The date on which loyalty points are accrued or redeemed. | |||
ActionID | NVARCHAR(MAX) | The unique identifier of the action using which loyalty points are accrued or redeemed.(Bi_DimLoyaltyPointActions) | Bi_DimLoyaltyPointActions_s3 ActionId | ||
CenterId | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 centerid | ||
EffectiveEndDate | TIMESTAMP | The date on which the accrued loyalty points expire. If the value is null, the loyalty points don't have any expiry date. | |||
EffectiveStartDate | TIMESTAMP | 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. | |||
GuestID | VARCHAR(36) | A unique identifier generated for the Guest.(Bi_DimUser) | Bi_Dimuser_s3 userid | ||
GuestPointsId | VARCHAR(36) | 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. | |||
InvoiceID | VARCHAR(36) | A unique identifier for the invoice. It is unique within the organization. | |||
InvoiceNo | VARCHAR(160) | A unique identifier for the invoice. It is unique within the center. | |||
LoyaltyPoints | FLOAT | Number of loyalty points accrued or redeemed with the particular action. The number is positive for accrual and negative for redemptions. | |||
LoyaltyPointsId | DECIMAL(19,0) | A unique identifier(integer value) for each record generated in the fact table. | Yes | ||
OrganizationID | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 centerid | ||
PaymentCenterID | NVARCHAR(MAX) | The unique identifier of the centre 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'.(Bi_DimCenter) | Bi_DimCenter_s3 centerid | ||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
ProgramID | NVARCHAR(MAX) | Unique Identifier of the program using which loyalty points are accrued or redeemed.(Bi_DimLoyaltyPointPrograms) | Bi_DimLoyaltyPointPrograms_s3 ProgramId | ||
RecordTypeID | SMALLINT | The identifier that indicates whether the action led to an accrual or redemption of loyalty points. | |||
REFPoints | BOOLEAN | 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. | |||
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||