Facebook Ads dbt Package

This dbt package transforms data from Fivetran's Facebook Ads connector into analytics-ready tables.

Resources

Number of materialized models¹: 34
Connector documentation
- Facebook Ads connector documentation
- Facebook Ads ERD
dbt package documentation

What does this dbt package do?

This package enables you to better understand the performance of your ads across varying grains and produces modeled tables that leverage Facebook Ads data. It creates enriched models with metrics focused on account, campaign, ad set, ad, and geographic reporting.

Note: If you do not have your Facebook Ads connection schema change settings set to Allow all, it is very possible that you are missing basic_ad_actions or basic_ad_action_values. If you would like to surface conversion metrics in your Facebook and/or Ad Reporting models, please ensure these reports are syncing. Otherwise, the conversions and conversions_value fields will be null.

Output schema

Final output tables are generated in the following target schema:

<your_database>.<connector/schema_name>_facebook_ads

Final output tables

By default, this package materializes the following final tables:

Table	Description
`facebook_ads__account_report`	Represents daily performance aggregated at the account level, including `spend`, `clicks`, `impressions`, and `conversions`. Example Analytics Questions: How does performance compare across different accounts by account manager? Are currency fluctuations affecting results across markets?
`facebook_ads__campaign_report`	Represents daily performance aggregated at the campaign level, including `spend`, `clicks`, `impressions`, and `conversions`. Example Analytics Questions: Which campaigns are most efficient in terms of cost per conversion? Are paused or limited-status campaigns still accruing impressions? Which campaigns contribute most to overall spend or conversions?
`facebook_ads__ad_set_report`	Represents daily performance aggregated at the ad set level (equivalent to ad groups in other platforms), including `spend`, `clicks`, `impressions`, and `conversions`. Example Analytics Questions: Which ad sets have the strongest engagement relative to their budget? Do certain ad sets dominate impressions within a campaign? Are new ad sets ramping up as expected after launch?
`facebook_ads__ad_report`	Represents daily performance at the individual ad level, including `spend`, `clicks`, `impressions`, and `conversions`. Example Analytics Questions: Which ad creatives are driving the lowest cost per click? Do expanded text ads perform better than responsive search ads? How do performance trends change after refreshing ad copy?
`facebook_ads__country_report`	Represents daily performance aggregated at the account level by country, including `spend`, `clicks`, `impressions`, and `conversions`, enriched with geographic context. Example Analytics Questions: Which countries are delivering the highest return on ad spend for each account? Are there seasonal performance variations by geographic region?
`facebook_ads__region_report`	Represents daily performance aggregated at the account level by region, including `spend`, `clicks`, `impressions`, and `conversions`, enriched with geographic context. Example Analytics Questions: Which regions are driving the most efficient account performance? How do regional performance trends correlate with local market conditions?
`facebook_ads__url_report`	Represents daily performance at the individual URL level, including `spend`, `clicks`, `impressions`, and `conversions`, enriched with ad context. By default, excludes ads with NULL `url` values. Example Analytics Questions: Which landing pages are driving the highest conversion rates? Are certain URLs performing better with specific ad creative combinations?
`facebook_ads__url_tags`	Represents unique combinations of creative_id and corresponding URL tag key-value pairs, providing UTM and custom parameter tracking. Excludes creatives without URL tags. Example Analytics Questions: Which UTM campaigns are driving the most traffic across different creatives? How do custom URL parameters correlate with conversion performance?

¹ Each Quickstart transformation job run materializes these models if all components of this data model are enabled. This count includes all staging, intermediate, and final models materialized as view, table, or incremental.

Visualizations

Many of the above reports are now configurable for visualization via Streamlit. Check out some sample reports here.

Prerequisites

To use this dbt package, you must have the following:

At least one Fivetran Facebook Ads connection syncing data into your destination.
A BigQuery, Snowflake, Redshift, PostgreSQL, or Databricks destination.
You will need to configure your Facebook Ads connection to pull the basic_ad pre-built report and its child basic_ad_actions and basic_ad_action_values pre-built reports. These pre-built reports should be enabled in your connection by default. However, to confirm these reports are actively syncing you may perform the following steps:
1. Navigate to the connection schema tab in Fivetran.
2. Search for basic_ad, basic_ad_actions, and basic_ad_action_values and confirm they are all selected/enabled. If you would like country and region-level transformations, do the same for demographics_country, demographics_country_actions, demographics_region, and demographics_region_actions.
3. If not selected, do so and sync. If already selected you are ready to run the models!

How do I use the dbt package?

You can either add this dbt package in the Fivetran dashboard or import it into your dbt project:

To add the package in the Fivetran dashboard, follow our Quickstart guide.
To add the package to your dbt project, follow the setup instructions in the dbt package's README file to use this package.

How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions.

We highly encourage and welcome contributions to this package. Learn how to contribute to a package in dbt's Contributing to an external dbt package article.

Contributors

We thank everyone who has taken the time to contribute. Each PR, bug report, and feature request has made this package better and is truly appreciated.

A special thank you to Seer Interactive, who we closely collaborated with to introduce native conversion support to our Ad packages.