Scheduled collections data model
Guests buy packages and make payments in regular intervals. For any package sold, the business can view past collections and future expected collections in Scheduled Collections data source.
Use Case
Amount Collected:
The guest bought a package and opted to pay for the package in monthly installments of 18 months. On the guest profile page, under the Packages section, invoices are listed. If an invoice is opened, collections details are listed in the following way:
 |
The guest started payment on May 11th, 2022, and has paid 7 installments. The business can also see a list of expected future installment collections. In the Scheduled Collections data source, this data is represented in the following way:
The Collected date at center column stores date on which installments are collected. Next try at column stores future collection dates. The Collection amount column shows the expected amount to be collected. The collected amount column shows the actual amount collected.
Pending collections:
Sometimes guests fail to make payments, which will fall into Pending status. The following screenshot from the Zenoti site shows how a pending status is displayed in invoice details:
The guest has paid their dues till 20th November 2021. December payment is voided. All payments from January 2022 are in a pending state as the amount is not collected from guests. This data is represented in DataSource as following:
 |
The Next try at column still shows the original collection date.
Granularity of data
Data is stored at the transaction level. For example, in the above case, the guest is expected to make payment in 18 installments. So, for an invoice, there are 18 transactions. All these 18 transactions are stored in 18 rows.
 |
Group | field name | field description | Example | field type |
|---|---|---|---|---|
item | business unit | The business unit to which the item belongs. |
| varchar(65535) |
Center, Zone, Organization | center city | The city where the center is located. |
| varchar(128) |
Center, Zone, Organization | center name | The center where the appointment is scheduled to be serviced. |
| varchar(128) |
Center, Zone, Organization | center state name | The state where the center is located. |
| varchar(4096) |
Center, Zone, Organization | center zip | The zipcode of the center's location. |
| varchar(32) |
payment | collected amount | amount collected |
| float8 |
payment | collected date in center | date on which amount is collected |
| date |
payment | collected date time in ccenter | date and time on which amount is collected |
| timestamp |
payment | collection amount | amount to be collected |
| float8 |
payment | collection date in center | date on which amount is expected to be collected |
| date |
payment | collection source | This field tells if amount is collected manually/online/etc |
| varchar(200) |
payment | collections status | Status of scheduled collection | Cancelled Pending Not Applicable Collected Failed Refunded | varchar(200) |
payment | considerinfinancials | This flag indicates whether the custom payment method should be considered in the sales or collections reports. This is a flag in the core system under Admin > Categories > Custom Payments. - True indicates that the custom payment method should be considered in the financial reports. - False indicates that the custom payment method should not be considered in the financial reports |
| boolean |
Center, Zone, Organization | country name | The country where the center is located. |
| varchar(4096) |
item | created date in center | date on which schedule is created |
| date |
item | created date time in center | date and time on which schedule is created |
| timestamp |
payment | custompaymentname | For custom payments, the name of the custom payment method gets populated in the custom payment name field. If the appropriate option isn't there, the user can enter the information in the Custom payment additional details field | varchar(800) | |
guest | guest anniversary date | Marriage anniversary date of the guest. This is a non-mandatory value and it can be blank |
| timestamp |
guest | guest city | The city to which the guest belongs. This is a non-mandatory value and it can be blank. |
| varchar(128) |
guest | guest country name | The country to which the guest belongs. This is a non-mandatory value and it can be blank. | varchar(4096) | |
guest | guest dob | Date of Birth of the guest. This is a non-mandatory value and it can be blank. |
| timestamp |
guest | guest email | Email ID of the guest. This is a mandatory value and it cannot be blank. |
| varchar(512) |
guest | guest first name | First name of the guest. This is a mandatory value and cannot be blank. |
| varchar(256) |
guest | guest gender | Gender of the guest. This is a non-mandatory value and it can be blank. |
| varchar(14) |
guest | guest last name | Last name of the guest. |
| varchar(256) |
guest | guest middle name | Middle name of the guest. This is a non-mandatory value and it can be blank. |
| varchar(256) |
guest | guest mobile phone | Mobile number of the guest. This is a mandatory value and it cannot be blank. |
| varchar(64) |
guest | guest phone code | Phone country code of the guest. This is a mandatory value and it cannot be blank. |
| integer |
guest | guest referral source | The source from where the guest got to know about the organization. | Possible values: - Advertisement - Driving By - Friend - Guest - Other - Walkin - Yelp - Youtube | varchar(3200) |
guest | guest state | The state to which the guest belongs. This is a non-mandatory value and it can be blank. |
| varchar(4096) |
guest | 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 | 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 | guest zipcode | The zipcode of the guest. This is a non-mandatory value and it can be blank. |
| varchar(128) |
invoice | invoice id | A unique identifier for the invoice. It is unique within the organization. |
| varchar(36) |
invoice | invoice status | This indicates whether the invoice is open or closed |
| varchar(14) |
invoice | invoice_no | The unique identifier for the invoice within the center |
| varchar(40) |
guest | 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 |
employee | is sale by employee inactive | This flag indicates whether the employee who made the sale is inactive. An employee is considered inactive if they have been removed from the system. - True indicates that the employee is inactive. - False indicates that the employee is active |
| boolean |
payment | last tried on | latest date on which collection is attempted |
| date |
Center, Zone, Organization | minutes to add | The number of minutes to add to the UTC time to get the current time at the center. If you use filters such as Today or Tomorrow, Tableau generates the timestamps in UTC. We require this field to calculate the center time. It is not required for timestamps that are in the data source directly, these are already aligned to the center’s time zone. |
| integer |
payment | next try at | upcoming date for scheduled collection. In case payment is to be made monthly, you will have one row each for all future payments with future collection date of each month for each row. In case payments fail or fall into "pending" status, Next Try at date still shoes original collection date |
| date |
Center, Zone, Organization | operating center | A flag that indicates whether the center is operational. - True indicates operational - False indicates not operational |
| boolean |
Center, Zone, Organization | organization account name | The domain name of the organization in Zenoti. |
| varchar(256) |
Center, Zone, Organization | organization name | The formal name of the organization. |
| varchar(512) |
payment | payment option type | For card payments, the type of card gets populated here. For cheque payments, the name of the cheque gets populated here | For Card, the following are payment option types: - VISA - DEBIT CARD - DISCOVER - CHECK - CC - DINERS CARD - AMEX - UNIONPAY - DISCOVER CARD For Cheque, the following are payment option types: - RBS - BANK OF AMERICA | varchar(200) |
guest | referred by guestid | The user id of the person who referred the guest. |
| varchar(36) |
employee | sale by employee anniversary date | anniversary date of the employee who made the sale |
| timestamp |
employee | sale by employee city | city of the employee who made the sale |
| varchar(128) |
employee | sale by employee country name | country name of the employee who made the sale |
| varchar(4096) |
employee | sale by employee dob | dob of the employee who made the sale |
| timestamp |
employee | sale by employee email | email of the employee who made the sale |
| varchar(512) |
employee | sale by employee first name | first name of the employee who made the sale |
| varchar(256) |
employee | sale by employee gender | gender of the employee who made the sale |
| varchar(14) |
employee | sale by employee id | id of the employee who made the sale |
| varchar(36) |
employee | sale by employee job code | job code of the employee who made the sale |
| varchar(8000) |
employee | sale by employee job name | job name of the employee who made the sale |
| varchar(1020) |
employee | sale by employee last name | last name of the employee who made the sale |
| varchar(256) |
employee | sale by employee middle name | middle name of the employee who made the sale |
| varchar(256) |
employee | sale by employee mobile phone | mobile phone of the employee who made the sale |
| varchar(64) |
employee | sale by employee phone code | phone code of the employee who made the sale |
| integer |
employee | sale by employee state | state of the employee who made the sale |
| varchar(4096) |
employee | sale by employee tags | tags of the employee who made the sale |
| varchar(65535) |
employee | sale by employee type name | 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) |
employee | sale by employee wid | Unique identifier for the employee. This is generated in analytics, it does not exist in the core system. |
| bigint |
employee | sale by employee zipcode | zipcode of the employee who made the sale |
| varchar(128) |
employee | salebywid | A unique identifier for the employee who made the sale. This is generated by Analytics and does not exist in the core system | bigint | |
payment | scheduled collection wid | A unique identifier for every collection at the most granular level in this table. This is generated in Analytics, it does not exist in the core system | bigint | |
payment | scheduled payment type | type of payment used to make payment | -Online credit card -credit card -Gift card -Etc -OfflineCheck | varchar(200) |
guest | user id | A unique identifier for the guest. This is a non-blank value that gets populated for every guest | varchar(36) | |
guest | user wid | A unique identifier for the guest. This is generated by Analytics and does not exist in the core system | bigint | |
Center, Zone, Organization | zone name | The zone to which the center belongs |
| varchar(800) |