Stay organized with collections
Save and categorize content based on your preferences.
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_*
person_outlinecookiesmartphone
Google
Marketing
Platform
via Data
Transfer:
Display &
Video 360
Campaign
Manager 360
adh.dv360_dt_* adh.cm_dt_*
person_outlinecookiesmartphone
YouTube
Google
sold
YouTube
Reserve
YouTube (in
Google Ads)
YouTube (in
Display &
Video 360)
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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2024-09-18 UTC."],[[["\u003cp\u003eData within the same Google buying door (e.g., Google Display Ads, YouTube Google sold) can usually be joined using common fields like \u003ccode\u003euser_id\u003c/code\u003e, \u003ccode\u003eexternal_cookie\u003c/code\u003e, or \u003ccode\u003edevice_id_md5\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eJoining data across different buying doors is generally not possible due to variations in user IDs across Google products.\u003c/p\u003e\n"],["\u003cp\u003eRDID tables (\u003ccode\u003eadh.*_rdid\u003c/code\u003e) have a specific requirement for joining, using only the \u003ccode\u003edevice_id_md5\u003c/code\u003e field.\u003c/p\u003e\n"],["\u003cp\u003eAds Data Hub provides a variety of joinable fields beyond user and device IDs, allowing for linking impressions, creatives, conversions, and metadata tables.\u003c/p\u003e\n"],["\u003cp\u003eExamples are provided to illustrate use cases like analyzing campaign performance, understanding user behavior, and linking assets to campaigns.\u003c/p\u003e\n"]]],["Data from tables within the same buying door can be joined using common fields like `impression_id`. Joining across different buying doors is complex due to varied user IDs across and within Google products. The provided table outlines which IDs (e.g., `user_id`, `external_cookie`, `device_id_md5`) are joinable within each buying door. Examples demonstrate joining using `user_id`, `impression_id`, and multiple IDs across tables to analyze branding campaigns, conversion stats, campaign assets, and metadata. RDID tables are joinable on `device_id_md5`.\n"],null,["# Joinable fields in Ads Data Hub\n\nGenerally, fields with the same name within the same buying door can be joined\ntogether across tables. For example, `adh.google_ads_impressions.impression_id`\ncan be joined with `adh.google_ads_clicks.impression_id`. In this example,\nGoogle Display Ads is the common buying door, `impression_id` is the common field,\nand `google_ads_impressions` and `google_ads_clicks` are the two different\ntables.\n\nComplexities exist that can make it difficult to join data across buying doors.\nDifferent Google products use different user IDs, and user IDs can also vary\nwithin a single product based on sign-in state.\n\nUse the following table as a guideline for joining across products. Joins within\neach buying door will generally work, whereas joins between buying doors\ngenerally won't.\n| **Important:** RDID tables (`adh.*_rdid`) can only be joined on RDID (`device_id_md5`). To learn more about joining first-party data on RDID and external IDs, see [Join first-party data overview](/ads-data-hub/marketers/guides/join-your-data).\n\n\n| Buying Door | Products | Tables | Joinable IDs |\n|---------------------------|---------------------------------------------------------------------------|-------------------------------------------------------------|----------------------------------|\n| Google Display Ads | Google Video Partners (excl. YouTube) | `adh.google_ads_*` | person_outline cookie smartphone |\n| Google Marketing Platform | via Data Transfer: Display \\& Video 360 Campaign Manager 360 | `adh.dv360_dt_*` `adh.cm_dt_*` | person_outline cookie smartphone |\n| YouTube Google sold | YouTube Reserve YouTube (in Google Ads) YouTube (in Display \\& Video 360) | `adh.yt_reserve_*` `adh.google_ads_*` `adh.dv360_youtube_*` | person_outline cookie smartphone |\n| YouTube Partner sold | Google Ad Manager Freewheel | `adh.partner_sold_cross_sell_*` `adh.freewheel_*` | person_outline cookie |\n| | | | |\n| ***Key*** | person_outline `user_id` | cookie `external_cookie` | smartphone `device_id_md5` |\n\nExamples\n--------\n\nIn addition to user and device IDs, you can join tables using a number of other\nfields. To learn how to join tables in Ads Data Hub, select a joinable field\nfrom the dropdown menu. This section contains a non-exhaustive set of examples. \nAll user_id impression_id campaign_id customer_id region_id This example demonstrates how to use `user_id` to join\nimpressions, creatives, and conversions tables.\n\nUse case: Understand if branding campaigns drive incremental\nconversions. \n\n```sql\nWITH imp AS (\n SELECT\n user_id,\n COUNTIF(campaign_id IN UNNEST(@brand_campaign_ids)) AS brand_impression,\n COUNTIF(campaign_id IN UNNEST(@perf_campaign_ids)) AS perf_impression,\n FROM adh.google_ads_impressions\n WHERE\n campaign_id IN UNNEST(ARRAY_CONCAT(@perf_campaign_ids, @brand_campaign_ids))\n AND user_id != '0'\n GROUP BY 1\n ),\n conv AS (\n SELECT\n c.user_id,\n COUNT(1) AS conv_count\n FROM adh.google_ads_conversions\n WHERE\n impression_data.campaign_id IN UNNEST(@perf_campaign_ids)\n AND conversion_type IN UNNEST(@conversion_type_list)\n AND user_id != '0'\n GROUP BY 1\n )\nSELECT\n SUM(IF(has_brand_traffic AND NOT has_perf_traffic, conv_count, 0)) AS brand_only,\n SUM(IF(NOT has_brand_traffic AND has_perf_traffic, conv_count, 0)) AS perf_only,\n SUM(IF(has_brand_traffic AND has_perf_traffic, conv_count, 0)) AS overlap\nFROM\n (\n SELECT\n imp.user_id,\n imp.brand_impression \u003e 0 AS has_brand_traffic,\n imp.perf_impression \u003e 0 AS has_perf_traffic,\n conv.conv_count\n FROM imp\n JOIN conv\n USING (user_id)\n )\n``` \nThis example demonstrates how to use `impression_id` to\nlink conversions data to impressions data.\n\nUse case: Slice impression and conversion stats based on country and\nCTC/EVC. \n\n```sql\nSELECT\n imp.location.country,\n COUNT(1) AS num_imps,\n SUM(IF(attribution_event_type = 'CLICK', 1, 0)) AS ctc_convs,\n SUM(IF(attribution_event_type = 'ENGAGED_VIEW', 1, 0)) AS evc_convs\nFROM adh.google_ads_impressions AS imp\nLEFT JOIN adh.google_ads_conversions AS conv\n ON (\n imp.impression_id = conv.impression_id\n AND conv.conversion_type IN UNNEST(@conversion_type_list))\nWHERE imp.campaign_id IN UNNEST(@campaign_ids)\nGROUP BY 1\n``` \nThis example demonstrates how to join several tables on multiple IDs.\n\nUse case: List assets linked to a particular campaign. \n\n```sql\nSELECT\n cmp.campaign_id,\n adg.adgroup_id,\n cr.video_message.youtube_video_id\nFROM adh.google_ads_campaign AS cmp\nJOIN adh.google_ads_adgroup AS adg\n USING (campaign_id)\nJOIN adh.google_ads_adgroupcreative AS agc\n USING (adgroup_id)\nJOIN adh.google_ads_creative AS cr\n ON (agc.customer_id = cr.customer_id\n AND agc.creative_id = cr.creative_id)\nWHERE campaign_id = 123\nGROUP BY 1, 2, 3\n``` \nThis example demonstrates how to join metadata tables.\n\nUse case: Join an impressions table with the state metadata table to\nshow unique cookie count and average frequency by state. \n\n```sql\nSELECT\n IFNULL(reg.region_name, 'unspecified') AS state,\n COUNT(DISTINCT user_id) AS users,\n COUNT(1) AS impressions,\n FORMAT('%0.2f', COUNT(1) / COUNT(DISTINCT user_id)) AS avg_imp_per_user\nFROM adh.google_ads_impressions AS imp\nLEFT JOIN adh.region AS reg\n ON (imp.location.geo_region_id = reg.region_id)\nWHERE\n imp.location.country = 'US'\nGROUP BY 1\n```"]]