Commercetools to BigQuery: How to Move Your Data

Moving commercetools into BigQuery gives a commerce platform somewhere to answer questions it was never built for. It handles orders and catalogue superbly and becomes slow and rigid the moment somebody asks about basket composition across three years or margin by channel.

This guide covers the managed path with Airbyte. Two things shape the build: one screen in the Merchant Center supplies most of your configuration, and commerce records nest deeply enough that the grain of your tables is a decision rather than a default.

Commercetools to BigQuery at a glance:

Why move data from Commercetools to BigQuery?

Two situations account for most of these pipelines.

The first is analysis across a long history, which a commerce platform serving live traffic is a poor place to run. Cohort retention, repeat purchase behaviour and product affinity are heavy aggregations that belong somewhere separate from the system taking orders.

The second is joining commerce to marketing spend and support contacts, which commercetools has never seen. If instead several systems need to react to orders as they change, that is a message bus rather than a warehouse and the pipeline looks quite different.

What do you need before you start?

Four things, and three of them come from the same place:

Your project key and region, from the API URL. Both appear in the URL shown under developer settings in the Merchant Center, and the region must be entered as one of the specific supported values. The commercetools source documentation covers the fields.

An API client, with its secret captured at creation. Created in the same area of the Merchant Center, with read scopes for the resources you want. The secret is displayed once, and missing it means creating another client.

A count of the projects you need. A source connects to one project key, so a business running separate projects per brand or region needs a source for each and a view to bring them together.

A BigQuery dataset in the right location. Location is fixed at creation and BigQuery will not join across locations, so put this where your marketing and support data already live.

If your network restricts traffic by IP, add the Airbyte Cloud IP addresses to the relevant allow list before you begin.

How do you build a Commercetools to BigQuery pipeline in Airbyte?

Step 1: Read your configuration off the API URL

Open developer settings in the Merchant Center and copy the API URL, because it contains both your project key and the region in a form you can trust. Guessing the region from where you think the project lives is the mistake worth avoiding, since the field expects one of a fixed set of values rather than a description of a place, and a wrong one fails in ways that look like bad credentials.

Step 2: Configure the commercetools source

Click Sources in the left navigation, then New Source, and select commercetools, following adding a source. Supply the project key, region, host, client identifier and secret, plus a start date. Name the source after the brand or market rather than the project key, which nobody will recognise in six months.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project identifier, dataset and service account credentials. Most commerce datasets are modest enough that batched standard inserts are ample and Cloud Storage staging is unnecessary.

Step 4: Create the connection and look at an order

Click Connections, then New connection, select your streams and a sync mode. Incremental suits commerce data and daily is usually enough. Then query a single order and look at its structure properly before anybody builds a report on top of it.

Have your views filter on the extraction timestamp partition too, since BigQuery bills on bytes scanned.

Why does one screen decide most of the setup?

Because commercetools keeps the pieces together. The API URL in the Merchant Center carries the project key and the region, the API client is created a few clicks away in the same developer settings, and between them that is almost everything the connector asks for. Knowing where to look turns a fiddly configuration into a two-minute one.

The secret is the part with no second chance. It is shown when the API client is created and not afterwards, so a half-finished setup that gets interrupted usually ends with somebody creating a second client. Capture it at the moment of creation and put it somewhere your team can find it, because the person rebuilding this pipeline later will not be you.

Scope is the remaining question, and it is a per-project one. A source covers a single project key, so a business with separate projects per brand or market needs several sources, several connections and a union view carrying a column naming the project. Doing that deliberately is much easier than discovering halfway through that your revenue figure covers one market.

How should orders land, and at what grain?

Nested, and then queried at whichever grain the question needs. An order carries its line items inside it, each with a product reference, a quantity and a price, and BigQuery holds that structure natively rather than forcing a choice at load time. That is a real advantage over destinations where nesting has to be flattened on arrival.

So resist flattening on principle and unnest in views instead. Revenue by month and repeat purchase rate are order-level questions answered directly from the table. Basket composition, product affinity and margin by product are line-level questions, answered by unnesting line items into a view with a row per product per order. Both read the same landing tables.

Currency deserves attention while you are designing those views. A platform built for international commerce records money alongside the currency it was taken in, and summing amounts across currencies produces a number that is meaningless and entirely plausible. Decide early whether you convert to a reporting currency and at which rate, and build that into the view rather than leaving it to whoever writes the next query.

Frequently asked questions

Where do I find the project key and region?

Both appear in the API URL under developer settings in the Merchant Center. The region must be one of the specific supported values rather than a description of a place.

I did not record the client secret.

It is shown only at creation, so create a new API client and capture it this time. There is no way to retrieve the original.

Can one source cover several projects?

No. A source connects to one project key, so separate projects per brand or market need separate sources and a union view naming each.

Should I flatten line items on the way in?

No. BigQuery holds nested structures natively, so let orders land intact and unnest line items in a view when you need line-level analysis.

Can I do this without writing code?

The pipeline, yes. The views that unnest line items and handle currency are SQL, and they are what makes the data answerable.

Get your Commercetools data into BigQuery

Take the project key and region from the API URL rather than guessing, and capture the client secret at creation because it is shown once. Count your projects, since each needs its own source. Then let orders land nested rather than flattening them, unnest line items in views for product-level questions, and decide how you handle currency before somebody sums across three of them and gets a plausible answer.

Airbyte's connector catalog includes 600+ pre-built connectors, so commerce data can be analysed beside the spend that drove it. For another storefront into the same destination, see Shopify to BigQuery, and for a self-hosted storefront into the same destination, Woocommerce to BigQuery.

Start syncing now →