- Help center
- Knowledge base
- Reports and Insights
Data Connector Tables
Data Connector Tables
Explore data connector tables, which give you raw data to analyze and gain insights for your business. Learn how to calculate key metrics from tables for bookings, clients, payments, sales, and more to uncover trends and evaluate your business's performance.
Bookings
The bookings table contains a list of booked services associated with each appointment. Each record in this table represents a booked service and can be linked to its corresponding appointment through the appointment_id, reflecting a one-to-many relationship.
This dataset includes all bookings statuses, such as canceled, no-show, future scheduled, and past bookings.
Key metrics
Bookings can be used to derive useful metrics around:
- Appointment sources
Using channel, which describes where the booking was made and the booking behaviors of your clients - Cancellation and no show rates
Using is_cancelled, is_no_show and cancelled_date to determine how cancellations are impacting your business - Client retention
Using client_id, it’s possible to determine how many bookings are coming from new vs existing clients and observe rebooking rates for acquired customers - Popular booking times
Using scheduled_date, you can look for peaks in demand and plan accordingly - Average time between appointments
Using scheduled_date and client_id, you can calculate the average time between appointment for each of your clients - Upcoming bookings
Using scheduled_date, you can identify upcoming bookings, which can be used to forecast sales and plan your team's schedule
Available columns
Column | Description |
---|---|
Booking ID | Primary key of the table. Unique identifier for each booking |
Appointment ID | Identifier for each appointment |
Location ID | Identifier for the booking's location |
Team Member ID | Identifier for the team member chosen to provide the service |
Client ID | Identifier for the client who received the service |
Service ID | Identifier for the service provided during the booking |
Created by team member ID | Identifier of the team member who created the booking |
Canceled by team member ID | Identifier of the team member who Canceled the booking, if applicable |
Scheduled date | Date the booking is scheduled for |
Created date | Timestamp of when the booking was created, in local time |
Scheduled time | Timestamp of when the booking is scheduled for, in local time |
Start time | Booking start time, in local time |
End time | Booking end time, in local time |
Appointment slot | Time slot for the appointment, in local time |
Duration | Duration of the service provided in minutes |
Canceled date | Timestamp of the booking cancellation in local time, if applicable |
Location | Name of the location for the booking |
Team member | Name of the team member chosen to provide the service |
Client | Name of the client who received the service |
Client gender | Gender of the client, if available |
Category | Name of the service's category |
Service | Name of the service provided during the booking |
SKU | Stock Keeping Unit for the service or product, if applicable |
Created by | Name of the team member who created the booking |
Canceled by | Name of the team member who Canceled the booking, if applicable |
Is requested | Indicates if the team member was requested to provide the service |
Appointment reference | Reference number for the appointment |
Appointment channel | Channel through which the appointment was made |
Status | Current status of the booking (e.g. completed, cancelled) |
Is cancelled | Indicates if the booking was cancelled |
Is no show | Indicates if the client did not show up for the booking |
Is online | Indicates if the booking was made online |
Cancellation reason | Reason for the booking cancellation, if applicable |
Appointment type | Type of appointment (e.g. first-time or returning) |
Gross sale | Gross sale amount for the service provided |
Discounts | Discounts applied to the service provided |
Net sale | Net sale amount after discounts |
Fees charged | Fees charged for Canceled / no-show bookings |
Upfront payments | Total value of upfront payments on the booking, if applicable |
Appointments reviewed | Number of reviews for the appointment, if applicable |
Average appointment review rating | Average rating from appointment reviews, if applicable |
Appointment notes | Notes or comments provided for the appointment, if applicable |
Clients
The clients table contains client information and their appointment history. Each record represents a unique client, identified by the client_id.
This table serves as the primary source for analysing client demographics, engagement, and booking patterns. It can be linked to other tables, such as bookings or payments, via client_id.
This table is a key resource for planning CRM campaigns, as it provides essential client information such as gender, age, phone number, and email, enabling tailored communications.
Key metrics
Clients can be used to derive useful metrics around:
- New and returning clients
A new client is identified as someone who has exactly one appointment. At Fresha, a returning client is defined as someone who had an appointment with you before. Using total_appointments, it's possible to identify those clients that have come more than once (returning) or only once (new clients), this can help you understand your retention rate - Rebooked
Using is_rebooked, it’s possible to determine how many clients have appointments scheduled for the future - Days since last appointment
Using last_appointment_date, it's possible to calculate the number of days since the most recent scheduled appointment - Reviews
Using reviews, you can identify top reviewers and, by combining it with total_appointments, calculate the review rate
Available columns
Column | Description |
---|---|
Client ID | Primary key of the table. Unique identifier for the client |
Sign up date | Date when the client signed up |
First appointment date | Date of the client's first appointment, if applicable |
Last appointment date | Date of the client's most recent appointment, if applicable |
Client | Full name of the client |
Gender | Gender of the client, if available |
Age | Age of the client, if available |
Mobile number | Mobile phone number of the client, if available |
Email of the client | |
Is rebooked | Indicates if the client has an appointment scheduled for the future |
Referral source | Source through which the client was referred, if applicable |
Last appointment location | Location of the client's most recent booking, if applicable |
Last appointment with | Name of the team member who handled the client's most recent appointment, if applicable |
Total appointments | Total number of appointments booked by the client |
Total appointment value | Total value of all appointments booked by the client |
Reviews | Total number of reviews provided by the client |
Payments
The payments table contains detailed records of all payments associated with various transactions, such as product sales, refunds, appointment fees, and cancellation and no-show fees.
Each record represents a payment event, uniquely identified by the sale_number, which serves as the primary key.
The table also captures gift card and deposit (i.e. upfront payments) transactions, maintaining separate records for both the sale and redemption of gift cards, as well as for the collection and application of deposits.
The table reflects a many-to-one relationship, where multiple payments can be linked to a single sale, appointment, client, or location through sale_id, appointment_id, client_id, or location_id, respectively.
The payments table includes all transactions linked to a sale and hence involving a client, whereas the cash flow data connector table captures a broader range of financial movements across the business, including all wallet transactions (both inflows and outflows) such as fees, blast messages, and other financial activities.
Key metrics
Payments can be used to derive useful metrics around:
- Gift card sales and collection
Using sale_id, you can join this table with the gift_cards table to calculate the number of gift cards sold and the payments collected for them - Gift card redemption & usage
Using is_gift_card_redemption, you can track gift card usage and identify seasonal trends or periods when gift cards are redeemed most frequently - Deposit redemption
Using is_deposit_redemption, it’s possible to determine the amount of payments done through a deposit - Amount of refunds and total refunded value
Using transaction_type and refund_amount, it's possible to identify those payments that were refunds and calculate the total value refunded
Available columns
Column | Description |
---|---|
Sale number | Primary key of the table. Unique identifier for the sale (invoice number) |
Payment number | Payment reference number |
Appointment reference | Reference number for the associated appointment |
Sale ID | Identifier for the sale |
Appointment ID | Identifier for the associated appointment |
Location ID | Identifier for the location of the transaction |
Team member ID | Identifier for the team member associated with the transaction |
Client ID | Identifier for the client associated with the transaction |
Payment date | Timestamp of when the payment was made, in local time |
Sale date | Timestamp of when the sale was recorded, in local time |
Location | Name of the location where the transaction occurred |
Team member | Name of the team member handling the transaction |
Client | Name of the client involved in the transaction |
Gift card code | Code for the gift card used in the transaction, if applicable |
Transaction type | Type of transaction (e.g. sale, refund) |
Is deposit redemption | Indicates if the payment was a deposit redemption |
Is gift card redemption | Indicates if the payment was a gift card redemption |
Payment method | Method of payment used in the transaction |
Currency code | Currency used for the transaction |
Amount | Total amount for the transaction |
Refund amount | Amount refunded in the transaction, if applicable |
Payment amount | Amount paid in the transaction |
Change amount | Change amount returned in the transaction |
Sales
The sales table contains a comprehensive listing of all sales transactions, with each record representing a unique sale event, identified by the sale_id.
The table includes key transaction details such as the sale number, payment status, transaction type, and various financial metrics (e.g., total sales, gross sales, net sales, refunds, discounts).
It also tracks sales-related attributes, including the location, team member, client, appointment, and the items involved in the transaction. The table establishes many-to-one relationships with locations, team_members, clients, or appointments.
Key metrics
Sales can be used to derive useful metrics around:
- Discounted value
using discounts and gross_sales, you can calculate the percentage of sales value attributed to discounts - Taxed value
Using total_taxes and gross_sales, you can determine the percentage of sales value subject to taxes and calculate the average tax rate - Service charge
Using service_charges and gross_sales, you can calculate the percentage of sales value attributed to service charges - Tips
Using total_tips and client, you can determine the percentage of sales that received a tip, calculate the average tip value, and identify the clients providing the tips - Open invoices
Using payment_status, it's possible to identify unpaid sales and track open invoices. Additionally, you can calculate payment rates to assess how much of the sales have been paid
Available columns
Column | Description |
---|---|
Sale ID | Primary key of the table. Unique identifier for the sale |
Sale number | Invoice number for the sale |
Location ID | Identifier for the location of the sale |
Team member ID | Identifier for the team member associated with the sale |
Client ID | Identifier for the client linked to the sale |
Appointment ID | Identifier for the associated appointment |
Sale date | Timestamp of when the sale occurred, in local time |
Location | Name of the location where the sale took place |
Client | Name of the client involved in the sale |
Client gender | Gender of the client, if available |
Team member | Name of the team member handling the sale |
Status | Current status of the sale |
Appointment channel | Channel through which the appointment was booked |
Payment method | Payment method used for the sale |
Transaction type | Type of transaction (e.g. sale, refund) |
Payment status | Current status of the payment |
Service charge | Name of the service charge applied, if applicable |
Client source | Origin of client acquisition |
Client retention source | Indicates if the sale was the client's first sale or a returning sale |
Items sold | Quantity of items sold in the transaction |
Total sales | Total amount of sales before adjustments |
Gross sales | Gross sales amount |
Net sales | Net sales amount after adjustments |
Total refunds | Total refund amount for the sale |
Total discounts | Total discount amount applied to the sale |
Total tips | Total amount of tips collected |
Total payments | Total amount of payments made for the sale, excluding redemptions |
Total taxes | Total tax amount for the sale |
Total service charges | Total amount of service charges applied |
Total gift cards | Net sales amount paid via gift cards |
Total change amount | Total change amount returned to the client |
Amount due | Total amount due for the sale |
Total payments and redemptions | Sum of total payments, gift card redemptions, and deposit redemptions |
Total gift card redemptions | Total amount redeemed via gift cards |
Total service charge taxes | Total taxes associated with service charges |
Gift card taxes | Taxes paid via gift cards |
Currency code | Currency used for the sale |
Sale items
The sale items table contains detailed records of individual items within sales transactions. Each record represents a unique sale item identified by the sale_item_id.
The table has a one to many relationship with sales and bookings, which can be examined using sale_id and booking_id, respectively.
It tracks item-specific financial data like discounts, refunds, and taxes, as well as other attributes such as item category, supplier, brand, and SKU.
Key metrics
Sale items can be used to derive useful metrics around:
- Average net sales per item
Using net_sales and quantity, you can calculate the average net sales per item sold - Margin
Using net_sales and cost_price, it's possible to determine the profit margin and margin percentage for each sale, providing insights into profitability - Client returning sales
Using client_id and client_retention_source, you can analyze client behavior and calculate the proportion of first-time sales versus returning sales - Popular sale times
Using sale_date, you can identify sales peaks and plan resources or promotions accordingly - Main value contributors
Using category, item, team_member, or client alongside gross_sales, you can gain insights into which categories, items, team members or clients are creating the most value for your business - Discounts
Using discount_type, sale_date, and discounts, it's possible to analyze which discounts are being applied, when they are used, and the average discount value
Available columns
Column | Description |
---|---|
Sale item ID | Primary key of the table. Unique identifier for the sale item |
Sale ID | Identifier for the sale associated with the item |
Booking ID | Identifier for the booking linked to the sale item |
Location ID | Identifier for the location of the sale item |
Merchant ID | Identifier for the merchant handling the sale item |
Team member ID | Identifier for the team member associated with the sale item |
Client ID | Identifier for the client linked to the sale item |
Sale date | Timestamp of the sale, in local time |
Sale number | Invoice number for the sale |
Sale status | Current status of the sale |
Location | Name of the location where the sale item was purchased |
Team member | Name of the team member handling the sale item |
Merchant | Name of the merchant handling the sale item |
Client | Name of the client linked to the sale item |
Client gender | Gender of the client linked to the sale item, if available |
Client source | Origin of client acquisition |
Client retention source | Indicates if the sale was the client's first sale or a returning sale |
Client mobile | Mobile number of the client, if available |
Discount category | Category of the discount applied to the sale item |
Discount type | Type of discount applied to the sale item |
Discount name | Name of the discount applied to the sale item |
Appointment channel | Channel through which the appointment was booked |
Appointment reference | Reference number for the associated appointment |
Transaction type | Type of transaction (e.g. sale, refund) |
Payment method | Method of payment used for the sale item |
Sale type | Type of the sale item (e.g., product, gift card) |
Category | Category of the sale item |
Item | Name of the sale item |
Supplier | Name of the supplier for the sale item, if available |
Brand | Brand of the sale item, if available |
SKU | Stock Keeping Unit for the sale item, if available |
Barcode | Barcode for the sale item, if available |
Is upsell | Indicates whether the sale item was sold on the appointment date as an upsell |
Currency code | Currency used for the sale |
Quantity | Quantity of the sale item sold |
Gross sales | Gross sales amount for the item |
Discounts | Total discounts applied to the sale item |
Cart discounts | Discounts applied to the whole cart value |
Item discounts | Discounts applied to single items |
Refunds | Total refunds applied to the sale item |
Net sales | Net sales amount for the item after discounts and refunds |
Taxes on net sales | Taxes applied to the net sales amount |
Total sales | Total sales amount for the item |
Cost | Cost price of the sale item, if applicable |
Tips
The tips table contains detailed records of tips collected and refunded during sales transactions. Each record represents a unique tip transaction, identified by the tip_id.
The table has a many-to-one relationship with sales, clients, locations, and team members, among others.
Available columns
Column | Description |
---|---|
Tip ID | Primary key of the table. Unique identifier for the tip transaction |
Sale ID | Identifier for the sale associated with the tip |
Sale number | Invoice number for the sale associated with the tip |
Location ID | Identifier for the location where the tip was collected |
Team member ID | Identifier for the team member who received the tip |
Client ID | Identifier for the client who provided the tip |
Tip date | Date when the tip was collected |
Team member | Name of the team member who received the tip |
Client | Name of the client who provided the tip |
Client gender | Gender of the client who provided the tip, if available |
Location | Name of the location where the tip was collected |
Transaction type | Type of transaction associated with the tip |
Tip channel | Channel through which the tip was provided |
Payment method | Payment method used for the tip transaction |
Tips collected | Total amount of tips collected |
Tips refunded | Total amount of tips refunded |
Client retention source | Indicates if the sale was the client's first sale or a returning sale |
Currency code | Currency used for the tip transaction |
Commissions
The commissions table contains detailed records of commissions earned by team members on sales transactions. Each record represents a unique commission transaction, identified by the commission_id. The table captures details about sales, clients, locations, and team members, along with financial metrics such as gross sales, taxes, and commissions.
The table reflects a many-to-one relationship, where multiple commissions can be linked to a single sale, location, or client through sale_id, location_id, or client_id.
Available columns
Column | Description |
---|---|
Commission ID | Primary key of the table. Unique identifier for the commission transaction |
Sale ID | Identifier for the sale associated with the commission |
Sale item ID | Identifier for the specific item sold that generated the commission |
Sale number | Invoice number for the sale associated with the commission |
Location ID | Identifier for the location where the sale occurred |
Team member ID | Identifier for the team member who earned the commission |
Client ID | Identifier for the client who made the purchase |
Sale date | Timestamp of when the sale occurred |
Client | Name of the client who made the purchase |
Team member | Name of the team member who earned the commission |
Location | Name of the location where the sale occurred |
Sale item | Name of the item sold that generated the commission |
Sale type | Type of sold item (e.g. gift card, product, membership, etc) |
Service category | Category of the service or product sold |
SKU | Stock Keeping Unit for the sold item, if applicable |
Gross sales | Total gross sales amount for the transaction |
Tax | Total tax amount applied to the sale |
Discounts | Discounts applied to the sale |
Cost | Base amount on which the commission is calculated |
Commission base | Total commission earned for the sale |
Percentage commission | Commission as a percentage of the commission base |
Currency code | Currency used for the transaction |
Service charges
The service charges table contains service charges applied during sales transactions. Each record represents a unique service charge, identified by the service_charge_id. The table captures details about sales, locations, team members, clients, and financial metrics such as flat rates, percentage rates, and total amounts.
The table reflects a many-to-one relationship, where multiple service charges can be linked to a single sale, location, client or team member through sale_id, location_id, client_id, or team_member_id.
Available columns
Column | Description |
---|---|
Service charge ID | Primary key of the table. Unique identifier for the service charge |
Sale ID | Identifier for the sale associated with the service charge |
Location ID | Identifier for the location where the service charge was applied |
Team member ID | Identifier for the team member associated with the service charge |
Client ID | Identifier for the client associated with the service charge |
Service charge date | Date when the service charge was applied |
Service charge | Name of the service charge applied |
Location | Name of the location where the service charge was applied |
Team member | Name of the team member associated with the service charge |
Client | Name of the client associated with the service charge |
Service charge flat rate | Flat rate of the service charge applied |
Service charge percentage rate | Percentage rate of the service charge, expressed as a decimal |
Net amount | Net amount of the service charge, excluding taxes |
Tax | Total tax amount applied to the service charge |
Total amount | Total amount of the service charge, including taxes |
Currency code | Currency used for the service charge transaction |
Occupancy
The occupancy table contains detailed records of employee occupancy and shift data. Each record represents a unique combination of team member, location, and date, identified by the occupancy_employee_pk. The table provides a summary of scheduled shifts, time off, and occupancy rates.
Available columns
Column | Description |
---|---|
Occupancy employee PK | Primary key of the table. Unique identifier for the occupancy record |
Location ID | Identifier for the location where the employee shift occurred |
Team member ID | Identifier for the team member associated with the shift |
Date | Date of the shift |
Location | Name of the location where the shift occurred |
Team member | Name of the team member associated with the shift |
Shift minutes | Total minutes the team member worked during the shift |
Time off minutes | Total minutes the team member took off during the shift |
Blocked minutes | Total minutes during which the team member's time was blocked and unavailable |
Available minutes | Total minutes the team member was available for shifts |
Booked minutes | Total minutes the team member was booked for shifts |
Unbooked minutes | Total minutes the team member was available but not booked for shifts, calculated as available_minutes - booked_minutes |
Occupancy rate | Ratio of booked minutes to available minutes, calculated as booked_minutes / available_minutes. If available minutes is 0, the value is set to null |
Products
The products table contains all products offered for sale, including information about suppliers, brands, and product specifications. Each record represents a unique product, identified by the product_id.
Available columns
Column | Description |
---|---|
Product ID | Primary key of the table. Unique identifier for the product |
Supplier name | Name of the supplier providing the product, if available |
Brand name | Name of the brand of the product, if available |
Product name | Name of the product |
Category name | Category to which the product belongs, if available |
SKU | Stock Keeping Unit for the product, if applicable |
Barcode | Barcode associated with the product, if applicable |
Has description | Indicates whether the product has a description available |
Has image | Indicates whether the product has an image available |
Has unlimited stock | Indicates whether the product has unlimited stock availability |
Units | Quantity of the product available, represented in the unit of measure, if applicable |
Unit of measure | Unit of measure used for the product quantity (e.g. millilitres, kilograms), if applicable |
Cost price | Cost price of the product |
Retail price | Retail price of the product |
Locations
The locations table provides a list of all your business' locations, including those that have been deleted. Each record uniquely represents a location, identified by the location_id.
Available columns
Column | Description |
---|---|
Location ID | Primary key of the table. Unique identifier for the location |
Name | Name of the business location |
Phone | Phone number associated with the location |
Email address associated with the location | |
Deleted at | Timestamp indicating when the location was deleted, if applicable |
Services
The services table provides a list of all services offered by your business. Each record represents a unique service, identified by the service_id. The table includes information about the service's name, description, and whether it has been deleted.
Available columns
Column | Description |
---|---|
Service ID | Primary key of the table. Unique identifier for the service |
Name | Name of the service |
Description | Description of the service |
Deleted at | Timestamp indicating when the service was deleted, if applicable |
Team members
The team members table provides a list of all team members in your business, including their personal and employment information. Each record represents a unique team member, identified by the team_member_id. The table includes information such as name, contact details, role, and employment dates, along with the status of the team member (whether deleted or active).
Available columns
Column | Description |
---|---|
Team member ID | Primary key of the table. Unique identifier for the team member |
First name | First name of the team member |
Last name | Last name of the team member |
Full name | Full name of the team member |
Mobile number | Mobile phone number of the team member |
Email address of the team member | |
Role | Job role of the team member |
Employment start date | Date when the team member started their employment |
Employment end date | Date when the team member ended their employment, if applicable |
Deleted at | Timestamp indicating when the team member record was deleted, if applicable |