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 CMS:
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 02:00 AM (containing the orders of the day before).
For example: a file containing media data for the 29th of July needs to reach us before 02:00 AM on the 30th of July. If you fail to do this, there might be up to a week lag before you see correct attribution for this channel.
Furthermore, the CSV file must contain all the data you want to have in Billy Grace. If any data is no longer included in the CSV, it will not be part of the sync and will no longer be visible in Billy Grace.
The data should contain the orders of the day before.
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 | Required |
id | String | Value with ID that is also sent with the transaction. | Yes |
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). | Yes |
date | string | Date in format YYYY-MM-DD. | Yes |
updated_at | string | Latest time order was updated. Can be used to have latest status for order or incorporate refunds. Date format YYYY-MM-DD | No |
status | string | Order status (i.e. paid/ cancelled) | No |
customer | string | customer ID | Yes |
country | string | shipping country | Yes |
total_discount | float | Total discount allocated (if available). | Yes |
total_tax | float | Total tax applied to the order. | Yes |
total_refunded | float | Total amount refunded (if available). | No |
total_shipping | float | Total shipping cost. | Yes |
items - json string with per product the following nested data: - product_id (string or int) - product_name (string) - product_qty - (int) - unit_cost - (float) if available - price - (float) - value - (float) - image_url - (string) if available | json string | Nested json data with information on product level for the order | Yes |
This table structure allows for the capture of essential information related to transactions, including pricing, taxes, shipping, and item details.
Example of CSV import
The CSV template is attached to this knowledge article. You can find it at the end of the article.
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.
Import in Billy Grace
When you have successfully set up the import via BigQuery or SFTP, you should see data in Billy Grace after 24 hours. You can see the imported orders on the E-commerce settings page.
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 completing this step, please navigate to Settings -> Import Custom Data and complete the form to activate your export.
How to connect via SFTP?
Navigate to Settings -> Import Custom Data. Here, you'll find personalized credentials, including a host, username, and password.
You're required to establish a daily file dump on your own, ensuring that a CSV or Parquet file, following the previously mentioned schema, is sent to us daily at the designated time.
Once set up, please create an export on the same page where you obtained your credentials by completing the form that appears when you click on 'Add Custom Import'. First, you'll have to give your export a name, specify either BigQuery or SFTP and the data type.
Then you need to enter the name of the file, and the format (CSV or Parquet).
After you have followed the three steps, you'll find yourself on the summary page. Here, you can click the button at the bottom saying 'Check data'. After you've verified the data, you can create the custom import at the right bottom.