# Ad Reporting dbt Package

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

## Resources

- Number of materialized models¹: 10
- Connector documentation
  - [Amazon Ads](https://fivetran.com/docs/applications/amazon-ads)
  - [Apple Search Ads](https://fivetran.com/docs/applications/apple-search-ads)
  - [Facebook Ads](https://fivetran.com/docs/applications/facebook-ads)
  - [Google Ads](https://fivetran.com/docs/applications/google-ads)
  - [LinkedIn Ad Analytics](https://fivetran.com/docs/applications/linkedin-ads)
  - [Microsoft Advertising](https://fivetran.com/docs/applications/microsoft-advertising)
  - [Pinterest Ads](https://fivetran.com/docs/applications/pinterest-ads)
  - [Reddit Ads](https://fivetran.com/docs/applications/reddit-ads)
  - [Snapchat Ads](https://fivetran.com/docs/applications/snapchat-ads)
  - [TikTok Ads](https://fivetran.com/docs/applications/tiktok-ads)
  - [Twitter Ads](https://fivetran.com/docs/applications/twitter-ads)
- dbt package documentation
  - [GitHub repository](https://github.com/fivetran/dbt_ad_reporting)
  - [dbt Docs](https://fivetran.github.io/dbt_ad_reporting/#!/overview)
  - [DAG](https://fivetran.github.io/dbt_ad_reporting/#!/overview?g_v=1)
  - [Changelog](https://github.com/fivetran/dbt_ad_reporting/blob/main/CHANGELOG.md)
- dbt Core™ supported versions
  - `>=1.3.0, <3.0.0`

## What does this dbt package do?

This package enables you to standardize schemas from various ad platform connectors and create reporting models for clicks, spend and impressions aggregated to the account, campaign, ad group, ad, keyword and search levels. It creates enriched models with metrics focused on cross-platform advertising performance analysis.

Currently supports the following Fivetran ad platform connectors:

- [Amazon Ads](https://github.com/fivetran/dbt_amazon_ads)
- [Apple Search Ads](https://github.com/fivetran/dbt_apple_search_ads)
- [Facebook Ads](https://github.com/fivetran/dbt_facebook_ads)
- [Google Ads](https://github.com/fivetran/dbt_google_ads)
- [LinkedIn Ad Analytics](https://github.com/fivetran/dbt_linkedin)
- [Microsoft Advertising](https://github.com/fivetran/dbt_microsoft_ads)
- [Pinterest Ads](https://github.com/fivetran/dbt_pinterest)
- [Reddit Ads](https://github.com/fivetran/dbt_reddit_ads)
- [Snapchat Ads](https://github.com/fivetran/dbt_snapchat_ads)
- [TikTok Ads](https://github.com/fivetran/dbt_tiktok_ads)
- [Twitter Ads](https://github.com/fivetran/dbt_twitter)

> NOTE: You do _not_ need to have all of these connector types to use this package, though you should have at least two. Additionally, individual platform models may include platform-specific metrics and fields that are better suited for deeper, platform-level analysis.

### Output schema

Final output tables are generated in the following target schema:

```terraform
<your_database>.<connector/schema_name>_ad_reporting

```

### Final output tables

By default, this package materializes the following final tables:

| Table | Description |
| --- | --- |
| [`ad_reporting__account_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__account_report) | Represents daily performance aggregated at the account level across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- How does cross-platform performance compare across different accounts and platforms? - Which advertising platforms are delivering the best ROI by account? - Are there seasonal performance patterns consistent across platforms? |
| [`ad_reporting__campaign_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__campaign_report) | Represents daily performance aggregated at the campaign level across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- Which campaigns are most efficient across different advertising platforms? - How do similar campaign types perform across Google, Facebook, LinkedIn, etc.? - Are there cross-platform budget allocation opportunities? |
| [`ad_reporting__ad_group_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__ad_group_report) | Represents daily performance aggregated at the ad group level across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- Which ad group strategies work best across different platforms? - Do certain audience segments perform consistently across platforms? - How should budget be reallocated between platforms for similar ad groups? |
| [`ad_reporting__ad_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__ad_report) | Represents daily performance at the individual ad level across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- Which creative formats perform best across different advertising platforms? - How do similar ad creatives perform on different platforms? - What creative elements drive success across multiple platforms? |
| [`ad_reporting__keyword_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__keyword_report) | Represents daily performance at the individual keyword level across search advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- Which keywords drive the best performance across Google, Microsoft, and other search platforms? - How do keyword match types perform differently across platforms? - Are there keyword expansion opportunities across platforms? |
| [`ad_reporting__search_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__search_report) | Represents daily performance at the individual search query level across search advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- What search queries convert best across different search platforms? - Which search terms should be added as keywords across multiple platforms? - How do search query patterns differ between platforms? |
| [`ad_reporting__url_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__url_report) | Represents daily performance at the individual URL level across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`, with UTM parameter tracking. **Example Analytics Questions:**- Which landing pages perform best across different advertising platforms? - How do UTM campaign parameters correlate with performance across platforms? - Which URLs should be promoted more heavily across multiple platforms? |
| [`ad_reporting__monthly_campaign_country_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__monthly_campaign_country_report) | Represents monthly performance aggregated at the campaign level by country across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`, with standardized ISO-3166 country names. **Example Analytics Questions:**- Which countries deliver the best cross-platform advertising performance? - How do geographic performance patterns vary between advertising platforms? - Are there country-specific platform preferences for ad performance? |
| [`ad_reporting__monthly_campaign_region_report`](https://fivetran.github.io/dbt_ad_reporting/#!/model/model.ad_reporting.ad_reporting__monthly_campaign_region_report) | Represents monthly performance aggregated at the campaign level by region across all advertising platforms, including `spend`, `clicks`, `impressions`, and `conversions`. **Example Analytics Questions:**- Which regions show the strongest cross-platform advertising performance? - How do regional preferences vary between different advertising platforms? - Are there regional opportunities for cross-platform budget optimization? |

¹ 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](https://github.com/fivetran/streamlit_ad_reporting). Check out some [sample reports here](https://fivetran-ad-reporting.streamlit.app/ad_performance).

[![Fivetran Ad Reporting Streamlit App](https://raw.githubusercontent.com/fivetran/dbt_ad_reporting/main/images/streamlit_example.png)](https://fivetran-ad-reporting.streamlit.app/ad_performance)

### Materialized Models

Each Quickstart transformation job run materializes the following model counts for each selected connector. The total model count represents all staging, intermediate, and final models, materialized as `view`, `table`, or `incremental`:

| **Connector** | **Model Count** |
| --- | --- |
| Ad Reporting | 10 |
| [Amazon Ads](https://github.com/fivetran/dbt_amazon_ads) | 30 |
| [Apple Search Ads](https://github.com/fivetran/dbt_apple_search_ads) | 26 |
| [Facebook Ads](https://github.com/fivetran/dbt_facebook_ads) | 34 |
| [Google Ads](https://github.com/fivetran/dbt_google_ads) | 29 |
| [LinkedIn Ad Analytics](https://github.com/fivetran/dbt_linkedin) | 25 |
| [Microsoft Advertising](https://github.com/fivetran/dbt_microsoft_ads) | 33 |
| [Pinterest Ads](https://github.com/fivetran/dbt_pinterest) | 34 |
| [Reddit Ads](https://github.com/fivetran/dbt_reddit_ads) | 34 |
| [Snapchat Ads](https://github.com/fivetran/dbt_snapchat_ads) | 29 |
| [TikTok Ads](https://github.com/fivetran/dbt_tiktok_ads) | 28 |
| [Twitter Ads](https://github.com/fivetran/dbt_twitter) | 32 |

## Timezone Considerations

Timezone differences across ad platforms impact standardization due to pre-aggregated data and non-standard timezones. See the [Decision Log - Timezone Considerations](https://github.com/fivetran/dbt_ad_reporting/blob/main/DECISIONLOG.md#timezone-considerations) for details.

## Prerequisites

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

- At least one of the below supported Fivetran ad platform connections syncing data into your destination. This package currently supports:
  - [Amazon Ads](https://fivetran.com/docs/applications/amazon-ads)
  - [Apple Search Ads](https://fivetran.com/docs/applications/apple-search-ads)
  - [Facebook Ads](https://fivetran.com/docs/applications/facebook-ads)
  - [Google Ads](https://fivetran.com/docs/applications/google-ads)
  - [LinkedIn Ad Analytics](https://fivetran.com/docs/applications/linkedin-ads)
  - [Microsoft Advertising](https://fivetran.com/docs/applications/microsoft-advertising)
  - [Pinterest Ads](https://fivetran.com/docs/applications/pinterest-ads)
  - [Reddit Ads](https://fivetran.com/docs/applications/reddit-ads)
  - [Snapchat Ads](https://fivetran.com/docs/applications/snapchat-ads)
  - [TikTok Ads](https://fivetran.com/docs/applications/tiktok-ads)
  - [Twitter Ads](https://fivetran.com/docs/applications/twitter-ads)

> While you need only one of the above connectors to utilize this package, we recommend having at least two to gain the rollup benefit of this package.

- A **BigQuery**, **Snowflake**, **Redshift**, **PostgreSQL**, **Databricks**, or **DuckDB** destination.

## 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](https://fivetran.com/docs/transformations/data-models/quickstart-management#quickstartmanagement).
- To add the package to your dbt project, follow the setup instructions in the dbt package's [README file](https://github.com/fivetran/dbt_ad_reporting/blob/main/README.md#how-do-i-use-the-dbt-package) 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](https://hub.getdbt.com/fivetran/ad_reporting/latest/) of the package. We highly recommend you stay consistent with the latest version of the package and refer to the [CHANGELOG](https://github.com/fivetran/dbt_ad_reporting/blob/main/CHANGELOG.md) and release notes for more information on changes across versions.

### Opinionated Decisions

In creating this package, which is meant for a wide range of use cases, we had to take opinionated stances on a few different questions we came across during development. We've consolidated significant choices we made in the [DECISIONLOG.md](https://github.com/fivetran/dbt_ad_reporting/blob/main/DECISIONLOG.md), and will continue to update as the package evolves. We are always open to and encourage feedback on these choices, and the package in general.

### 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](https://discourse.getdbt.com/t/contributing-to-a-dbt-package/657).

#### Contributors

We thank [everyone](https://github.com/fivetran/dbt_ad_reporting/graphs/contributors) 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](https://www.seerinteractive.com/?utm_campaign=Fivetran%20%7C%20Models&utm_source=Fivetran&utm_medium=Fivetran%20Documentation), who we closely collaborated with to introduce native conversion support to our Ad packages.