Employee Commission Data Model
An employee's earnings in Zenoti can have five main components:
Salary
Hourly Pay
Commissions
Tips
SSG
Further, commissions are of three types:
Sales Commissions
Tenure Commissions
Center Commissions
This data source brings in details of each employee's Sales Commissions by pay period.
Example Use-cases
Sales commissions earned by an employee in a particular pay period
Granularity of the Data
Data is organized by pay period for each employee. For each pay period and employee, all the invoice items that contributed to a commission are brought in as separate rows.
 |
Note
If a particular payroll for a past period is regenerated in the core system, all the rows in the data source associated with that payroll get deleted and get re-populated as per the regeneration date.
Group | Description | Field | New 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 employee's payroll is generated. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | center name | The center where the corresponding employee's payroll is generated. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | center zip | The postal code in which the center is located. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | 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 employee's payroll is generated. | minutes to add | The number of minutes that need to be added to UTC time to get the current time at the center. Timestamps that are generated by Tableau [E.g. if you use filters such as Today, Tomorrow, etc.] are in UTC, hence this field is needed to arrive at the center time. This is not needed for timestamps that are in the datasource directly, these are already in the center time zone. | int4 | |
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 employee's payroll is generated. | etl created date | The date on which the record was created in Analytics. | timestamp | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee first name | First name of the employee as per employee profile. | varchar(160) | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee middle name | Middle name of the employee as per employee profile. | varchar(128) | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee last name | Last name of the employee as per employee profile. | varchar(256) | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee mobile phone | Mobile number of the employee as per employee profile. | varchar(64) | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee - code | Unique code of the employee. This is the code taken from the employee's profile in Zenoti. | varchar(128) | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee gender | The gender of the employee. | Possible values: - Male - Female - Not applicable | varchar(14) |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee job name | Job name associated with the employee. | Therapist, Manager, Aesthetician etc. | varchar(1020) |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | is employee inactive | A flag indicating whether the employee is inactive. An employee is considered inactive if they have been removed from the system. - True: Employee is inactive - False: Employee is active | boolean | |
Employee | These fields detail the attributes of the employee whose payroll information is populated. | employee code | Unique code of the employee. This is the code from the employee's guest profile. When an employee's profile is created, Zenoti automatically creates their guest profile. Note: The 'employee code' is usually the same as 'employee - code' unless someone manually changes it in the employee's guest profile. | DO NOT USE- THIS FIELD IS BEING DEPRECATED | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest first name | First name of the guest. | varchar(160) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest middle name | Middle name of the guest. | varchar(128) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest last name | Last name of the guest. | varchar(256) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | user wid | A unique identifier for the user. This is generated in Analytics, it does not exist in the core system. | bigint | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | 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 | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest gender | Gender of the guest. | Possible values: - Male - Female - Not Applicable | varchar(14) |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | 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 who took the item which resulted in a commission for the employee. | guest dob | The guest's date of birth. | timestamp | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest anniversary date | The guest's marriage anniversary. | timestamp | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest city | The city to which the guest belongs. | varchar(128) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest state | The state to which the guest belongs. | varchar(4096) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest country name | The country to which the guest belongs. | varchar(4096) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest zipcode | The postal code in which the guest belongs. | varchar(128) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest email | Email ID of the guest. | varchar(256) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest mobile phone | Mobile number of the guest. | varchar(64) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest phone code | Phone country code of the guest. | integer | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | 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(1600) |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | referred by guestid | The user id of the person who referred the guest. | varchar(36) | |
Guest | These fields detail the attributes of the guest who took the item which resulted in a commission for the employee. | guest is active | Flag that indicates whether the guest is active. A guest is considered inactive if their profile has been removed from the system. - True: Guest is active. - False: Guest is inactive. | boolean | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | item type | The type of item sold or refunded. An item can be a service, product, membership, package, gift card or a prepaid card. | Possible values: - Day Package - Day Package Refund - Gift Card - Membership - Membership Freeze Fee - Membership Refund - Package - Package Refund - PrePaid Card - PrePaid Card Refund - Product - Product Refund - Service - Service Refund | varchar(100) |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | item name | The name of the item sold. For example, If item type code is Service, then the item name is the name of the service. If item type code is Product, then item name is the name the product. | varchar(512) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | item code | The unique code of the item sold. E.g. If item type code is Service, then item code is the code associated with that service. If item type code is Product, then item code is the code associated with that product. It is slightly different for a gift card. If item type code is Giftcard, then item code is unique for every giftcard sold. | varchar(256) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | refund item type | The type of item refunded. This is populated if an item for which an employee earned commission earlier is refunded. | Possible values: - Service - Product - Prepaid/Gift Card - Package - Membership | varchar(100) |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | item tags | The tags associated with the item. | Tags can be created for a service, product, package, or membership in the Organization view. These will be comma separated values if there are multiple tags. | varchar(65535) |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | businessunit | The business unit to which the item belongs. | varchar(65535) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | is item inactive | A flag indicating if the item is inactive. An item is considered inactive if it has been removed from the system. -True indicates that the item is inactive -False indicates that the item is active | boolean | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | service name | The name of the service. | varchar(512) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | service category | The category of the service. This is setup as sub-category in core. | For a service 'Brazilian Wax', category could be 'Waxing', and parent category could be 'Hair removal'. | varchar(256) |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | service parent category | The parent category of the service. This is setup as category in core. | For a service 'Brazilian Wax', category could be 'Waxing', and parent category could be 'Hair removal'. | varchar(256) |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | service length | The ideal time taken to complete a service (in minutes), as setup in Zenoti | integer | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | is add on | A flag indicating whether the service is an add-on. - True indicates that the service is an add-on - False indicates that the service is not an add-on | boolean | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | service date | The date on which the service was performed. | date | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | appointment status | The current status of the appointment. Possible values: - Cancel - Checkin - Closed - Confirmed - Noshow - Open | varchar(100) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | is therapist requested | Indicates whether the guest requested for a particular employee to perform the service. Possible values: - True: Employee was requested - False: Employee was not requested | boolean | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | list price | List price of the item. | numeric(19,4) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | sale price | The price at which the item was sold. The price is inclusive of discounts and taxes. | numeric(19,4) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | invoice id | A unique identifier for the invoice. It is unique within the organization. | varchar(36) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | invoice item id | Unique identifier for each invoice item within the invoice. | varchar(36) | |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | invoice close date | The date on which the invoice was closed. | This value is not populated if the invoice is not closed. | date |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | payment date in center | The date on which the invoice was closed. | The date and time at which the payment was made for the invoice item id. | timestamp |
Item | These fields detail the attributes of the item whose sale resulted in a commission for the employee. | split commission percentage | Split commission percentage to which the employee is entitled. | float | |
Pay Period | These fields detail out the time period for which the payroll is calculated. | pay period name | The name of the payroll generation period. | varchar(254) | |
Pay Period | These fields detail out the time period for which the payroll is calculated. | payroll start date | The start date of the payroll period. | Timestamp | |
Pay Period | These fields detail out the time period for which the payroll is calculated. | payroll end date | The end date of the payroll period. | Timestamp | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | deduction | While commissions are awarded to employees for items they sell or services they perform, businesses may also want to apply deductions for costs they incur. Deductions can include costs such as advertising, rent, and other internal costs. This field populates the deduction applied to the corresponding item. | For more information, refer: Configure Organization Level Settings for Employee Commissions | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | item sale revenue | The revenue associated with the employee for performing services and selling products, memberships, packages, and giftcards. This revenue already takes into account 'deduction'. It represents the revenue on which the employee commission is calculated. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | commission factor | The commission factor or adjustment applicable. This can be configured for each service, product, package, etc. For example, if this is 200%- the employee earns double the configured commission. If this is 50%- the employee earns half the configured commission. | float | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | commission | The commission amount earned by the employee for the corresponding item. For refunded items, this can be a negative value. This is calculated against 'item sale revenue' and already has the commission factor calculated in. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | free service revenue | The revenue associated with the employee that accounts for free service revenue. | For more information, refer: What are Free Services in Zenoti? | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | free service commission | The commission earned by the employee as a result of free service revenue. This is calculated against 'free service revenue' and already has the commission factor calculated in. | For more information, refer: Configure Free Service Commissions | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | no show revenue | Guests may need to pay a no-show fee when they do not show up for their appointments. This is called no-show revenue. This field populates the no-show fee applicable to the corresponding item. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | no show commission | The commission earned by the employee as a result of no show revenue. | For more information, refer: Configure Commissions for No-Show and Canceled Invoices - An Overview | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | cancellation revenue | Guests may need to pay a cancellation fee when they cancel their appointments. This is called cancellation revenue. This field populates the cancellation fee applicable to the corresponding item. | For more information, refer: Configure Commissions for No-Show and Canceled Invoices - An Overview | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | cancellation commission | The commission earned by the employee as a result of cancellation revenue. | For more information, refer: Configure Commissions for No-Show and Canceled Invoices - An Overview | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | invoice level deduction | Commission deductions can be set up on each invoice from which the employee earns commission. This field displays the amount that is to be deducted from the employee's commission for the corresponding invoice. If there are multiple items in the invoice, this is prorated against each item. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | service deduction | Commission deductions can be set up on each service from which the employee earns commission. This field displays the amount that is to be deducted from the employee's commission for the corresponding service. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | request therapist bonus | The bonus earned by the employee as a result of the corresponding guest requesting for that particular employee to perform the service. | For more information, refer: Configure Request Therapist Bonus | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | bonus commission | Any additional commission bonus earned by the employee such as amount earned for being a good performer. | For more information, refer: Add Job Info for a New Employee | decimal(19,4) |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | class commission | The commission earned by an employee as a result of selling a class. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | class bonus | The bonus earned by the employee when the required number of people attend a class. | decimal(19,4) | |
Commissions | These fields detail out the Sales Commissions related details associated with the corresponding item, employee, and pay period. | tip | The tip amount earned by the employee against the corresponding invoice. This is populated in a separate row against the invoice id, since this cannot be split out for each item in the invoice. Note that tips are populated only for employees who earned some commission in the corresponding pay period. | decimal(19,4) |