Initial Setup
- Create an account with Google BigQuery at https://bigquery.cloud.google.com
- You will be prompted to Create Project if you have not used Google Cloud before: enter a project name and continue
- Once your own project is created, please provide us with the email address of your account, so that we can grant you access to your company's data. You will be given a Project name to use in the rest of the instructions.
- After you've received your Project name from us, open up https://bigquery.cloud.google.com again
- On the left, the project you created earlier will be automatically selected. Click the arrow to the right of that project name to bring up a menu
- Hover over 'Switch to project' then click 'Display project…'
- Type the Project name provided by support, then click OK
- The new project now appears on the left, underneath your own project. Click the arrow to the left of the project name to view the data tables available to you
Usage
Once you've completed the Initial Setup, you can now write queries to view the data. Click 'Compose Query' to open the query entry box.
You must use Standard SQL mode - click the 'Show Options' button underneath the query entry box, then untick 'Use Legacy SQL'.
An example query to view all active clients:
SELECT * FROM View_YourCompany.Client WHERE isActive=true
The cost of queries will be charged to your account by Google - you can view pricing details here: https://cloud.google.com/bigquery/pricing.
You can view how much data a query will process before running it, by clicking the circle below the query entry box, at the right.
If you copy data to tables in your own project, the cost of that storage will also be charged to your account.
Notes:
- Data is synced at certain intervals in the day, it is not real time.
Database Diagram
This diagram offers a quick overview of how the tables link up with one another.
Tables
This is a brief summary of the tables available to you.
Note that almost all Date fields are in UTC timezone. The exception is dates that are independent of timezone, including Date Of Birth, and Service Date.
|
Table Name |
Description |
|
Activity |
This table displays the data from the Activity Log report. |
|
Appointment |
This table displays all Appointments. |
|
Client |
This table displays all clients. |
|
Company |
This table displays your company information, and your company list (if you are using the Dome) |
|
Discount |
This table lists your discount types. |
|
ItemTypeCategory |
This table lists the categories for your products & services. The ItemType field specifies what type of item (Product/Service) the category applies to. |
|
Operator |
This table lists your operators, with their KPI and Commission values. |
|
Package |
This table lists your packages. |
|
Product |
This table lists your products. |
|
Roster |
This table lists your rosters. |
|
Roster Type |
This table lists your roster types. |
|
SaleItems |
This table lists all the items sold. The ItemType field determines what type of item was sold (Product/Service/Voucher/Package). Some item types have specific fields, these are prefixed (eg. Service_Duration) - these fields will be null for other item types. |
|
Service |
This table lists your services. |
|
Sold Package Items |
This table lists the items that have been sold as part of a package. The ItemType field determines what type of item was sold (Product/Service/Voucher). Some item types have specific fields, these are prefixed (eg. Service_Duration) - these fields will be null for other item types. |
|
Transaction |
This table lists your transactions. |
|
Voucher |
This table lists your gift vouchers. |
Comments