Facebook Ads to BigQuery: How to Move Your Data

Moving Facebook Ads to BigQuery gives you campaign structure and performance metrics in a warehouse, joinable against revenue, other paid channels, and your own attribution model. Airbyte's Facebook Marketing connector handles it with no code. One fact about Meta's data governs the whole design: Facebook freezes insight data 28 days after it was generated.

Everything inside that 28-day window may still change. A pipeline that reads each day once will faithfully record a version of the numbers that Meta later revises, and the gap only becomes visible when someone reconciles against Ads Manager.

How does the lookback window work?

The Facebook Marketing connector uses a lookback window parameter to repeatedly re-read the last several days during an incremental sync. That means some data is synced twice or more even though the cursor is up to date, deliberately, in order to capture updated conversion data.

You can set it up to a maximum of 28 days, matching the freeze period. Airbyte's guidance is direct about the trade: smaller values produce fewer duplicates, larger values produce more accurate results. Facebook permits click-through attribution windows of up to 28 days and limits view-through attribution to 1 day, and if you have configured a custom attribution window in your Facebook account you should set the same value here rather than leaving the default.

Because rows arrive repeatedly, plan deduplication into your model from the start. Deduplicating on the combination of ad identifier and report date, keeping the most recently extracted version, gives you a table that converges on Meta's final numbers instead of accumulating every intermediate revision.

Timezones are handled per account and need no configuration. If you sync several ad accounts in different timezones through one connection, each account's date range is computed independently from its own timezone setting, and this applies to all Ads Insights streams including custom ones.

Why might purchase metrics be missing?

This one is worth knowing before it costs you a week of investigation, because it is a documented behaviour of Meta's API rather than a connector defect.

API users have reported missing purchase metrics, where action values such as offsite purchase conversions appear correctly at the ad or ad set level but disappear at the campaign or account level. Similar behaviour was documented in the Facebook Developer Community several years ago and appears to have resurfaced more frequently from 2025 onwards. The underlying cause is that Meta's Ads Insights API dynamically aggregates and filters metrics, and attribution window processing means Facebook re-attributes conversions as data settles.

The practical response is to sync at the ad level and aggregate upward yourself in BigQuery, rather than requesting campaign-level or account-level insights and trusting them. You get more rows, but you get purchase data that is actually present, and rolling ad-level numbers up to campaign is trivial in SQL. If your reporting depends on purchase conversions, treat this as the default approach rather than an optimisation.

Configuration that affects sync size

A Time Increment setting aggregates statistics into windows of N days. Setting it to 7 turns a month into roughly five weekly records instead of thirty daily ones. That is a real reduction in volume and a real loss of resolution, so choose according to whether anyone actually looks at day-level performance. Note that Time Increment cannot be used together with Time Increment Period, and switching between them triggers a full re-sync.

An Insights Job Timeout sets the maximum minutes to wait for a report job to complete. On timeout the job is treated as failed and the connector retries by breaking it into smaller jobs, which is sensible behaviour but means a large account can spend a long time subdividing work. There is also a page size setting defaulting to 100 records per page, which most people never need to change.

For access, your token needs the ads management, ads read, read insights, and business management permissions. You will also supply the ad account ID, which you can find in the account dropdown in Meta Ads Manager or in the browser address bar. There is an option to include deleted campaigns, ads, and ad sets, which is usually worth enabling for historical spend reporting, since spend against a deleted campaign still happened.

Landing the data in BigQuery

The BigQuery destination uses direct load, writing straight to final tables, with Batched Standard Inserts as the default loading method. Your service account needs BigQuery User and BigQuery Data Editor, and the dataset location must match the datasets you intend to join against, since BigQuery cannot query across physical locations and the location cannot be changed after creation.

Tables are partitioned daily on the extraction timestamp rather than the report date. Because the lookback window means the same report date is written on many different extraction dates, this partitioning is close to useless for the queries you will actually run. Build a downstream model partitioned on the report date and clustered on campaign, deduplicated to the latest extraction per ad and date, and point dashboards there.

Frequently asked questions

Why is the same day appearing multiple times?

That is the lookback window working as intended. The connector re-reads recent days to capture Meta's revisions, so deduplicate on ad and report date keeping the latest extraction.

My purchase metrics are missing at campaign level.

This is documented API behaviour rather than a connector fault. Purchase action values often appear at ad and ad set level while disappearing at campaign and account level. Sync at ad level and aggregate upward in SQL.

What lookback window should I set?

Match whatever attribution window your Facebook account uses. If you have not customised it, the maximum of 28 days aligns with the point at which Meta freezes insight data.

Do I need to set a timezone?

No. The adjustment is applied per account with no configuration required, and accounts in different timezones within one connection each get their own date range computation.

Get your Facebook Ads data into BigQuery

Match the lookback window to your account's attribution setting, sync at ad level so purchase metrics survive, deduplicate on ad and report date, and model a table partitioned on report date. For other sources landing in the same warehouse, see our guides to Datadog to BigQuery and MongoDB to BigQuery.

Start syncing now →