Skip to main content
Custom Import - Order data

Learn how to import order data from your webshop

Tim Schouten avatar
Written by Tim Schouten
Updated today

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:

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.

  1. Granting Permissions via IAM:

    1. Go to the IAM & Admin section in their Google Cloud Console.

    2. Click "Add" at the top of the IAM page.

    3. Assign appropriate roles that your service account needs to access the required resources (e.g., Viewer, Storage Object Viewer, etc.).

    4. Click "Save".

  2. Granting Permissions on Specific Resources:

    1. 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).

    2. For example, to grant access to a Cloud Storage bucket:

      1. Go to the Cloud Storage section in the Google Cloud Console.

      2. Select the bucket.

      3. Click on "Permissions".

      4. Click "Add" and enter your service account email (data-retriever@billy-grace.iam.gserviceaccount.com).

      5. Assign the necessary roles (e.g., Storage Object Viewer).

    3. Click "Save".

  3. After completing this step, please navigate to Settings -> Import Custom Data and complete the form to activate your export.

How to connect via SFTP?

  1. Navigate to Settings -> Import Custom Data. Here, you'll find personalized credentials, including a host, username, and password.

  2. 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.

  3. 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.

  4. Then you need to enter the name of the file, and the format (CSV or Parquet).

  5. 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.

Did this answer your question?