Documentation

UTM Audit

Updated on

Oct 19, 2023

{% docs-informer info title="Important" %}

To better understand the Improvado Marketing Common Data Model and how it works, you can check the Improvado MCDM Overview.

{% docs-informer-end %}

Description

The UTM Audit recipe provides you with information about the types and quality of UTMs used in the customer data (only in Google Ads and Facebook). This information will help determine the feasibility of using UTMs to join with web analytics systems.

The final report contains a breakdown by the advertiser, campaign, ad set, destination URL, and quality of UTMs calculated on top of the destination URL.

This report also contained paid ads metrics such as spend, impressions, conversions, clicks, etc.

Dashboards

You can find the Demo Dashboard here.

Technical Details

MCDM Data Dictionary

Report structure
mcdm_table_name mcdm_field_name field_value_type
utm_audit__quality date date
utm_audit__quality add_to_cart float64
utm_audit__quality clicks float64
utm_audit__quality comments float64
utm_audit__quality engagements float64
utm_audit__quality impressions float64
utm_audit__quality installs float64
utm_audit__quality likes float64
utm_audit__quality link_clicks float64
utm_audit__quality post_click_conversions float64
utm_audit__quality post_view_conversions float64
utm_audit__quality posts float64
utm_audit__quality purchase float64
utm_audit__quality registrations float64
utm_audit__quality revenue float64
utm_audit__quality shares float64
utm_audit__quality spend float64
utm_audit__quality total_conversions float64
utm_audit__quality video_100_quartile_views float64
utm_audit__quality video_25_quartile_views float64
utm_audit__quality video_50_quartile_views float64
utm_audit__quality video_75_quartile_views float64
utm_audit__quality video_starts float64
utm_audit__quality video_views float64
utm_audit__quality ad_id string
utm_audit__quality ad_name string
utm_audit__quality adset_id string
utm_audit__quality adset_name string
utm_audit__quality advertiser_id string
utm_audit__quality advertiser_name string
utm_audit__quality campaign_id string
utm_audit__quality campaign_name string
utm_audit__quality creative_id string
utm_audit__quality creative_name string
utm_audit__quality placement_id string
utm_audit__quality placement_name string
utm_audit__quality source string
utm_audit__quality medium string
utm_audit__quality destination_url string
utm_audit__quality campaign_status string
utm_audit__quality utm_source_type string
utm_audit__quality utm_medium_type string
utm_audit__quality utm_campaign_type string
utm_audit__quality utm_content_type string
utm_audit__quality utm_source string
utm_audit__quality utm_medium string
utm_audit__quality utm_campaign string
utm_audit__quality utm_content string
utm_audit__quality utm_quality string
Required extraction templates
Data Source Report Type Extraction Template
Facebook Ads ads_creative Ads Creative
Facebook Ads entity_creatives Entity Creatives
Google Ads ads Ads
Mapping
Datasource Extraction Template sql_field_name mcdm_field_name
Google Ads Ads ad_id ad_id
Google Ads Ads ad_name ad_name
Google Ads Ads ad_group_id adset_id
Google Ads Ads ad_group_name adset_name
Google Ads Ads campaign_id campaign_id
Google Ads Ads campaign_name campaign_name
Google Ads Ads clicks clicks
Google Ads Ads ad_final_url destination_url
Google Ads Ads engagements engagements
Google Ads Ads impressions impressions
Google Ads Ads cost spend
Google Ads Ads all_conversions total_conversions
Google Ads Ads video_quartile_100 video_100_quartile_views
Google Ads Ads video_quartile_25 video_25_quartile_views
Google Ads Ads video_quartile_50 video_50_quartile_views
Google Ads Ads video_quartile_75 video_75_quartile_views
Google Ads Ads video_quartile_25 video_starts
Google Ads Ads video_views video_views
Google Ads Ads campaign_status campaign_status
Facebook Ads Creatives ad_id ad_id
Facebook Ads Creatives ad_name ad_name
Facebook Ads Creatives add_to_cart add_to_cart
Facebook Ads Creatives adset_id adset_id
Facebook Ads Creatives adset_name adset_name
Facebook Ads Creatives campaign_id campaign_id
Facebook Ads Creatives campaign_name campaign_name
Facebook Ads Creatives clicks clicks
Facebook Ads Creatives comments comments
Facebook Ads Creatives creative_id creative_id
Facebook Ads Creatives creative_name creative_name
Facebook Ads Creatives likes + shares + comments + views + clicks engagements
Facebook Ads Creatives impressions impressions
Facebook Ads Creatives mobile_app_install installs
Facebook Ads Creatives likes likes
Facebook Ads Creatives inline_link_clicks link_clicks
Facebook Ads Creatives purchase purchase
Facebook Ads Creatives complete_registration registrations
Facebook Ads Creatives purchase_value revenue
Facebook Ads Creatives shares shares
Facebook Ads Creatives spend spend
Facebook Ads Creatives video_p100_watched_actions video_100_quartile_views
Facebook Ads Creatives video_p25_watched_actions video_25_quartile_views
Facebook Ads Creatives video_p50_watched_actions video_50_quartile_views
Facebook Ads Creatives video_p75_watched_actions video_75_quartile_views
Facebook Ads Creatives video_view_3s video_starts
Facebook Ads Creatives views video_views
Facebook Ads Creatives effective_status campaign_status
Facebook Entity Creatives website_destination_url destination_url

Tech Schema

Preparation stages
  1. Create normalized union of paid ads data with destination_url
  2. Extract UTM parameters from destination_url:
  • ~utm_source
  • ~utm_medium
  • ~utm_campaign
  • ~utm_content
  1. UTM parameters type classification:
  • ~define
  • ~auto define
  • ~short URL (f.e. bit.ly)
  • ~destination URL is empty
  1. UTM quality classification:
  • ~Good (utm_source, utm_medium, utm_campaign and utm_content is define or auto define)
  • ~Medium (utm_source, utm_medium, utm_campaign is define or auto define)
  • ~Bad (any other case)
  1. Calculate the sum of all metrics across all dimensions and properties + utm parameter types and utm quality
GDS Dashboard description

Page 1 - UTM Analysis

  • Table 1
  • ~This page shows the sum of spend and quantity of adsets broken down by data sources, advertisers and UTM Quality
  • ~Filters by date, advertiser and datasource are available for you

Page 2 - List of Ad Sets

  • Table 1
  • ~This table shows the example of adsets and related UTM Quality
  • ~Filters by date, advertiser, datasource, campaign, adset and UTM Quality are available for you
  • Table 2
  • ~This table shows examples of destination URL and UTM Quality
  • ~Filters by date, advertiser, datasource, campaign, adset and UTM Quality are available for you

Page 3 - UTM Examples

  • Table 1 - UTM Examples
  • ~This table shows examples of UTM parameters and UTM Quality
  • ~Filters by date, advertiser, data source, campaign, adset, and UTM Quality and UTM parameters are available for you
  • Table 2 - Direct URL + UTM examples
  • ~This table shows examples of direct URL, UTM parameters, and UTM Quality
  • ~Filters by date, advertiser, datasource, campaign, adset, and UTM Quality and UTM parameters are available for you

Schema information

Setup guide

Settings

No items found.

Troubleshooting

Troubleshooting guides

Check out troubleshooting guides for
UTM Audit
here:

Limits

Frequently asked questions

No items found.
☶ On this page
Description
Related articles
No items found.
No items found.

Questions?

Improvado team is always happy to help with any other questions you might have! Send us an email.

Contact your Customer Success Manager or raise a request in Improvado Service Desk.