Membership Fact Consolidated
ZTZenoti Docs Team Updated April 10, 2026 5 min readGuide
Consolidated membership fact table combining membership status, guest, relationship manager, and center dimensions for membership lifecycle and status analytics. Note: The actual Redshift table name is membership_fact_consoidated (with a typo preserved from the original schema).
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| organization name | VARCHAR(512) | Yes | Name of the organization. Sourced from Dim_Organization.organization_name. |
| organization account name | VARCHAR(256) | Yes | Account name of the organization. Sourced from Dim_Organization.organization_account_name. |
| zone name | VARCHAR(800) | Yes | Name of the zone the center belongs to. Sourced from Dim_Center.zone_name. |
| center name | VARCHAR(128) | Yes | Name of the center. Sourced from Dim_Center.center_name. |
| center city | VARCHAR(128) | Yes | City where the center is located. Sourced from Dim_Center.center_city. |
| center zip | VARCHAR(32) | Yes | Zip/postal code of the center. Sourced from Dim_Center.center_zip. |
| center state name | VARCHAR(4096) | Yes | State name of the center. Sourced from Dim_Center.center_state_name. |
| country name | VARCHAR(4096) | Yes | Country name of the center. Sourced from Dim_Center.country_name. |
| operating center | BOOLEAN | Yes | Whether the center is an operating center. Sourced from Dim_Center.operating_center. |
| user wid | BIGINT | Yes | Warehouse identifier for the guest/user dimension. Sourced from Dim_User.wid. |
| guest first name | VARCHAR(256) | Yes | First name of the guest. Sourced from Dim_User.first_name. |
| guest last name | VARCHAR(256) | Yes | Last name of the guest. Sourced from Dim_User.last_name. |
| guest middle name | VARCHAR(256) | Yes | Middle name of the guest. Sourced from Dim_User.middle_name. |
| guest city | VARCHAR(128) | Yes | City of the guest. Sourced from Dim_User.city. |
| guest zipcode | VARCHAR(128) | Yes | Zip/postal code of the guest. Sourced from Dim_User.zipcode. |
| guest base center | VARCHAR(128) | Yes | Base/home center of the guest. Sourced from Dim_User.base_center. |
| guest type name | VARCHAR(128) | Yes | Type classification of the guest. Sourced from Dim_User.guest_type_name. |
| guest email | VARCHAR(512) | Yes | Email address of the guest. Sourced from Dim_User.email. |
| guest mobile phone | VARCHAR(64) | Yes | Mobile phone number of the guest. Sourced from Dim_User.mobile_phone. |
| guest phone code | INTEGER | Yes | Phone country code of the guest. Sourced from Dim_User.phone_code. |
| guest gender | VARCHAR(14) | Yes | Gender of the guest. Sourced from Dim_User.gender. |
| guest dob | TIMESTAMP WITHOUT TIME ZONE | Yes | Date of birth of the guest. Sourced from Dim_User.dob. |
| referred by guestid | VARCHAR(36) | Yes | GUID of the guest who referred this guest. Sourced from Dim_User.referred_by_guestid. |
| guest anniversary date | TIMESTAMP WITHOUT TIME ZONE | Yes | Anniversary date of the guest. Sourced from Dim_User.anniversary_date. |
| guest state | VARCHAR(4096) | Yes | State of the guest address. Sourced from Dim_User.state. |
| guest country name | VARCHAR(4096) | Yes | Country name of the guest. Sourced from Dim_User.country_name. |
| guest is active | BOOLEAN | Yes | Whether the guest is active. Sourced from Dim_User.is_active. |
| guest tags | VARCHAR(65535) | Yes | Tags associated with the guest. Sourced from Dim_User.tags. |
| guest referral source | VARCHAR(3200) | Yes | Referral source of the guest. Sourced from Dim_User.referral_source. |
| is membership active | BOOLEAN | Yes | Whether the membership is active. Sourced from Dim_UserMembership.is_membership_active. |
| membership recurring | VARCHAR(5) | Yes | Whether the membership is recurring (Yes/No). Sourced from Dim_Membership.recurring. |
| mmembership type | VARCHAR(65535) | Yes | Type of the membership. Sourced from Dim_Membership.membership_type. |
| is mmebership voided | BOOLEAN | Yes | Whether the membership is voided. Sourced from Dim_UserMembership.is_membership_voided. |
| memberhip tags | VARCHAR(65535) | Yes | Tags associated with the membership. Sourced from Dim_Membership.tags. |
| membership code | VARCHAR(32) | Yes | Code of the membership. Sourced from Dim_Membership.membership_code. |
| membership category name | VARCHAR(200) | Yes | Category name of the membership. Sourced from Dim_Membership.membership_category_name. |
| membership name | VARCHAR(512) | Yes | Name of the membership. Sourced from Dim_Membership.membership_name. |
| user id | VARCHAR(36) | Yes | Unique identifier (GUID) of the user. Sourced from Dim_User.user_id. |
| membership id | VARCHAR(36) | Yes | Unique identifier (GUID) of the membership. Sourced from Dim_Membership.membership_id. |
| membership version id | VARCHAR(36) | Yes | Version identifier of the membership. Sourced from Dim_Membership.membership_version_id. |
| user mmebership id | VARCHAR(36) | Yes | Unique identifier of the user-membership association. Sourced from Dim_UserMembership.user_membership_id. |
| memberhip start date | DATE | Yes | Start date of the membership. Sourced from Dim_UserMembership.membership_start_date. |
| membership end date | DATE | Yes | End date of the membership. Sourced from Dim_UserMembership.membership_end_date. |
| membership status | VARCHAR(26) | Yes | Status of the membership. Sourced from Dim_UserMembership.membership_status. |
| has setup fee | VARCHAR(3) | Yes | Whether the membership has a setup fee (Yes/No). Sourced from Dim_UserMembership.has_setup_fee. |
| is setup fee | VARCHAR(3) | Yes | Whether the current record is a setup fee (Yes/No). Sourced from Dim_UserMembership.is_setup_fee. |
| is current record | VARCHAR(3) | Yes | Whether this is the current/latest record (Yes/No). Sourced from Dim_UserMembership.is_current_record. |
| termination reason | VARCHAR(26) | Yes | Reason for membership termination. Sourced from Dim_UserMembership.termination_reason. |
| setup user membershipid | VARCHAR(36) | Yes | User membership ID of the setup record. Sourced from Dim_UserMembership.setup_user_membershipid. |
| invoice item id | VARCHAR(36) | Yes | Unique identifier of the related invoice item. Sourced from Dim_UserMembership.invoice_item_id. |
| is auto renew | VARCHAR(10) | Yes | Whether the membership auto-renews. Sourced from Dim_UserMembership.is_auto_renew. |
| is record active | BOOLEAN | Yes | Whether the record is active. Sourced from Dim_UserMembership.is_record_active. |
| recurring cancelled date | DATE | Yes | Date when the recurring membership was cancelled. Sourced from Dim_UserMembership.recurring_cancelled_date. |
| cancellation reason | VARCHAR(65535) | Yes | Reason for membership cancellation. Sourced from Dim_UserMembership.cancellation_reason. |
| relationship manager first name | VARCHAR(256) | Yes | First name of the relationship manager. Sourced from Dim_Employee.first_name (relationship manager). |
| relationship manager last name | VARCHAR(256) | Yes | Last name of the relationship manager. Sourced from Dim_Employee.last_name (relationship manager). |
| relationship manager user code | VARCHAR(128) | Yes | User code of the relationship manager. Sourced from Dim_Employee.user_code (relationship manager). |
| relationship manager employee - code | VARCHAR(512) | Yes | Alternate employee code of the relationship manager. Sourced from Dim_Employee.employee_code_alt (relationship manager). |
| membership expiry date | DATE | Yes | Expiry date of the membership. Sourced from Dim_UserMembership.membership_expiry_date. |
| membership begin date | DATE | Yes | Begin date of the membership. Sourced from Dim_UserMembership.membership_begin_date. |
| minutes to add | INTEGER | Yes | Minutes offset to add for timezone conversion. Sourced from Dim_Center.minutes_to_add. |
| upgrade/downgrade to usermembershipid | VARCHAR(36) | Yes | User membership ID of the upgrade/downgrade target. Sourced from Dim_UserMembership. |
| upgrade/downgrade from usermembershipid | VARCHAR(36) | Yes | User membership ID of the upgrade/downgrade source. Sourced from Dim_UserMembership. |
| Relmgr userwid | BIGINT | Yes | Warehouse identifier for the relationship manager. Sourced from Dim_Employee.wid (relationship manager). |
| dimusermembershipwid | BIGINT | Yes | Warehouse identifier for the user membership dimension. Sourced from Dim_UserMembership.wid. |
| dimusermembershipid | NUMERIC(19,0) | Yes | Internal identifier for the user membership dimension. Sourced from Dim_UserMembership.id. |
| Center Code | VARCHAR(40) | Yes | Code of the center. Sourced from Dim_Center.center_code. |
| is voluntary cancellation | BOOLEAN | Yes | Whether the membership cancellation was voluntary. |
Primary Key
No explicit primary key defined. The combination of user id and user mmebership id serves as a logical identifier.
Foreign Keys
user wid- References Dim_User.widuser id- References Dim_User.user_id (also used as DISTKEY)membership id- References Dim_Membership.membership_idmembership version id- References Dim_Membership.membership_version_iduser mmebership id- References Dim_UserMembership.user_membership_idinvoice item id- References the invoice item recordRelmgr userwid- References Dim_Employee.wid (relationship manager)dimusermembershipwid- References Dim_UserMembership.widsetup user membershipid- References Dim_UserMembership (setup record)upgrade/downgrade to usermembershipid- References Dim_UserMembership (target)upgrade/downgrade from usermembershipid- References Dim_UserMembership (source)
Enumerator Values
| Column Name | Known Values |
|---|---|
| membership | 0 = Non-Recurring 1 = Recurring 3 = All |
| termination reason | Reason for termination |
| membership recurring | Yes/No flag |
| is auto renew | Auto-renew flag |
| has setup fee | Yes/No flag |
| is setup fee | Yes/No flag |
| is current record | Yes/No flag |
Standard ETL metadata columns (EtlLastUpdatedOn, EtlLastUpdatedBy, podname, groupid) are present in the underlying table but omitted from this reference for brevity.
Was this article helpful?
Your feedback helps us improve our documentation.