Membership Datewise Data Model
This data source describes all the memberships and tracks the life cycle of the membership. It describes attributes such as “membership name”, “membership status”, “membership type”, “memberhip start date”, “membership end date”, “membership expiry date” etc. It also describes whether the memberships have been upgraded or downgraded.
Important
The fields in this data source are identical to the fields in the Memberships data source (http://reports.zenoti.com/knowledgebase/memberships-data-model). The only difference is that this data source brings in a row for every single day and captures the status on that day, irrespective of whether a status change happened or not. Due to this, this data source is far larger. Data from the Membership datewise data source is available for dates between 180 days in the past and 90 days in the future only. We recommend that users use the Memberships data source instead of the Memberships Datewise data source to ensure more efficient calculations.
Example Use Cases
Membership status day-wise
Granularity of Data
At the most granular level, unique rows can be identified through the combination of ‘user mmebership id’ and ‘date’. The status on every day is captured for each membership.Membership status is tracked at the end of the day. So, even if statuses are changed multiple times in a day, the data source will reflect the latest status at the end of the day.
The below table shows how data is captured in this data source. The ‘date’ field has an entry for every date from the beginning of the membership till the end. The ‘membership status’ field captures the status as of that day. The highlighted rows represent days where there was an actual change in the status of the membership. In this example, since the membership expired with prepaids left, this status is populated till the last configured date for this data source.
For examples of how data is captured in other scenarios, please refer to the examples in the Memberships data source documentation (https://reports.zenoti.com/knowledgebase/memberships-data-model).
Group | Description | Field | Definition | Examples/Notes | Data Type |
|---|---|---|---|---|---|
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | organization name | The formal name of the organization. | 'Massage Spa Express' is the formal name of an organization. | varchar(512) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | organization account name | The domain name of the organization in Zenoti. | If you use this URL to log in to Zenoti 'https://massagespa.zenoti.com', then "massagespa" is your account name. The formal name and domain name may be different. | varchar(256) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | center name | The center where the membership is sold. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | zone name | The zone to which the center belongs. | varchar(800) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | center city | The city where the center is located. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | center state name | The state where the center is located. | varchar(4096) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | country name | The country where the center is located. | varchar(4096) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | center zip | Zipcode of the center's location. | varchar(32) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | operating center | A flag that indicates whether the center is operational. - True: Operational - False: Not operational | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | minutes to add | Number of minutes that need to be added to UTC time to calculate the current time at the center. Timestamps that are generated by Tableau (for example, if you use filters such as Today and Tomorrow) are in UTC. As a result, you need this field to find out the current center time. Note: You do not need this field for timestamps that are directly in the data source - these timestamps are already in the center's time zone. | integer | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | etllastupdatedon | Latest timestamp at which the corresponding record was either inserted or updated in this data source. | timestamp | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to the center where the membership was sold. | etllastupdatedby | ETL system admin user who inserted or updated the corresponding record in this data source. | char(63) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest first name | First name of the guest. | varchar(256) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest middle name | Middle name of the guest. | varchar(256) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest last name | Last name of the guest. | varchar(256) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | user wid | A unique identifier for the guest. This is generated in Analytics, it does not exist in the core system. | bigint | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | user id | A unique identifier for the guest. This is a non-blank value that gets populated for every guest. | varchar(36) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest city | The city to which the guest belongs. | varchar(128) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest state | The state to which the guest belongs. | varchar(4096) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest country name | The country to which the guest belongs. | varchar(4096) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest zipcode | Zipcode of the guest. | varchar(128) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest email | Email ID of the guest. | varchar(512) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest mobile phone | Mobile number of the guest. | varchar(64) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest phone code | Phone country code of the guest. | integer | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest gender | Gender of the guest. | varchar(14) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest dob | Date of birth of the guest. | timestamp | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | referred by guestid | The user id of the person who referred the guest. | varchar(36) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest anniversary date | Marriage anniversary date of the guest. | timestamp | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | 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 the Webstore. | varchar(128) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest tags | The tags associated with the guest. | Tags can be setup for guests in their profile under the 'General' tab. These will be comma separated values if there are multiple tags. | varchar(65535) |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest is active | A flag indicating whether the guest is inactive. A guest is considered inactive if their profile has been removed from the system. - True: Guest is inactive. - False: Guest is active. | boolean | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest referral source | The source from where the guest got to know about the organization. | Possible values: - Advertisement - Driving By - Friend - Guest - Instagram - Other - Walkin - Yelp - Youtube | varchar(3200) |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | relationship manager first name | The first name of the employee who is assigned as the Relationship Manager/ Primary Consultant to the guest. | An employee can be set as a 'Relationship Manager' by adding his/ her name as 'Primary Employee' in the Guest profile. | varchar(256) |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | relationship manager last name | The last name of the employee who is assigned as the Relationship Manager/ Primary Consultant to the guest. | An employee can be set as a 'Relationship Manager' by adding his/ her name as 'Primary Employee' in the Guest profile. | varchar(256) |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | relationship manager employee - code | The employee - code of the empoyee who is assigned as the Relationship Manager/ Primary Consultant to the guest. | An employee can be set as a 'Relationship Manager' by adding his/ her name as 'Primary Employee' in the Guest profile. | varchar(128) |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | relmgr userwid | Unique identifier of the relationship manager to the guest. This is generated in Analytics. | bigint | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | relationship manager user code | DO NOT USE- THIS FIELD IS BEING DEPRECATED | varchar(128) | |
Guest | These fields detail the attributes of the guest to whom the membership belongs. | guest base center | Base center of the guest. This is where the guest's profile was created. | varchar(128) | |
Membership | This group of fields describes the attributes of the membership. | membership name | The name of the membership. | varchar(512) | |
Membership | This group of fields describes the attributes of the membership. | membership code | A unique identifier for the membership. This maps to the membership code in the Membership configuration page of the core system. | varchar(32) | |
Membership | This group of fields describes the attributes of the membership. | 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 'mmembership type' is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to. | varchar(36) | |
Membership | This group of fields describes the attributes of the membership. | 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) | |
Membership | This group of fields describes the attributes of the membership. | user mmebership id | A unique identifier for every membership sold. If the 'mmembership type' is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to. | varchar(36) | |
Membership | This group of fields describes the attributes of the membership. | dimusermembershipwid | Unique identifier of the membership and the user combination. This is generated in Analytics; it does not exist in the core system. | bigint | |
Membership | This group of fields describes the attributes of the membership. | dimusermembershipid | Unique identifier for the membership and the user combination. This is a non-blank value that gets populated for every membership. | numeric(19) | |
Membership | This group of fields describes the attributes of the membership. | membership category name | The category of the membership. | varchar(200) | |
Membership | This group of fields describes the attributes of the membership. | mmembership type | The type of the membership. Possible values: - Recurring - Non-Recurring - Setup Membership: This is populated for memberships that have setup involved. | varchar(65535) | |
Membership | This group of fields describes the attributes of the membership. | membership recurring | A flag indicating whether the corresponding membership is a recurring membership. This is useful when the 'mmembership type' is 'Set up'. This field lets you infer whether the set up was done for a Recurring or Non-recurring membership. Possible values: - True: Recurring membership - False: Not a recurring membership | varchar(5) | |
Membership | This group of fields describes the attributes of the membership. | has setup fee | Indicates whether the membership version has a Setup Membership associated with it. - True: Setup membership associated - False: Setup membership not associated | varchar(3) | |
Membership | This group of fields describes the attributes of the membership. | setup user membershipid | The usermembershipid of the Setup Membership. | Populates only if a setup membership is created for a user. | varchar(36) |
Membership | This group of fields describes the attributes of the membership. | 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 a 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) | Upgrade or Downgrade status just indicates OPTING for an upgrade or downgrade. In this status, the current membership will continue as is until the new membership gets activated. | varchar(26) |
Membership | This group of fields describes the attributes of the membership. | upgrade/downgrade to usermembershipid | The target usermembershipid for the current membership. Which membership is the current membership getting upgraded/ downgraded to? | varchar(36) | |
Membership | This group of fields describes the attributes of the membership. | upgrade/downgrade from usermembershipid | The source usermembershipid for the current membership. Which membership did the current membership get upgraded/ downgraded from? | varchar(36) | |
Membership | This group of fields describes the attributes of the membership. | memberhip start date | The start date of the time period corresponding to the 'membership status'. | date | |
Membership | This group of fields describes the attributes of the membership. | membership end date | The end date of the time period corresponding to the 'membership status'. | date | |
Membership | This group of fields describes the attributes of the membership. | membership begin date | The initial start date of the membership for the user, irrespective of the 'membership status' for that row. If a membership involves setup, this is the date on which the setup membership started. This remains constant throughout the membership lifecycle. Even if the membership gets cancelled before the expiry date, this remains constant. | date | |
Membership | This group of fields describes the attributes of the membership. | membership expiry date | The expiry date of the membership for the user, irrespective of the 'membership status' for that row. This remains constant throughout the membership lifecycle. Even if the membership gets cancelled before the expiry date, this remains constant. | date | |
Membership | This group of fields describes the attributes of the membership. | date | The date corresponding to the 'membership status'. This field will have an entry for every date from the beginning till the end of the corresponding membership. | timestamp | |
Membership | This group of fields describes the attributes of the membership. | memberhip tags | The tags associated with the membership. | Tags can be setup for memberships in their profile under the 'General' tab. These will be comma separated values if there are multiple tags. | varchar(65535) |
Membership | This group of fields describes the attributes of the membership. | recurring cancelled date | The date from which the recurring payment for a membership is no longer carried out. | Note: This date doesn't signify the cancellation of membership. It signifies the date of cancellation of payment. | date |
Membership | This group of fields describes the attributes of the membership. | termination reason | The reason for the termination of a membership. Possible values: - Expired - Expired with prepaids remaining - Suspended - Cancelled - Closed | varchar(26) | |
Membership | This group of fields describes the attributes of the membership. | is mmebership voided | Indicates whether the membership has been deleted from the UI. - True: Membership is voided - False: Membership is not voided | boolean | |
Membership | This group of fields describes the attributes of the membership. | is auto renew | Indicates whether the membership is set to auto renew for the guest. - True: Is set to auto renew - False: Is not set to auto renew | varchar(10) | |
Membership | This group of fields describes the attributes of the membership. | cancellation reason | Comment entered as the reason for the cancellation of membership. | varchar(65535) | |
Membership | This group of fields describes the attributes of the membership. | invoice item id | The Invoice item id of the membership. If the 'mmembership type' is a 'Setup Membership', this field always points to the Main Membership that the Setup is associated to. | varchar(36) | |
Membership | This group of fields describes the attributes of the membership. | is membership active | DO NOT USE- THIS FIELD IS BEING DEPRECATED | boolean | |
Membership | This group of fields describes the attributes of the membership. | is setup fee | DO NOT USE- THIS FIELD IS BEING DEPRECATED | varchar(3) | |
Membership | This group of fields describes the attributes of the membership. | is current record | DO NOT USE- THIS FIELD IS BEING DEPRECATED | varchar(3) | |
Membership | This group of fields describes the attributes of the membership. | is record active | DO NOT USE- THIS FIELD IS BEING DEPRECATED | boolean |