Ads Data Hub のサンプルクエリ

注: サンプルクエリはこのほかにも UI 内のクエリ テンプレートとして提供されています。詳細

ここで紹介するサンプルクエリは、SQL と BigQuery の実務知識をお持ちであることを想定した内容です。詳細: BigQuery での SQL

キャンペーン マネージャー 360 Data Transfer クエリ

Floodlight 変数と一時テーブルのマッチング

アクティビティ テーブル内で、user_id とカスタム Floodlight 変数の間のマッチを生成します。得られた結果は、ファーストパーティ データをキャンペーン マネージャー 360 のデータと結合するのに使用できます。

/* Creating the match temp table. This can be a separate query and the
temporary table will persist for 72 hours. */

CREATE TABLE
  temp_table AS (
  SELECT
    user_id,
    REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS u1_val
  FROM
    adh.cm_dt_activities_attributed
  GROUP BY
    1,
    2 )

/* Matching to Campaign Manager 360 impression data */

SELECT
  imp.event.campaign_id,
  temp.u1_val,
  COUNT(*) AS cnt
FROM
  adh.cm_dt_impressions AS imp
JOIN
  tmp.temp_table AS temp USING (user_id)
GROUP BY
  1,
  2

インプレッション実績

インプレッションの管理に役立つクエリの例です。フリークエンシー キャップを超えて配信されたインプレッションの数や、広告露出が少なかった見込み顧客などを確認できます。こういった情報は、目的のオーディエンスに適切な数のインプレッションを届けられるよう、サイトや戦術を最適化するのに役立ちます。

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345] */

WITH filtered_uniques AS (
  SELECT
    user_id,
    COUNT(event.placement_id) AS frequency
  FROM adh.cm_dt_impressions
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
  GROUP BY user_id
)
SELECT
  frequency,
  COUNT(*) AS uniques
FROM filtered_uniques
GROUP BY frequency
ORDER BY frequency
;

合計ユニーク Cookie 数 / フリークエンシー

このサンプルは、ユニーク Cookie 数またはフリークエンシーを増加ないし減少させるための戦術や広告フォーマットの発見に役立ちます。

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */

SELECT
  COUNT(DISTINCT user_id) AS total_users,
  COUNT(DISTINCT event.site_id) AS total_sites,
  COUNT(DISTINCT device_id_md5) AS total_devices,
  COUNT(event.placement_id) AS impressions
FROM adh.cm_dt_impressions
WHERE user_id != '0'
  AND event.advertiser_id IN UNNEST(@advertiser_ids)
  AND event.campaign_id IN UNNEST(@campaign_ids)
  AND event.placement_id IN UNNEST(@placement_ids)
  AND event.country_domain_name = 'US'
;

WHERE 句にサイトまたはプレースメントの ID を組み込んで、クエリを絞り込むことも可能です。

合計ユニーク Cookie 数と平均フリークエンシー（州ごと）

このサンプルでは、cm_dt_impressions テーブルと cm_dt_state メタデータ テーブルを結合し、合計インプレッション数、州ごとの Cookie 数、ユーザーごとの平均インプレッション数を、北米各国の州ごとにグループ化して示します。

WITH impression_stats AS (
  SELECT
    event.country_domain_name AS country,
    CONCAT(event.country_domain_name, '-', event.state) AS state,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.cm_dt_impressions
  WHERE event.country_domain_name = 'US'
    OR event.country_domain_name = 'CA'
  GROUP BY 1, 2
)
SELECT
  country,
  IFNULL(state_name, state) AS state_name,
  users,
  impressions,
  FORMAT(
    '%0.2f',
    IF(
      IFNULL(impressions, 0) = 0,
      0,
      impressions / users
    )
  ) AS avg_imps_per_user
FROM impression_stats
LEFT JOIN adh.cm_dt_state USING (state)
;

ディスプレイ＆ビデオ 360 のオーディエンス

このサンプルは、ディスプレイ＆ビデオ 360 のオーディエンスを分析する方法を示しています。インプレッションが届いているオーディエンスを確認し、オーディエンス間のパフォーマンスの差を調べることが可能です。この情報は、ユニーク Cookie 数（より多くのユーザーへの広告表示）と品質（絞り込んだターゲティング、視認可能なインプレッション）のバランスを、広告掲載の目的に応じて調整するのに役立ちます。

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */

WITH filtered_impressions AS (
  SELECT
    event.event_time as date,
    CASE
      WHEN (event.browser_enum IN ('29', '30', '31')
            OR event.os_id IN
              (501012, 501013, 501017, 501018,
               501019, 501020, 501021, 501022,
               501023, 501024, 501025, 501027))
      THEN 'Mobile'
      ELSE 'Desktop'
    END AS device,
    event.dv360_matching_targeted_segments,
    event.active_view_viewable_impressions,
    event.active_view_measurable_impressions,
    user_id
  FROM adh.cm_dt_impressions
  WHERE event.dv360_matching_targeted_segments != ''
    AND event.advertiser_id in UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.dv360_country_code = 'US'
)
SELECT
  audience_id,
  device,
  COUNT(*) AS impressions,
  COUNT(DISTINCT user_id) AS uniques,
  ROUND(COUNT(*) / COUNT(DISTINCT user_id), 1) AS frequency,
  SUM(active_view_viewable_impressions) AS viewable_impressions,
  SUM(active_view_measurable_impressions) AS measurable_impressions
FROM filtered_impressions
JOIN UNNEST(SPLIT(dv360_matching_targeted_segments, ' ')) AS audience_id
GROUP BY 1, 2
;

視認性

アクティブ ビュー プラスの視認性指標を測定する方法を示したサンプルです。

WITH T AS (
   SELECT cm_dt_impressions.event.impression_id AS Impression,
          cm_dt_impressions.event.active_view_measurable_impressions AS AV_Measurable,
          SUM(cm_dt_active_view_plus.event.active_view_plus_measurable_count) AS AVP_Measurable
     FROM adh.cm_dt_impressions
FULL JOIN adh.cm_dt_active_view_plus
          ON (cm_dt_impressions.event.impression_id =
              cm_dt_active_view_plus.event.impression_id)
    GROUP BY Impression, AV_Measurable
)
SELECT COUNT(Impression), SUM(AV_Measurable), SUM(AVP_Measurable)
  FROM T
;

WITH Raw AS (
  SELECT
    event.ad_id AS Ad_Id,
  SUM(event.active_view_plus_measurable_count) AS avp_total,
  SUM(event.active_view_first_quartile_viewable_impressions) AS avp_1st_quartile,
  SUM(event.active_view_midpoint_viewable_impressions) AS avp_2nd_quartile,
  SUM(event.active_view_third_quartile_viewable_impressions) AS avp_3rd_quartile,
  SUM(event.active_view_complete_viewable_impressions) AS avp_complete
  FROM
    adh.cm_dt_active_view_plus
  GROUP BY
    1
)

SELECT
  Ad_Id,
  avp_1st_quartile / avp_total AS Viewable_Rate_1st_Quartile,
  avp_2nd_quartile / avp_total AS Viewable_Rate_2nd_Quartile,
    avp_3rd_quartile / avp_total AS Viewable_Rate_3rd_Quartile,
    avp_complete / avp_total AS Viewable_Rate_Completion_Quartile
FROM
  Raw
WHERE
  avp_total > 0
ORDER BY
  Viewable_Rate_1st_Quartile DESC
;

キャンペーン マネージャー 360 Data Transfer の動的データ

ダイナミック プロファイルおよびフィードごとのインプレッション数

SELECT
  event.dynamic_profile,
  feed_name,
  COUNT(*) as impressions
FROM adh.cm_dt_impressions
JOIN UNNEST (event.feed) as feed_name
GROUP BY 1, 2;

フィード 1 のダイナミック レポートラベルごとのインプレッション数

SELECT
  event.feed_reporting_label[SAFE_ORDINAL(1)] feed1_reporting_label,,
  COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(1)] <> “” # where you have at least one reporting label set
GROUP BY 1;

フィード 2 でレポートラベルが「red」のインプレッション数

SELECT
  event.feed_reporting_label[SAFE_ORDINAL(2)] AS feed1_reporting_label,
  COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(2)] = “red”
GROUP BY 1;

フィード 1 でレポート ディメンション 1 が「red」、レポート ディメンション 2 が「car」のインプレッション数

SELECT
  event.feed_reporting_label[SAFE_ORDINAL(1)] AS feed1_reporting_label,
  event.feed_reporting_dimension1[SAFE_ORDINAL(1)] AS feed1_reporting_dimension1,
  event.feed_reporting_dimension2[SAFE_ORDINAL(1)] AS feed2_reporting_dimension1,
  event.feed_reporting_dimension3[SAFE_ORDINAL(1)] AS feed3_reporting_dimension1,
  event.feed_reporting_dimension4[SAFE_ORDINAL(1)] AS feed4_reporting_dimension1,
  event.feed_reporting_dimension5[SAFE_ORDINAL(1)] AS feed5_reporting_dimension1,
  event.feed_reporting_dimension6[SAFE_ORDINAL(1)] AS feed6_reporting_dimension1,
  COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_dimension1[SAFE_ORDINAL(1)] = “red”
AND event.feed_reporting_dimension2[SAFE_ORDINAL(1)] = “car”
GROUP BY 1,2,3,4,5,6,7;

キャンペーン マネージャー 360 Data Transfer の広告フォーマット

以下のサンプルは、ユニーク Cookie 数と広告表示のフリークエンシーが最も高い広告フォーマットを判断する方法を示しています。この情報は、合計ユニーク Cookie 数とユーザーの広告露出のバランスを取るのに役立ちます。

インプレッション実績

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset.*/

WITH filtered_uniques AS (
  SELECT
    user_id,
    CASE
      WHEN creative_type LIKE '%Video%' THEN 'Video'
      WHEN creative_type IS NULL THEN 'Unknown'
      ELSE 'Display'
    END AS creative_format,
    COUNT(*) AS impressions
  FROM adh.cm_dt_impressions impression
  LEFT JOIN YOUR_BQ_DATASET.campaigns creative
    ON creative.rendering_id = impression.event.rendering_id
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
  GROUP BY user_id, creative_format
)
SELECT
  impressions AS frequency,
  creative_format,
  COUNT(DISTINCT user_id) AS uniques,
  SUM(impressions) AS impressions
FROM filtered_uniques
GROUP BY frequency, creative_format
ORDER BY frequency
;

ユニーク Cookie 数とフリークエンシー

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset. */

WITH filtered_impressions AS (
  SELECT
    event.campaign_id AS campaign_id,
    event.rendering_id AS rendering_id,
    user_id
  FROM adh.cm_dt_impressions
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
)
SELECT
  Campaign,
  CASE
    WHEN creative_type LIKE '%Video%' THEN 'Video'
    WHEN creative_type IS NULL THEN 'Unknown'
    ELSE 'Display'
  END AS creative_format,
  COUNT(DISTINCT user_id) AS users,
  COUNT(*) AS impressions
FROM filtered_impressions
LEFT JOIN YOUR_BQ_DATASET.campaigns USING (campaign_id)
LEFT JOIN YOUR_BQ_DATASET.creatives USING (rendering_id)
GROUP BY 1, 2
;

Google 広告

_rdid テーブルによるモバイルアプリ インプレッション数

クエリ 1:

SELECT
  campaign_id,
  COUNT(*) AS imp,
  COUNT(DISTINCT user_id) AS users
FROM adh.google_ads_impressions
WHERE is_app_traffic
GROUP BY 1
;

クエリ 2:

SELECT
  campaign_id,
  COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.google_ads_impressions_rdid
GROUP BY 1
;

得られた結果は campaign_id を使って結合できます。

ユーザー属性リーチ実績

このサンプルは、特定のユーザー属性にリーチしているキャンペーンを判断する方法を示しています。

/* For this query to run, @customer_id
must be replaced with an actual ID. For example [12345] */

WITH impression_stats AS (
  SELECT
    campaign_id,
    demographics.gender AS gender_id,
    demographics.age_group AS age_group_id,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.google_ads_impressions
  WHERE customer_id = @customer_id
  GROUP BY 1, 2, 3
)
SELECT
  campaign_name,
  gender_name,
  age_group_name,
  users,
  impressions
FROM impression_stats
LEFT JOIN adh.google_ads_campaign USING (campaign_id)
LEFT JOIN adh.gender USING (gender_id)
LEFT JOIN adh.age_group USING (age_group_id)
ORDER BY 1, 2, 3
;

視認性

視認性の概要をクエリサンプル付きで確認するには、高度なアクティブ ビュー指標をご覧ください。

Google 広告の広告主のタイムゾーン設定

SELECT
  customer_id,
  customer_timezone,
  count(1) as impressions
FROM adh.google_ads_impressions i
  INNER JOIN adh.google_ads_customer c
    ON c.customer_id = i.customer_id
WHERE TIMESTAMP_MICROS(i.query_id.time_usec) >= CAST(DATETIME(@date, c.customer_timezone) AS TIMESTAMP)
AND TIMESTAMP_MICROS(i.query_id.time_usec) < CAST(DATETIME_ADD(DATETIME(@date, c.customer_timezone), INTERVAL 1 DAY) AS TIMESTAMP)
GROUP BY customer_id, customer_timezone

広告枠タイプ

このサンプルクエリは、広告枠タイプの概念を示しています。inventory_type このフィールドで、広告を配信した広告枠（Gmail、YouTube Music など）を特定できます。値は YOUTUBE、YOUTUBE_TV、YOUTUBE_MUSIC、SEARCH、GMAIL、OTHER のいずれかです。それ以外の値は Google ディスプレイ ネットワークまたは動画ネットワークを示します。

SELECT
 i.campaign_id,
 cmp.campaign_name,
 i.inventory_type,
 COUNT(i.query_id.time_usec) AS impressions
FROM adh.google_ads_impressions i
LEFT JOIN adh.google_ads_campaign cmp ON (i.campaign_id = cmp.campaign_id)
WHERE
 TIMESTAMP_MICROS(i.query_id.time_usec)
  BETWEEN @local_start_date
  AND TIMESTAMP_ADD(@local_start_date,INTERVAL @number_days*24 HOUR)
GROUP BY 1, 2, 3
ORDER BY 4 DESC

YouTube 連続配信広告に関するクエリ

連続配信広告では、広告 2 個をグループ化して、比較的長い YouTube 視聴セッション中にミッドロール挿入点として表示します（広告 2 本だけの CM 枠のようなものです）。連続配信広告で配信される広告はスキップ可能です。ただし、ユーザーが 1 個目の広告をスキップすると、2 個目の広告もスキップされます。

Google 広告 TrueView インストリーム キャンペーンのインプレッションと TrueView 視聴回数

SELECT
 cmp.campaign_name,
 imp.is_app_traffic,
 COUNT(*) AS total_impressions,
 COUNTIF(clk.click_id IS NOT NULL) AS total_trueview_views
FROM adh.google_ads_impressions imp
JOIN adh.google_ads_campaign cmp USING (campaign_id)
JOIN adh.google_ads_adgroup adg USING (adgroup_id)
LEFT JOIN adh.google_ads_clicks clk ON
  imp.impression_id = clk.impression_id
WHERE
 imp.customer_id IN UNNEST(@customer_ids)
 AND adg.adgroup_type = 'VIDEO_TRUE_VIEW_IN_STREAM'
 AND cmp.advertising_channel_type = 'VIDEO'
GROUP BY 1, 2

ディスプレイ＆ビデオ 360 の視認性指標（広告申込情報ごと）

WITH
 imp_stats AS (
   SELECT
     imp.line_item_id,
     count(*) as total_imp,
     SUM(num_active_view_measurable_impression) AS num_measurable_impressions,
     SUM(num_active_view_eligible_impression) AS num_enabled_impressions
   FROM adh.dv360_youtube_impressions imp
   WHERE
     imp.line_item_id IN UNNEST(@line_item_ids)
   GROUP BY 1
 ),
 av_stats AS (
   SELECT
     imp.line_item_id,
     SUM(num_active_view_viewable_impression) AS num_viewable_impressions
   FROM adh.dv360_youtube_impressions imp
   LEFT JOIN
     adh.dv360_youtube_active_views av
     ON imp.impression_id = av.impression_id
   WHERE
     imp.line_item_id IN UNNEST(@line_item_ids)
   GROUP BY 1
 )
SELECT
 li.line_item_name,
 SUM(imp.total_imp) as num_impressions,
 SUM(imp.num_measurable_impressions) AS num_measurable_impressions,
 SUM(imp.num_enabled_impressions) AS num_enabled_impressions,
 SUM(IFNULL(av.num_viewable_impressions, 0)) AS num_viewable_impressions
FROM imp_stats as imp
LEFT JOIN av_stats AS av USING (line_item_id)
JOIN adh.dv360_youtube_lineitem li ON (imp.line_item_id = li.line_item_id)
GROUP BY 1

YouTube Reserve に関するクエリ

インプレッション実績（広告主ごと）

このクエリでは、インプレッション数と個別ユーザー数を広告主ごとに測定します。この数値は、ユーザーあたりの平均インプレッション数（広告フリークエンシー）を計算するのに役立ちます。

SELECT
  advertiser_name,
  COUNT(*) AS imp,
  COUNT(DISTINCT user_id) AS users
FROM adh.yt_reserve_impressions AS impressions
JOIN adh.yt_reserve_order order ON impressions.order_id = order.order_id
GROUP BY 1
;

広告のスキップ回数

このクエリでは、広告のスキップ回数を、顧客、キャンペーン、広告グループ、クリエイティブごとに測定します。

SELECT
  impression_data.customer_id,
  impression_data.campaign_id,
  impression_data.adgroup_id,
  impression_data.ad_group_creative_id,
  COUNTIF(label = "videoskipped") AS num_skips
FROM
  adh.google_ads_conversions
GROUP BY 1, 2, 3, 4;

一般的なクエリ

あるユーザー グループを別のユーザー グループから差し引く

このサンプルは、あるユーザー グループを別のユーザー グループから差し引く方法を示しています。この手法は幅広い応用が可能で、たとえばコンバージョンに至らなかったユーザー、視認範囲のインプレッションが発生していないユーザー、クリックが発生していないユーザーなどをカウントできます。

WITH exclude AS (
  SELECT DISTINCT user_id
  FROM adh.google_ads_impressions
  WHERE campaign_id = 123
)

SELECT
  COUNT(DISTINCT imp.user_id) -
      COUNT(DISTINCT exclude.user_id) AS users
FROM adh.google_ads_impressions imp
LEFT JOIN exclude
  USING (user_id)
WHERE imp.campaign_id = 876
;

カスタム重複チェック

このクエリは、2 つ以上のキャンペーンの重複を測定します。任意の条件をもとに重複を測定できるようカスタマイズすることも可能です。

/* For this query to run, @campaign_1 and @campaign_2 must be replaced with
actual campaign IDs. */

WITH flagged_impressions AS (
SELECT
  user_ID,
  SUM(IF(campaign_ID in UNNEST(@campaign_1), 1, 0)) AS C1_impressions,
  SUM(IF(campaign_ID in UNNEST(@campaign_2), 1, 0)) AS C2_impressions
FROM adh.cm_dt_impressions
GROUP BY user_ID

SELECT COUNTIF(C1_impressions > 0) as C1_cookie_count,
 COUNTIF(C2_impressions > 0) as C2_cookie_count,
 COUNTIF(C1_impressions > 0 and C2_impressions > 0) as overlap_cookie_count
FROM flagged_impressions
;

パートナー経由販売 - クロスセル

このクエリは、パートナー経由販売の広告枠のインプレッション数とクリックスルー数を測定します。

SELECT
  a.record_date AS record_date,
  a.line_item_id AS line_item_id,
  a.creative_id AS creative_id,
  a.ad_id AS ad_id,
  a.impressions AS impressions,
  a.click_through AS click_through,
  a.video_skipped AS video_skipped,
  b.pixel_url AS pixel_url
FROM
  (
    SELECT
      FORMAT_TIMESTAMP('%D', TIMESTAMP_MICROS(i.query_id.time_usec), 'Etc/UTC') AS record_date,
      i.line_item_id as line_item_id,
      i.creative_id as creative_id,
      i.ad_id as ad_id,
      COUNT(i.query_id) as impressions,
      COUNTIF(c.label='video_click_to_advertiser_site') AS click_through,
      COUNTIF(c.label='videoskipped') AS video_skipped
    FROM
      adh.partner_sold_cross_sell_impressions AS i
      LEFT JOIN adh.partner_sold_cross_sell_conversions AS c
        ON i.impression_id = c.impression_id
    GROUP BY
      1, 2, 3, 4
    ) AS a
    JOIN adh.partner_sold_cross_sell_creative_pixels AS b
      ON (a.ad_id = b.ad_id)
;

アプリストア インプレッション数

このクエリは、インプレッションの総数をアプリストアおよびアプリごとにグループ化してカウントします。

SELECT app_store_name, app_name, COUNT(*) AS number
FROM adh.google_ads_impressions AS imp
JOIN adh.mobile_app_info
USING (app_store_id, app_id)
WHERE imp.app_id IS NOT NULL
GROUP BY 1,2
ORDER BY 3 DESC