Membership user fact data model
field name | field description | Example | field type |
accuralinvoiceid | Invoice where benefits are accrued |
| varchar(36) |
accuralinvoiceitemid | Invoice item id within accural invoice id |
| varchar(36) |
actual quantity | membership benefits (service) acrued on sale of membership |
| float8 |
benefit accrued date | date on which benefits are acrued |
| timestamp |
benefit expiration date | date on which benefits are supposed to be expired |
| timestamp |
benefit redemption date | date on which benefits are redeemed |
| timestamp |
benefitconversionpk | unique identifier for benefits to credits conversion |
| bigint |
cancelled date | date on which membership is cancelled |
| timestamp |
center company name | The name with which the center is registered. Center is the center name mentioned in "Center name" field | Businesses, especially in a franchisee model, may register each center as a different company, such details are tracked in the Centers > General tab | varchar(128) |
center name | The center where the record entry is made | If record is for membership sale, center name is where membership is sold. If record is for redemption, then center name is where membership is redeemed | varchar(128) |
class id | Unique identifier for Class name |
| varchar(36) |
class name | Name of the class corresponding to the session for which the guest registered |
| varchar(256) |
closed by user - code | code of employee who closed the invoice |
| varchar(128) |
closed by user code | code of the employee who closed the record |
| varchar(128) |
closed by user id | unique identifire of an employee who closed the record |
| varchar(36) |
closed date time in center | Date and time on which record is closed, either by sale of membership, redemption, transfer,e tc |
| timestamp |
created by user - code | code of the employee who created the invoice |
| varchar(128) |
created by user code | code of the employee who created the invoice for the record |
| varchar(128) |
created by user id | unique identifier of the employee who created the record |
| varchar(36) |
created date in center | Date on which record is created at center |
| timestamp |
discount | discount applied on the membership |
| float8 |
employee - code | Unique code of the employee marked as primary 'Sold by'. This is the code taken from the employee's profile in Zenoti |
| varchar(128) |
employee code | unique identifier of the employee |
| varchar(128) |
employee first name | First name of the employee |
| varchar(160) |
employee gender | Gender of the employee |
| varchar(14) |
employee job code | Job code of the employee |
| varchar(1020) |
employee job name | Job name of the employee |
| varchar(1020) |
employee last name | last name of the employee |
| varchar(256) |
employee middle name | middle name of the employee |
| varchar(128) |
employee mobile phone | mobile number of employee |
| varchar(64) |
etllastupdatedby | unique identfier in Table. Exists only in Analytics |
| varchar(50) |
etllastupdatedon | unique identfier in Table. Exists only in Analytics |
| timestamp |
factsegmentid | unique identfier in Table. Exists only in Analytics |
| integer |
factsegmentwid | unique identfier in Table. Exists only in Analytics |
| bigint |
final sale price | Sale price after deducting discounts and adding taxes |
| float8 |
group invoice id | 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 |
| varchar(36) |
guest base center | base center of the guest |
| varchar(256) |
guest city | The city to which the guest belongs. This is a non-mandatory value and it can be blank. |
| varchar(128) |
guest code | unique code of guest |
| varchar(128) |
guest country name | The country to which the guest belongs. This is a non-mandatory value and it can be blank. |
| varchar(4096) |
guest email | Email ID of the guest. This is a mandatory value and it cannot be blank. |
| varchar(256) |
guest first name | First name of the guest |
| varchar(160) |
guest gender | Gender of the guest. This is a non-mandatory value and it can be blank. |
| varchar(14) |
guest home phone | Home phone number of guest |
| varchar(64) |
guest id | A unique identifier for the guest. This is a non-blank value that gets populated for every guest |
| varchar(36) |
guest last name | Last name of the guest. |
| varchar(256) |
guest middle name | Middle name of the guest. This is a non-mandatory value and it can be blank. |
| varchar(128) |
guest mobile phone | Mobile number of the guest. This is a mandatory value and it cannot be blank. |
| varchar(64) |
guest phone code | Phone country code of the guest. This is a mandatory value and it cannot be blank. |
| integer |
guest state | geographical state of the guest |
| varchar(4096) |
guest tags | The tags associated with the guest. |
| varchar(65535) |
guest type name | An indicator of whether the guest is indeed a guest or an employee. | Possible values: - Guest - Employee - Internal Guest/Virtual Guest: Guests who made their first purchase on Webstore. | varchar(128) |
guest zipcode | The zipcode of the guest. This is a non-mandatory value and it can be blank. |
| varchar(128) |
invoice id | Unique identifer for invoice |
| varchar(36) |
invoice item id | 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 |
| varchar(36) |
invoice no | Unique number for invoice. Unique for a center |
| varchar(80) |
is employee inactive | Flag that indicates whether the employee is inactive. An employee is considered inactive if they have been removed from the system. - True: Employee is inactive. - False: Employee is active |
| boolean |
is guest inactive | A flag indicating if the guest is inactive. A guest is considered inactive if his/her profile has been removed from the system. - True indicates that the guest is inactive. - False indicates that the guest is active. |
| boolean |
is product inactive | Flag that indicates whether the product is inactive. A product is considered inactive if it has been removed from the system. - True: Product is inactive - False: Product is active |
| boolean |
is service inactive | Flag that indicates whether the service is active. A service is considered inactive if it has been removed from the system. - True: Service is inactive - False: Service is active |
| boolean |
item code | Unique code of the sold item. For example, if item type code = Service, item code is the code associated with that service. If item type code = Product, item code is the code associated with that product. It is slightly different for a gift card. If item type code = Gift card, item code is unique for every gift card sold |
| varchar(256) |
item id | unique ID for an item. |
| varchar(36) |
item name | Name of the sold item. For example, if item type code = Service, item name is the name of the service. If item type code = Product, item name is the name of the product | varchar(512) | |
item revenue | recognised revenue on amount redeemed |
| float8 |
item type code | Type of item sold or refunded. An item is usually a service, product, membership, package, gift card, prepaid card, or class |
| varchar(100) |
membership business unit id | the business unit to which the membership belongs |
| varchar(36) |
membership category name | The category of the membership |
| varchar(200) |
membership end date | The end date of the time period corresponding to the "membership status" |
| timestamp |
membership id | 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 |
| varchar(36) |
membership name | The name of the membership |
| varchar(512) |
membership service price |
|
| float8 |
membership start date | The start date of the time period corresponding to the "membership status" |
| timestamp |
membership status | Status of the membership between "membership start date" and "membership end date". Possible values: - Active - Frozen - Suspended - Expired with prepaids remaining - Upgrade (when the current membership is opted for an upgrade) - Downgrade (when the current membership is opted for an downgrade) - Upgraded (when the opted upgrade has actually started) - Downgraded (when the opted downgrade has actually started) - Not Applicable (when there are multiple status changes on the same day. These are not considered valid statuses when that happens) |
| integer |
membership type | The type of the membership. Possible values: - Recurring - Non-Recurring - Setup Membership: This is populated for memberships that have setup involved |
| varchar(50) |
membership version id | 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. |
| varchar(36) |
minutes to add | The number of minutes that need to be added to UTC time to get the current time at the 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. This is not needed for timestamps that are in the datasource directly- these are already in the center time zone |
| integer |
no of cycles paid | Number of cycles of payment made for recurring memberships |
| integer |
organization account name | The domain name of the organization in Zenoti. |
| varchar(256) |
organization name | The formal name of the organization. |
| varchar(512) |
other credit amount | total other credit amount acrued. In product, this is set as service+product credits. These credits can be used to redeem product or services. |
| float8 |
processed | Invoice status |
| smallint |
product business unit | Business unit to which the product belongs |
| varchar(200) |
product category | Category of the product. This is set up as sub-category in core |
| varchar(256) |
product credit amount | total product credit amount acrued |
| float8 |
product has variant | Flag that indicates whether the product has a variant. - True: Product has a variant - False: Product does not have a variant - Not Applicable |
| boolean |
product name | Name of the product |
| varchar(256) |
product parent category | Parent category of the product. This is set up as category in core |
| varchar(256) |
product tags | Tags associated with the product |
| varchar(65535) |
quantity used | number of benefit points (services) used for redemption |
| float8 |
record category | Explains why record entry is made | Recurring Membership Sale Information - if membership is sold Membership At benefit level - if membership is redeemed Membership Credit Redemption - if credits are redeemed Non-Recurring Membership Sale Information - if non-recurring membership is sold Membership Refund Information - if refunds are made Membership benefit Transfer details - if benefits are transferred Membership Service Redemption - if services are redeemed under membership | varchar(50) |
redeem post expiry | 1- redemption allowed post expiry 0 - redemption not allowed post expiry |
| integer |
redemption amount | amount redeemed from the acrued credits. This amount is at invoice item level |
| float8 |
return date | refund date |
| timestamp |
return value | refund value of membership sale |
| float8 |
sale price | sale price of the membership |
| float8 |
service businessunit | The business unit to which the service belongs |
| varchar(200) |
service category | category of service |
| varchar(256) |
service category id | category id of the service received under membership |
| varchar(36) |
service credit amount | total service credit amount acrued |
| float8 |
service has variant | Flag that indicates whether the service has a variant. - True: Service has a variant - False: Service does not have a variant - Not Applicable | A center has a service called Massage that has 2 variants: 30 minute massage and 60 minute massage. This flag would be True for the Massage service; it would be False for the 30 minute massage and 60 minute massage services | varchar(14) |
service is variant | Flag that indicates whether the service is a variant. - True: Service is a variant - False: Service is not a variant - Not Applicable | A center has a service called Massage that has 2 variants: 30 minute massage and 60 minute massage. This flag would be False for the Massage service; it would be True for the 30 minute massage and 60 minute massage services | varchar(14) |
service length | length of the service |
| integer |
service name | Name of the service redeemed under the membership |
| varchar(512) |
service parent category | parent category of service |
| varchar(256) |
service tags | Tags associated with the service | Tags can be created for a service in Organization view. If there are multiple tags, they will be comma-separated values | varchar(65535) |
setup fee | setup fee for membership |
| float8 |
setup membership id | The usermembershipid of the Setup Membership |
| varchar(36) |
shared on_transferred |
|
| timestamp |
sold center company name | The name with which the center is registered. Center is the center name mentioned in "sold center name" field |
| varchar(128) |
sold center name | Center where membership is sold |
| varchar(128) |
sold invoice item id | first membership sale invoice item id |
| varchar(36) |
sold to guest code | unique code for a guest to whom membership is sold |
| varchar(128) |
sold to guest id | unique Identifer for a guest to whom membership is sold |
| varchar(36) |
sold zone name | The zone that the center belongs to where the membership is sold |
| varchar(800) |
taxes | taxes applied on membership sale |
| float8 |
total credit amount | Sum of service+product+other credit amount |
| float8 |
transfer detail id | unique identifier for benefits transfer |
| varchar(36) |
transferred quantity | quantity of acrued benefits transferred |
| float8 |
transferred to center company name | company name of the center to which membership is transferred |
| varchar(128) |
transferred to center name | center to which membership is transferred |
| varchar(128) |
transferred to guest code | guest to whom benefits are transferred |
| varchar(128) |
transferred to user id | guest to whom benefits are transferred |
| varchar(36) |
unitbenefitlevelpayment | amount recognized for each unit benefit |
| float8 |
unused amount | unused amount after benefits are converted to credits and redeemed |
| numeric(18,4) |
user membership id | 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 | varchar(36) | |
zone name | The zone that the center belongs to |
| varchar(800) |
benefits ignored | Flag to indicate if benefits are ignored in cases where membership fee is waived | Boolean | |