廣告資料中心的查詢範例

這些查詢範例需要用到 SQL 和 BigQuery 的實務知識。進一步瞭解 BigQuery 中的 SQL

Campaign Manager 360 資料移轉查詢

將 Floodlight 變數與暫存資料表進行比對

在活動資料表中比對 user_id 與 Floodlight 自訂變數,來彙整第一方資料與 Campaign Manager 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 的數量或頻率。

/* 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,縮小查詢範圍。

本例彙整 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)
;

Display & Video 360 目標對象

本例顯示如何分析 Display & Video 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
;

可視度

本例顯示如何評估 Active View Plus 可視度指標。


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
;

Campaign Manager 360 資料移轉中的動態資料

每個動態設定檔和動態饋給的曝光次數

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 中報表標籤為「紅色」的曝光次數

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 為「紅色」,且報表維度 2 為「汽車」的曝光次數

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;

Campaign Manager 360 資料移轉中的廣告格式

這些範例顯示如何判斷哪些廣告格式能提高不重複 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
;

/* 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
;

行動應用程式曝光和 _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
;

可視度

如需可視度總覽和查詢範例,請參閱「進階 Active View 指標」一文

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。可能的值:YOUTUBEYOUTUBE_TVYOUTUBE_MUSICSEARCHGMAILOTHER。「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

使用歸因模式

廣告資料中心支援 Google Ads 轉換資料表中的以數據為準歸因 (DDA) 和最終點擊歸因 (LCA) 模式。2023 年 9 月 19 日前,廣告資料中心只支援最終點擊歸因 (LCA) 模式。以下例子說明如何查看任一模式的轉換資料,以及如何使用轉換設定中繼資料表。

查看以數據為準歸因轉換

以下例子說明如何查看使用以數據為準歸因模式的轉換資料:

SELECT
  s.name
  SUM(conv.num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
  ON (conv.conversion_type = s.conversion_type_id)
WHERE s.action_optimization = 'Primary'
    AND s.attribution_model = 'DATA_DRIVEN'
GROUP BY 1;

查看最終點擊歸因轉換

如要延續舊版行為,請在查詢中加入 WHERE 子句,從結果中篩選出最終點擊歸因轉換:

SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 123
  AND conversion_attribution_model_type = 'LAST_CLICK';

依轉換名稱篩選 (使用中繼資料表)

您可以使用轉換設定中繼資料表,根據有意義的名稱 (而非數字) 來篩選轉換。

舉例來說,如果您不想依據 conversion_type 篩選轉換:

SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 291496508;

請使用 JOIN 子句,根據轉換設定中繼資料表中的欄位進行篩選:

SELECT SUM(num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
     ON (conv.conversion_type = s.conversion_type_id)
WHERE s.name = 'LTH Android Order';
SELECT s.name, SUM(conv.num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
     ON (conv.conversion_type = s.conversion_type_id)
WHERE s.conversion_category = 'PURCHASE'
  AND s.action_optimization = 'Primary'
GROUP BY 1;

YouTube 廣告連播查詢

在較長的 YouTube 觀看時間中,廣告連播會將 2 則廣告分到一個廣告插播時間點 (也就是廣告時段,但最多只有 2 則廣告)。在廣告連播中放送的廣告仍可略過。不過,如果使用者略過第一則廣告,第二則廣告也會略過。

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

Display & Video 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