There is a simple and effective way to ingest and transform ad reporting data from multiple platforms by using Fivetran and dbt in tandem. Fivetran is used to ingest the data from a variety of platforms including Apple Search, Facebook, Google, LinkedIn, Microsoft, Pinterest, Snapchat, TikTok and Twitter. Then a dbt model created by the Fivetran team is installed and configured to transform the data for applicable platforms. The process is simple but effective. It enables businesses to efficiently transform raw data into a digestible state that is all housed in a single standardized view for consumption.

Prior to installing this package in dbt there are some prerequisites that must be met:

• You have at least two connectors setup in Fivetran for the platforms listed above.
• Your database is hosted by Snowflake, BigQuery, Redshift, Postgres or Databricks.
• dbt Version falls within the range of >=1.0.0, <2.0.0.

Once the prerequisites have been met, then it is time to install the Fivetran ad reporting package. The two-step process is as follows:

• Include the following in your yml file:

  packages:
    - package: fivetran/ad_reporting
      version: 1.0.4

   

• Run dbt deps to pull the most recent list of dependencies in your yml file.

With the package installed, the configuration is as simple as flagging which models to enable then mapping the database and schema variables to the location of the raw ad reporting data. The work is done within the dbt_project.yml file and is outlined below.

• Set the variable’s value to True to enable a model. Set the variable’s value to False to disable a model (see below):

vars:
  ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
  ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
  ad_reporting__microsoft_ads_enabled: False # by default this is assumed to be True
  ad_reporting__linkedin_ads_enabled: False # by default this is assumed to be True
  ad_reporting__google_ads_enabled: False # by default this is assumed to be True
  ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
  ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True
  ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
  ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True

NOTE: The ad reporting package can be updated with new platforms at any time. Just create a new branch in dbt and follow the process from here.

• Set the variable’s schema and database to the location of the raw ad reporting data (see below).

vars:
    apple_search_ads_schema: apple_search_ads
    apple_search_ads_database: your_database_name

    facebook_ads_schema: facebook_ads
    facebook_ads_database: your_database_name 

    google_ads_schema: google_ads
    google_ads_database: your_database_name 

    microsoft_ads_schema: bingads
    microsoft_ads_database: your_database_name

    linkedin_ads_schema: linkedin_ads 
    linkedin_ads_database: your_database_name  

    pinterest_schema: pinterest
    pinterest_database: your_database_name 

    twitter_ads_schema: twitter_ads
    twitter_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

The package is now configured to build the enabled models. There is an additional step of customization that is unnecessary but highly recommended. The package is automatically configured to build all models into a target database and schema with standardized naming conventions. However, in your dbt_project.yml file, you can override these naming conventions using the +database and +schema configurations shown below.

models:  
  ad_reporting:
    +schema: ad_reporting

  apple_search_ads:
    +schema: apple_search_ads
  apple_search_ads_source:
    +schema: apple_search_ads_source
  
  facebook_ads:
    +schema: facebook_ads
  facebook_ads_source:
    +schema: facebook_ads_source
  
  google_ads:
    +schema: google_ads
  google_ads_source:
    +schema: google_ads_source

  linkedin:
    +schema: linkedin
  linkedin_source:
    +schema: linkedin_source

  microsoft_ads:
    +schema: microsoft_ads
  microsoft_ads_source:
    +schema: microsoft_ads_source

  pinterest:
    +schema: pinterest
  pinterest_source:
    +schema: pinterest_source
  
  twitter_ads:
    +schema: twitter_ads
  twitter_ads_source:
    +schema: twitter_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

Now that the package has been installed and configured, the rest of the process is straightforward. First, you need to test and troubleshoot the models to validate that the data is flowing as expected. Once it is confirmed the data is clean, then it is time to create a pull request and push the models to production. The final product will include the seven models listed below by default.

• ad_reporting__account_report – each record represents daily metrics by account.
• ad_reporting__campaign_report – each record represents daily metrics by campaign and account.
• ad_reporting__ad_group_report – each record represents daily metrics by ad group, campaign and account.
• ad_reporting__ad_report – each record represents daily metrics by ad, ad group, campaign and account.
• ad_reporting__keyword_report – each record represents daily metrics by keyword, ad group, campaign and account.
• ad_reporting__search_report – each record represents daily metrics by search query, ad group, campaign and account.
• ad_reporting__url_report – each record represents daily metrics by URL (and if applicable, URL UTM parameters), ad group, campaign and account.

These reports are ready for consumption by linking your database to tools such as Tableau and ThoughtSpot. There is also potential to transform these reports even further into a customized view that fits your company’s business needs. However, we’ll save that one for later 😉