How to add additional metrics in ADL

Overview

This document provides guidance on incorporating pass_through_metrics to the ADL 2.0 Models without the dependence on the Data Engineering team for metric additions. ADL users now have the ability to add metrics to any {platform}-specific ADL data sources, facilitating automatic updates to the {platform} specific DBT models on full refresh App executions. 🎉

This pass-through metrics feature exclusively applies to Metrics. Adding dimensions necessitates an ASD ticket for inclusion into ADL models.

Currently, the pass-through metrics feature is implemented on all platforms, except for CM360. Please read through What to Know before Utilizing the Feature first.

Please refer to DAGs for platform specific ADL data sources information.
For Meta, ensure that conversion columns are added to facebookads_insights_adconversion, and all general ad-related performance metrics are added to facebookads_insights_ad. Please reach out DE team for further clarification.

Note: Please do not select same metrics in both facebookads_insights_adconversion andfacebookads_insights_ad this will lead to a Column duplication error in the ADL build.

What to Know Before Utilizing the Feature:

If you want to add custom metrics into insights datasources from those platforms,

Trade Desk, Bidtellect, Vistar - follow the onboarding docs (Onboarding a platform) to ensure the Alli datasources have those metrics
Facebook Ads, Microsoft Ads, Pinterest Ads - make sure to add metrics into new datasources starting with facebookads_insights_xxxx, microsoftads_insights_xxx, pinterestads_insights_xxxx
Google Ads - pmax report is not supported currently

If you want to add…	Supported?	Then…
Metrics that can be summed up		Feel free to add those metrics!
Metrics to Trade Desk, Bidtellect, and Vistar datasources		Follow the onboarding docs (Onboarding a platform) to ensure the datasources have those metrics
Metrics to Microsoft Ads	⚠️	We’ve deployed the feature within Microsoft Ads. But for current Microsoft Ads v2 models, we’ve included all possible non-calculated metrics. The following list provides metrics that will cause your datasource to fail if you add them Unsupported metrics in Microsoft Ads datasource CODE 'microsoftads_insights_adgroup_impressionshare': [ 'audience impression lost to budget percent', 'audience impression lost to rank percent', 'audience impression share percent', 'relative ctr' ], 'microsoftads_insights_adgroup': [ 'absolute top impression rate percent', 'absolute top impression share lost to budget percent', 'absolute top impression share lost to rank percent', 'absolute top impression share percent', 'audience impression lost to budget percent', 'audience impression lost to rank percent', 'audience impression share percent', 'click share percent', 'exact match impression share percent', 'impression lost to budget percent', 'impression lost to rank agg percent', 'impression share percent', 'relative ctr' ], 'microsoftads_insights_campaign_impressionshare': [ 'audience impression lost to budget percent', 'audience impression lost to rank percent', 'audience impression share percent', 'relative ctr' ], 'microsoftads_insights_campaign': [ 'absolute top impression rate percent', 'absolute top impression share lost to budget percent', 'absolute top impression share lost to rank percent', 'absolute top impression share percent', 'audience impression lost to budget percent', 'audience impression lost to rank percent', 'audience impression share percent', 'click share percent', 'exact match impression share percent', 'impression lost to budget percent', 'impression lost to rank agg percent', 'impression share percent', 'relative ctr', 'top impression rate percent', 'top impression share lost to budget percent', 'top impression share lost to rank percent', 'top impression share percent' ], 'microsoftads_insights_productpartition': [ 'absolute top impression share percent', 'benchmark bid', 'benchmark ctr', 'click share percent', 'impression lost to budget percent', 'impression lost to rank percent', 'impression share percent'
Metrics to Google Ads Pmax, CM360 report		An ASD ticket if a metric into `googleads_insights_campaign_interactiontarget` `googleads_insights_shopping_campaign` `googleads_insights_shopping`
Metrics to legacy datasources `facebook_insights_ad` `facebook_insights_adset` `bing_insights_account` `bing_insights_ad` `bing_insights_adgroup` `bing_insights_campaign` `bing_insights_audience` `bing_insights_keyword` `bing_insights_productpartition` `bing_insights_searchquery` `pinterest_insights_adgroup` `pinterest_insights_pin`		Add custom metrics into new datasources starting with `facebookads_insights_xxx` `microsoftads_insights_xxx` `pinterestads_insights_xxx`
Calculated metrics like `cpm`, `ctr`, `roas`…		We recommend getting these calculated metrics by formulas instead of extracting them from datasources
Metrics to aggregate by specific aggregation formulas like weighted average, like `click share`, `impression share`, etc.		The feature now supports `sum()` aggregation only
Any dimension		An ASD ticket

Instructions:

ADL 2.0 Data Source Setup

Access any platform-specific ADL insights data source and add the required metrics to the {platform}_insights_datasource.

Adding `video_continuous_2_sec_watched_actions` to `facebookads_insights_ad`

Backfill Process

After the successful addition of metrics, you can Backfill the data source according to your required dates in Alli.
Historical Data Backfill Considerations:
- Ensure to check the minimum date of the insights data source and backfill data up to that point.
- For platforms such as Meta, Pinterest, and Microsoft, where legacy data exists:
  - Add the necessary columns to backfill the required data, while considering any applicable 37-month time limit for that platform. ( see Backfilling data for a platform )

ADL 2.0 APPs Refresh:

Upon adding the metrics, simply execute a refresh on the ALL Reports app for BigQuery, Redshift, and Snowflake. (See Building and refreshing your ADL reports )
Later, you can query the added metrics in {platform}__{ad/adgroup/campaign/account}_performance models.