Appointment fact table for Dedicated Redshift
This table lists the fact table columns used for appointment data model.
Bi_FactAppointment
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 Feedbackwid | ||
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 | |||
appointmentcancelreasonwid | bigint | A code that identifies the reason for cancellation of the appointment.(Bi_DimReasons) | Bi_DimReasons Reasonwid | ||
AppointmentCategorywid | bigint | A uniqie identifier that indicates the Appointment's category(Bi_DimAppointmentCategories) | Bi_DimAppointmentCategories AppointmentCategorywid | ||
Appointmentid | VARCHAR(36) | Unique identfier for the appoinment | |||
AppointmentPK | INT | Unique identfier for the appoinment. Internal to Zenoti system | |||
appointmentrecurrencewid | BIGINT | The unique identifier for group of recurring appointments. | |||
AppointmentSourcewid | INT | A unique identifier that indicates the source of appointment, generated in status dimension(Bi_DimItemSource) | Bi_DimItemSource SourceNum | ||
appstatuswid | 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 | Source from where the appointment was cancelled. | |||
CancelledSourcewid | bigint | A code that identifies the source of Cancellation of the appointment.(Bi_DimItemSource) | Bi_DimItemSource 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 |
| ||
centerwid | INT | Unique identifier for the center | Bi_DimCenter Centerwid | ||
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. | |||
closebywid | INT | A unique identifier of the employee who closed the invoice(bi_dimuser). | Bi_Dimuser userwid | ||
CompletedTime | TIMESTAMP | The date and time on which the appointment/ segment status was changed to 'Closed'. | |||
createdbywid | INT | A unique identifier of the employee who created the invoice | Bi_Dimuser userwid | ||
CreationDateInCenter | DATE | The date on which the appointment was created. | |||
CreationDateTimeInCenter | TIMESTAMP | The date and time on which the appointment was created. | |||
daypackageversionwid | bigint | 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 | |||
equipmentwid | bigint | unique identifier for the equipment used for the appointment | Bi_DimEquipment Equipmentwid | ||
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. | |||
factappointmentwid | bigint | 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. | |||
FeedbackSourcewid | INT | A code that identifies the source of the feedback.(Bi_DimItemSource) | Bi_DimItemSource SourceNum | ||
FeedbackUpdatedBywid | INT | A code that identifies the User that updated the feedback(Bi_DimUser) | Bi_Dimuser userwid | ||
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 | |||
feedbackwid | bigint | A unique identifier to get the feed back details of an appointment, generated in feedback dimension(Bi_DimFeedback) | Bi_DimFeedback_s3 Feedbackwid | ||
FinalSalePrice | FLOAT | DO NOT USE - THIS FIELD IS BEING DEPRECATED. | |||
initialsuggestedstarttimeincenter | TIMESTAMP | For queue model. Indicates initial suggested start time of appointment when guest enters queue | |||
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. | |||
isaddonservicewid | bigint | Indicates if there is an addon for the service or not | |||
isAppointmentReScheduled | SMALLINT | Indicates if the Appointment has been rescheduled. | |||
IsPrimaryAppointmentwid | INT | The code that indicates if within the group, the appointment will be the first one to be serviced | |||
isrebookwid | bigint | A unique identifier that signifies whether this appointment resulted in a rebook or not | |||
IsRebookedTargetwid | bigint | 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. | |||
MemberGuestStatuswid | bigint | 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 organizationId | ||
PackageGroupNo | INT | The package group code | |||
ParentAppointmentPK | DECIMAL(19,0) | The unique identifier of the service with which the segment is associated. | |||
PODID | integer | Internal to the ETL, says from which POD data is being synced the table | |||
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. | |||
resourcewid | bigint | unique identifier for the room in which is booked for the appointment | Bi_DimResource Resourcewid | ||
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. | |||
servicewid | bigint | Uniue identifier for the service provided. | Bi_DimService Servicewid | ||
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 | |||
servicesegmentwid | bigint | A unique identifier that identifies the segment of the service booked as part of the appointment(Bi_DimServiceSegment). | Bi_DimServiceSegment ServiceSegmentwid | ||
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. | |||
SoldBywid | NVARCHAR(MAX) | A unique identifier of the employee who is sale by of the invoice | Bi_Dimuser userwid | ||
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. | |||
Therapistwid | bigint | Unique identifier of the Therapist. | Bi_Dimuser userwid | ||
TherapistRequestType_statuswid | bigint | A unique identifier that identifies the therapist request type made for the appointment | |||
userwid | bigint | Unique identifier of the guest. | Bi_Dimuser userwid | ||
void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||
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. | |||