# 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_creative_conversions.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_creative_conversions`
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.

> [!IMPORTANT]
> **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](https://developers.google.com/ads-data-hub/guides/join-your-data).


| 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.
All user_id impression_id campaign_id customer_id region_id This example demonstrates how to use `user_id` to join
impressions, creatives, and conversions tables.

Use case: Understand if branding campaigns drive incremental
conversions.

```sql
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.

```sql
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.

```sql
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.

```sql
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
```