DOCSEARCHbyAlgolia
Submit Docs

Fivetran Ad Reporting dbt Package

repository·main·Indexed 42 days ago

https://github.com/fivetran/dbt_ad_reporting

A dbt package that standardizes and aggregates advertising data from multiple Fivetran connectors into unified reporting models. It enables cross-platform performance analysis of metrics like spend, clicks, impressions, and conversions across platforms including Google Ads, Facebook Ads, Amazon Ads, LinkedIn, and others. Compatible with dbt Core™ >=1.3.0, <3.0.0 and supported destinations such as Snowflake, BigQuery, Redshift, PostgreSQL, Databricks, and DuckDB.

Tokens
5.6K
Snippets
11
Records
20
Agent score
3%

What's inside Fivetran Ad Reporting

  1. Overview of the Ad Reporting dbt Package

    main

    The dbt_ad_reporting package transforms data from Fivetran's Ad Reporting connectors into standardized, analytics-ready tables. It enables cross-platform advertising performance analysis by aggregating metrics like spend, clicks, impressions, and conversions across various levels (account, campaign, ad group, ad, keyword, etc.).

    Key Features:

    • Standardizes schemas from multiple ad platform connectors.
    • Creates enriched models for cross-platform comparison.
    • Supports a wide range of connectors including Amazon Ads, Facebook Ads, Google Ads, LinkedIn, etc.

    Requirements:

    • dbt Core™ version: >=1.3.0, <3.0.0
    • Connectors: While you don't need all connectors, it is recommended to have at least two to leverage the cross-platform standardization features.

  2. Understand the Ad Reporting hierarchy and mapping

    main

    The package follows a standard ad reporting hierarchy: Account > Campaign > Ad Group > Ad / Search / Keyword / URL.

    Key Mapping Details:

    • Reporting Level: To ensure compatibility across all platforms, URL reports are provided at the Ad Group level.
    • Linkedin Mapping: Linkedin uses different terminology that is mapped to the standard model:
      • Linkedin campaign group $\rightarrow$ standard campaign
      • Linkedin campaign $\rightarrow$ standard ad group

  3. Understand the grain of monthly country and region reports

    main

    The models ad_reporting__monthly_campaign_country_report and ad_reporting__monthly_campaign_region_report provide data at a monthly grain.

    This monthly grain is used to maintain compatibility with Linkedin Ads, which only provides geographic performance data by month. Consequently, data from other platforms (like TikTok or Microsoft Ads) is rolled up from a daily grain to a monthly grain in these specific models.

    If you require daily geographic reports, you should use the platform-specific upstream models (e.g., tiktok_ads__campaign_country_report) instead of the unified ad_reporting monthly models.

  4. Use the dbt Semantic Layer with Ad Reporting

    main

    The package defines common metrics using MetricFlow (e.g., Spend, Impressions, Clicks, CTR, etc.) that can be queried via the dbt Semantic Layer.

    Metricflow Time Spine Configuration:

    • The package includes a metricflow_time_spine.sql model. If you already have a time spine in your project, disable this one using ad_reporting__metricflow_time_spine_enabled: false.
    • The time spine is generated using the dbt_date:time_zone variable. The default is America/Los_Angeles. You can override this in your dbt_project.yml.

    Disabling Semantic Models: To disable all semantic layer resources from the package, use the following configuration:

    semantic-models:
  ad_reporting:
    +enabled: false

metrics:
  ad_reporting:
    +enabled: false
    ## root dbt_project.yml
vars:
  ad_reporting__metricflow_time_spine_enabled: false ## True by default

  "dbt_date:time_zone": "America/New_York" # Overriding default America/Los_Angeles

  5. How Facebook Ads geographic data is represented

    main

    In the ad_reporting__monthly_campaign_country_report and ad_reporting__monthly_campaign_region_report models, Facebook Ads data is presented at the account level because geographic performance data is not available at the campaign level for Facebook.

    To distinguish this from other platforms, Facebook Ads records in these models use:

    • campaign_name: set to Account-level
    • campaign_id: set to null

  6. Understand platform timezone behaviors

    main

    Timezone handling varies by platform because many ad platforms provide pre-aggregated data that cannot be back-calculated to finer granularities. To achieve standardization across your reporting, it is recommended to configure all Ad accounts and Fivetran connectors to UTC whenever possible.

    Platform Timezone Reference

    PlatformGrain of DataTimezone
    Amazon AdsDayCLIENT SET in Amazon Ads
    Apple Search AdsDayCLIENT SET in Fivetran Connector (UTC or account timezone)
    Facebook AdsDayCLIENT SET in Facebook Ads
    Google AdsDay (package uses daily)CLIENT SET in Google Ads
    Linkedin Ad AnalyticsDayUTC
    Microsoft AdvertisingDay (package uses daily)CLIENT SET in Microsoft Ads, UTC if not set
    Pinterest AdsDay (package uses daily)UTC
    Reddit AdsDayUTC
    Snapchat AdsHourUTC
    TikTok AdsHourUTC
    Twitter AdsDayCLIENT SET in Twitter Ads

  7. Standardized country and region names

    main
    In the ad_reporting__monthly_campaign_country_report model, country names are standardized to align with official ISO-3166 names. Additionally, the package leverages global region names (e.g., "Southern Asia") sourced from the lukes/ISO-3166-Countries-with-Regional-Codes repository.

  8. Prerequisites for the Ad Reporting dbt package

    main

    Before using this dbt package, ensure you meet the following requirements:

    1. Supported Fivetran Ad Platform Connections

    You must have at least one of the following Fivetran connectors syncing data into your destination. While the package works with a single connector, it is recommended to have at least two to leverage the rollup capabilities.

    Supported platforms:

    • Amazon Ads
    • Apple Search Ads
    • Facebook Ads
    • Google Ads
    • LinkedIn Ad Analytics
    • Microsoft Advertising
    • Pinterest Ads
    • Reddit Ads
    • Snapchat Ads
    • TikTok Ads
    • Twitter Ads

    2. Supported Destinations

    The package is compatible with the following data warehouses:

    • BigQuery
    • Snowflake
    • Redshift
    • PostgreSQL
    • Databricks
    • DuckDB

  10. Install the Ad Reporting dbt package

    main

    To use this package in your dbt project, add it to your packages.yml file. It is recommended to use a version range to automatically capture non-breaking changes.

    Note: Do not manually include individual ad platform packages (e.g., google_ads_source); the ad_reporting package manages these dependencies automatically.

    packages:
  - package: fivetran/ad_reporting
    version: [">=2.7.0", "<2.8.0"]

  11. Configure platform-specific report features

    main

    Many platforms allow you to enable or disable specific sub-reports (like search terms, geo-targeting, or demographics) via variables in dbt_project.yml.

    Examples of available toggles:

    • Apple Search Ads: apple_search_ads__using_search_terms (Default: True)
    • Facebook Ads: facebook_ads__using_demographics_country and facebook_ads__using_demographics_region (Default: True)
    • Google Ads: google_ads__using_search_term_keyword_stats (Default: True)
    • LinkedIn Ads: linkedin_ads__using_geo, linkedin_ads__using_monthly_ad_analytics_by_member_country, and linkedin_ads__using_monthly_ad_analytics_by_member_region (Default: True)
    • TikTok Ads: tiktok_ads__using_campaign_country_report (Default: True)

    Note: Default behaviors vary by platform. Check the specific platform documentation for exact defaults.

    vars:
  # Apple Search Ads
  apple_search_ads__using_search_terms: False 

  # Facebook Ads
  facebook_ads__using_demographics_country: True 
  facebook_ads__using_demographics_region: True 

  # Google Ads
  google_ads__using_search_term_keyword_stats: False 

  # Linkedin Ads
  linkedin_ads__using_geo: False 
  linkedin_ads__using_monthly_ad_analytics_by_member_country: False 
  linkedin_ads__using_monthly_ad_analytics_by_member_region: False 

  # Microsoft Ads
  microsoft_ads__using_geographic_daily_report: True 

  # Pinterest Ads
  pinterest__using_keywords: False 
  pinterest__using_pin_promotion_targeting_report: False 
  pinterest__using_targeting_geo: False 
  pinterest__using_targeting_geo_region: False 

  # Reddit Ads
  reddit_ads__using_business_account: false 
  reddit_ads__using_campaign_country_report: false 
  reddit_ads__using_campaign_country_conversions_report: false 

  # Snapchat Ads
  snapchat_ads__using_campaign_country_report: true 
  snapchat_ads__using_campaign_region_report: true 

  # TikTok Ads
  tiktok_ads__using_campaign_country_report: False 

  # Twitter Ads
  twitter_ads__using_keywords: False 
  twitter_ads__using_campaign_locations_report: True 
  twitter_ads__using_campaign_regions_report: True

  12. Override default build schemas

    main

    By default, the package builds models in your <target_schema> with specific suffixes (e.g., ad_reporting, amazon_ads, google_ads_source). You can override these naming conventions in dbt_project.yml using the +schema config. To write directly to the target_schema without any suffix, provide a blank +schema: .

    models:  
  ad_reporting:
    +schema: ad_reporting # default schema suffix

  amazon_search_ads:
    +schema: amazon_ads # default schema suffix
    staging:
      +schema: amazon_ads_source # default schema suffix

  # ... and so on for other platforms