Forms data source
The forms that guest fills out are useful to record guest details, service requirements, service journey, etc. These forms can give businesses insights into guest preferences and satisfaction. Currently, Analytics captures data filled in Guest and Service forms.
The data source stores the form data in the Forms_fact_consolidated table.
Data from the forms can be divided into two categories: Metadata and guest-filled data.
Metadata is the data about the form. For example, information about when the form was filled, which center, who is the therapist, who is the reviewer, etc. This data is standard across all customers.
Guest-filled data is the data a guest or therapist fills in the form. For example, guest details, preferences, signatures, etc. This data is specific for each customer and each form.
Use case:
A guest fills out a consent or consultation form for a booked appointment. The guest-filled data is stored against the booked appointment for each form.
If the guest books another appointment for the same service, the data source stores the new form data for the new appointment. If the form does not expire, guests can use the same data from the first appointment or edit forms for the second appointment.
If an appointment is No Show or canceled, the appointment still retains the form data. For each service and form, a new version of data is stored against each appointment.
Each service form has a unique "form name" column. Forms data is stored in the Forms_fact_consolidated table in the following columns:
Column | Description |
|---|---|
form name | Name of the form |
field name | Name given to input field in the form. This is different from the label that is visible in the form |
value1 | Stores value of fields with data type as float |
value2 | Stores value of fields with date data type |
value3 | Stores value of fields with date type boolean |
value4 | Stores value of fields with date type string |
datatype description | This flag indicates what type of data is stored in the input field |
pointer | Accepted values are 1, 2, 3, and 4. 1 indicates the field value stored in the "value1" column 2 indicates the field value stored in the "value2" column 3 indicates the field value stored in the "value3" column 4 indicates the field value stored in the "value4" column |
lastupdateddate | This field indicates when the form was last filled |
apphtmlformpk | Internal identifier for Forms ETL process |
htmlformpk | Internal identifier for forms ETL process |
Examples:
Forms data is represented in the UI in HTML and stored in JSON format in the database. The JSON is converted into a table entry in the Forms_fact_consolidated table.
JSON example
Each data entry is recorded as a row in the database table. For example, for the field name "note", the value is empty.
For the next row in the table, the field name is "Name" and the value is "Sagar Kshatri"
 |
Table entry for this example:
Field name | Data type description | Pointer | value1 | value2 | value3 | value4 |
name | string | 4 | Sagar Kshatri | |||
DOB | date | 2 | 06/01/1988 |
In this sample form, the field name is note and the input value is a string. So, the value entered in the note field is stored in column value4. The pointer column value identifies which of the 4 value columns is considered for any field name.
Keep the field names unique so that data can be easily deciphered.
In multi-select options scenarios, selected options are updated in the value4 column with their assigned value in HTML. Unselected options are updated as 0 in the value1 column.
When we assign name-value to different inputs, as a best practice, avoid assigning numbers to values as it is difficult to interpret what those numbers mean.
Assume in a multi-select option, you assign values 1 to Male and 2 to Female. If you assign 0 as gender, the analytics team will have to interpret what it indicates as 0 is used for not selected scenarios also.
For signatures and annotations, if there is an entry, the value is 1.
If the input of a text field has more than 5000 characters, the data in the field is a boolean value, which means the value is 1. If the input has less than 5000 characters, the input text is stored as-is in the data source.
Forms data model
Group | Field | Definition | Examples/Notes | Data type |
|---|---|---|---|---|
Center,Zone,Organization | "center city" | The City where Center is located | varchar(128) | |
Center,Zone,Organization | "center name" | Center where Form is filled | varchar(128) | |
Center,Zone,Organization | "center time zone" | Timezone of the center | varchar(128) | |
Center,Zone,Organization | "center zip" | Zip code of the Center location | varchar(32) | |
Form | "data type description" | This flag indicated what type of data is stored in the input field | data input in Form fields are stored in "value1", "value2", "value3", value4" columns. This field value will indicate what type of data is stored in those field. For a given row, data is stored only in 1 of the 4 columns. List of description types are "String", "Boolean", "Float", "Date" | varchar(36) |
Form | "dim form wid" | unique identfier for each each of table. | bigint | |
Form | "fact form id" | Unique id for each record of table. Generated within Analytics | bigint | |
Form | "field name" | Name given to input field in the form. This is different from label that is visible in the form. | varchar(65535) | |
Form | "form name" | Name of the form | varchar(2048) | |
Form | "form source" | Indicates where form is filled | Examples: Web, IOS, Webstore v2, Android | varchar(36) |
Form | "form type" | Identifes what type of form it is | Example: service form, guest form, etc.. | varchar(36) |
Guest | "guest first name" | First name of the guest | varchar(160) | |
Guest | "guest gender" | Gender of guest | varchar(14) | |
Guest | "guest id" | Unique identifier of the guest. Unique for a guest across the product | varchar(36) | |
Guest | "guest last name" | Last name of the guest | varchar(256) | |
Guest | "guest middle name" | Middle name of guest | varchar(128) | |
Form | "is form submitted" | Indicates if form is submitted or not | "true" for submitted, "false" for not submitted | boolean |
Service | "is service inactive" | A flag indicating whether the service is inactive. A service is considered inactive if it has been removed from the system. - True indicates that the service is inactive - False indicates that the service is active | 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) | |
Employee | "reviewed on" | Date on which form is reviewed | timestamp | |
Employee | "reviewer first name" | Name of the form reviewer | varchar(160) | |
Employee | "reviewer gender" | Gender of form reviewer | varchar(14) | |
Employee | "reviewer id" | unique identifier of reviewer | varchar(36) | |
Employee | "reviewer last name" | Last name of form reviewer | varchar(256) | |
Employee | "reviewer middle name" | Middle Name of Form reviewer | varchar(128) | |
Service | "service businessunit" | The business unit to which the service belongs. | varchar(200) | |
Service | "service category" | The category of the service. This is set up as Sub-category in core | varchar(256) | |
Service | "service length" | The ideal time taken to complete a service (in minutes), as set up in Zenoti. | integer | |
Service | "service name" | Name of the service | varchar(512) | |
Service | "service parent category" | The parent category of the service. This is set up as Category in core. | varchar(256) | |
Service | "service tags" | The tags associated with the service. | varchar(65535) | |
Form | "start date" | Date of appoinment | timestamp | |
Form | "submitted date" | The date on which a form is submitted for the first time. When a user clicks on the submit button in UI, this column value is updated. | timestamp | |
Employee | "therapist first name" | First name of therapist | varchar(160) | |
Employee | "therapist gender" | Gender of Therapist | varchar(14) | |
Employee | "therapist id" | Unique identifier of Therapist | varchar(36) | |
Employee | "therapist last name" | Last name of therapist | varchar(256) | |
Employee | "therapist middle name" | Middle name of Therapist | varchar(128) | |
Center,Zone,Organization | "zone name" | The Zone that the center belongs to | varchar(800) | |
Form | appointmentid | Appointment id of the appointment for which form is filled | varchar(36) | |
Form | appointmentnoshoworcancel | Tells whether fee is collected in case of no show or cancelled scenarios | "-1": cancel and fee collected, "-2": noshow and fee collected. 0: fee not collected | integer |
Center,Zone,Organization | centerwid | Unique identfier of Center, present only in Analytics | bigint | |
Form | etllastupdatedby | ID of user who updated the row in Analytics | varchar(50) | |
Form | etllastupdatedon | Timestamp on when the row is updated in Analytics | timestamp | |
Form | formappointmentstatus | Current status of appointment | 0-new; 1-closed; 2-check in; 4-confirm; -1 Cancel; -2 Noshow, -3-void | integer |
Form | formexpirydate | Expiry date for the form | timestamp | |
Form | formwid | unique identifier of form present only in Analytics | bigint | |
Guest | guestwid | Unique identifier of guest present only in Analytics | bigint | |
Form | isappointmentvoided | Indicates if appointment is void or not | "true" for void, "False" for not void | boolean |
Form | pointer | Indicates which of the 4 columns to look at for the values against "field name column" | If data is stored in "value1" column, field will be updated as "1". Similarly for "value2" it is "2", "value3", it is "3", and for "value4" it is "4" | integer |
Employee | reviewedbywid | unique identifier of reviewer. This identifier is present only in Analytics | bigint | |
Form | reviewneeded | Indicates if review is needed or not. "true" stands for review needed. "False" stands for review not needed | boolean | |
Service | servicewid | unique identifier for a service. This identifier is present only in Analytics | bigint | |
Employee | therapistwid | unique identifier of therapist. This identifier is present only in Analytics | bigint | |
Form | value1 | Indicates the input provided in form against "field name" section. "value1" column is stores Float data types | float8 | |
Form | value2 | Indicates the input provided in form against "field name" section. "value2" column is stores Date-time data types | timestamp | |
Form | value3 | Indicates the input provided in form against "field name" section. "value3" column is stores Boolean data types | boolean | |
Form | value4 | Indicates the input provided in form against "field name" section. "value4" column is stores String data types | varchar(65535) | |
Form | void | Indicates if the row is void or not | "1" for void. "0" for not void rows | integer |
Form | lastupdatedate | The date on which a form is last updated. This value is updated whenever a form is saved, submitted, reviewed, locked, and unlocked | timestamp |