[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.
References and Further Reading
