ZenotiHelp

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.wid
  • user id - References Dim_User.user_id (also used as DISTKEY)
  • membership id - References Dim_Membership.membership_id
  • membership version id - References Dim_Membership.membership_version_id
  • user mmebership id - References Dim_UserMembership.user_membership_id
  • invoice item id - References the invoice item record
  • Relmgr userwid - References Dim_Employee.wid (relationship manager)
  • dimusermembershipwid - References Dim_UserMembership.wid
  • setup 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.