Membership dunning collections in the data source
As a business, you can know about your future cashflows from recurring memberships using membership_dunning_consolidated table. This table uses membership dunning data.
To read more about dunning settings for a center, refer to Set up Dunning Settings for a Center.
Recurring memberships have different statuses:
Active membership: Membership is active, and there are no issues with payments. You can expect future collections are on a specific date based on the collection cycle.
Frozen membership: A guest requests the customer to freeze membership for a certain period. As the recurring fee is not collected in the freeze period, the next collection date shifts by the number of freeze days.
Suspended membership: When a payment fails for recurring memberships, it leads to membership suspension, and there are no collections in the future unless the guest gets the membership re-activated. In this case, do not expect future collections.
Expired membership: When a membership reaches its expiry date, the membership expires, and there are no future collections from this membership.
Column | Description |
|---|---|
next collection date | Auto populated to the upcoming collection date, as per your previous payment. |
cycle number | The number of payment cycles completed - monthly or annual. For example, if there is an active membership for 6 months, the cycle number will have 6 entries. |
collection status | Displays the collection status - Refunded/cancelled/waived/collected. |
expected collection date in freeze period | In case a membership is frozen, this column shows the expected collection date after which membership is resumed |
final sale price | Amount collected as part of membership |
collected date | Date of invoice |
Assume you want to find out the collection amount for the next two months.
Go to the column expected collection date in freeze period and apply a time filter for two months.
Sum the filter results.
Remove all filters.
Apply filter on blank values for column expected collection date in freeze period and apply a filter for two months on the column next collection date.
Sum the filter results.
Sum the values calculated in steps 2 and 4.
The corresponding value in the final sale price column represents the expected collection amount.
To get granular or consolidated future collection details, apply filters on center, zone, or user membership id columns.
Use cases
Here we describe how the membership dunning data from Zenoti is stored in the data source.
1. Active memberships
In this example, the recurring membership has a collection date of 19th of every month in Zenoti. The last payment collection was done on June 19th. The next collection date is on the 19th of July.
 |
The following image shows how Zenoti's data is represented in the data source in the membership_dunning_consolidated table.
 |
2. Suspended membership
In the case of suspended membership, the next collected date stays as-is in the data source until the user pays the membership dues and activates the membership.
In this example, the collection date is the 24th of every month. The last collection date was 24th April. Here the membership is suspended and the next payment is due on 24th June. This screenshot was taken on the 30th of June.
 |
The following image captures the suspended membership scenario in the data source:
 |
The next collection date column displays 24th June until the membership becomes active again.
3. Frozen membership
Assume that membership was frozen on 24th May 2022. The next collection date shifts by the number of days the membership is frozen. If membership is frozen by 40 days then the next collection date extends by 40 days.
In Zenoti, a membership was frozen on 22nd May and the next collection date ideally would have been on 24th May.
 |
Data source representation
In this example, the membership is frozen. The expected collection date in freeze period displays the collection date after the unfreeze date. It doesn't display unfreeze date. In this case, unfreeze date is 22nd August, while the collection date is 24th August.
 |
View fact table
View data mappings
Table details
Group | Column name | Description | Values |
|---|---|---|---|
Center | center city | city in which the center is located | Pre-configured values, sourced from center dimensions |
Center | center company name | company name of the center | Pre-configured values, sourced from center dimensions |
Center | center name | center name | Pre-configured values, sourced from center dimensions |
Center | center state name | state in which center is located | Pre-configured values, sourced from center dimensions |
Center | center time | zone geographical timezone of the center | Pre-configured values, sourced from center dimensions |
Center | center wid | center id for the table | numerical value |
Center | center zip | zipcode of the center | Pre-configured values, sourced from center dimensions |
Center | zone name | zone to which the center is mapped | Pre-configured values, sourced from center dimensions |
Center | operating center | tells if the center is void; "true" for void, "false" for active | "true", "false" |
Center | organization account name | account name of organisation | Pre-configured values, sourced from center dimensions |
Center | organization name | Name of the organisation | Pre-configured values, sourced from center dimensions |
Center | country name | country of the center | Pre-configured values, sourced from center dimensions |
Guest info | guest anniversary date | The guest's anniversary date | sourced from guest dimensions |
Guest info | City in which guest resides sourced from guest dimensions. | City in which guest resides | sourced from guest dimensions |
Guest info | guest code | code assigned to a guest in the core system | sourced from guest dimensions |
Guest info | guest country name | country from which guest belongs | sourced from guest dimensions |
Guest info | guest dob | date of birth of guest | sourced from guest dimensions |
Guest info | guest email | email address of guest | sourced from guest dimensions |
Guest info | guest first name | first name of guest | sourced from guest dimensions |
Guest info | guest gender | gender of guest | sourced from guest dimensions |
Guest info | guest home phone | guest home phone number | sourced from guest dimensions |
Guest info | guest last name | last name of guest | sourced from guest dimensions |
Guest info | guest middle name | middle name of guest | sourced from guest dimensions |
Guest info | guest mobile phone | mobile phone number of guest | accepts numerical values or blank values |
Guest info | guest phone code | guest phone code | accepts numerical values or blank values |
Guest info | guest referral source | referral source for the guest s | sourced from guest dimensions. |
Guest info | guest state | geographical state where guest lives | sourced from guest dimensions. |
Guest info | guest tags | Tags assigned to guest in guest profile in core system | sourced from guest dimensions |
Guest info | guest type name | Tells whether the person is a guest or employee | sourced from guest dimensions |
Guest info | guest zipcode | zipcode of place where guest lives | sourced from guest dimensions |
Guest info | user id | user id of the guest. Same across all data | sourced from guest dimensions |
Guest info | user wid | id for the user created in BI table | sourced from guest dimensions |
Guest info | is guest inactive | Tells if the guest is active | "true", "false" |
membership details | membership recurring | Tells if membership is recurring type or not. "True" for recurring and "False" for non-recurring | "True", "False" |
membership details | duration type | tells if payment cycle is in days or months | "Days", "Month" |
membership details | duration | Number of "duration types" for a cycle. Join with "Duration type" field. Number of days or months for a cycle | integer |
membership details | membership start date | Start date of membership | date |
membership details | membership tags | tags associated with memberships | varchar |
membership details | membership type | Tells if membership is recurring or a one time payment | "Recurring" |
collection details | expected collection date in freeze period | Incase there is a freezing period, collection date gets extended. This field tells when is the next collection date in case of Freeze scenarios | date |
collection details | final sale price | Final sale price after deducting "discount" from "Sale price" | float |
collection details | invoice id | Id for the invoice generated | string |
collection details | InvoiceNo | Invoice number that helps in identifying and tracking the invoice. | varchar(160) |
collection details | invoice item id | item IDs for the items within an invoice | string |
collection details | last tried on | Last attempted date to collect amount | date |
collection details | membership credit amount | credit amount associated with membership | float |
collection details | next collection date | Tells when is the next collection date in recurring membership scenarios | YES |
collection details | next try at | next try to collect payments in case there is a failure. Usually, multiple attempts are made to collect amount based on settings in the product. This field tells when will the next attempt to collect payment be in case of failed attempts. While these attempts are made, "collection status" will show as "Pending". Once all attempts are completed and if customer is still unable to receive payment, "collection status" will change to "Failed" | date |
collection details | recurring collection pk | PK value for table | bigint |
collection details | sale price | sale price of membership. Sale price of the recurring payment in case of recurring memberships | float |
collection details | discount | discount on the sale price | float |
collection details | collection status | Indicates the status of the amount collection | collected-payment is collected
|
collection details | tax | Tax on the final sale price | float |
collection details | collected date | date on which amount is collected | date |
collection details | cycle number | tells about the recurring cycle number for which the collection is attempted | date |
collection details | Record type | Indicates fee type like recurring collection, annual fee, setup fee, etc | |
collection details | Group invoice id | Unique identifier for group invoice | |
collection details | Primary Payment Type | Indicates primary payment type for recurring collections. E.g: cash, cheque, Credit card, etc. | |
collection details | Primary Payment Brand | Brand of primary payment type | |
collection details | Secondary Payment Type | Indicates Secondary payment type for recurring collections. E.g: cash, cheque, Credit card, etc. | |
collection details | Secondary Payment Brand | Brand of Secondary payment type |