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 😉