How to Load Data from Facebook Ads to BigQuery: 3 Best Methods

Summary

  • Method 1: Google BigQuery Data Transfer Service: A native Google option that automates scheduled transfers into BigQuery, suitable for basic ingestion but limited in flexibility and cross-platform logic.
  • Method 2: Custom Python Scripts & APIs: A developer-driven approach using APIs to extract Facebook Ads data and load it into BigQuery, offering maximum control at the cost of ongoing maintenance and engineering effort.
  • Method 3: Skyvia: A no-code data integration platform that automates extraction, transformation, and loading from Facebook Ads to BigQuery with scheduling, monitoring, and minimal setup time.

Three routes get Facebook Ads to BigQuery. Google has its BigQuery Data Transfer Service, developers can script against Meta's Graph API in Python, and no-code tools (Skyvia among them) do it from a browser. None is wrong. They just trade off freshness, table layout and upkeep differently.

Picture the usual Monday report. Ads Manager holds the spend. GA4 or BigQuery holds the conversions. And somebody ends up pasting both into a sheet for a ROAS figure nobody fully believes. When the Facebook Ads (now Meta Ads) data sits in the warehouse, nobody has to paste anything. You join it with CRM records, revenue and other channels, and the BI tool reads the result.

First we'll cover each method step by step. Then comes what actually shows up in BigQuery, a side-by-side comparison, and a SQL example joining Facebook spend to GA4 revenue.

Why Move Facebook Ads Data to BigQuery?

On its own, Facebook Ads does a decent job of reporting. But once you start asking bigger questions — cross-channel performance, attribution logic, long-term trends — you quickly hit the ceiling.  

That’s where you need BigQuery to play this game. 

Unified Reporting 

Marketing rarely lives in just one platform. You’re probably running Google Ads, maybe LinkedIn Ads, and tracking behavior in GA4. When all that data lands in BigQuery, users finally get a proper cross-channel view. No more jumping between dashboards or reconciling numbers manually. 

Overcoming Native Limitations 

Facebook Ads Manager reports can be sampled, and historical retention isn’t unlimited. BigQuery gives companies granular, unsampled data with long-term storage. So, users can control how long they keep it and how deeply analyze it. 

Advanced Analytics 

Once the info is in BigQuery, SQL becomes the superpower. You can build custom attribution models like First Touch or Time Decay, combine ad data with revenue tables, and calculate metrics that simply aren’t available in the Facebook interface. 

Visualization 

BigQuery easily connects to BI tools like: 

  • Looker Studio. 
  • Tableau. 
  • Power BI.  

That means interactive dashboards, stakeholder-ready reports, and a single source of truth instead of scattered screenshots from different ad platforms. 

Method 1: Google BigQuery Data Transfer Service

For users that need to schedule automatic imports from advertising platforms, Google offers a native transfer service inside BigQuery. Instead of exporting CSV files, you configure a transfer once and let BigQuery pull the data on a schedule. 

It’s the most “official” way to do it — fully inside Google Cloud. 

How it Works 

Setting up the BigQuery Data Transfer Service for Facebook Ads happens entirely inside Google Cloud. Here’s what the real process looks like. 

1. Enable the BigQuery Data Transfer API 
In your Google Cloud project, enable the BigQuery Data Transfer API. This allows BigQuery to pull data from external sources on your behalf. It’s a one-time setup step at the project level. 

2. Create a New Transfer Configuration 
Inside BigQuery, go to Transfers → Create Transfer. 
As the source type, select Facebook Ads (if available in your region). 

Then configure: 

  • Destination dataset: for example: marketing_facebook_ads. 
  • Display name: something like fb_ads_daily_sync. 
  • Schedule: every 24 hours. 

You’ll also get a fixed refresh window: each run pulls up to the last 30 days of data for the AdInsights and AdInsightsActions tables, and the window can’t be changed for this connector. 

3. Authorize Facebook Access 
Next, sign in with the Facebook account and grant permissions. BigQuery needs access to your ad account(s). 
And select the specific Ad Account ID you want to sync. 

Note: This step often causes friction: expired tokens or permission mismatches are common issues. 

4. Choose Tables and Confirm Settings 
Review the configuration:  

  • Dataset. 
  • Region. 
  • Schedule. 
  • Linked ad account.  

Once saved, the transfer runs on the defined schedule. 

After that, BigQuery automatically creates tables such as: 

  • AdInsights
  • Campaigns
  • AdSets
  • AdCreatives

Each scheduled run reloads all data available for the dates in the refresh window. Incremental transfers aren’t supported for the AdInsights, AdInsightsActions, Ads, Campaigns and AdSets tables.

No CSVs. No manual uploads. 

Note: Once configured, the pipeline runs automatically — but with one important caveat: the minimum refresh interval is typically 24 hours. That means this setup is designed for daily reporting, not real-time optimization. 

Pros 

  • Native integration inside Google Cloud. 
  • Automated scheduled transfers. 
  • No need to manage external infrastructure. 
  • Simple initial setup for basic use cases. 

Cons 

  • Rigidity: You largely get the structure Google provides, which limits flexibility if you need custom transformations. but can’t easily customize the schema.  
  • Latency: The minimum refresh interval is 24 hours. So, near real-time reporting isn’t possible. For performance marketing teams making daily bid decisions, this is a serious limitation. 
  • Backfilling Issues: If schemas change or fields are added, backfilling historical data can be difficult. This is a common frustration raised in developer forums. 
  • Errors: Token expiration, authorization failures, or INVALID_ARGUMENT errors can occur and often require manual reauthorization or troubleshooting. 

Verdict 

The BigQuery Data Transfer Service works for basic reporting needs and simple scheduled ingestion. 

But for serious data teams that need flexible schemas, faster refresh cycles, or reliable historical backfills, it often feels too rigid and slow for production-grade marketing analytics. 

Method 2: Custom Python Scripts & APIs

This approach involves building your own pipeline using the Facebook Graph API and writing custom scripts, typically in Python, to extract data and load it into BigQuery.  

On paper, it looks flexible and cost-efficient. In reality, you’re building and maintaining your own mini data platform. 

Instead of relying on a managed connector, the team handles authentication, API calls, schema mapping, error logging, retries, and warehouse loading logic.  

Pros 

  • Full control over what data you extract and how you structure it. 
  • Complete flexibility in transformation logic before loading into BigQuery. 
  • No license fees for third-party tools. 
  • Custom workflows tailored exactly to your reporting model. 

Cons 

  • Maintenance Overhead: Facebook’s Graph API versions change frequently (v19.0, v20.0, and so on). When endpoints or fields are deprecated, scripts break. This can happen multiple times a year. 
  • Token Management: OAuth tokens expire. Handling refresh tokens securely and automatically requires additional logic. When something fails, it often requires manual intervention. 
  • Pagination & Rate Limits: The API enforces request limits. Properly handling pagination, throttling, retries, and backoff strategies means writing more complex and defensive code. 
  • Monitoring & Reliability: Users have to build their own logging, alerting, and failure recovery. If the script silently fails, your reporting simply stops updating. 
  • Scaling Complexity: What starts as a “simple script” can quickly grow into dozens of scheduled jobs, schema mappings, and maintenance tasks as reporting needs expand. 

Verdict 

DIY scripting is free in terms of software licenses, but not in practice. The cost shows up in engineering hours, ongoing maintenance, and operational risk. It’s viable for highly technical teams, but for most marketing-driven analytics setups, it becomes more effort than it’s worth.

Method 3: Skyvia

If Method 1 feels too rigid and Method 2 feels like a side engineering project, Skyvia sits right in the middle. 

It gives users the reliability of an automated connector without the need to write code, and on Standard and higher plans, without the 24-hour delay of Google’s native service. You get scheduling, monitoring, transformations, and proper schema control, all in a visual interface. 

Step-by-Step Guide 

1. Connect 

  • Click + Create New → Connection, select the Facebook Ads connector, and authenticate with your account.  
Skyvia connectors
  • Then create a BigQuery connection using your Google Cloud credentials and service account key. 

Once both connections are set up, you’re ready to build the integration. 

2. Select Data 

Create a new Import or Replication package and choose Facebook Ads as the source and BigQuery as the target. 

Import by Skyvia

Unlike basic connectors, Skyvia automatically handles nested JSON structures coming from the Facebook API. Complex metrics, breakdowns, and hierarchical campaign data are unpacked and mapped cleanly into relational tables in BigQuery — no manual flattening required. 

You can select exactly what you want: 

  • Campaigns 
  • Ad sets 
  • Insights metrics 
  • Creative data 

Automatic field mapping is available, but you can customize it as needed. 

3. Filter (ETL) 

Here’s where things get practical. 

You can apply filters before loading data into BigQuery. For example, you might exclude paused campaigns or limit data to the last months. 

Skyvia filters

This reduces unnecessary storage costs and keeps your warehouse lean. Instead of moving everything and cleaning it later, you control what enters BigQuery upfront. 

You can also add transformations, conditions, or calculated fields directly in the visual designer. 

4. Schedule 

Set the refresh rate based on your needs. 

How often Skyvia can run depends on the plan: once a day on Free and Basic, once an hour on Standard, and as often as every minute on Professional and Enterprise. On Standard and higher, that’s a major difference compared to the daily transfers of Google’s native service. 

That means closer-to-real-time visibility for performance monitoring and budget optimization. 

You can also trigger runs manually if needed. 

Matrix: Which Method is Right for You? 

Each method solves a different problem. The real question isn’t “which works?” — they all work. The question is how much time, flexibility, and control you need. To compare more connectors than these three, see our roundup of ELT tools. 

Here’s the comparison: 

Feature Google Data Transfer (Native) Custom Python Script Skyvia (Automated) 
Setup Time Fast (~15 min) Slow (Dev setup required) Fast (~10–20 min) 
Data Freshness Low (24h minimum) Depends on your code Daily to every minute, by plan (every minute on Professional) 
Maintenance Medium (token & config issues) High (API updates, scripts break) Low (fully managed) 
Historical Backfill Difficult Manual & complex Easy built-in backfill 
Schema Flexibility Rigid (limited control) Full control Flexible with mapping 
Error Handling Manual troubleshooting You build it Built-in monitoring 
Cost Model Google usage fees Engineering time Flat/usage-based pricing 

Which Facebook Ads Data Ends Up in BigQuery?

Your choice of method isn't only about the schedule. It shapes your tables too, and decides whether late-arriving conversions ever get in.

BigQuery Data Transfer ServiceSkyvia
TablesA fixed set: AdAccounts, AdInsights, AdInsightsActions, Ads, AdCreatives, AdSets, Campaigns, AdImages, AdLabels, Businesses and CustomAudiences. Custom reports aren't supportedStandard objects (Campaigns, AdSets, Ads, AdCreatives and more) plus *Insights objects with metrics, including Daily and Monthly versions per account, campaign, ad set and ad
BreakdownsChosen once per transfer (generic and action breakdowns) and baked into AdInsights and AdInsightsActionsSet in the Breakdowns and ActionBreakdowns fields when you query an *Insights object
Late conversionsA refresh window of up to 30 days re-reads recent days for AdInsights, AdInsightsActions, Ads, Campaigns and AdSetsIncremental replication looks back 7 days for impression-based and 1 day for click-based conversions by default; both are connection settings
LoadingEach run transfers all data for the scheduled date; incremental transfers aren't supported for the insights, ads, campaigns and ad sets tablesIncremental replication after the first full load

Before that first load, settle two things. Breakdowns in the Data Transfer Service define how the AdInsights tables are built, so choose the ones your reports will need from day one. And skip AdInsightsMMM for now, since Google temporarily disabled that report on July 6, 2026.

Over in Skyvia, the Daily and Monthly insights pull in the current day or month even though it isn't finished yet. Great for a live dashboard. Less great when you notice today's numbers keep shifting. Want only complete days in BigQuery? Switch off Include Incomplete Data in Reports in the connection.

With a Python script, nothing is fixed: you call the Graph API's insights endpoint with the fields and breakdowns you want and design the tables yourself. The catch is that late conversions become your problem as well, which is why scripts like this should re-pull the last few days each run.

Advanced Use Case: Joining Facebook Spend with GA4 Revenue 

Now that your Facebook Ads data is sitting in BigQuery alongside GA4 events, this is where things start to get interesting. 

You’ve got spend in one table. 
You’ve got revenue and conversions in another. 

To calculate true ROAS, you need to bring them together — usually on a shared parameter like utm_campaign. 

Let’s say you have: 

  • facebook_ads table with campaign_name, date, and spend. 
  • ga4_events table with utm_campaign, event_date, and purchase_revenue. 

Here’s a simple example using a LEFT JOIN: 

WITH fb AS (
    SELECT date, campaign_name, SUM(spend) AS spend
    FROM `project.dataset.facebook_ads`
    GROUP BY date, campaign_name
),
ga4 AS (
    SELECT
        PARSE_DATE('%Y%m%d', event_date) AS event_date,
        utm_campaign,
        SUM(purchase_revenue) AS revenue
    FROM `project.dataset.ga4_events`
    GROUP BY 1, 2
)
SELECT
    f.date,
    f.campaign_name,
    f.spend AS total_spend,
    COALESCE(g.revenue, 0) AS total_revenue,
    SAFE_DIVIDE(g.revenue, f.spend) AS roas
FROM fb f
LEFT JOIN ga4 g
    ON f.campaign_name = g.utm_campaign
    AND f.date = g.event_date
ORDER BY
    roas DESC; 

A few things to note: 

  • We’re using a LEFT JOIN so we keep all Facebook campaigns, even if GA4 shows zero revenue. That way you don’t accidentally hide underperforming campaigns. 
  • Both tables are summed per campaign and day before the join. Otherwise every GA4 event row repeats the spend row and inflates total spend. 
  • In the GA4 export, event_date is a string like 20260115, so PARSE_DATE turns it into a DATE before the join. 
  • SAFE_DIVIDE protects you from division-by-zero errors. 
  • You can easily extend this by grouping by channel, ad set, or even blending in CRM revenue for more precise attribution. 

Once you start joining spends with actual revenue, reporting shifts from “how many clicks did we get?” to “which campaigns actually make money?” And that’s where BigQuery really earns its keep. 

Conclusion 

The “best” method always depends on: 

  • Setup; 
  • Team’s skills; 
  • Budget; 
  • How fast the company needs insights.  

Some organizations are comfortable building and maintaining their own scripts. Others are fine with a basic daily sync inside Google Cloud. 

But manual data work shouldn’t slow down any marketing decisions. If you’re spending hours exporting, fixing, and reconciling reports, that’s time you’re not optimizing campaigns or testing new ideas. 

The goal is get your Facebook Ads data into BigQuery reliably, keep it fresh, and stop worrying about whether the numbers are up to date. 

If you want automation without the engineering overhead, Skyvia makes it straightforward. 

Ready to automate your Facebook Ads reporting? Start your free trial of Skyvia today and get your data into BigQuery in under 10 minutes. 

FAQ for How to Load Data from Facebook Ads to BigQuery

Loader image

Yes, though not through an ads connector, because organic Page metrics come from a separate API. Google’s Data Transfer Service lists Facebook Ads as its only Meta source. Skyvia has a separate Facebook Pages connector with PageDailyInsights, PostLifetimeInsights and VideoLifetimeInsights objects covering the last two years. Today’s numbers are never included, and yesterday’s can still be incomplete.

Facebook Ads is one of its paid connectors, so you pay for the compute each transfer uses, measured in slot-hours. In us-central1, a slot-hour costs $0.06 at the default rate. Google suggests planning for up to 20 slot-hours per hour of run time, which works out to about $1.20 an hour, on top of normal BigQuery storage and queries.

Check the token first. The long-lived user access token that BigQuery’s Facebook Ads transfer uses expires after 60 days. Edit the transfer and generate a new one, or switch to a Meta system user token, which can be created without an expiry date. While you’re in there, confirm the token has the ads_management, ads_read and business_management permissions.

Yes, though the tool changes by platform. Skyvia has connectors for Google Ads, LinkedIn Ads, Microsoft Ads and Twitter Ads next to Facebook Ads. Google’s BigQuery Data Transfer Service handles Google Ads and Facebook Ads natively, but LinkedIn Ads isn’t on its list.

With the BigQuery Data Transfer Service, the first run loads only the refresh window; older dates need backfill runs of up to 180 days each. In Skyvia, Facebook Ads insights objects default to the last 36 months, monthly reports to the last 36 full months, and daily reports to 6 months; a DateStart filter sets a custom period.

Share

Nata Kuznetsova

Nata Kuznetsova is a seasoned writer with nearly two decades of experience in technical documentation and user support. With a strong background in IT, she offers valuable insights into data integration, backup solutions, software, and technology trends.

One platform for all your data work

One platform for all your data work

Integration, automation, live data access, and backup. No code, 200+ connectors.

Start free