Pinterest Ads to BigQuery: How to Move Your Data

Moving Pinterest Ads into BigQuery puts campaign performance beside every other channel you spend on. Pinterest reports capably within its own interface and cannot tell you how its cost per acquisition compares with search or paid social, which is the question anybody allocating a budget is actually asking.

This guide covers the managed path with Airbyte. Two things shape the build: two groups of streams have very different ideas about how far back you can look, and attribution windows mean yesterday's figures are not final.

Pinterest Ads to BigQuery at a glance:

Why move data from Pinterest Ads to BigQuery?

Two situations account for most of these pipelines.

The first is comparing channels on the same terms. Every advertising platform reports favourably on itself using its own attribution rules, and the only way to judge them against each other is to bring the numbers together and apply consistent definitions. That is a warehouse job, and it is usually the reason somebody asks for this pipeline.

The second is outliving the platform's own retention, which matters here more than most because one group of streams keeps only ninety days. The pairing is a poor fit if you want granular, event-level data about individual users, since what this provides is aggregated campaign reporting; a warehouse will hold that faithfully and cannot invent the detail underneath it.

What do you need before you start?

Four things, and two of them are defaults that quietly shape your dataset:

Pinterest credentials. OAuth is the default route, or supply a client identifier, secret and refresh token directly. An optional ad account identifier restricts the source to one account, which is worth setting when you manage several. The Pinterest source documentation covers obtaining them.

A decision about the status filter. For the ads, ad groups and campaigns streams you can filter by status, and leaving it unset does not mean everything: the default covers active and paused only. Archived campaigns are excluded, which matters if you are analysing anything historical.

Agreed attribution windows. Click window days defaults to thirty and accepts 0, 1, 7, 14, 30 or 60, with a separate engagement window for saves and similar actions. These decide what counts as a conversion, so agree them with whoever compares channels rather than accepting a default nobody chose.

A BigQuery dataset in the right location. Location is fixed at creation and BigQuery will not join across locations, so this belongs wherever your other marketing data already lives.

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 Pinterest Ads to BigQuery pipeline in Airbyte?

Step 1: Settle the attribution windows before the first sync

Changing a click or engagement window later does not adjust one setting, it changes what every historical figure means, and a chart whose definition shifted halfway along is worse than no chart. Agree the numbers with the people who will compare Pinterest against other channels, write them down beside the dataset, and treat a later change as a deliberate restatement rather than a tweak.

Step 2: Configure the Pinterest source

Click Sources in the left navigation, then New Source, and select Pinterest, following adding a source. Authenticate, then set your start date, status values and any custom reports. Custom reports take a granularity, which defaults to aggregating across the whole range, so choose daily if you want a time series rather than one total.

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. Advertising data is modest in volume, so batched standard inserts are fine and the interesting cost here is query time rather than loading.

Step 4: Create the connection and start the ninety-day clock

Click Connections, then New connection, select your streams and a sync mode. Get the analytics streams running promptly, because every day you delay is a day of history that expires unrecoverably. Concurrent workers can be tuned if syncs are slow, though volume is rarely the problem on this connector.

Sync daily at minimum. A weekly schedule combined with a ninety-day window leaves far less margin for a few failed runs than anybody expects.

Why do your streams disagree about how far back they go?

Because there are two retention regimes in one connector. Analytics streams reach back ninety days. Report streams reach back 913, which is a little over two and a half years. Leave the start date blank and each stream defaults to its own maximum, which is sensible behaviour and means your dataset has a ragged edge nobody warned you about.

The ninety-day limit is the one that hurts, because it is not a limit on what you can request but on what exists to be requested. History older than that was never available and cannot be recovered later by fixing a configuration, so the warehouse only ever holds what you were already collecting. The value of this pipeline grows from the day it starts, which is an argument for starting it before anybody strictly needs it.

The practical consequence is that a failed sync is more expensive here than elsewhere. A pipeline broken for a fortnight against a ninety-day window is recoverable; one broken for four months has permanently lost the difference. Alert on failures rather than reviewing occasionally, and be careful comparing an analytics stream against a report stream over a long period, since one of them will simply stop having data partway back.

How should BigQuery hold figures that keep changing?

Deliberately, because attribution means a day's numbers are provisional for as long as the window runs. With a thirty-day click window, conversions keep being credited to a campaign for a month after the click, so the figure you record for the first of the month on the second is not the figure that day will eventually settle at.

BigQuery handles this rather well if you let it. Tables arrive partitioned daily on the extraction timestamp rather than on the reporting date, which means successive syncs of the same reporting day land in different partitions instead of overwriting each other. You therefore have a record of how each day's figures matured, which is exactly what you need when somebody asks why last month's report no longer matches the one they printed.

Build two views over that. One taking the most recent extraction per reporting date, which is the current best answer and what most reporting should read. One preserving the extraction timestamp for anybody investigating a discrepancy. Have both filter on the partitioning column, since BigQuery bills on bytes scanned and a query that ignores it reads every version of every day you have ever collected.

Frequently asked questions

How far back can I backfill?

It depends which stream. Analytics streams allow ninety days and report streams around 913. Leaving the start date blank gives each stream its own maximum rather than a single consistent window.

Why are some campaigns missing?

Probably the status filter, which defaults to active and paused and therefore omits archived entities. Set it explicitly if your analysis covers campaigns that have since been archived.

Why did last month's conversions go up?

Attribution. With a thirty-day click window, conversions continue being credited to earlier clicks for a month, so recent figures mature rather than staying fixed.

Can I turn attribution off?

For clicks, yes, by setting the click window to zero. That gives stable figures that will not match Pinterest's own reporting, so make sure everyone understands the choice before you make it.

Can I do this without writing code?

Yes for the pipeline, which is entirely UI-driven. The views handling restatement are modest SQL and they are what stops two people quoting different numbers for the same week.

Get your Pinterest Ads data into BigQuery

Start the analytics streams sooner than you think you need them, because ninety days of history expires whether or not anybody is collecting it. Set the status filter explicitly so archived campaigns are not silently missing, and agree the attribution windows before the first sync rather than restating everything later. Then use the extraction timestamp partitioning to keep both answers, the matured figure and the history of how it matured, and filter on that column so the queries stay cheap.

Airbyte's connector catalog includes 600+ pre-built connectors, so every advertising channel can be judged on the same terms. For comparable ad platforms into the same destination, see Google Ads to BigQuery and Bing Ads to BigQuery.

Start syncing now →