Appointment fact table
Bi_FactAppointment_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
ActionPerformedDateTimeInCenter | TIMESTAMP | The latest date and time on which a change was made to the appointment status, the appointment start time, or the appointment end time. | |||
ActualAppointmentTime | TIMESTAMP | The scheduled start date and time of an appointment at the time of its creation. | |||
ActualStartTime | TIMESTAMP | The date and time on which the appointment/ segment status was changed to 'Checkin'. | |||
AmbienceFdbk | INT | Feedback rating given by guest for question 'guest feedback ambience' in the 'V1' feedback form. | Bi_DimFeedback_s3 FeedbackId | ||
AppGroupId | VARCHAR(36) | A unique identifier for each appointment group. Appointment group refers to a set of appointments that are booked together for a guest | |||
AppGroupPK | INT | Unique identifier is created in case of appointments clubbed into a group. This is the identifier to the appointment group | |||
AppointmentCancellationReasonId | VARCHAR(36) | A code that identifies the reason for cancellation of the appointment.(Bi_DimReasons) | Bi_DimReasons_s3 ReasonId | ||
AppointmentCategoryId | NVARCHAR(MAX) | A uniqie identifier that indicates the Appointment's category(Bi_DimAppointmentCategories) | Bi_DimAppointmentCategories_s3 AppointmentCategoryId | ||
AppointmentPK | INT | Unique identfier for the appoinment | |||
AppointmentSource | INT | A unique identifier that indicates the source of appointment, generated in status dimension(Bi_DimItemSource) | Bi_DimItemSource_s3 SourceNum | ||
appointmentstatus | INT | A unique identifier that signifies the status of the Appointment | |||
AvgFeedback | FLOAT | Feedback rating given by guest for question 'guest feedback ambience' in the 'V1' feedback form. | |||
CancelledSource | INT | A code that identifies the source of Cancellation of the appointment.(Bi_DimItemSource) | Bi_DimItemSource_s3 SourceNum | ||
CancelOrNoShowStatus | INT | 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 |
| ||
CenterId | VARCHAR(36) | Unique identifier for the center | Bi_DimCenter_s3 CenterId | ||
CheckInDateTime | TIMESTAMP | The date and time when the appointment status was changed to 'Checkin'. | |||
CleanlinessFdbk | INT | Feedback rating given by guest for question 'guest feedback cleanliness' in the 'V1' feedback form. | |||
ClosedBy | NVARCHAR(MAX) | A unique identifier of the employee who closed the invoice(bi_dimuser). | Bi_Dimuser_s3 userid | ||
CompletedTime | TIMESTAMP | The date and time on which the appointment/ segment status was changed to 'Closed'. | |||
CreatedBy | NVARCHAR(MAX) | A unique identifier of the employee who created the invoice | Bi_Dimuser_s3 userid | ||
CreationDateInCenter | DATE | The date on which the appointment was created. | |||
CreationDateTimeInCenter | TIMESTAMP | The date and time on which the appointment was created. | |||
DayPackageID | NVARCHAR(MAX) | Unique identifier for day package | |||
Discount | FLOAT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
EndDateInCenter | DATE | date on which appointment ended | |||
EndTimeInCenter | TIMESTAMP | date and time on which appointment ended | |||
EquipmentId | NVARCHAR(MAX) | unique identifier for the equipment used for the appointment | Bi_DimEquipment_s3 EquipmentId | ||
etlCreatedBy | INT | ETL system admin user who inserted or updated the corresponding record in this table. | |||
etlCreatedDate | TIMESTAMP | Latest timestamp at which the corresponding record was either inserted or updated in this table. | |||
factAppointmentId | INT | Unique identifier for the row. Primary Key | Yes | ||
FeedbackGiven | BOOLEAN | A flag indicating whether the feedback was provided. | |||
FeedbackNotes | NVARCHAR(65535) | The feedback comments for the appointment. | |||
FeedbackOrganizationId | VARCHAR(36) | A unique identifier to get the feed back details of an appointment, generated in feedback dimension(Bi_DimFeedback) | Bi_DimFeedback_s3 OrganizationId | ||
FeedbackSource | INT | A code that identifies the source of the feedback.(Bi_DimItemSource) | Bi_DimItemSource_s3 SourceNum | ||
FeedbackUpdatedBy | VARCHAR(36) | A code that identifies the User that updated the feedback(Bi_DimUser) | Bi_Dimuser_s3 userid | ||
FeedbackUpdatedDateTime | TIMESTAMP | The date and time on which the feedback was provided by the guest. | |||
FeedBackVersion | SMALLINT | The version of feedback form used. Possible values: - 1 indicates Guest Feedback V1 - 2 indicates Guest Feedback V2 | |||
FinalSalePrice | FLOAT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
Invoice_no | VARCHAR(160) | Unique identifier for invoice generated. Unique within center | |||
InvoiceClosedDateInCenter | DATE | The date at which the invoice was closed in the center of appointment | |||
InvoiceClosedDateTimeInCenter | TIMESTAMP | The timestamp at which the invoice was closed in the center of appointment | |||
invoiceid | VARCHAR(36) | Uniue identifier for the invoice generated. Unique within an organization | |||
InvoiceStatus | SMALLINT | This indicates whether the invoice is open or closed. | |||
IsAddOn | INT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
IsAddonPrimaryAppointmentPK | DECIMAL(19,0) | The 'appointment pk' of the appointment to which the add-on service belongs. | |||
IsAddOnSoldForThisService | INT | Indicates if there is an addon for the service or not | |||
isAppointmentReScheduled | SMALLINT | Indicates if the Appointment has been rescheduled. | |||
IsPrimaryAppointment | INT | The code that indicates if within the group, the appointment will be the first one to be serviced | |||
IsRebooked | INT | A unique identifier that signifies whether this appointment resulted in a rebook or not | |||
IsRebookedTarget | SMALLINT | A unique identifier that signifies whether this appointment is an rebooked appointment or not, generated in status dimension(Bi_DimStatus) | |||
IsSegment | INT | A flag indicating whether the corresponding appointment is a segment or not. - True: Indicates that the appointment is a segment - False: Indicates that the appointment is not a segment | |||
IsSegmented | INT | A flag indicating whether the service has segments. | |||
IsSurprise | BOOLEAN | A flag to indicate whether the appointment was created for a walk-in guest. -1 indicates a walk-in guest. -0 indicates that it is not for a walk-in guest. | |||
MemberGuestStatus | INT | Membership status of the guest as a primary member. If the guest is associated with a membership as a primary member, the status of the membership is populated here. If the guest is associated with multiple memberships as a primary member, the status is populated as per a precedence order.(Bi_DimStatus) | |||
Note | NVARCHAR(16000) | The additional notes for the appointment | |||
OrganizationId | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 organizationId | ||
PackageGroupNo | INT | The package group code | |||
ParentAppointmentPK | DECIMAL(19,0) | The unique identifier of the service with which the segment is associated. | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
RebookedAppointmentCount | INT | The number of appointments or appointment groups that got rebooked from the source. This is populated only for source appointments. If the entire appointment group is rebooked, this field populates the number of times the appointment group is rebooked. If a few appointments within a group are rebooked, this field populates the number of times the appointment is rebooked. | |||
rebookedAppointmentPK | DECIMAL(19,0) | Indicates the original appointment pk that has been rebooked(Source appointment pk) | |||
RebookedSourceAppointmentPK | DECIMAL(19,0) | The 'appointment pk' of the source appointment which caused the rebooking. This is filled only when few of the appointments within the appointment group are rebooked. | |||
RebookedSourceGroupId | VARCHAR(36) | The 'appointment group id' of the source appointment which caused the rebooking. This is filled only if the entire appointment group is rebooked. | |||
Recommended | BOOLEAN | This indicates whether the guest has selected 'Yes'/'No' for the 'guest feedback question 1' in the 'V1' feedback form. | |||
RecoveryMinutes | INT | The ideal recovery time taken to complete a service (in minutes), as set up in Zenoti. | |||
RecurrenceAppointmentGroupPrimaryKey | DECIMAL(19,0) | The 'appointment group primary key' of the first appointment in the appointment group which was set to recur. This field can be used to identify the first appointment from where the recurring appointments started. | |||
RecurrenceAppointmentInvoiceNo | NVARCHAR(160) | The 'invoice no' of the first appointment in the appointment group which was set to recur. This field can be used to identify the first appointment from where the recurring appointments started. | |||
RecurrenceAppointmentPk | DECIMAL(19,0) | The 'appointment pk' of the first appointment in the appointment group which was set to recur. This field can be used to identify the first appointment from where the recurring appointments started. | |||
RecurrenceId | NVARCHAR(MAX) | The unique identifier for group of recurring appointments. | |||
RoomID | NVARCHAR(MAX) | unique identifier for the room in which is booked for the appointment | Bi_DimResource_s3 ResourceId | ||
SalePrice | FLOAT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
ServiceFdbk | INT | Feedback rating given by guest for question 'guest feedback service' in the 'V1' feedback form. | |||
serviceid | VARCHAR(36) | Uniue identifier for the service provided. | Bi_DimService_s3 ServiceID | ||
ServiceInternalCost | DECIMAL(19,4) | The internal cost that a center incurs for a service. | |||
ServiceMinutes | INT | This is the duration of an appointment in minutes. This is the difference between 'appointment start date and time' and 'appointment end date and time'. Note: 'service minutes' does not include the recovery time | |||
servicesegmentid | VARCHAR(36) | A unique identifier that identifies the segment of the service booked as part of the appointment(Bi_DimServiceSegment). | Bi_DimServiceSegment_s3 ServiceSegmentID | ||
ServiceSoldCount | INT | This field is filled with '1' in each row. Sum of this column would give us the number of rows in the data source. | |||
ShareFeedback | INT | This indicates whether the guest has selected 'Yes'/'No' for the 'guest feedback question 2' in the 'V1' feedback form. | |||
SoldBy | NVARCHAR(MAX) | A unique identifier of the employee who is sale by of the invoice | Bi_Dimuser_s3 userid | ||
StartDateInCenter | DATE | date on which appointment is started | |||
StartTimeInCenter | TIMESTAMP | date and time on which appointment started | |||
Taxes | FLOAT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
TherapistFdbk | INT | Feedback rating given by guest for question 'guest feedback therapist' in the 'V1' feedback form. | |||
TherapistId | VARCHAR(36) | Unique identifier of the Therapist. Unique for a guest across the organization | Bi_Dimuser_s3 userid | ||
TherapistRequestType | INT | A unique identifier that identifies the therapist request type made for the appointment | |||
userid | VARCHAR(36) | Unique identifier of the guest. Unique for a guest across the organization | Bi_Dimuser_s3 userid | ||
void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
groupid | BIGINT | Internal to Zenoti. Used for data processing. | |||
utmsource | NVARCHAR(128) | Helps you the identify the booking source (Google, Facebook, Instagram) | |||
utmmedium | NVARCHAR(128) | Helps you the identify the booking source (Google, Facebook, Instagram) | |||
BundlePackageID | VARCHAR(36) | Helps you identify the bundle package (GUID). In case of multiple bundle packages, then ID remains same. | |||
BundlePackageNo | INT | Helps you identify the number of bundle packages in a invoice. | |||