In this guide, we will guide you through the steps to connect your custom CMS store to Billy Grace. By syncing order data to Billy Grace, you will get insights in extra metrics like:
New Customers
Returning Customers
New Customer Revenue
Returning Customer Revenue
Customer Lifetime metrics
Profit (when unit cost data is supplied)
Importing order data from a custom CMS is not available in all plans. Please contact your Customer Success Manager for more information.
Giving access to the total number of orders also increases tracking and modelling accuracy.
There are two main ways of sending us data from a custom CRM:
SFTP: you can share a daily csv or parquet file with the orders.
BigQuery: you can share access to a BigQuery table from which we will extract the data.
It is essential that the data you share has the correct schema, so that we will be able to ingest it correctly:
If you share data with an incorrect schema with us, we will not be able to ingest it into your dashboard.
The schema we expect is listed below. Next to this, it is important that the orders are available for ingestion at the correct time:
Data should reach us before 3AM (containing the orders of the day before). For example, a file containing orders for the 29th of July needs to reach us before 3AM on the 30th of July.
More information on the specific schema we expect and how to connect the different methods is given below.
Schema
The schema of the data we expect is listed below:
Field Name | Data Type | Description |
id | String | Value with ID that is also sent with the transaction. |
value | float | Value of the order including any discounts. (E.g., If the total value was 1000 and there was a 100 discount, this field should be 900). |
date | string | Date in format YYYY-MM-DD. |
customer | string | customer ID |
country | string | shipping country |
total_discount | float | Total discount allocated (if available). |
total_tax | float | Total tax applied to the order. |
total_refunded | float | Total amount refunded (if available). |
total_shipping | float | Total shipping cost. |
items - json string with per product the following nested data: - product_id (string or int) - product_name (string) - product_qty - (int) - unit_cost - (float) - price - (float) - value - (float) - image_url - (string) if available | json string | Nested json data with information on product level for the order |
This table structure allows for the capture of essential information related to transactions, including pricing, taxes, shipping, and item details.
An example of items is:
'[{"item_id":"5165869","product_id":1112494,"product_name":"CYZ","product_qty":1.0,"unit_cost":16.9528,"price":35.5,"value":35.5,"image_url":"https://your.website.nl/images/thumbs/cc.jpeg"},{"item_id":"5165870","product_id":177161,"product_name":"CC","product_qty":1.0,"unit_cost":3.7076,"price":6.99,"value":6.99,"image_url":"https://your.website.nl/images/thumbs/cc.jpeg"}]'
If you do not supply sufficient data in the items column we will not be able to supply you with profitability metrics and/or insights on product level.
How to connect via BigQuery?
Follow these steps to share the specific table with schema from above.
Granting Permissions via IAM:
Go to the IAM & Admin section in their Google Cloud Console.
Click "Add" at the top of the IAM page.
Enter service account email (data-retriever@billy-grace.iam.gserviceaccount.com)
Assign appropriate roles that your service account needs to access the required resources (e.g., Viewer, Storage Object Viewer, etc.).
Click "Save".
Granting Permissions on Specific Resources:
If the service account needs access to specific resources, please grant permissions at the resource level (e.g., on a particular Cloud Storage bucket or BigQuery dataset).
For example, to grant access to a Cloud Storage bucket:
Go to the Cloud Storage section in the Google Cloud Console.
Select the bucket.
Click on "Permissions".
Click "Add" and enter your service account email (data-retriever@billy-grace.iam.gserviceaccount.com).
Assign the necessary roles (e.g., Storage Object Viewer).
Click "Save".
After you've done this, please share with us the following information:
the project name
the database name
the table name
How to connect via SFTP?
Contact our CSM team (support@billygrace.com). We will provide you with a host, username and password specific to you.
You need to set up a daily file dump yourself, where you ensure a CSV or parquet file containing the schema specified above reaches us daily at the correct time.
After setting it up they need to contact us to send us the naming structure they've chosen for the files (i.e. live_orders_<date>).