Membership user redemptions fact table

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

float8

membership benefits (service) acrued on sale of membership

benefitconversionpk

bigint

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

integer

Number of days for which benefit is valid.

benefitredemptiondate

timestamp

date on which benefits are redeemed

cancelleddate

timestamp

date on which membership is cancelled

centeridto_transferredwid

bigint

center to which membership is transferred

bi_dimcenter

centerwid

centerwid

bigint

unique identifier for a center in an organization

bi_dimcenter

centerwid

classid

varchar(36)

Unique identifier for Class name

classname

varchar(256)

Name of the class corresponding to the session for which the guest registered

closebywid

bigint

code of employee who closed the invoice

closeddatetimeincenter

timestamp

Date and time on which record is closed, either by sale of membership, redemption, transfer,e tc

createdbywid

bigint

code of the employee who created the invoice

createddateincenter

timestamp

Date on which record is created at center

discount

float8

discount applied on the membership

etlcreatedby

bigint

unique identfier in Table. Exists only in Analytics

etlcreateddate

timestamp

unique identfier in Table. Exists only in Analytics

factsegmentid

integer

unique identfier in Table. Exists only in Analytics

factsegmentwid

bigint

unique identfier in Table. Exists only in Analytics

YES

finalsaleprice

float8

Sale price after deducting discounts and adding taxes

freeservicerecognizedamt

float8

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

float8

IRR of membership

invoice_no

varchar(80)

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

integer

Indicate type of membership : 0 - non-recurring, 1 - recurring, 2 - Set up membership

itemrevenue

float8

recognised revenue on amount redeemed

itemtypewid

bigint

Type of item sold or refunded. An item is usually a service, product, 

membership, package, gift card, prepaid card, or class

itemwid

bigint

unique ID for an item.

membershipbusinessunitid

varchar(36)

the business unit to which the membership belongs

membershipcategoryid

varchar(36)

The category of the membership

membershipdays

integer

Number of days from sale date to expiry

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

float8

List price of service

membershipservicepricewithoutrecognizedrevenue

float8

List price of service excluding recognized revenue

membershipstartdate

timestamp

The start date of the time period corresponding to the "membership status"

membershipstatus

integer

Status of the membership between "membership start date" 

and "membership end date"

membershipversionwid

bigint

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.

monthlyrecognizedamt

float8

MRR of membership sale

noofcyclespaid

integer

Number of cycles of payment made for recurring memberships

organizationid

varchar(36)

A unique identifier for the organition for which this data belongs

othercreditamount

float8

total other credit amount acrued. In product, this is set as service+product credits. These credits can be used to redeem product or services.

podid

integer

Internal to the ETL, says from which POD data is being synced the table

podname

varchar(20)

Internal to the ETL, says from which POD data is being synced the table

processed

smallint

Invoice status

productcreditamount

float8

total product credit amount acrued

productname

varchar(512)

Name of the product

productwid

bigint

unique identifier for the product sold

quantityused

float8

number of benefit points (services) used for redemption

recordcategory

varchar(50)

Explains why record entry is made

redeempostexpiry

integer

1- redemption allowed post expiry

0 - redemption not allowed post expiry

redemptionamount

float8

amount redeemed from the acrued credits. This amount is at invoice item level

returndate

timestamp

refund date

returnvalue

float8

refund value of membership sale

salebywid

bigint

ID of employee who made the sale

saleprice

float8

sale price of the membership

servicecategoryid

varchar(36)

category id of the service received under membership

servicecreditamount

float8

total service credit amount acrued

servicename

varchar(256)

Name of the service

servicewid

bigint

unique identifer for the service received

setupfee

float8

setup fee for membership

setupmembershipid

varchar(36)

The usermembershipid of the Setup Membership

sharedon_transferred

timestamp

Benift transfer date time

showfrequencyinweeks

integer

Indicates if redemption frequency is based on weeks

soldatcenterwid

bigint

Center where membership is sold

soldinvoiceitemid

varchar(36)

first membership sale invoice item id

soldtouserwid

bigint

unique Identifer for a guest to whom membership is sold

taxes

float8

taxes applied on membership sale

totalcreditamount

float8

Sum of service+product+other credit amount

transferdetailid

varchar(36)

unique identifier for benefits transfer

transferredquantity

float8

quantity of acrued benefits transferred

unitbenefitlevelpayment

float8

amount recognized for each unit benefit

unusedamount

numeric(18,4)

unused amount after benefits are converted to credits and redeemed

useridto_transferredwid

bigint

guest to whom benefits are transferred

usermembershipwid

bigint

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

userwid

bigint

A unique identifier for the guest. This is a non-blank value that gets populated for every guest

void

boolean

Indicates if the row should be inserted or deleted. 1- delete, 0-insert

BenefitsIgnored

SMALLINT

Flag to indicate if benefits are ignored in cases where membership fee is waived

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.

Dimsessionwid

DECIMAL(19,0)

Identifier for the session guest/member attended to redeem guestpass/membership benefits. Join with bi_dimsession_s3

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

groupid

Internal to Zenoti. Used for data processing.

BIGINT