Ad Reporting

This dbt package aggregates and models data from multiple Fivetran advertising connectors. The package standardizes the schemas from the various ad connectors and creates a single reporting model for all activity. It enables you to analyze your daily ad spend, clicks, and impressions by campaigns, ad groups, and UTM parameters.

Currently, this package supports the following ad connector types:

NOTE: You do not need to have all of these connector types to use this package, though you should have at least two.

Models

This package contains a number of models, which all build up to the final ad_reporting model. The ad_reporting model combines the data from all of the connectors. A dependency on all the required dbt packages is declared in this package's packages.yml file, so it will automatically download them when you run dbt deps. The primary outputs of this package are described below.

model	description
ad_reporting	Each record represents the daily ad performance from all connectors, including information about the UTM parameters you used.

Installation Instructions

Check dbt Hub for the latest installation instructions, or read the dbt docs for more information on installing packages.

Include in your packages.yml

packages:
  - package: fivetran/ad_reporting
    version: [">=0.8.0", "<0.9.0"]

Configuration

Connector selection

The package assumes that all connector models are enabled, so it will look to pull data from all of the connectors listed above. If you don't want to use certain connectors, disable those connectors' models in this package by setting the relevant variables to false:

# dbt_project.yml

...
config-version: 2

vars:
  ad_reporting__pinterest_enabled: False
  ad_reporting__microsoft_ads_enabled: False
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__google_ads_enabled: False
  ad_reporting__twitter_ads_enabled: False
  ad_reporting__facebook_ads_enabled: False
  ad_reporting__snapchat_ads_enabled: False
  ad_reporting__tiktok_ads_enabled: False

Next, you must disable the models in the unwanted connector's related package, which has its own configuration. Disable the relevant models under the models section of your dbt_project.yml file by setting the enabled value to false.

Only include the models you want to disable. Default values are generally true but that is not always the case.

models:
  # disable both pinterest models if not using pinterest ads
  pinterest:
    enabled: false
  pinterest_source:
    enabled: false
  
  # disable both microsoft ads models if not using microsoft ads
  microsoft_ads:
    enabled: false
  microsoft_ads_source:
    enabled: false
  
  # disable both linkedin ads models if not using linkedin ads
  linkedin:
    enabled: false
  linkedin_source:
    enabled: false
  
  # disable both twitter ads models if not using twitter ads
  twitter_ads:
    enabled: false
  twitter_ads_source:
    enabled: false
  
  # disable all three facebook ads models if not using facebook ads
  facebook_ads:
    enabled: false #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  facebook_ads_source:
    enabled: false #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  facebook_ads_creative_history:
    enabled: false #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  
  # disable both google ads models if not using google ads
  google_ads:
    enabled: false #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  google_ads_source:
    enabled: false #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  
  # disable both snapchat ads models if not using snapchat ads
  snapchat_ads:
    enabled: false
  snapchat_ads_source:
    enabled: false
  
  # disable both tiktok ads models if not using tiktok ads
  tiktok_ads:
    enabled: false
  tiktok_ads_source:
    enabled: false

Google Ads and Adwords API Configuration

This package allows users to leverage either the Adwords API or the Google Ads API. You will be able to determine which API your connector is using by navigating within your Fivetran UI to the setup tab -> edit connection details link -> and reference the API configuration used. You will want to refer to the respective configuration steps below based off the API used by your connector. You may set the api type by using the below config in your root dbt_project.yml.

Note: As of April, 2022 all Fivetran Google Ads connectors leverage the Google Ads API rather than Adwords. Additionally, please be aware that the Adwords API version of the package will be sunset in August of 2022.

# dbt_project.yml

...
config-version: 2

vars:
  api_source: google_ads  ## google_ads by default, but may be changed to 'adwords' if using a previous version of the connector.

Adwords API

If your Google Ads connector is setup using the Adwords API (no longer default) then you will want to follow the steps outlined in the dbt_google_ads package for configuring your package to leverage the adwords API.

Data Location

By default, this package looks for your advertising data in your target database. If this is not where your advertising data is stored, add the relevant _database variables to your dbt_project.yml file (see below).

By default, this package also looks for specific schemas from each of your connectors. The schemas from each connector are highlighted in the code snippet below. If your data is stored in a different schema, add the relevant _schema variables to your dbt_project.yml file:

# dbt_project.yml

...
config-version: 2

vars:
    microsoft_ads_schema: bingads
    microsoft_ads_database: your_database_name
    linkedin_schema: linkedin_ads 
    linkedin_database: your_database_name  
    pinterest_schema: pinterest
    pinterest_database: your_database_name 
    twitter_ads_schema: twitter_ads
    twitter_ads_database: your_database_name  
    facebook_ads_schema: facebook_ads
    facebook_ads_database: your_database_name 
    google_ads_schema: adwords
    google_ads_database: your_database_name 
    snapchat_schema: snapchat_ads
    snapchat_database: your_database_name 
    tiktok_ads_schema: tiktok_ads
    tiktok_ads_database: your_database_name

For more configuration information, see the relevant connectors (listed above).

Changing the Build Schema (RECOMMENDED)

By default this package will build all models in your <target_schema>. This behavior can be tailored to your preference by making use of custom schemas. We highly recommend use of custom schemas for this package, as multiple sources are involved. To do so, add the following configuration to your dbt_project.yml file:

# dbt_project.yml

...
models:  
  ad_reporting:
    +schema: ad_reporting
  pinterest:
    +schema: pinterest
  pinterest_source:
    +schema: pinterest_source
  
  microsoft_ads:
    +schema: microsoft_ads
  microsoft_ads_source:
    +schema: microsoft_ads_source
  
  linkedin:
    +schema: linkedin
  linkedin_source:
    +schema: linkedin_source
  
  twitter_ads:
    +schema: twitter_ads
  twitter_ads_source:
    +schema: twitter_ads_source

  facebook_ads:
    +schema: facebook_ads
  facebook_ads_source:
    +schema: facebook_ads_source
  facebook_ads_creative_history:
    +schema: facebook_ads
  
  google_ads:
    +schema: google_ads
  google_ads_source:
    +schema: google_ads_source
  
  snapchat_ads:
    +schema: snapchat_ads
  snapchat_ads_source:
    +schema: snapchat_ads_source
  
  tiktok_ads:
    +schema: tiktok_ads
  tiktok_ads_source:
    +schema: tiktok_ads_source

Database Support

This package has been tested on BigQuery, Snowflake, Redshift, Postgres, and Databricks.

Databricks Dispatch Configuration

dbt v0.20.0 introduced a new project-level dispatch configuration that enables an "override" setting for all dispatched macros. If you are using a Databricks destination with this package you will need to add the below (or a variation of the below) dispatch configuration within your dbt_project.yml. This is required in order for the package to accurately search for macros within the dbt-labs/spark_utils then the dbt-labs/dbt_utils packages respectively.

# dbt_project.yml

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['spark_utils', 'dbt_utils']

Contributions

Additional contributions to this package are very welcome! Please create issues or open PRs against main. Check out this post on the best workflow for contributing to a package.

Resources:

Provide feedback on our existing dbt packages or what you'd like to see next
Have questions, feedback, or need help? Book a time during our office hours using Calendly or email us at [email protected]
Find all of Fivetran's pre-built dbt packages in our dbt hub
Learn how to orchestrate your models with Fivetran Transformations for dbt Core™
Learn more about Fivetran overall in our docs
Check out Fivetran's blog
Learn more about dbt in the dbt docs
Check out Discourse for commonly asked questions and answers
Join the chat on Slack for live discussions and support
Find dbt events near you
Check out the dbt blog for the latest news on dbt's development and best practices

dbt_ad_reporting
dbt_ad_reporting copied to clipboard

Metadata

Ad Reporting

Models

Installation Instructions

Configuration

Connector selection

Google Ads and Adwords API Configuration

Adwords API

Data Location

Changing the Build Schema (RECOMMENDED)

Database Support

Databricks Dispatch Configuration

Contributions

Resources:

← Metadata

Owner

Metadata

dbt_ad_reporting dbt_ad_reporting copied to clipboard

Metadata

Ad Reporting

Models

Installation Instructions

Configuration

Connector selection

Google Ads and Adwords API Configuration

Adwords API

Data Location

Changing the Build Schema (RECOMMENDED)

Database Support

Databricks Dispatch Configuration

Contributions

Resources:

← Metadata

Owner

Metadata

dbt_ad_reporting
dbt_ad_reporting copied to clipboard