Employee Schedule Data Model
This data source brings in details of how employees' time is utilized- how much time was scheduled, how much time was clocked-in, how much time was spent on services, etc.
Example Use-Cases
Scheduled hours, Attendance hours, Break hours, Serviced hours by employee
Utilization % of employees by day of week
Granularity of Data
Data for each employee and day is brought in as a separate row. Further, Scheduled time records and Attendance time (clocked time) records for each day are split out into different rows. Furthermore, if a schedule/attendance within a day has multiple types of entries - such as breaks, work tasks, multiple checkins and checkouts - data is again split into different rows to accommodate this granularity. If appointments are done outside an employee's scheduled time, each appointment is brought in as a separate row with a dummy schedule created against it. Cancelled, noshow, and voided appointments are not brought in. In the below example, Kristin was scheduled for 9 AM - 5 PM on 2/15, with two breaks in between. However, she did two services from 8-8:30 AM. Since these are outside scheduled hours, they are brought in as two separate rows against a staged schedule. Her organization allows multiple check-ins during a day. She was clocked in from 8:55 AM-12:01 PM and then again from 12:07-2:19 PM.
![]() |
Category | Description | Field | New Definition | Examples/Notes | Data Type |
---|---|---|---|---|---|
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | active | A flag indicating whether the employee break category is active. This field is populated only when the 'record name' is 'Break'. | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | base center code | The unique code associated with the base center. | Integer | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | base center name | The center that the employee is associated with as per employee profile. | varchar(200) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | break account name | The break account name of the organization. In most cases, this is the same as 'organization account name'. This field is populated only when the record name is 'Break'. | varchar(1024) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | can exceed room capacity | A flag indicating whether the room capacity can be exceeded. | boolean | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | capacity | The capacity of the resource. | integer | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | center city | The city in which 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 two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | center code | The unique code associated with the center. | varchar(20) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | Center End Time | The time at which the center is scheduled to close. This corresponds to the 'scheduledate' field. This is stored in HHMM format. E.g. 1630, 1400, etc. | Note: This column populates the most up-to-date time configured for 'CENTER END TIME' when you configure Hours at the center level in Zenoti. Any exceptions configured to the end time are honored. | Integer |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | center name | The center where the employee provided the service. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | Center Start Time | The time at which the center is scheduled to start. This corresponds to the 'scheduledate' field. This is stored in HHMM format. E.g. 1630, 1400, etc. | Note: This column populates the most up-to-date time configured for 'CENTER START TIME' when you configure Hours at the center level. Any exceptions configured to the start time are honored. | Integer |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | center state name | The state in which 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 two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | center zip | The postal code of the center's location. | varchar(32) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | count in utilization | A flag indicating whether the break should be counted in utilization. This field is populated only when the 'record name' is 'Break'. | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | country name | The country in which the center is located. | varchar(4096) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | day of week | The day of the week corresponding to the 'scheduledate'. | varchar(320) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee - code | Unique code of the employee. This is the code taken from the employee's profile in Zenoti. | varchar(128) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | employee break category code | The code associated with the break category. This field is populated only when the 'record name' is 'Break'. | varchar(512) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | employee break category name | The category name of the break. This field is populated only when the 'record name' is 'Break'. | varchar(1024) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | employee break duration | The duration (in minutes) that is configured for the corresponding break. This is a default duration and it can be modified from the appointment book. This field is populated only when the 'record name' is 'Break'. | integer | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | 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 | varchar(128) |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee first name | First name of the employee. | varchar(160) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee gender | The gender of the employee. | varchar(14) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee job name | Job name associated with the employee. This is the most up-to-date job name associated with the employee as per employee profile. | Therapist, Manager, Aesthetician etc. | varchar(510) |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee last date | The date on which the employee stopped working for the organization. | timestamp | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee last name | Last name of the employee. | varchar(256) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee middle name | Middle name of the employee. | varchar(128) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee mobile phone | Mobile number of the employee. | varchar(64) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee start date | The date on which the employee started working for the organization. | timestamp | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee tags | Tags associated with the employee. | varchar(65535) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | employee wid | A unique identifier for the employee. This is generated in Analytics, it does not exist in the core system. | integer | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | endtime | The end date and time corresponding to the 'record name'. For example, if the record name is 'Schedule', this field populates the end date and time of the schedule. | timestamp | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | etllastupdatedby | ETL system admin user who inserted or updated the corresponding record in this data source. | varchar(50) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | etllastupdatedon | Latest timestamp at which the corresponding record was either inserted or updated in this data source. | timestamp without time zone | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | factemployeescheduleid | A system-generated unique identifier for each record in the employee schedule data source. This is generated within Analytics. | integer | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | holiday date | The date of a particular holiday. It is populated only if the 'scheduledate' falls on a holiday. | timestamp | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | holiday description | The description of the holiday. | varchar(800) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | indicator color | The hexadecimal color code of the room. This is the color code that appears in the Appointment Book for appointments associated with the corresponding room. | varchar(128) | |
Employee | These fields describe the attributes of the employee associated with the corresponding schedule/attendance. | is employee inactive | A flag indicating if the employee is inactive. An employee is considered inactive if he has been removed from the system. - True: Employee is inactive - False: Employee is active | boolean | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | is holiday valid | A flag indicating whether an earlier declared holiday is valid or not. For example, a holiday may be retracted - in this case, this field shows whether the holiday is still valid. | boolean | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | lunch time old | The time (in minutes) taken for lunch. This is the difference between 'endtime' and 'starttime'. This is populated only when the 'record name' is 'Break' and the 'employee break category name' contains the word 'lunch' in it. | float | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | mandatory break time per day | The mandatory break time (in mins) given to the employee every day. This field is populated only when the 'record name' is 'Break'. | integer | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | 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. | Integer | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | online booking end time | The time at which online bookings close at the center. This corresponds to the 'scheduledate' field. This is stored in HHMM format. E.g. 1630, 1400, etc. | Note: This column populates the most up-to-date time configured for 'ONLINE BOOKING END' when you configure Hours at the center level in Zenoti. Any exceptions configured to the end time are honored. | Integer |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | online booking start time | The time at which online bookings start at the center. This corresponds to the 'scheduledate' field. This is stored in HHMM format. E.g. 1630, 1400, etc. | Note: This column populates the most up-to-date time configured for 'ONLINE BOOKING START' when you configure Hours at the center level in Zenoti. Any exceptions configured to the start time are honored. | Integer |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | operating center | A flag that indicates if 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 two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | 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 two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | organization name | The formal name of the organization. | 'Massage Spa Express' is the formal name of an organization. | varchar(512) |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | record name | This field describes the type of record being populated. Possible values: - Schedule: Indicates an employee's configured schedule. - Break: Indicates a break within the employee's schedule. - Attendance: Indicates the time that the employee checks-in and checks-out during the day. Businesses can choose whether to allow multiple check-ins in a single day through an org-level setting in Zenoti. This record name is used when only one check-in and check-out time is recorded for the employee during the entire day. - MultipleAttendance: Indicates the time that the employee checks-in and checks-out during the day. Businesses can choose whether to allow multiple check-ins in a single day through an org-level setting in Zenoti. This record name is used when the employee checks-in and checks-out multiple times during the day. Each instance of check-in and check-out shows up in a separate row. - StagedSchedule: This is a dummy record created when the employee provides services outside his/her schedule hours. For each appointment which is serviced outside schedule hours, a separate row is created. - Staged Attendance: This is a dummy record created when the employee has multiple check-in and check-out records in the Multiple Attendance table, but no entry in the Attendance table. This is very rare and only occurs in case of data errors. | This Attendance/ Multiple Attendance setting is defined in Zenoti by enabling the Allow multiple check-ins setting from the Employee configuration section. | varchar(36) |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | recovery time | The total recovery time (in minutes) taken by the employee within the scheduled time. Only appointments that are in (Open, Closed, Check-in, Confirmed) statuses are considered. This is populated only when the 'record name' is 'Schedule' or 'StagedSchedule'. | integer | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | resource code | The unique code associated with the resource. | varchar(128) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | resource name | The name of the resource. | varchar(128) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | resource type name | The type of resource. | varchar(128) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | room category code | The category code of the room. | varchar(800) | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | room category name | The category name of the room. | varchar(800) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | scheduled hours | The scheduled time (in hours). This is the difference between 'endtime' and 'starttime'. This is populated only when the 'record name' is 'schedule'. | float | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | scheduledate | The date associated with the corresponding record. | timestamp | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | service time | The total time (in minutes) for which the employee provided a service within the scheduled time. Only appointments that are in (Open, Closed, Check-in, Confirmed) statuses are considered. This includes the recovery time and is populated only when the 'record name' is 'Schedule' or 'StagedSchedule'. | integer | |
Rooms and Resources | These fields describe the utilization of resources/rooms which are associated to employees. If the setting to associate a room with an employee is turned off, these fields are blank. | show in receipt | A flag indicating whether to show resource details in the receipt or not. | boolean | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | starttime | The start date and time corresponding to the 'record name'. For example, if the record name is 'Schedule', this field populates the start date and time of the schedule. | timestamp | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | Status | This is very specific for a particular use-case. DO NOT USE. | varchar(1024) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | status name | The status of the employee in the scheduled time. This is populated only when 'record name' is 'Schedule' or 'StagedSchedule'. It shows whether the employee was working or not in the scheduled time. | Any leave types defined from 'Organization level appear here as the status. Examples: Available, Leave, Maternity, Sick Time, Training, Vacation | varchar(1024) |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | Utilization Factor | This field describes whether the employee's utilization should be calculated based on scheduled hours or attendance hours. This is a center-level setting in Zenoti. Possible values: - Schedule - Attendance | This setting is defined in Zenoti at the center level by navigating to 'Configuration> Search for and select Calculate Payroll Hours/Employee Utilization Based on' | varchar(200) |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | worktaskcode | The code associated with the 'worktaskname'. | varchar(512) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | worktaskname | The name of the work task. These are tasks that employees can undertake beyond their principal job. It is advised that employees check-in and checkout separately for each such task so that an accurate log of their work is maintained in Zenoti. | varchar(1024) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center -> Center where the employee provided the service. 2) Base Center -> Center that the employee is associated with as per employee profile. In some situations, it is possible that these 2 centers are different. The fields in this group detail the various attributes of the center and base center. | zone name | The zone to which the center belongs. | varchar(800) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | minutes withinschedule | double precision | ||
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | minutes outsideschedule | double precision | ||
Internal | Internal to Zenoti system | podname | Internal to the ETL, says from which POD data is being synced the table | varchar ( 200 ) | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | holiday timeoff in mins | Indicates number of minutes employee has logged as holiday hours | float8 | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | vacation timeoff in mins | Indicate number of minutes employee has logged as vacation hours | float8 | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | timeoff in minutes | Time off employee has taken in minutes in the day | integer | |
Schedule, Attendance | These fields detail out the employee's schedule and attendance records. | is mandatory break complete | Indicates if employee completed mandatory break | boolean |