一般に、同じ購入プラットフォーム内で同じ名前を持つフィールドは、テーブルをまたいで結合できます。たとえば adh.google_ads_impressions.impression_id は adh.google_ads_clicks.impression_id と結合できます。この例では、Google ディスプレイ広告が共通の購入プラットフォーム、impression_id が共通のフィールドであり、google_ads_impressions と google_ads_clicks がそれぞれ異なるテーブルです。
購入プラットフォームをまたいだデータ結合は、さまざまな複雑化要因により、難しいことがあります。Google のサービスが異なれば、使われるユーザー ID は異なります。また、同じサービス内であっても、ユーザーのログイン状態によってユーザー ID が変わることもあります。
サービスをまたいだ結合の際は、次のリストをガイドラインとしてご活用ください。同じ購入プラットフォーム内であれば、通常は問題なく結合が可能です。購入プラットフォームが異なる場合は結合できないことが一般的です。
| 購入プラットフォーム | サービス | テーブル | 結合可能な ID |
|---|---|---|---|
| Google ディスプレイ広告 | Google 動画パートナー（YouTube を除く） | adh.google_ads_*
|
|
| Google マーケティング プラットフォーム | Data Transfer 経由:
ディスプレイ＆ビデオ 360 キャンペーン マネージャー 360 |
adh.dv360_dt_* adh.cm_dt_*
|
|
| YouTube（Google 経由販売） | YouTube Reserve YouTube（Google 広告で） YouTube（ディスプレイ＆ビデオ 360 で） |
adh.yt_reserve_*
adh.google_ads_*
adh.dv360_youtube_*
|
|
| YouTube（パートナー経由販売） | Google アド マネージャー FreeWheel |
adh.partner_sold_cross_sell_*
adh.freewheel_* |
|
| 凡例 | user_id |
external_cookie |
device_id_md5 |
例
ユーザー ID とデバイス ID 以外にも、テーブルの結合に使用できるフィールドはいくつかあります。結合可能なフィールドをプルダウン メニューから選択すると、Ads Data Hub でのテーブル結合方法を確認できます。なお、このセクションはすべての例を網羅したものではありません。
- すべて
- user_id
- impression_id
- campaign_id
- customer_id
- region_id
この例では、user_id を使って、インプレッション、クリエイティブ、コンバージョンの各テーブルを結合する方法を示しています。
ユースケース: ブランディング キャンペーンによってコンバージョンが増加したかどうか確認する
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)
)
この例では、impression_id を使って、コンバージョン データとインプレッション データをリンクする方法を示しています。
ユースケース: インプレッションとコンバージョンのデータを国と 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
この例では、いくつかのテーブルを複数の ID によって結合する方法を示しています。
ユースケース: 特定のキャンペーンとリンクしているアセットをリストアップする
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
この例では、メタデータ テーブルを結合する方法を示しています。
ユースケース: インプレッションのテーブルを州のメタデータ テーブルと結合することにより、各州のユニーク Cookie 数と平均フリークエンシーを表示する
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