Ad Reporting dbt Package (Docs)

Union multiple connectors

If you have multiple ad reporting connectors in Fivetran and would like to use this package on all of them simultaneously, we have provided functionality to do so. The package will union all of the data together and pass the unioned table into the transformations. You will be able to see which source it came from in the source_relation column of each model. To use this functionality, you will need to set either the <package_name>_union_schemas OR <package_name>_union_databases variables (cannot do both) in your root dbt_project.yml file. Below are the variables and examples for each connector:

vars:
    amazon_ads_union_schemas: ['amazon_ads_usa','amazon_ads_canada']
    amazon_ads_union_databases: ['amazon_ads_usa','amazon_ads_canada']

    apple_search_ads_union_schemas: ['apple_search_ads_usa','apple_search_ads_canada']
    apple_search_ads_union_databases: ['apple_search_ads_usa','apple_search_ads_canada']

    facebook_ads_union_schemas: ['facebook_ads_usa','facebook_ads_canada']
    facebook_ads_union_databases: ['facebook_ads_usa','facebook_ads_canada']

    google_ads_union_schemas: ['google_ads_usa','google_ads_canada']
    google_ads_union_databases: ['google_ads_usa','google_ads_canada']

    linkedin_ads_union_schemas: ['linkedin_usa','linkedin_canada']
    linkedin_ads_union_databases: ['linkedin_usa','linkedin_canada']

    microsoft_ads_union_schemas: ['microsoft_ads_usa','microsoft_ads_canada']
    microsoft_ads_union_databases: ['microsoft_ads_usa','microsoft_ads_canada']

    pinterest_ads_union_schemas: ['pinterest_usa','pinterest_canada']
    pinterest_ads_union_databases: ['pinterest_usa','pinterest_canada']

    reddit_ads_union_schemas: ['reddit_ads_usa','reddit_ads_canada']
    reddit_ads_union_databases: ['reddit_ads_usa','reddit_ads_canada']

    snapchat_ads_union_schemas: ['snapchat_ads_usa','snapchat_ads_canada']
    snapchat_ads_union_databases: ['snapchat_ads_usa','snapchat_ads_canada']

    tiktok_ads_union_schemas: ['tiktok_ads_usa','tiktok_ads_canada']
    tiktok_ads_union_databases: ['tiktok_ads_usa','tiktok_ads_canada']

    twitter_ads_union_schemas: ['twitter_usa','twitter_canada']
    twitter_ads_union_databases: ['twitter_usa','twitter_canada']

NOTE: The native source.yml connection set up in the package will not function when the union schema/database feature is utilized. Although the data will be correctly combined, you will not observe the sources linked to the package models in the Directed Acyclic Graph (DAG). This happens because the package includes only one defined source.yml.

To connect your multiple schema/database sources to the package models, follow the steps outlined in the Union Data Defined Sources Configuration section of the Fivetran Utils documentation for the union_data macro. This will ensure a proper configuration and correct visualization of connections in the DAG.

Configure types of conversion events

For the following platforms, conversion data is sent along with the type of event that you may or may not consider to be a conversion. For the most part, the packages consider leads, purchases, and custom user-defined events as conversions by default, but this can be configured via the appropriate variables.

For the other platforms, conversions are sent as a whole (though you may configure them in the platform UIs) and cannot be separated by event type in the connector data:

• Amazon Ads
• Apple Search Ads (does not include conversion_value)
• Google Ads
• Microsoft Advertising
• Pinterest Ads
• TikTok Ads

Adding custom metrics to final reports

By default, this package selects clicks, impressions, spend, conversions, and conversions_value metrics from the upstream Ad platform reports. Additionally, each specific upstream Ad platform package allows for custom passthrough metrics to be included in the individual platform's final reports. You can find a complete list of available passthrough metric variables for each platform by referring to the relevant links below and inspecting the additional configurations for each platform: - Amazon Ads - Apple Search Ads - Facebook Ads - Google Ads - LinkedIn Ad Analytics - Microsoft Advertising - Pinterest Ads - Reddit Ads - Snapchat Ads - TikTok Ads - Twitter Ads

Furthermore, this package allows you to include these configured upstream passthrough metrics in the final roll-up models of the combined Ad Reporting package. To include passthrough metrics in the respective final models, you need to define the following ad_reporting__* variables in your dbt_project.yml file:

vars:
  ad_reporting__account_passthrough_metrics:
    - name: view_through_conversions
  ad_reporting__campaign_passthrough_metrics: 
    - name: total_shares
  ad_reporting__ad_group_passthrough_metrics:
    - name: interactions
  ad_reporting__ad_passthrough_metrics: ## For both Ad and URL reports
    - name: video_views_captured
  ad_reporting__keyword_passthrough_metrics:
    - name: interactions
  ad_reporting__search_passthrough_metrics:
    - name: local_spend_amount

It is important to ensure that if you want to configure a passthrough metric for an ad reporting end model, that metric must be included in all of your upstream variables. Additionally, the name of the metric must be consistent across platforms. If a certain upstream platform does not include the metric you must include a transform_sql argument to pass a null value through (see below for examples). The following configuration is an example when using the Microsoft Ads, Apple Search Ads, Google Ads, Snapchat Ads, TikTok Ads, and Reddit Ads platforms within a dbt_project.yml file:

Note: While the below configuration is only for a subset of Ad platforms, the same strategy will be used for all other possible combinations of upstream Ad platform dependencies.

vars:
  ## Account Report Passthrough Metrics
  microsoft_ads__account_passthrough_metrics:
    - name: video_views_captured
      transform_sql: "null"
  apple_search_ads__campaign_passthrough_metrics:
    - name: video_views_captured
      transform_sql: "null"
  google_ads__account_stats_passthrough_metrics:
    - name: video_views
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  # snapchat_ads__ad_hourly_passthrough_metrics: # Defined below in the ad/url metrics therefore, not needed here but kept for documentation.
  #   - name: video_views
  #     alias: video_views_captured
  #     transform_sql: cast(video_views_captured as int64)
  tiktok_ads__ad_hourly_passthrough_metrics:
    - name: video_watched_2_s
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  reddit_ads__account_passthrough_metrics: # already includes view_through_conversions
    - name: video_watched_3_seconds
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  ad_reporting__account_passthrough_metrics:
    - name: video_views_captured

  ## Campaign Report Passthrough Metrics
  microsoft_ads__campaign_passthrough_metrics:
    - name: total_shares
      transform_sql: "null"
  google_ads__campaign_stats_passthrough_metrics:
    - name: total_shares
      transform_sql: cast(total_shares as int)
  snapchat_ads__campaign_hourly_report_passthrough_metrics:
    - name: shares
      alias: total_shares
  tiktok_ads__campaign_hourly_passthrough_metrics:
    - name: shares
      alias: total_shares
  reddit_ads__campaign_passthrough_metrics:
      transform_sql: "null"
    - name: total_shares
      transform_sql: "null"
  ad_reporting__campaign_passthrough_metrics: 
    - name: total_shares

  ## Ad Group Report Passthrough Metrics
  microsoft_ads__ad_group_passthrough_metrics:
    - name: phone_calls
      alias: interactions
  apple_search_ads__ad_group_passthrough_metrics:
    - name: new_downloads
      alias: interactions
  google_ads__ad_group_stats_passthrough_metrics:
    - name: interactions
  snapchat_ads__ad_squad_hourly_passthrough_metrics:
    - name: saves
      alias: interactions
  tiktok_ads__ad_group_hourly_passthrough_metrics:
    - name: likes
      alias: interactions
  reddit_ads__ad_group_passthrough_metrics:
    - name: video_started
      alias: interactions
  ad_reporting__ad_group_passthrough_metrics:
    - name: interactions

## Ad and URL Report Passthrough Metrics
  microsoft_ads__ad_passthrough_metrics:
    - name: video_views_captured
      transform_sql: "null"
  apple_search_ads__ad_passthrough_metrics:
    - name: video_views_captured
      transform_sql: "null"
  google_ads__ad_stats_passthrough_metrics:
    - name: video_views
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  snapchat_ads__ad_hourly_passthrough_metrics:
    - name: video_views
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  tiktok_ads__ad_hourly_passthrough_metrics:
    - name: video_watched_2_s
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  reddit_ads__ad_passthrough_metrics:
    - name: video_watched_3_seconds
      alias: video_views_captured
      transform_sql: cast(video_views_captured as int64)
  ad_reporting__ad_passthrough_metrics:
    - name: video_views_captured

  # Keyword Report Passthrough Metrics
  microsoft_ads__keyword_passthrough_metrics:
    - name: interactions
      transform_sql: "null"
  apple_search_ads__keyword_passthrough_metrics:
    - name: new_downloads
      alias: interactions
  google_ads__keyword_stats_passthrough_metrics:
    - name: interactions
  ad_reporting__keyword_passthrough_metrics:
    - name: interactions

  # Search Report Passthrough Metrics
  microsoft_ads__search_passthrough_metrics:
    - name: local_spend_amount
      transform_sql: "null"
  apple_search_ads__search_term_passthrough_metrics:
    - name: local_spend_amount
      transform_sql: "cast(local_spend_amount as int64)"
      transform_sql: "null"
  ad_reporting__search_passthrough_metrics:
    - name: local_spend_amount

Note: Please make sure to use due diligence when adding metrics to these models. The metrics added by default (clicks, impressions, spend, conversions, and conversions_value) have been vetted by the Fivetran team maintaining this package for accuracy. There are metrics included within the source reports, for example metric averages, which may be inaccurately represented at the grain for reports created in this package. You will want to ensure whichever metrics you pass through are indeed appropriate to aggregate at the respective reporting levels provided in this package.

Disabling null URL filtering from URL reports

The default behavior for the ad_reporting__url_report end model is to filter out records having null URL fields, however, you are able to turn off this filter if needed. To turn off the filter, include the below in your dbt_project.yml file. This variable will affect ALL Fivetran platform packages enabled in Ad Reporting, therefore either all URL reports will have null URLs filtered, or all URL reports will have null URLs included.

vars:
  ad_reporting__url_report__using_null_filter: False # Default is True.

Fivetran offers the ability for you to orchestrate your dbt project through Fivetran Transformations for dbt Core™. Learn how to set up your project for orchestration through Fivetran in our Transformations for dbt Core™ setup guides.

On top of the ad_reporting__ad_report final model, the Ad Reporting dbt package defines common Metrics using MetricFlow that can be queried with the dbt Semantic Layer. These metrics include:

• Spend
• Impressions
• Clicks
• Cost per click
• Clickthrough rate
• Bounce rate
• Count of active ads
• Average spend
• Average non-zero spend

You can find the supported dimensions and full definitions of these metrics here, and the semantic model definitions here.

Refer to the Semantic Layer quickstart guide for instructions on how to get setup with the dbt Semantic Layer and start querying these metrics.

Metricflow Time Spine Configuration This package includes a model called metricflow_time_spine.sql that MetricFlow requires to build cumulative metrics. Documentation on the metricflow time spine model can be found here. If you have already configured a metricflow time spine model in your project, you will need to disable the one in this package by defining the ad_reporting__metricflow_time_spine_enabled variable as false in your project.

## root dbt_project.yml
vars:
  ad_reporting__metricflow_time_spine_enabled: false ## true by default

Additionally, the dbt_date.get_base_dates macro is used in the generation of the metricsflow_time_spine.sql model. This macro requires the dbt_date:time_zone variable to be defined in the project to generate a time spine based on the defined time zone. The default value in this package is America/Los_Angeles. However, you may override this variable in your own project if you wish.

Note: This variable is defined under the ad_reporting hierarchy within this package and should not adjust any local global variable values in your project if you already have this variable defined. For more information on why this variable is needed and the different value options, refer to the dbt-date package documentation.

## root dbt_project.yml
vars:
  "dbt_date:time_zone": "America/Chicago" # Default is "America/Los_Angeles"

Semantic Manifest You may notice a new run artifact called semantic_manifest.json. This file serves as the integation point between dbt-core and metricflow, and contains all the information MetricFlow needs to build a semantic graph, and generate SQL from query requests. You can learn more about the semantic manifest file in the docs.

Note: Metricflow is only supported in dbt>=v1.6.0, therefore, please take note of the correct dbt version.