How to Build a Clean Facebook Ads Reporting Layer in BigQuery with dbt (Free Package)

Raw Facebook Ads data in BigQuery is messy. Table structures differ between campaigns. Metric definitions often change over time.

This makes consistent reporting difficult. Analysts often rework SQL just to get CTR (click-through rate), ROAS (return on ad spend), or CPC (cost per click) right. Inconsistent models force marketing teams to work with dashboards that show conflicting results or data mismatches.

dbt, data build tool, addresses this challenge by transforming raw data into a clean, well-documented layer. It utilizes modular SQL, performs data quality checks, and maintains models in version control. When paired with BigQuery, it handles millions of ad records quickly and efficiently, eliminating the need for manual work.

Windsor.ai makes Facebook Ads analytics into BigQuery even easier. Our Facebook Ads to BigQuery connector delivers raw tables directly into your warehouse with correct field mappings. From there, you can use our free dbt Facebook Ads package to turn that raw data into an analytics‑ready reporting layer.

Explore Windsor.ai’s dbt-BigQuery Package for Facebook Ads Data: https://github.com/windsor-ai/dbt-facebook-big_query.  

This is a production-ready dbt package that transforms raw Facebook Ads data integrated with Windsor.ai into clean tables in BigQuery following standardized architecture patterns.

This package includes staging, intermediate, and mart models designed for tracking ad performance. It calculates metrics, converts currencies, and segments audiences without extra setup. The data ends up in BigQuery, organized and consistent, ready for custom reports or deeper analysis.

This guide walks you through Facebook Ads to BigQuery integration via Windsor.ai, deploying the dbt package, and, ultimately, turning Facebook Ads data into a dependable reporting layer.

Why use Windsor + dbt + BigQuery for Facebook Ads data modeling?

• Windsor.ai handles a fully automated data integration, so the data pipeline stays fresh and reliable without any code or manual work.
• dbt models and transformations help to keep the integrated data clean and well-formatted. 
• BigQuery then helps process large ad datasets with great speed and scale, allowing for comprehensive analytics. 

Benefits of dbt for Facebook Ads data modeling

• Modular SQL pipeline

dbt allows you to split the complicated Facebook Ads logic into smaller, reusable SQL models. Each one handles a single transformation step. This setup limits errors and facilitates debugging.

• Built-in testing and documentation

dbt enables you to test data quality before it reaches the reporting layers. You can document models alongside the SQL code. This ensures that Facebook Ads data is accurate and easy for the team to interpret.

• Version control and collaboration for data teams

dbt links directly with Git, tracking edits as they happen. Teams review changes before deployment. This step helps stop unwanted updates from reaching production models.

Benefits of BigQuery for Facebook Ads data modeling

• Handles large volumes of data

BigQuery processes billions of Facebook Ads records lightning-fast. This allows daily refreshes without performance delays.

• Fast performance

BigQuery’s distributed architecture returns results in seconds. Marketing teams get near-real-time campaign insights.

• Compatible with dbt

dbt connects to BigQuery seamlessly. You can use dbt run to transform your Facebook Ads data directly in BigQuery,  modeling raw data into clean, analytics-ready tables using SQL.

Benefits of Windsor.ai for Facebook Ads data modeling

• Automated data integration

Windsor.ai’s no-code ELT connectors automatically pull Facebook Ads data into BigQuery, eliminating manual exports and custom scripts.

• Clean, structured schema

Windsor streams Facebook Ads data to BigQuery in a normalized, analysis-ready format, perfect for immediate use with dbt models. With consistent naming and table structure, Windsor.ai removes the guesswork from building or customizing dbt models for Facebook Ads.

• Up-to-date data

Continuous, scheduled syncs ensure your BigQuery tables always reflect the latest campaign performance, which is essential for accurate, near-real-time insights.

• Saves engineering time

Skip building ETL pipelines from scratch. By handling ingestion or schema mapping, Windsor lets you focus on writing dbt transformations instead.

Common challenges with raw Facebook Ads data

The common challenges with Facebook Ads data are inconsistent naming and schema gaps. Missing metrics and complex segment structures are other frequent problems. Finally, currency conversion errors can significantly impact the accuracy of Facebook Ads datasets.

You can face all these and more issues when working with raw Facebook Ads data: 

Inconsistent naming and structures across campaigns

Facebook Ads data often has inconsistent naming for campaigns, ad sets, and ads. Field names and structures may differ between accounts or change over time. In BigQuery, this forces analysts to write extra joins or mappings before using dbt for data modeling.

No type safety and schema enforcement

Because Facebook Ads API returns JSON responses, numeric fields like impressions or spend may arrive as strings. Type casting must be applied during ingestion so BigQuery stores numeric fields with the correct types (INT64, FLOAT64). Windsor.ai handles this automatically via schema definitions.

Keep in mind that downstream dbt models fail unless you cast fields to the correct types during the dbt Facebook Ads modeling stage.

Example query:

SELECT
SAFE_CAST(impressions AS INT64) AS impressions,
SAFE_CAST(spend AS FLOAT64) AS spend
FROM `raw.facebook_ads`;

Lack of pre‑calculated metrics

While Facebook Ads API can return some pre-calculated metrics like CTR or CPC, relying on them can be risky due to changes in definitions or missing breakdowns. Calculating them in dbt ensures consistency and full control.

Example query:

SELECT
spend,
revenue / NULLIF(spend, 0) AS roas
FROM `stg_facebook_ads`

Difficult to analyze audience segments

Raw Facebook Ads data stores audience breakdowns in nested or separate tables. In BigQuery, setting up these structures for Facebook Ads reporting means using UNNEST and careful join logic. Without dbt models, metrics can become inconsistent when analysts filter results by different segments.

Currency conversion issues in multi‑market accounts

Facebook Ads accounts running in multiple countries often report spend in their respective local currencies. BigQuery doesn’t automatically convert or aggregate these values, so without currency normalization, totals will be misleading.

In a dbt workflow, you need to join exchange rate tables and convert spend amounts to a common currency (usually USD or EUR) before aggregating them.

Example query:

SELECT
spend * rate AS spend_usd
FROM `stg_facebook_ads`
JOIN `currency_rates` USING (currency)

Windsor.ai’s dbt Facebook Ads package standardizes schemas, enforces data types, and applies required transformations. 

Introducing Windsor’s dbt package for Facebook Ads

Windsor’s dbt Facebook Ads package streamlines BigQuery data modeling with a clean, multi‑layer architecture. It delivers pre‑built metrics, audience insights, currency conversion, and built‑in data validation. Optimized SQL (Structured Query Language) and partitioning ensure fast, cost-efficient reporting for high-volume Facebook Ads datasets.

What’s included

1. Multi‑layer architecture: Staging → Intermediate → Marts

The package implements a three-layer structure for modeling BigQuery Facebook Ads data.

stg_ models clean API fields, enforce naming standards, and format timestamps. int_ models combine connected data, and marts_ models build the final tables used in reports.

Example dbt structure:

models:
 facebook_ads:
   staging:
     stg_facebook_ads__campaigns.sql
     stg_facebook_ads__ad_sets.sql
   intermediate:
     int_facebook_ads__performance.sql
   marts:
     facebook_ads__summary.sql

2. Pre‑built performance metrics

The dbt Facebook Ads modeling layer includes SQL for core KPIs. Metrics such as ROAS, CTR, CPC, CPM, and conversion rates come pre‑built in the mart’s layer. This ensures consistency in calculations across all reports.

Example query:

SELECT
spend,
clicks,
conversions,
impressions,
revenue / NULLIF(spend, 0) AS roas,
clicks / NULLIF(impressions, 0) AS ctr
FROM {{ ref('int_facebook_ads__performance') }}

3. Audience insights: geography and demographics

This dbt package transforms Facebook Ads data, allowing you to segment by location and audience details. Reports can show results by country, region, age, or gender. This provides you with detailed Facebook Ads reporting in BigQuery without requiring additional joins.

4. Currency conversion: built‑in exchange rate support

For multi‑currency campaigns, this package applies automated conversion. Daily FX rates standardize spend and revenue to a chosen currency. This keeps transforming Facebook Ads data with dbt consistent across regions.

5. Validation logic: dbt tests for data quality

Windsor.ai’s Facebook Ads dbt+BigQuery package uses dbt tests to catch issues before they reach your dashboards. It checks for null campaign IDs, negative spend, and date mismatches. Problems can be fixed at the source, so analysts work with clean data.

6. Optimized for BigQuery: efficient SQL logic and partitioning

When configured in dbt, date-partitioned models in BigQuery reduce scan costs and improve performance for high-volume Facebook Ads datasets.

7. Deduplication & idempotent loads

The package generates stable surrogate keys and deduplicates incoming insight rows (per event/ad/date/action) so repeated syncs, backfills, or retries won’t create duplicate records.

Setup guide: how to use this dbt package

To get started, first, you have to connect your Facebook Ads data to BigQuery using Windsor.ai (this open-source package is built to integrate with it). Then clone the package, configure variables for your specific use case, and run the installation to set up dependencies, build the models, and validate data quality. The dbt package transforms your data into ready‑to‑use tables. Running dbt commands ensures clean, tested, and analytics‑ready campaign metrics.

Integrating Facebook Ads data to BigQuery via Windsor.ai

Before using this dbt package, you have to integrate Facebook Ads data into BigQuery using the Windsor.ai connector to ensure the schema matches the expected format:

1. Sign up for Windsor.ai’s free trial.
2. Connect your Facebook Ads account(s).
3. Choose BigQuery as a data destination.
4. Create and run a destination task for every required table by selecting specific fields. 

To ensure this dbt package works smoothly, you must sync all required Facebook Ads fields from Windsor to BigQuery.

In Windsor, create separate destination tasks using these recommended presets:

• ad
• location
• campaign
• user

Use Windsor’s predefined “Report Presets” to auto-select all fields needed for dbt model compatibility. Manual changes to fields may break model assumptions.

These tables must be created with the field structure defined in the sources.yml file:

1. facebook_ads_windsor_campaigns

   Use the Campaign preset from the Report Presets dropdown (it retrieves campaign-level info such as objectives, budgets, and status).

   Key fields: account_id, campaign_id, campaign_name, campaign_objective, campaign_status, campaign_budget_*

2. facebook_ads_windsor_ads

   Use the Ad preset from the Report Presets dropdown (it retrieves ad creative metadata and configuration).

   Key fields: actor_id, ad_id, ad_name, adset_id, title, body, link_url, thumbnail_url

3. facebook_ads_windsor_insights

   You have to manually select the fields from the list below (to retrieve daily ad performance metrics).

   Key fields: date, account_id, campaign_id, ad_id, impressions, clicks, spend, actions_purchase

4. facebook_ads_windsor_audience_location

   Use the User preset from the Report Presets dropdown (to retrieve audience performance segmented by demographics (age and gender)).

   Key fields: date, age, gender, clicks, frequency, impressions, reach

5. facebook_ads_windsor_audience_demographics

   Use the Location preset from the Report Presets dropdown (to retrieve audience metrics segmented by location (country and region)).

   Key fields: date, country, region, clicks, frequency, impressions, reach

Windsor.ai will stream your Facebook Ads data to your BigQuery project in minutes. After verifying that the data is present, you’re ready to start transforming it using this dbt package.

For more details, visit our dedicated integration guide: How to Integrate Facebook Ads into BigQuery.

How to deploy the dbt package

Now you’re ready to run this dbt package to transform your raw Meta Ads data integrated via Windsor.ai.

1. Configure your dbt_project.yml

Add or update the following under vars::

vars:  
  facebook_ads_source_table: 'your-project.raw_data.facebook_ads_windsor_campaigns'  
  facebook_ads_start_date: '2024-01-01'  
  exclude_test_campaigns: true  
  min_spend_threshold: 0  
  min_impressions_threshold: 1

2. Make sure these source tables are available in your BigQuery project:

• facebook_ads_windsor_campaigns
• facebook_ads_windsor_ads
• facebook_ads_windsor_insights
• facebook_ads_windsor_audience_location
• facebook_ads_windsor_audience_demographics

These should be configured as sources in your sources.yml, and the table names should match exactly (case-sensitive).

3. Run the models and tests:

Run all models:

dbt run

Run specific layers:

dbt run --select +stg_facebook_ads    # Staging only  
dbt run --select +int_facebook_ads    # Staging + Intermediate  
dbt run --select +facebook_ads        # All models

Run tests:

dbt test

Best practices:

• Run Windsor syncs at the same time as dbt jobs to keep Facebook Ads data in BigQuery up-to-date.
• Track how Windsor tables change over time by setting them up as dbt sources.
• Save dbt logs so you can quickly find and fix issues when a model or test fails.

With Windsor feeding Facebook Ads data into BigQuery and dbt applying the Facebook Ads modeling package, you get ready‑to‑query metrics. This setup supports faster campaign analysis without manual data preparation and uploads.

Key models and metrics explained

In BigQuery, the dbt Facebook Ads models organize campaign, ad, and audience data. They use structural transformations to determine CTR, ROAS, CPC, and conversion rates. This approach supports accurate budgeting, creative testing, and audience targeting.

Models overview

1. campaign_summary: campaign-level performance

The campaign_summary model aggregates Facebook Ads performance at the campaign level. It brings spend, clicks, impressions, and conversions together in one place for clear analysis and insight. In dbt, this typically runs incremental loads on BigQuery to optimize processing costs.

select
campaign_id,
campaign_name,
sum(impressions) as impressions,
sum(clicks) as clicks,
sum(spend) as spend
from {{ ref('raw_facebook_ads') }}
group by campaign_id, campaign_name

2. ad_performance_daily: daily breakdowns by ad set and ad

This model enables fine‑grained Facebook Ads reporting in BigQuery. It shows day‑by‑day trends for each ad set and ad. The dbt Facebook Ads package handles joins between the date, ad, and campaign tables for easy querying.

select
date,
ad_id,
adset_id,
clicks,
impressions,
spend
from {{ ref('raw_facebook_ads') }}

3. audience_metrics: demographic and geographic performance

The audience_metrics model tracks engagement across demographic and geographic dimensions. It uses age, gender, and region fields from Facebook Ads BigQuery tables. Transform Facebook Ads data with dbt to create filters for audience‑level optimization.

KPIs overview

CTR (click-through rate)

CTR = clicks/impressions*100

It is essential for comparing creative performance since it indicates the frequency with which users click after viewing an advertisement.

ROAS (return on ad spend)

ROAS = revenue/spend

It measures how much revenue is generated per dollar spent on ads. It helps evaluate return efficiency, not engagement.

CPC (cost per click)

CPC = spend/clicks

It guides budget allocation across campaigns and ad sets based on click costs.

Conversion rate

Conversion Rate = conversions/clicks*100

It measures how efficiently ad clicks turn into actual business outcomes.

Custom tests & macros for Facebook Ads data in BigQuery with dbt

BigQuery dbt pipelines, custom tests, and macros are designed to keep Facebook Ads metrics accurate and dependable. They verify that clicks never outnumber impressions and that spend values are always non-negative. Pairing Windsor.ai’s clean feed with dbt macros ensures stable and trustworthy reporting.

Windsor.ai’s package includes 5 utility macros and 4 custom tests designed specifically for Facebook Ads data:

• calculate_performance_metrics: Calculate CTR, CPC, CPM, conversion rate, ROAS, and cost per conversion.
• classify_performance_tier: Classify performance into tiers (High/Good/Average/Poor/No Spend).
• standardize_campaign_objective: Standardize Facebook campaign objectives to consistent names.
• validate_facebook_ads_data: Generate data quality validation flags.
• generate_facebook_ads_surrogate_key: Generate consistent surrogate keys for different grains.

• facebook_ads_ctr_range: Test CTR values are within the 0-100% range.
• facebook_ads_metric_consistency: Test clicks never exceed impressions.
• facebook_ads_spend_consistency: Test spend consistency with activity.
• facebook_ads_performance_tier_valid: Test performance tier values are valid.

Deduplication & idempotency

Facebook/Meta insights can include duplicate rows (retries, overlapping sync windows, or changed action records). Proper dedupe ensures metrics (CTR, CPC, spend, conversions) aggregate correctly and that incremental runs are idempotent.

What this package does:

This package uses a stable surrogate key (generated from natural keys such as account_id, campaign_id, ad_id, date, and action identifiers) and a deterministic ordering (event timestamp then ingestion timestamp) to collapse duplicate rows. Staging models produce a deduped intermediate table via ROW_NUMBER() (keeping the latest record per surrogate key). Incremental mart models use BigQuery MERGE (or dbt incremental logic) keyed on that surrogate key to ensure upserts are idempotent, safe to run repeatedly, or to backfill without creating duplicates.

Data quality tests

Define strict rules in schema.yml to validate raw Facebook Ads data before modeling. There are two more vital things to check:

1. Clicks should never exceed impressions

If clicks exceed impressions, the source data is likely corrupt. Add:

tests:
 - dbt_utils.expression_is_true:
     expression: "clicks <= impressions"

2. Spending should not be negative

Negative spend typically indicates poor currency mapping or incomplete data loads. Use:

tests:
 - dbt_utils.expression_is_true:
     expression: "spend >= 0"

Run these tests right after loading data into BigQuery. This way, problems are caught early and never reach the models.

Ensuring data reliability

Custom tests and macros refine Facebook Ads data in dbt while maintaining quality. They help prevent bad data from reaching BigQuery reports.

Windsor.ai’s Facebook Ads connector delivers a clean raw data layer from the start. dbt applies targeted tests and macros to preserve accuracy across the pipeline. This produces a consistent, production‑ready data model built for reliable dashboard reporting.

What’s next: customize or extend the package

You can adapt this dbt Facebook Ads package to match your reporting needs. Add custom macros and connect data from other sources. Test every change to keep your BigQuery Facebook ads data modeling accurate and stable.

Windsor.ai’s dbt Facebook Ads package provides a solid foundation. It’s built to adapt, not lock you in. You can further refine the models to match the exact requirements of your reports.

Write macros tailored to your KPIs. Merge Facebook Ads BigQuery dbt results with Google Ads, CRM, or other data sources. Keep naming patterns consistent to make models easy to maintain and update.

Track all changes in Git. Before deploying to production, test in a staging environment. This ensures the accuracy and reliability of the large BigQuery Facebook ads data modeling.

Conclusion

With BigQuery, building a clean Facebook Ads reporting layer with dbt means keeping control and consistency. It replaces manual SQL fixes with structured, reusable transformations. Every metric is calculated the same way, every time.

Windsor.ai delivers raw Facebook Ads data arrives in BigQuery correctly mapped and schema‑ready in just minutes. The dbt Facebook Ads package then handles transformations, currency conversions, and checks in a single automated run.

Data quality tests and macros ensure that metrics are trustworthy before they reach dashboards. It catches issues like clicks exceeding impressions or negative spend before they spread. Macros ensure that performance metrics are calculated consistently in every report.

For multi-market advertisers, built-in currency normalization ensures that spend and revenue remain consistent across all base currencies. This helps in campaign comparisons to avoid mismatched numbers across regions. Partitioned models in BigQuery reduce costs while delivering near‑real‑time reporting performance.

The result is a stable, production‑ready reporting layer that grows with your advertising data. Marketing and analytics teams spend less time cleaning up data and more time focusing on insights. 

With Windsor.ai providing clean inputs and dbt shaping the outputs, BigQuery stays a dependable source of truth for Facebook Ads performance.

🚀 Ready to streamline your Facebook Ads reporting in BigQuery?

Get started with Windsor.ai with a 30-day free trial to bring your Facebook Ads data into your BigQuery project, transform it with dbt, and unlock powerful insights.

FAQs

How does DBT improve Facebook Ads data modeling in BigQuery?

DBT transforms raw data into clean, modular SQL models, runs tests, and maintains consistency in transformations, ensuring accurate Facebook Ads reporting in BigQuery.

Why use BigQuery for Facebook Ads reporting?

BigQuery can process huge ad datasets without slowing down performance, run queries in seconds, and work smoothly with dbt to automate and scale Facebook Ads reporting.

What is included in Windsor’s DBT Facebook Ads package?

This package comes with staging, intermediate, and mart models, plus pre‑built metrics, audience insights, currency conversion, and validation tests tuned for optimized BigQuery performance.

How are data quality tests implemented in dbt?

In schema.yml, data quality tests make sure clicks don’t exceed impressions and spend isn’t negative before the data reaches reporting tables.

What do DBT macros do for Facebook Ads data?

Macros like calculate_performance_metrics and currency normalization handle calculations and currency conversions, ensuring metrics remain uniform across every reporting layer.

How does the package handle multi‑currency campaigns?

Built‑in currency conversion macros connect to exchange rate tables. They convert spend and revenue into a single base currency for accurate cross-market reports.

Can I customize Windsor’s DBT package?

You can change it according to your preferences and needs. Like swap in macros, add new models, or connect other data sources while keeping names and checks consistent.

What are the key pre‑built metrics in the package?

ROAS, CTR, CPC, CPM, and conversion rates are ready out of the box. That way, your reports stay consistent, and you don’t have to keep writing the same SQL again.

How is audience data modeled in BigQuery?

Audience metrics are broken down by age, gender, and location. You can filter reports without writing complex SQL joins.

How do I set up Windsor’s dbt BigQuery Facebook Ads package?

Link your Facebook Ads account in Windsor.ai, then push the data straight into BigQuery. Set up dbt_project.yml and run the dbt models.

Find step-by-step instructions in the repo: dbt-facebook-big_query.