[1col]Why Import Facebook Ads Data to BigQuery?
Centralizing Facebook Ads (Meta Ads) data in Google BigQuery empowers marketing, analytics, and data engineering teams to:
- [connection]Unify data sources: Join paid media data with CRM, product, and web analytics for enhanced incrementality and multi-touch attribution.
- [automation]Automate reporting: Build dynamic cost and performance dashboards without manual CSV exports or error-prone spreadsheets.
- [reliable]Strengthen governance: Create repeatable, scheduled data pipelines that ensure data freshness, reliability, and compliance.
Many organizations leverage ETL tools or native integrations, but Keboola offers a secure, flexible, and scalable way to automate this integration—saving time and reducing manual errors.
Integration Architecture Overview
- Source: Facebook Marketing API via Keboola’s Facebook Ads data source connector.
- Landing: Data loads into Keboola Storage tables within your secure project.
- Transformation (Optional): Model campaign metrics, normalize data, clean hierarchies, and deduplicate as needed.
- Destination: Google BigQuery Writer component pushes curated data tables into your BigQuery dataset using a dedicated service account.
This modular approach allows you to tailor data flows, apply business logic, and ensure analytics-readiness with minimal engineering effort.
Prerequisites
- [person]Facebook Ads Access: You need permission to relevant Facebook ad accounts and the
ads_management API scope. - [cloud]Google Cloud Project: BigQuery enabled, with a service account holding BigQuery Data Editor and BigQuery Job User roles. The service account JSON key is required for setup.
- [tool]Keboola Project: Access to Keboola Connection with permissions to use components.
For more guidance, see the BigQuery Setup Guide and Keboola Documentation.
Step-by-Step Integration Guide
Step 1 — Configure Facebook Ads Data Connector
- Add the Facebook Ads extractor in your Keboola project. See the Keboola Facebook Ads connector docs.
- Authorize your Facebook account. Keboola will request the
public_profile and ads_management scopes for secure, read-only data access. - Select Ad Accounts you wish to extract data from.
- Create extraction queries:
- Choose a template (e.g., Ads Insights by Day) or define custom metrics and dimensions.
- Specify fields like spend, impressions, clicks, and conversions. Set the lookback window, breakdowns, and attribution models as needed.
- Run the extractor to land raw Facebook Ads tables into Keboola Storage.
Tech note: Keboola's Facebook Ads extractor is open-source—see the GitHub repo for implementation details.
Step 2 — (Optional) Transform and Model Data in Keboola
Apply business logic and enhance data quality before loading to BigQuery:
- Normalize campaign, ad set, and ad names and IDs for consistent analytics.
- Map conversions and standardize currencies to unify measurement.
- Deduplicate data using unique keys (
date, ad_id, metric), especially when re-pulling historical data. - Aggregate data to the required level (e.g., daily, campaign, ad set, ad).
- Conform schemas for stable, reliable downstream analytics tables.
Step 3 — Set Up the Google BigQuery Writer in Keboola
- Add the Google BigQuery Writer to your Keboola project. Learn more at the component page.
- Authenticate using your service account JSON key. Double-check that required roles (BigQuery Data Editor, BigQuery Job User) are assigned.
- Select or create your BigQuery dataset as the data destination.
- Map tables from Keboola Storage:
- Use incremental write mode for daily facts and append-only tables.
- Use full load mode for small dimensions (e.g., campaign details).
- Schedule the writer to run after Facebook Ads data extraction for seamless pipeline orchestration.
Recommended BigQuery Table Design
fact_facebook_ads_daily:- Primary keys:
date, account_id, campaign_id, adset_id, ad_id - Metrics:
spend, impressions, clicks, ctr, cpc, cpm, conversions_* - Partition by
date for efficient querying and cost control. dim_campaign, dim_adset, dim_ad:- Attributes include names, objectives, statuses, and created time.
Example BigQuery Validation Queries
Daily Spend Check:
SELECT date, SUM(spend) AS spend FROM `project.dataset.fact_facebook_ads_daily` WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() GROUP BY date ORDER BY date;
Top Campaigns by ROAS:
SELECT c.campaign_name, SUM(f.revenue) / NULLIF(SUM(f.spend), 0) AS roas, SUM(f.spend) AS spend FROM `project.dataset.fact_facebook_ads_daily` f LEFT JOIN `project.dataset.dim_campaign` c USING (campaign_id) WHERE f.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY c.campaign_name ORDER BY roas DESC LIMIT 20;
Scheduling and Orchestration Tips
- [flow]Pipeline Order: Extractor → (Optional Transforms) → Writer.
- [clock]Lookback Window: Schedule a rolling 7–14 day re-pull to catch late conversions and attribution updates.
- [stable]Schema Stability: Pin field lists to avoid downstream schema drift. Monitor API changes via GitHub releases.
- [cost]Cost Control: Partition tables by date and avoid
SELECT * in BI tools. Aggregate data to reporting grain to reduce storage and query costs.
Troubleshooting Checklist
- [exclamation]Authentication Errors: Re-authorize your Facebook account, confirm
ads_management scope, and verify account permissions. - [incomplete]Missing Ad Accounts: Ensure the OAuth user has access to the correct Facebook Business Manager ad accounts.
- [broken]Writer Failures: Verify service-account roles, dataset existence, and JSON key validity.
- [slow]Late Data: Increase lookback window and apply deduplication on unique keys during transformation.
Security and Data Governance
- [lock]Store your Google service account key securely within Keboola’s encrypted credentials vault. Rotate credentials regularly to minimize risk.
- [disconnect]You can revoke Keboola’s Facebook API access any time from your Facebook account settings for added control.
[1col]Why stop at Facebook?
Getting your Facebook Ads data into BigQuery is just the first step — but most marketing teams don’t stop there. Campaigns typically span LinkedIn Ads, Instagram, Google Ads, TikTok, or affiliate networks, and the real challenge is bringing all those sources together in one place.
With Keboola, you can:
- [360]Unify all paid media data: in a single warehouse (BigQuery, Snowflake, Redshift, etc.), alongside sales, CRM, or product data.
- [data-source]Standardize metrics across channels: ROAS, CAC, and attribution can be calculated consistently everywhere.
- [collaboration]Enrich and activate audiences: Use CRM segments for lookalike audiences, push unified audiences back into multiple ad platforms, or optimize budgets across channels.
- [scalable]Go beyond ad-specific tools: Keboola lets you reshape schemas, apply your own business logic, or integrate data from ERP, e-commerce, or internal apps.
- [centralized]Plug in what you already use: Tools like Supermetrics or Funnel can feed into Keboola for further enrichment, governance, and orchestration.
So even if today’s goal is simply “Facebook Ads → BigQuery,” Keboola ensures you’re ready for what’s next — like answering “Can we see blended CAC across Facebook, LinkedIn, and Google Ads, matched against actual revenue in the CRM?”
References and Further Reading
