Membership dunning fact table
Bi_FactMembershipDunning_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
Amount | FLOAT | Sale Price | |||
CenterId | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 centerid | ||
CollectedDate | TIMESTAMP | date on which amount is collected | |||
CollectionStatus | INT | tells about status of amount collection | |||
CycleNumber | INT | tells about the recurring cycle number for which the collection is attempted | |||
Discount | FLOAT | discount on the sale price | |||
Duration | INT | Number of "duration types" for a cycle. Join with "Duration type" field. Number of days or months for a cycle | |||
DurationType | INT | tells if payment cycle is in days or months | |||
ExpectedCollectionDateInFreezePeriod | TIMESTAMP | Incase there is a freezing period, collection date gets extended. This field tells when is the next collection date in case of Freeze scenarios | |||
FactMembershipDunningId | DECIMAL(19,0) | unique identfier for the row in the table | Yes | ||
FinalSalePrice | FLOAT | Final sale price after deducting "discount" from "Sale price" | |||
InvoiceId | VARCHAR(36) | Id for the invoice generated | |||
InvoiceItemId | VARCHAR(36) | item IDs for the items within an invoice | |||
LastTriedOn | TIMESTAMP | This field tells when was the last attempt made to collect amount. | |||
MembershipEndDate | TIMESTAMP | Date on which membership ends | |||
MembershipStartDate | TIMESTAMP | Start date of membership | |||
MembershipVersionId | NVARCHAR(MAX) | Unique identifier for membership version | |||
NextCollectionDate | TIMESTAMP | Tells when is the next collection date in recurring membership scenarios | |||
NextTryAt | TIMESTAMP | next try to collect payments in case there is a failure. Usually multiple attempts are made to collect amount based on settings in the product. This field tells when will the next attempt to collect payment be in case of failed attempts. While these attempts are made, "collection status" will show as "Pending". Once all attempts are completed and if customer is still unable to receive payment, "collection status" will change to "Failed" | |||
OrganizationId | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 OrganizationId | ||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
RecurringCollectionPK | DECIMAL(19,0) | PK value for table | |||
Tax | FLOAT | Tax on the final sale price | |||
UserId | VARCHAR(36) | user id of the guest. Same across all data | Bi_Dimuser_s3 userid | ||
UserMembershipId | VARCHAR(36) | Membership id of the membership user bought. Unique for each guest even if same membership is bought | Bi_DimMembershipUser_s3 UserMembershipId | ||
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
factrecordtype | VARCHAR(400) | Indicates fee type like recurring collection, annual fee, setup fee, etc | |||
groupinvoiceid | VARCHAR(36) | Unique identifier for group invoice | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||
InvoiceNo | VARCHAR(160) | Invoice number that helps in identifying and tracking the invoice. | |||
collectioninitiateddate | timestamp | ||||
nooftries | integer | ||||
collectionmode | varchar(400) | ||||