BigQuery is a cloud‑based enterprise data warehouse that lets you store and analyse large volumes of structured and semi‑structured data. When connected with Dotdigital, you can sync datasets from individual tables or combined views to bring customer, product, and order data into one central marketing platform.
This integration supports syncing contacts, products, and orders from BigQuery into Dotdigital.
For order syncing, your data must follow a fixed structure.
Before you start
You need to have:
A Google BigQuery service account email and unencrypted private key.
Your service account must have
bigquery.dataViewerpermission or higher.A table or view for each dataset you want to sync; contacts, products, and orders. Each table/view must contain a TIMESTAMP column for incremental syncing.
A dedicated Orders table that follows the required schema.
See Order data structure requirements below.
The BigQuery integration syncs data into Dotdigital only.
To sync data out of Dotdigital and into BigQuery, use the Firehose integration.
Use both integrations together if you need two‑way data movement.
What you can do
Import contact data from BigQuery to create targeted campaigns.
This lets you bring together customer information from multiple systems into Dotdigital.Import product data from BigQuery to enrich marketing campaigns with dynamic content, product recommendations, and personalised offers.
Import order data to bring purchase history into Dotdigital, enabling RFM analysis, ecommerce segments, product recommendations, and personalised automations.
Use product data alongside contact data to improve relevance and engagement.
All BigQuery dataset syncs run daily.
1. Integrate BigQuery with Dotdigital
Start the integration by finding BigQuery in the self-serve integrations menu.
In Dotdigital, go to Connect > Integrations.
In the left menu, under SHOW, select Self-serve.
Find Google BigQuery, then select +ADD.
2. Connect your account
Set up the connection between Dotdigital and your BigQuery account.
Read the integration information, then select NEXT.
For Connect to BigQuery, select Connect account.
For Your authentication name, enter a descriptive name for this connection, to help identify the integration later.
Enter the service account email address and associated private key from your BigQuery service account.
The full private key must start with "-----BEGIN PRIVATE KEY-----" and end with "-----END PRIVATE KEY-----\n".Select CREATE.
Select NEXT.
After authentication, you configure each dataset separately using the same structured flow.
3. Set up your datasets
You can configure contacts, products, and orders independently, but the setup flow is linear.
You move through each dataset in order, starting with contacts.
At each stage, you must choose Yes or No:
If you select Yes, the wizard guides you through the setup steps for that dataset.
If you select No, the wizard skips the detailed steps and moves on to the next dataset.
You cannot jump directly to Products or Orders without first completing the Contacts step.
Contacts setup
1. Sync contacts
Choose whether you want to sync contact data from BigQuery into Dotdigital.
If you already import your contacts from another source, such as a CRM, select No.
Select Yes or No.
Select NEXT.
2. Select your data source for Contacts
Select where your contact data lives in BigQuery.
Select Project.
Select Dataset.
Select Table.
Select NEXT.
3. Select timestamp for Contacts
Choose the column that tracks when each record was last updated. If using a view, ensure the timestamp column is included in the view output.
The column must be type TIMESTAMP, not STRING or DATETIME.
Rows without timestamps cannot be synced.
Only rows with timestamps newer than the last successful sync are imported.
Select your timestamp column.
Select NEXT.
4. Sync opt-in/out status
Select if you want to sync the opt-in/out status.
Select NEXT.
Select the email opt-in field.
Select the SMS opt-in field, and then select NEXT.
5. Fallback behaviour
If your contact table includes an opt‑in column, for example, subscribed, marketing_opt_in, is_active, then Dotdigital expects the values to be clear Yes/No indicators such as true/false, yes/no, or 1/0.
Sometimes this column contains unexpected values, for example blank, maybe, unknown, or custom values that don’t map cleanly to Yes/No. The fallback setting tells Dotdigital what to do with those records.
Choose what should happen when the opt‑in value is not recognised:
Yes - any unexpected or blank value is imported as subscribed.
No - any unexpected or blank value is imported as not subscribed.
Select NEXT.
6. Map data fields
Map BigQuery columns to Dotdigital contact fields.
Contacts require email or mobile number.
Select a Dotdigital field.
Select the matching BigQuery column.
Add mappings if needed.
Select NEXT.
7. Set sync frequency
Select the frequency you want to sync the contacts.
Select NEXT.
Products setup
1. Sync product catalog
Choose whether to sync product data into Dotdigital.
Select Yes or No.
Select NEXT.
2. Select data source for Products
Select where your contact data lives in BigQuery.
Select your Project.
Select your Dataset.
Select your Table.
Select NEXT.
3. Select timestamp for Products
Choose the column that tracks when each record was last updated.
The column must be type TIMESTAMP, not STRING or DATETIME.
Rows without timestamps cannot be synced.
Only rows with timestamps newer than the last successful sync are imported.
Select your timestamp column.
Select NEXT.
4. Map data fields
Some product fields are greyed out because they’re mandatory. You must map these fields for the product sync to work, and you can’t remove or change them. You cannot skip this step.
Select a Dotdigital field.
Select the BigQuery product fields for the matching Dotdigital insight fields.
Add additional mappings as required.
Select NEXT.
5. Set sync frequency
Choose how often your data syncs. Dotdigital checks for updates on your chosen schedule, but only imports rows with timestamps newer than the last successful sync.
Select your sync frequency.
Select NEXT.
To change the schedule later, edit your integration settings.
To pause syncing, disable the integration and re‑enable it when you are ready.
Orders setup
1. Sync orders
Order syncing allows you to bring purchase history into Dotdigital.
Order data must follow a fixed schema containing:
Order‑level fields (ID, totals, currency, purchase date).
Customer identifier fields.
Billing and delivery details.
A nested items field for order lines.
The integration will not work if your table does not follow the required structure.
Select Yes or No.
Select NEXT.
You can't manually map order fields. The integration requires an exact schema because Dotdigital parses the structure directly.
2. Select data source for orders
Select where your dataset lives in BigQuery.
Projects - choose the Google Cloud project that contains your contact table or view.
Dataset - pick the dataset where your contact table or view is stored.
Table - choose the specific table that contains your contact records.
Select NEXT.
3. Set sync frequency
Choose how often your data syncs. Dotdigital checks for updates on your chosen schedule, but only imports rows with timestamps newer than the last successful sync.
Select your sync frequency.
Select FINISH.
To change the schedule later, edit your integration settings.
To pause syncing, disable the integration and re‑enable it when you are ready.
The Orders sync uses a fixed schema, so Dotdigital reads the structure of your table directly instead of asking you to map fields manually.
This ensures every order record is imported consistently, and avoids issues that can occur when custom field names or formats don’t match expected ecommerce standards.
If your table doesn’t follow the required structure, you must update it before starting the sync.
Example SQL for creating a compatible Orders table
Example SQL for creating a compatible Orders table
CREATE OR REPLACE TABLE `ddgintegrationtest.My_Orders.MY_ORDERS_SYNC` (
CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
ID STRING,
ORDER_TOTAL NUMERIC(18, 2) NOT NULL,
PAYMENT STRING,
DELIVERY_METHOD STRING,
DELIVERY_TOTAL NUMERIC(18, 2),
CURRENCY STRING NOT NULL,
ORDER_STATUS STRING,
EMAIL STRING NOT NULL,
QUOTE_ID STRING,
PURCHASE_DATE DATE NOT NULL,
BILLING_ADDRESS_1 STRING,
BILLING_ADDRESS_2 STRING,
BILLING_CITY STRING,
BILLING_COUNTRY STRING,
BILLING_POSTCODE STRING,
DELIVERY_ADDRESS_1 STRING,
DELIVERY_ADDRESS_2 STRING,
DELIVERY_CITY STRING,
DELIVERY_COUNTRY STRING,
DELIVERY_POSTCODE STRING,
PRODUCTS JSON,
ORDER_SUBTOTAL NUMERIC(18, 2) NOT NULL,
BASE_SUBTOTAL_INCL_TAX NUMERIC(18, 2),
DISCOUNT_AMOUNT NUMERIC(18, 2),
COUPONCODE STRING
);