UTM Audit

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

Recipes 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

Data Source	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

Create normalized union of paid ads data with destination_url
Extract UTM parameters from destination_url:

~utm_source
~utm_medium
~utm_campaign
~utm_content

UTM parameters type classification:

~define
~auto define
~short URL (f.e. bit.ly)
~destination URL is empty

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)

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 data source 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, data source, 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, data source, 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, data source, campaign, adset, and UTM Quality and UTM parameters are available for you