Membership user redemptions fact table
Bi_FactMembershipUserRedemptions_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable | DimensionTableField |
AccruedDate | TIMESTAMP | date on which benefits are accrued | |||
AccuralInvoiceID | VARCHAR(36) | Invoice where benefits are accrued | |||
AccuralInvoiceItemID | VARCHAR(36) | Invoice item id within accural invoice id | |||
ActualQuantity | FLOAT | membership benefits (service) acrued on sale of membership | |||
BenefitConversionPK | DECIMAL(19,0) | unique identifier for benefits to credits conversion | |||
BenefitCreationDate | TIMESTAMP | Date on which benefits are created | |||
BenefitExpirationDate | TIMESTAMP | date on which benefits are supposed to be expired | |||
BenefitExpirationDays | INT | Number of days for which benefit is valid. | |||
BenefitRedemptionDate | TIMESTAMP | date on which benefits are redeemed | |||
CancelledDate | TIMESTAMP | date on which membership is cancelled | |||
CenterID | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 | CenterId | |
CenterIDTo_Transferred | VARCHAR(36) | center to which membership is transferred | Bi_DimCenter_s3 | CenterId | |
ClassID | VARCHAR(36) | Unique identifier for Class name | |||
ClassName | NVARCHAR(1024) | Name of the class corresponding to the session for which the guest registered | |||
ClosedBy | VARCHAR(36) | code of employee who closed the invoice | Bi_Dimuser_s3 | userid | |
ClosedDateTimeInCenter | TIMESTAMP | Date and time on which record is closed, either by sale of membership, redemption, transfer,e tc | |||
CreatedBy | VARCHAR(36) | code of the employee who created the invoice | Bi_Dimuser_s3 | userid | |
CreatedDateInCenter | TIMESTAMP | Date on which record is created at center | |||
Discount | FLOAT | discount applied on the membership | |||
EtlCreatedBy | DECIMAL(19,0) | unique identfier in Table. Exists only in Analytics | |||
EtlCreatedDate | TIMESTAMP | unique identfier in Table. Exists only in Analytics | |||
FactSegmentID | INT | unique identfier in Table. Exists only in Analytics | Yes | ||
FinalSalePrice | FLOAT | Sale price after deducting discounts and adding taxes | |||
FreeServiceRecognizedAmt | FLOAT | Revenue recognition of benefit redemption | |||
GroupInvoiceId | VARCHAR(36) | Invoice ID of the actual sale of the membership. This gets populated against all the subsequent recurring cycles and helps identify the original membership sale. In case of redemptions, this field is left blank | |||
InitiallyRecognizedAmt | FLOAT | IRR of membership | |||
Invoice_no | NVARCHAR(320) | Unique number for invoice. Unique for a center | |||
InvoiceID | VARCHAR(36) | Unique identifer for invoice | |||
InvoiceitemID | VARCHAR(36) | The Invoice item id of the membership. If the membership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to If it is redemption of service or product, then it is invoice item id of sale of service/product | |||
IsRecurringMembership | INT | Indicate type of membership : 0 - non-recurring, 1 - recurring, 2 - Set up membership | |||
ItemID | VARCHAR(36) | unique ID for an item. | Bi_DimItems_S3 | itemid | |
ItemRevenue | FLOAT | recognised revenue on amount redeemed | |||
ItemType | INT | Type of item sold or refunded. An item is usually a service, product, membership, package, gift card, prepaid card, or class | Bi_DimItemType_S3 | itemtypeid | |
MembershipBusinessUnitId | VARCHAR(36) | the business unit to which the membership belongs | |||
MembershipCategoryId | VARCHAR(36) | The category of the membership | |||
MembershipDays | INT | ||||
MembershipEndDate | TIMESTAMP | The end date of the time period corresponding to the "membership status" | |||
MembershipId | VARCHAR(36) | A unique identifier for the membership.This maps to the ID in the URL in the Membership configuration page of the core system. If the membership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to | |||
MembershipServicePrice | FLOAT | List price of service | |||
MembershipServicePriceWithoutRecognizedRevenue | FLOAT | ||||
MembershipStartDate | TIMESTAMP | The start date of the time period corresponding to the "membership status" | |||
MembershipStatus | INT | Status of the membership between "membership start date" and "membership end date" | |||
MembershipVersionId | VARCHAR(36) | A unique identifier for every version of a membership.This maps to the version ID in the URL in the Membership configuration page of the core system. | BI_DimMembership_s3 | membershipversionid | |
MonthlyRecognizedAmt | FLOAT | MRR of membership sale | |||
NoOfCyclesPaid | INT | Number of cycles of payment made for recurring memberships | |||
OrganizationID | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 | CenterID | |
OtherCreditAmount | FLOAT | total other credit amount acrued. In product, this is set as service+product credits. These credits can be used to redeem product or services. | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
Processed | SMALLINT | Invoice status | |||
ProductCreditAmount | FLOAT | total product credit amount acrued | |||
ProductID | VARCHAR(36) | unique identifier for the product sold | BI_DimProduct_s3 | ProductID | |
ProductName | NVARCHAR(2048) | Name of the product | |||
QuantityUsed | FLOAT | number of benefit points (services) used for redemption | |||
RecordCategory | VARCHAR(200) | Explains why record entry is made | |||
RedeemPostExpiry | INT | 1- redemption allowed post expiry 0 - redemption not allowed post expiry | |||
RedemptionAmount | FLOAT | amount redeemed from the acrued credits. This amount is at invoice item level | |||
ReturnDate | TIMESTAMP | refund date | |||
ReturnValue | FLOAT | refund value of membership sale | |||
SaleByID | VARCHAR(36) | ID of employee who made the sale | Bi_Dimuser_s3 | userid | |
SalePrice | FLOAT | sale price of the membership | |||
ServiceCategoryID | VARCHAR(36) | category id of the service received under membership | |||
ServiceCreditAmount | FLOAT | total service credit amount acrued | |||
ServiceId | VARCHAR(36) | unique identifer for the service received | Bi_DimService_s3 | ServiceID | |
ServiceName | NVARCHAR(1024) | Name of the service redeemed under the membership | |||
Setupfee | FLOAT | setup fee for membership | |||
SetupMembershipId | VARCHAR(36) | The usermembershipid of the Setup Membership | |||
SharedOn_Transferred | TIMESTAMP | Benift transfer date time | |||
ShowFrequencyInWeeks | BOOLEAN | ||||
SoldAtCenterID | VARCHAR(36) | Center where membership is sold | Bi_DimCenter_s3 | CenterId | |
SoldInvoiceItemID | VARCHAR(36) | first membership sale invoice item id | |||
SoldToUserID | VARCHAR(36) | unique Identifer for a guest to whom membership is sold | Bi_Dimuser_s3 | userid | |
Taxes | FLOAT | taxes applied on membership sale | |||
TotalCreditAmount | FLOAT | Sum of service+product+other credit amount | |||
TransferDetailID | VARCHAR(36) | unique identifier for benefits transfer | |||
TransferredQuantity | FLOAT | quantity of acrued benefits transferred | |||
UnitBenefitLevelPayment | FLOAT | amount recognized for each unit benefit | |||
UnusedAmount | DECIMAL(19,4) | unused amount after benefits are converted to credits and redeemed | |||
UserID | VARCHAR(36) | A unique identifier for the guest. This is a non-blank value that gets populated for every guest | Bi_Dimuser_s3 | userid | |
UserIDTo_Transferred | VARCHAR(36) | guest to whom benefits are transferred | Bi_Dimuser_s3 | userid | |
UserMembershipId | VARCHAR(36) | A unique identifier for every membership sold. If the membership type is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to | bi_dimmembershipuser_S3 | UserMembershipId | |
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
ClassCategory | VARCHAR(512) | Class category name of the session attended using membership | |||
UserGuestPassID | VARCHAR(36) | Guest Pass ID used to redeem the benefit | |||
NumberPerPeriod | INTEGER | Number of guest passes assigned on sale of membership | |||
MaxPassesAllowed | INTEGER | Maximum number of guest passes that can be shared with a single guest in a period. | |||
SessionFk | BIGINT | Identifier for the session guest/member attended to redeem guestpass/membership benefits. Join with bi_dimsession_s3 | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||