Customer Io to BigQuery: How to Move Your Data

Moving Customer.io into BigQuery lets you set messaging activity against what it produced. Customer.io reports on campaigns competently and knows nothing about revenue, retention or support load, which is where every interesting question about a messaging programme actually lives.

This guide covers the managed path with Airbyte. Two things shape the build: Customer.io splits its platform across several API hosts and this connector uses one of them, and campaign structures arrive nested rather than as flat columns.

Customer Io to BigQuery at a glance:

Why move data from Customer Io to BigQuery?

Two situations account for most of these pipelines.

The first is measuring whether messaging works. A campaign's open rate is a messaging metric; whether people who received it went on to buy, stay or complain is a business one, and answering it requires joining campaign activity to systems Customer.io has never seen. A warehouse is the only place that join happens.

The second is keeping a record of what was sent, which matters more than people expect when somebody asks why a particular customer received a particular message eighteen months ago. What this will not give you is the raw behavioural event stream you push into Customer.io in the first place, since that belongs to a different part of their API and is better read from wherever you originally sent it.

What do you need before you start?

Four things, and the second is the one that decides whether this project is viable:

An App API key. Generated in your Customer.io workspace settings, and distinct from the keys used for sending data in. The Customer.io source documentation points at their instructions for creating one.

A check that the streams you need are covered. Customer.io divides its API across three hosts serving different parts of the platform, and this connector reads one of them. Whatever that host exposes is what you can have, so confirm it covers your questions before scoping anything.

A BigQuery dataset in the right location. Location is fixed at creation and BigQuery will not join across locations, so this belongs wherever your revenue and product data already sits, since joining to them is the entire point.

Patience for the first sync. The host this connector uses enforces ten requests per second, which is workable for a messaging catalogue and not something you can tune your way around.

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 Customer Io to BigQuery pipeline in Airbyte?

Step 1: Work out which part of Customer.io you actually need

Write down the questions this pipeline is meant to answer, then check them against what the connector's streams cover. Questions about campaigns, newsletters, segments and what was sent are well served. Questions about the individual behavioural events you push into Customer.io are a different part of their platform and a different problem. Ten minutes here prevents the version of this project that delivers a working pipeline nobody wanted.

Step 2: Configure the Customer.io source

Click Sources in the left navigation, then New Source, and select Customer.io, following adding a source. Supply the App API key. Select the streams your questions need rather than everything available, since each one costs requests against a limit of ten per second that you share with nothing else but also cannot raise.

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. Messaging catalogues are small by warehouse standards, so batched standard inserts are ample and Cloud Storage staging is unnecessary here.

Step 4: Create the connection and schedule around the limit

Click Connections, then New connection, select your streams and a sync mode. Daily suits a messaging catalogue, which changes when marketers build campaigns rather than continuously. Ten requests per second is a real ceiling, so a first sync across a large workspace takes as long as it takes and is not a sign of anything wrong.

Then model the nested structures into something analysts recognise, because the raw tables are faithful to the API and not shaped for querying.

Which part of Customer.io does this reach?

One of three. Customer.io divides its API across separate hosts serving different components of the platform, and this connector reads a single one of them. That is a reasonable design decision by whoever built it and it means the boundary of what you can sync was set by that choice rather than by anything you configure.

The practical effect is a clean split between messaging configuration and behavioural events. Campaigns, newsletters, segments and the record of what was sent sit on the readable side. The stream of customer events you push into Customer.io to trigger those messages does not, which surprises people because that data feels like it should be retrievable from the platform holding it.

That is usually fine, because you sent those events from your own systems and can read them from the source rather than round-tripping through a messaging tool. It is only a problem when nobody checks first and a project is scoped around event-level analysis. Confirm the available streams against your questions at the start, and this connector does a useful job within a boundary you understood going in.

How should BigQuery hold a campaign's structure?

As it arrives, then modelled in views. A campaign is not a flat record: it carries arrays of actions, references to message templates, and lists of segment identifiers that decide who enters and who is excluded. BigQuery holds nested and repeated fields natively, so none of that needs flattening on the way in and nothing is lost.

Querying it directly is another matter. An analyst asking which campaigns target a particular segment has to unnest an array of identifiers and join to the segments table, which is a reasonable thing to write once and an unreasonable thing to expect everybody to rediscover. Build a view exposing campaigns with their segments and actions as proper rows, and point reporting at that.

Two habits keep the result affordable and honest. Tables arrive partitioned on the extraction timestamp rather than on any date inside the campaign, so filter on that column as well or every query reads the whole history. And because campaign definitions change while campaigns are running, decide whether your view shows the current state or the state at a point in time, since those answer different questions and people will assume whichever suits them.

Frequently asked questions

Can I get the events I send into Customer.io back out?

Not through this connector, which reads one of Customer.io's API hosts. Those events came from your own systems, so reading them at source is the sensible route.

Why is the first sync slow?

The host this connector uses enforces ten requests per second. That is a fixed ceiling rather than something to tune, so a large workspace simply takes a while on the first pass.

Which key do I need?

An App API key from your workspace settings, which is different from the credentials used for sending data into Customer.io.

Why are campaign fields arrays?

Because a campaign genuinely contains several actions, templates and segment references. BigQuery stores those natively, and a view that unnests them is what makes the data comfortable to query.

Can I do this without writing code?

The pipeline, yes. The views unnesting campaign structures are modest SQL, and without them people will write the same awkward query repeatedly and disagree about the results.

Get your Customer Io data into BigQuery

Check your questions against the available streams first, because this connector reads one of Customer.io's API hosts and the boundary is fixed. Create the dataset where your revenue and product data already live. Expect ten requests per second and a patient first sync. Then unnest campaign structures into views, filter on the partitioning column, and decide whether those views show current state or a point in time before somebody assumes the wrong one.

Airbyte's connector catalog includes 700+ pre-built connectors, so messaging activity can be measured against what it actually produced. For a comparable engagement platform into the same destination, see Braze to BigQuery, and for the commercial systems to join it against, Hubspot to BigQuery.

Start syncing now →