Skip to main content

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.

httpreportszenoticomwp-contentuploads201711datasource__employeeschedule_001jpg.jpeg

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