Reservation Automated Reports

This document is provided to help merchants have a better understanding of what automated reports are, how they can be configured and what data is available with each report type.


What You Will Find in this Document

  • Transfer and hosting configurations
  • Report configuration and options
  • List of all reports and metrics
  • Data exported for each report type

Requirements

Transfer Configuration

The below table lists the information required to configure where automated reports will be transferred and how they will be stored. Take note that Booxi doesn’t provide hosting service.

Protocol Select one of the supported protocols: FTP, FTPS, SFTP or GCS. For GCS, no further details are required for the transfer configuration. Please contact your Booxi representative for the GCS guide.
Storage URL

Hostname or IP of FTP site where reports will be sent.

  • Must no include protocol (ex. ftp.domain.com)
  • Must not include a trailing / (slash) (ex.domain.com/)
Storage Port FTP port on which to connect.
Storage Directory Folder on the destination FTP site in which to store reports.
Host Key Fingerprint If you opt for SFTP, please provide a host key fingerprint.
Storage Login User User to log into the storage URL.
Storage Login Password Password to log into the storage URL.

Report Configuration

The content of the reports will be configured per the information below.

Merchant ID

or

Merchant ID Template

Merchant ID or template or any merchant within group for which the report will be generated. For group reports, it must be used in combination with "includeMerchantGroupLocations".
Options Select one of the preconfigured reports. For custom reports, a list of fields to be exported must be provided in a JSON format.
Options JSON For custom reports only. Fields to be exported in JSON format.
Schedule Time at which reports must be generated. Time must be provided in 24H format, in merchant local time. All reports are generated daily.

Report Types

Preconfigured Reports

Booxi provides a list of reports to choose from, each report comes with a predetermined list of fields. Take note that the preconfigured report will only export data marked as default. However, it is possible for a merchant to create a custom report by selecting any combination of fields. Please consult with your Booxi representative to make a selection.

Reservation Export all daily reservations of a merchant location. The report includes date, duration, service, staff, client and revenue associated with a reservation.
Time Slots Export all calendar time slots associated with a merchant. The report includes dates, duration, status, associated staff and store information.
Metric Merchant Occupancy Export data on the number of bookings, scheduled, booked and serviced hours for a merchant.
Metric Group Event Export metrics for all group events held by a merchant. It includes the number of attendees, capacity and occupancy as well as revenue.
Metric Service Occupancy Export occupancy for each service offered by a merchant. It includes the number of bookings, booked and serviced hours as well as revenue service. 
Metric Staff Occupancy Export the occupancy of each staff associated with a merchant. It includes the number of scheduled, booked and serviced hours, time slots and #bookings.
Metric Resource Occupancy Export the occupancy of each resource associated with a merchant. It includes the number of scheduled, booked and serviced hours. 

Customizations

Field Selection

Reports can be customized by selecting any combination of fields and so for each report type. If you wish to customize the content of any report, please consult with your Booxi representative.

Field Names

Reports are generated in CSV format with each field as an individual column. If you wish to rename any or all columns appearing in a report, please consult your Booxi representative.

Field Order

When providing a custom list of fields, the order in which the fields are listed will dictate the order in which they will appear in the report. If you wish to change the order in which fields will appear in a report, please consult with your Booxi representative.


Reservation

Generate a report of all daily reservations of a merchant location. The report includes date, duration, service, staff, client and revenue associated with a reservation.

Filters

The following filters are provided to further customize the report’s content. Once set,  all reservation reports will be generated with the selected filters.

Filter Description Default Value
excludeCancelled Exclude cancelled reservations when exporting. FALSE
includeMerchantGroupLocations Include all merchants in the target merchant group. FALSE
splitPriceAndCurrency Strip currency symbols from all prices and display them in a distinct column. FALSE
useDateFormatYMD Display dates as “yyyy-mm-dd” instead of “dd-mmm-yyyy”. TRUE
useTimeFormat24h Display time in 24H format instead of 12H. FALSE
detailOptions Determine the level of details to export with, group summary, reservations or attendees. RESERVATIONS
quoteMode Specify which quote mode to use, all, minimal, all none null, non numeric. Leave blank if none. ALL
encodingFormat Specify which file encoding format to use, UTF-8 or UTF-16-LE. UTF-16-LE

Fields

The below table lists all available fields for a “Reservation” type report. Fields marked with an asterisk (*) are exported by default. Three levels of detail are available for this report type. Consult the table to know what fields are available for each detail option.

Legend: S=Summary, R=Reservation, A=Attendee

Field Description Detail Options
S R A

group_id*

Group event’s ID.

start_date* Date on which the group event starts.
start_time* Time on which the group event starts.
start_date_time Date and time on which the group event starts.
duration* Group event’s duration excluding spacing.
capacity Group event’s capacity.
attendee_count Group event’s attendee count.
place_left Number of places left in the group event.
price_per_person Price per person.
service_price Service's price.
staff_id* ID of the staff assigned to the group event.
staff_first_name* Staff’s first name.
staff_last_name* Staff’s last name.
service_id* ID of the service provided at the event.
service_name* Service’s name.
category_name Service’s category.
location Location where the event is held.
location_details Details associated with the event.
store_id Store id (i.e. merchant id).
store_number* Store’s number.
store_name Store’s name.
store_city City where the store is located.
store_state State, province or territory where the store is located.
longitude Location’s longitude (geolocation).
latitude Location’s latitude (geolocation).
merchant_id Merchant’s ID.
reservation_id* Group event’s ID. NA
reservation_attendee_count Number of attendees for the event. NA
reservation_status* Reservation’s status. NA
customer_id* External customer ID used with a custom client module. NA
membership_id Client’s membership ID. NA
client_id* Client’s ID. NA
client_first_name Client’s first name. NA
client_last_name Client’s last name. NA
client_email* Client’s email address. NA
client_phone* Client’s phone number. NA
client_mobile* Client’s mobile phone number. NA
client_mobile_or_phone Uses client_mobile, but falls back to client_phone. NA
client_dob Client’s date of birth. NA
client_gender Client’s gender. NA
client_language Client’s preferred language. NA
client_postal_code Client’s postal or ZIP code. NA
home_street Street of a client’s home address. NA
home_city City of a client’s home address. NA
home_state State, province or territory of a client's home address. NA
home_country Country of a client’s home address. NA
home_postal_code Postal or ZIP code of a client’s home address. NA
reminder Should a reminder be sent to the client? NA
booked_by* Shows if a reservation was booked by a client or a member of staff. NA
modified_by Shows if a reservation was last modified by a client or a member of staff. NA
modified_date Date a reservation was last modified on. NA
modified_time Time a reservation was last modified on. NA
modified_date_time Date and time a reservation was last modified. NA
created_date Creation date of the reservation. NA
created_time Creation time of the reservation. NA
created_date_time Creation date and time of the reservation. NA
invoice_subtotal Invoice’s subtotal. NA NA
invoice_tax_1 Invoice’s first tax. NA NA
invoice_tax_2 Invoice’s second tax. NA NA
invoice_total* Total price. NA NA
invoice_fee Transaction fee. NA NA
invoice_paid Has the invoice been paid? NA NA
invoice_balance Balance due on the payment. NA NA
currency Currency in ISO code. NA NA
attendee_id Attendee’s ID. NA NA
attendee_name Attendee’s full name. NA NA
attendee_first_name Attendee’s first name. NA NA
attendee_last_name Attendee’s last name. NA NA
attendee_email Attendee’s email address. NA NA
attendee_phone Attendee’s phone. NA NA
attendee_mobile Attendee’s mobile phone. NA NA
attendee_dob Attendee’s date of birth. NA NA
attendee_gender Attendee’s gender. NA NA
attendee_language Attendee’s language. NA NA
attendee_postal_code Attendee’s postal or ZIP code. NA NA
attendee_status Attendee’s status. NA NA
service_question Service pre-booking question (single question). If a survey is assigned to the service, this field will be set to "survey" but questions will not be exported. NA NA
service_answer Service pre-booking answer (single question). If a survey is assigned to the service, this field will contain an array of answers. NA NA
client_request Client’s request associated with a reservation. NA NA
client_metadata.{SUB_FIELD} Client metadata sub-field, where {SUB_FIELD} is any client metadata sub-field.  ☐  ☐
service_metadata.{SUB_FIELD} Service metadata sub-field, where {SUB_FIELD} is any service metadata sub-field.   ☐  ☐
staff_metadata.{SUB_FIELD} Staff metadata sub-field, where {SUB_FIELD} is any staff metadata sub-field.  ☐  ☐
store_metadata.{SUB_FIELD} Store metadata sub-field, where {SUB_FIELD} is any store metadata sub-field.  ☐  ☐

Time Slot

Identify peak and downtimes by generating a report of all calendar time slots associated with a merchant. The report includes dates, duration, status, associated staff and store information.

Filters

The following filters are provided to further customize the report’s content. Once set,  all time slot reports will be generated in accordance with the selected filters.

Filter Description Default Value
includeMerchantGroupLocations Include all merchants in the target merchant group. FALSE
useDateFormatYMD Display dates as “yyyy-mm-dd” instead of “dd mmm yyyy”. FALSE
useTimeFormat24h Display time in 24H format instead of 12H. FALSE
detailOptions Determine what level of details to export with, available, busy, imported or all. ALL
quoteMode Specify which quote mode to use, all, minimal, all none null, non numeric. Leave blank if none. ALL
encodingFormat Specify which file encoding format to use, UTF-8 or UTF-16-LE. UTF-16-LE

Fields

The below table lists all available fields for a “Time Slot” type report.

Field Description Default
start_date Date on which the time slot starts.
start_time Time on which the time slot starts.
duration Time slot’s duration in minutes.
status Time slot’s status, available, busy, etc…
label The custom label assigned to the time slot.
description Time slot’s description.
staff_id ID of the staff assigned to the time slot.
staff_name Name of the staff assigned to the time slot.
cegid_staff_id External staff ID used for the CEGID module.
staff_metadata.{SUB_FIELD} Staff metadata sub-field, where {SUB_FIELD} is any staff metadata sub-field.
store_id Store’s ID.
store_group_id Store’s merchant group ID.
store_tag Location filter tags assigned to the store.
store_number Store’s number.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.

Metric Reports

Metric reports provide a concise and comprehensive set of metrics to analyze how your business is performing from resource occupancy, popularity of services to trends. Several report types are offered to focus on specific data. See below for a list of all metric reports.

Merchant Occupancy Generate a comprehensive summary of all merchants within a group. Examine the performance of each location by comparing the number of bookings, booked, scheduled and serviced hours as well as busy time slots.
Metric Group Event Analyze how group events are performing, compare the number of bookings with the actual attendance and see how much revenue is generated per event. From these metrics, evaluate if events are financially successful and adjust capacity if attendance is lower than expected.
Service Occupancy Analyze how each service provided is performing by examining the number of bookings, booked and serviced hours as well as the revenue generated. Use these metrics to implement changes to your service offering, refocus resources on services generating more revenue, etc.
Staff Occupancy Analyze how staff are performing, from the number of bookings they were involved with to the number of scheduled, booked and serviced hours. Modify work schedule based on peak hours.
Resource Occupancy Survey how resources are performing, the number of bookings the resource was used for along with booked and serviced time. 

Metric Filters

The following filters are provided to further customize the report’s content. Take note that all metric report types will be generated with the selected filters.

Filter Description Default Value
includeMerchantGroupLocations Include all merchants in the target merchant group. TRUE
splitPriceAndCurrencry Strip currency symbols from all prices and display them in a distinct column. FALSE
useDateFormatYMD Display dates as “yyyy-mm-dd” instead of “dd mmm yyyy”. FALSE
useTimeFormat24h Display time in 24H format instead of 12H. FALSE
quoteMode Specify which quote mode to use, all, minimal, all none null, non numeric. Leave blank if none. ALL
encodingFormat Specify which file encoding format to use, UTF-8 or UTF-16-LE. UTF-16-LE

Merchant Occupancy 

Fields

The below table lists all available fields for a “Merchant Occupancy” type report.

Field Description Default
date The date on which the data was compiled.
store_id Booxi Merchant ID.
store_group_id Booxi Merchant Group ID.
store_tag Location tags associated with a store.
store_number Store count of the merchant group.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.
open_minutes Total scheduled time in minutes for all staff on the same date.
open_hours Total scheduled time in hours for all staff on the same day.
booked_minutes Total time booked in minutes.
booked_hours Total time booked in hours.
serviced_minutes Total time serviced in minutes.
serviced_hours Total time serviced in hours.
busy_timeslot_minutes Total of all busy time slots in minutes.
busy_timeslot_hours Total busy time slots in hours.
nb_bookings_total Number of bookings.
 

Metric Group Event 

Fields

The below table lists all available fields for a “Metric Group Event” type report.

Field Description Default
date Date on which the group event started.
store_id Booxi merchant ID.
store_group_id Booxi merchant group ID.
store_tag Location tags assigned to the store.
store_number Store’s number.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.
group_id The group event ID.
time Time at which a group event started.
staff_id ID of the staff assigned to a group event.
staff_name Staff’s name.
cegid_staff_id Cegid Staff ID (if applicable).
service_id ID of the service provided.
service_name Service’s name.
service_tags Tags assigned to the service.
cegid_product_id CEGID product ID (if applicable).
nb_reservation Number of bookings for the event.
nb_attendee Number of attendees for the event.
capacity Event’s capacity.
pc_booked Booking percentage (attendees / capacity)
nb_present Number of attendees with the status “arrived”.
booked_revenue_subtotal Revenue booked in a day (excluding taxes).
booked_revenue_total Revenue booked in a day (including taxes).
service_metadata.salesforce_id Salesforce service ID (service metadata).
staff_metadata.salesforce_staff_id Sales staff ID (staff metadata).

Service Occupancy

Fields

The below table lists all available fields for a “Service Occupancy” type report.

Field Description Default
date Date on which the reservation started.
store_id Booxi merchant ID.
store_group_id Booxi Merchant Group ID.
store_tag Location filter tags associated with a store.
store_number Store count in the merchant group.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.
service_id Booxi Service ID.
service_name Service’s name.
service_tags Tags assigned to a service.
cegid_product_id CEGID product ID (if applicable).
nb_bookings_online Number of bookings made online (client origin).
nb_bookings_offline Number of bookings made offline (staff origin).
booked_minutes Total minutes booked in a day for a service.
booked_hours Total hours booked in a day for a service.
serviced_minutes Total minutes serviced in a day for service.
serviced_hours Total hours serviced in a day for service.
booked_revenue_subtotal Subtotal revenue booked in a day (excluding taxes) for a service.
booked_revenue_total Total revenue booked in a day (including taxes) for a service.
serviced_revenue_subtotal Subtotal revenue serviced in a day (excluding taxes) for a service.
serviced_revenue_total Total revenue serviced in a day (including taxes) for a service.
nb_bookings_total Number of bookings made with a service.
service_metadata.salesforce_service_id Salesforce service ID from metadata (if applicable).

Staff Occupancy

Fields

The below table lists all available fields for a “Staff Occupancy” type report.

Field Description Default
date

Date on which the reservation started.

store_id Booxi merchant ID.
store_group_id Booxi Merchant Group ID.
store_tag Location filter tags associated with a store.
store_number Store count in the merchant group.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.
staff_id Booxi Staff ID.
staff_name Staff’s name.
cegid_staff_id CEGID Staff ID (if applicable).
open_minutes Open hours in minutes.
open_hours Open hours in hours.
scheduled_minutes Total scheduled time in minutes for a staff member.
scheduled_hours Total scheduled time in hours for a staff member.
booked_minutes Total booked time in minutes for a staff member.
booked_hours Total booked time in hours for a staff member.
serviced_minutes Total serviced time in minutes for a staff member.
serviced_hours Total serviced time in hours for a staff member.
busy_timeslot_minutes Total of all busy time slots in minutes for a staff member.
busy_timeslot_hours Total of all busy time slots in hours for a staff member.
nb_bookings_total Number of bookings for a staff member.
nb_assigned_services Number of services assigned to the staff.
staff_metadata.salesforce_staff_id Salesforce Staff ID from metadata (if applicable).

Resource Occupancy

Fields

The below table lists all available fields for a “Resource Occupancy” type report.

Field Description Default
date Date on which the reservation started.
store_id Booxi merchant ID.
store_group_id Booxi Merchant Group ID.
store_tag Location filter tags associated with a store.
stoer_number Store count in the merchant group.
store_name Store’s name.
store_city Store’s city.
store_state Store’s state, province or territory.
store_country Store’s country.
resource_id Booxi Resource ID.
resource_name Resource’s name.
open_minutes Open hours in minutes.
open_hours Open hours in hours.
scheduled_minutes Total scheduled time in minutes for a resource.
scheduled_hours Total scheduled time in hours for a resource.
booked_minutes Total booked time in minutes for a resource.
booked_hours Total booked time in hours for a resource.
serviced_minutes Total serviced time in minutes for a resource.
serviced_hours Total serviced time in hours for a staff resource.
nb_bookings_total Number of reservations that were booked with the resource.

Protocol Options

Here is a list of protocols supported with the automated FTP report. When it comes to selecting a transfer method between FTP, FTPS or SFTP we highly recommend avoiding the basic FTP protocol and choosing a more secure option. As an alternative, reports can be uploaded to Google Cloud Storage by specifying GCS as a protocol.

Booxi is using dedicated outbound IPs for all reports except GCS. They should be whitelisted by customers.

FTP

FTP exchanges data using two separate channels known as the command channel and data channel. With FTP, both channels are unencrypted, leaving any data sent over these channels vulnerable to being intercepted and read. It should only be used in limited cases or on networks you trust.

FTPS

Like FTP, FTPS uses two connections: a command channel and a data channel. It authenticates your connection using a user ID and password, a certificate, or both. When connecting to a trading partner's FTPS server, your FTPS client will validate if the server's certificate is trusted. 

SFTP

SSH File Transfer Protocol is a separate protocol packaged with SSH that works in a similar way but over a secure connection. The advantage is the ability to leverage a secure connection to transfer files and traverse the filesystem on both the local and remote system. SFTP is preferable to FTP because of its underlying security features and ability to piggy-back on an SSH connection. Furthermore, SFTP needs only a single port number for all SFTP communications, making it easy to secure.


Public Key Fingerprint 

SFTP supports server fingerprint as an optional security feature. Our implementation requires the key to be in MD5 format.


Your server’s fingerprint can be obtained with the following console command:

ssh-keygen -lf /path/to/public_key/pubkey_in_openssh_format.pub


A remote server’s fingerprint can be obtained like so:

ssh-keyscan -t rsa ftp.host.com > key.pub
ssh-keygen -l -f key.pub -E md5

Google Cloud Storage (GCS)

Google Cloud Storage is the object storage service offered by Google Cloud. In Cloud Storage, different objects are grouped in unique “namespaces” called buckets. A bucket can hold multiple objects yet, a single object will belong to only one bucket.


Client Configuration

The client must configure its destination storage bucket, using a Service Account provided by Booxi. A destination folder must be created as files can’t be saved on the root. Contact your Account Manager or CS to request an account. 


We recommend configuring the storage bucket with the following settings.

  • Region: Can either be Multi-Region or Region specific
  • Storage Class: Standard
  • Public Access: NOT Public
  • Access Control: Uniform
  • Protection: Activate one or more of the following protection options: Object Versioning Retention Policy or Default Event-Based Hold Option.
  • Life Cycle rules: Weekly data deletion
  • Requesters Pay: OFF
  • Permissions: storage.buckets.get and storage.objects.create

The permissions above are included into the below roles:

  • Storage Legacy Bucket Reader
    • Needed to read the metadata and the permissions on the bucket.
  • Storage Object Creator
    • Needed to write the file blob.

Storage Directory

The client can specify in what “destination folder” reports will be exported. Folders and sub-folders must be formatted as : 'level1/level2/'. Leading '/' is unnecessary unless a root '/' folder is needed.


Furthermore, the storage directory must follow the below naming guidelines as provided by Google:

  • Object names can contain any sequence of valid Unicode characters, of length 1-1024 bytes when UTF-8 encoded.
  • Object names cannot contain Carriage Return or Line Feed characters.
  • Object names cannot start with .well-known/acme-challenge/.
  • Objects cannot be named . or ...

For further information about the naming guidelines, please consult this article.