This is legacy documentation, and may not be complete. To see the latest documentation, if you are a marketer, refer to the Marketers site. If you are a measurement partner, refer to the Measurement Partners site.

Joinable fields in Ads Data Hub

Generally, fields with the same name within the same buying door can be joined together across tables. For example, adh.google_ads_impressions.impression_id can be joined with adh.google_ads_clicks.impression_id . In this example, Google Display Ads is the common buying door, impression_id is the common field, and google_ads_impressions and google_ads_clicks are the two different tables.

Complexities exist that can make it difficult to join data across buying doors. Different Google products use different user IDs, and user IDs can also vary within a single product based on sign-in state.

Use the following table as a guideline for joining across products. Joins within each buying door will generally work, whereas joins between buying doors generally won't.

Buying Door	Products	Tables	Joinable IDs
Google Display Ads	Google Video Partners (excl. YouTube)	`adh.google_ads_*`
Google Marketing Platform	via Data Transfer: Display & Video 360 Campaign Manager 360	`adh.dv360_dt_` `adh.cm_dt_`
YouTube Google sold	YouTube Reserve YouTube (in Google Ads) YouTube (in Display & Video 360)	`adh.yt_reserve_` `adh.google_ads_` `adh.dv360_youtube_*`
YouTube Partner sold	Google Ad Manager Freewheel	`adh.partner_sold_cross_sell_` `adh.freewheel_`

*Key*	`user_id`	`external_cookie`	`device_id_md5`

Examples

In addition to user and device IDs, you can join tables using a number of other fields. To learn how to join tables in Ads Data Hub, select a joinable field from the dropdown menu. This section contains a non-exhaustive set of examples.

This example demonstrates how to use user_id to join impressions, creatives, and conversions tables.

Use case: Understand if branding campaigns drive incremental conversions.

 WITH 
  
 imp 
  
 AS 
  
 ( 
  
 SELECT 
  
 user_id 
 , 
  
 COUNTIF 
 ( 
 campaign_id 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 brand_campaign_ids 
 )) 
  
 AS 
  
 brand_impression 
 , 
  
 COUNTIF 
 ( 
 campaign_id 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 perf_campaign_ids 
 )) 
  
 AS 
  
 perf_impression 
 , 
  
 FROM 
  
 adh 
 . 
 google_ads_impressions 
  
 WHERE 
  
 campaign_id 
  
 IN 
  
 UNNEST 
 ( 
 ARRAY_CONCAT 
 ( 
 @ 
 perf_campaign_ids 
 , 
  
 @ 
 brand_campaign_ids 
 )) 
  
 AND 
  
 user_id 
  
 != 
  
 '0' 
  
 GROUP 
  
 BY 
  
 1 
  
 ), 
  
 conv 
  
 AS 
  
 ( 
  
 SELECT 
  
 c 
 . 
 user_id 
 , 
  
 COUNT 
 ( 
 1 
 ) 
  
 AS 
  
 conv_count 
  
 FROM 
  
 adh 
 . 
 google_ads_conversions 
  
 WHERE 
  
 impression_data 
 . 
 campaign_id 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 perf_campaign_ids 
 ) 
  
 AND 
  
 conversion_type 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 conversion_type_list 
 ) 
  
 AND 
  
 user_id 
  
 != 
  
 '0' 
  
 GROUP 
  
 BY 
  
 1 
  
 ) 
 SELECT 
  
 SUM 
 ( 
 IF 
 ( 
 has_brand_traffic 
  
 AND 
  
 NOT 
  
 has_perf_traffic 
 , 
  
 conv_count 
 , 
  
 0 
 )) 
  
 AS 
  
 brand_only 
 , 
  
 SUM 
 ( 
 IF 
 ( 
 NOT 
  
 has_brand_traffic 
  
 AND 
  
 has_perf_traffic 
 , 
  
 conv_count 
 , 
  
 0 
 )) 
  
 AS 
  
 perf_only 
 , 
  
 SUM 
 ( 
 IF 
 ( 
 has_brand_traffic 
  
 AND 
  
 has_perf_traffic 
 , 
  
 conv_count 
 , 
  
 0 
 )) 
  
 AS 
  
 overlap 
 FROM 
  
 ( 
  
 SELECT 
  
 imp 
 . 
 user_id 
 , 
  
 imp 
 . 
 brand_impression 
  
 > 
  
 0 
  
 AS 
  
 has_brand_traffic 
 , 
  
 imp 
 . 
 perf_impression 
  
 > 
  
 0 
  
 AS 
  
 has_perf_traffic 
 , 
  
 conv 
 . 
 conv_count 
  
 FROM 
  
 imp 
  
 JOIN 
  
 conv 
  
 USING 
  
 ( 
 user_id 
 ) 
  
 )

This example demonstrates how to use impression_id to link conversions data to impressions data.

Use case: Slice impression and conversion stats based on country and CTC/EVC.

 SELECT 
  
 imp 
 . 
 location 
 . 
 country 
 , 
  
 COUNT 
 ( 
 1 
 ) 
  
 AS 
  
 num_imps 
 , 
  
 SUM 
 ( 
 IF 
 ( 
 attribution_event_type 
  
 = 
  
 'CLICK' 
 , 
  
 1 
 , 
  
 0 
 )) 
  
 AS 
  
 ctc_convs 
 , 
  
 SUM 
 ( 
 IF 
 ( 
 attribution_event_type 
  
 = 
  
 'ENGAGED_VIEW' 
 , 
  
 1 
 , 
  
 0 
 )) 
  
 AS 
  
 evc_convs 
 FROM 
  
 adh 
 . 
 google_ads_impressions 
  
 AS 
  
 imp 
 LEFT 
  
 JOIN 
  
 adh 
 . 
 google_ads_conversions 
  
 AS 
  
 conv 
  
 ON 
  
 ( 
  
 imp 
 . 
 impression_id 
  
 = 
  
 conv 
 . 
 impression_id 
  
 AND 
  
 conv 
 . 
 conversion_type 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 conversion_type_list 
 )) 
 WHERE 
  
 imp 
 . 
 campaign_id 
  
 IN 
  
 UNNEST 
 ( 
 @ 
 campaign_ids 
 ) 
 GROUP 
  
 BY 
  
 1

This example demonstrates how to join several tables on multiple IDs.

Use case: List assets linked to a particular campaign.

 SELECT 
  
 cmp 
 . 
 campaign_id 
 , 
  
 adg 
 . 
 adgroup_id 
 , 
  
 cr 
 . 
 video_message 
 . 
 youtube_video_id 
 FROM 
  
 adh 
 . 
 google_ads_campaign 
  
 AS 
  
 cmp 
 JOIN 
  
 adh 
 . 
 google_ads_adgroup 
  
 AS 
  
 adg 
  
 USING 
  
 ( 
 campaign_id 
 ) 
 JOIN 
  
 adh 
 . 
 google_ads_adgroupcreative 
  
 AS 
  
 agc 
  
 USING 
  
 ( 
 adgroup_id 
 ) 
 JOIN 
  
 adh 
 . 
 google_ads_creative 
  
 AS 
  
 cr 
  
 ON 
  
 ( 
 agc 
 . 
 customer_id 
  
 = 
  
 cr 
 . 
 customer_id 
  
 AND 
  
 agc 
 . 
 creative_id 
  
 = 
  
 cr 
 . 
 creative_id 
 ) 
 WHERE 
  
 campaign_id 
  
 = 
  
 123 
 GROUP 
  
 BY 
  
 1 
 , 
  
 2 
 , 
  
 3

This example demonstrates how to join metadata tables.

Use case: Join an impressions table with the state metadata table to show unique cookie count and average frequency by state.

 SELECT 
  
 IFNULL 
 ( 
 reg 
 . 
 region_name 
 , 
  
 'unspecified' 
 ) 
  
 AS 
  
 state 
 , 
  
 COUNT 
 ( 
 DISTINCT 
  
 user_id 
 ) 
  
 AS 
  
 users 
 , 
  
 COUNT 
 ( 
 1 
 ) 
  
 AS 
  
 impressions 
 , 
  
 FORMAT 
 ( 
 '%0.2f' 
 , 
  
 COUNT 
 ( 
 1 
 ) 
  
 / 
  
 COUNT 
 ( 
 DISTINCT 
  
 user_id 
 )) 
  
 AS 
  
 avg_imp_per_user 
 FROM 
  
 adh 
 . 
 google_ads_impressions 
  
 AS 
  
 imp 
 LEFT 
  
 JOIN 
  
 adh 
 . 
 region 
  
 AS 
  
 reg 
  
 ON 
  
 ( 
 imp 
 . 
 location 
 . 
 geo_region_id 
  
 = 
  
 reg 
 . 
 region_id 
 ) 
 WHERE 
  
 imp 
 . 
 location 
 . 
 country 
  
 = 
  
 'US' 
 GROUP 
  
 BY 
  
 1

Joinable fields in Ads Data Hub Stay organized with collections Save and categorize content based on your preferences.

Examples

Joinable fields in Ads Data Hub