Google広告 × BigQueryによる基本SQL

Google広告もGA4と同様にBigQueryにデータをエクスポートすることができる。これによりGA4と同様にSQLを使用して高度な分析が可能になる。またモニタリングレポートとしてもAPI接続よりも高速になるため単純な指標の取り出しにもSQLで抽出する意味はある。ここではBigQueryにエクスポートされたGoogle広告の基本的な指標を取得するSQLのサンプルクエリを提示したい。

参考:Google 広告の転送

GA4とのテーブル構造の違い

GA4はすべてのデータが非正規化して格納されているのに対して、Google広告のテーブルは正規化されている。つまり、データカテゴリーごとに分類されており、数十ものテーブルに格納されている点が大きな違いとなる。そのため、分析SQLではテーブルを非正規化する必要があるが、GA4ではそもそも最初からテーブルが非正規化されていたためこの処理は不要であった。対してGoogle広告では必要なデータをつなぎ合わせて非正規化する処理が必要になる。

GA4と同様にGoogleが公式に設計したスキーマであるため、特段の理由がなければGoogle広告のBigQueryへのデータ格納はGoogleが用意した転送サービスを使用することを推奨する。またこの点について考慮点を挙げると、実際のWEB広告はGoogle広告以外の媒体も活用されることが多い。そのため、Google広告だけで完結できるものではない。ではあるが、Google広告がWEB広告のメイン媒体であることは多くなるため、他のELTツールを使うにしても分析に特化した用途としての活用も使えるためBigQueryと連携しておいた方がよいだろう。

参考:Google 広告レポートの変換

基本指標の取得:Imp・Click・CV

キャンペーン単位の取得

Google広告のキャンペーンの最新ステータスは`ads_Campaign`テーブルに格納されている。このテーブルを基本的には結合元として利用し、必要なデータを左結合でつなぎ合わせていくイメージとなる。最新ステータスへの限定は”_LATEST_DATE”カラムと”_DATA_DATE”を条件一致させることで最新ステータスのみを取得することが可能になる。

SELECT
  cs._DATA_DATE AS ymd,
  c.campaign_id,
  c.campaign_advertising_channel_type,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS _imp,
  SUM(cs.metrics_clicks) AS _click,
  CAST(SUM(cs.metrics_cost_micros / 1000000) AS INT64) AS _cost,
  ROUND(SUM(cs.metrics_conversions), 2) AS _allcv,
  ROUND(COALESCE(cv._cv01, 0), 2) AS _cv_TargetCV
FROM
  `<project>.<dataset>.ads_Campaig_<CUSTOMER_ID> AS c
LEFT JOIN
  `<project>.<dataset>.ads_CampaignBasicStats_<CUSTOMER_ID>` AS cs
  ON
    (
      c.campaign_id = cs.campaign_id
    AND
      cs._DATA_DATE BETWEEN DATE("2023-01-01") AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    )
LEFT JOIN (
    SELECT
      campaign_id,
      _DATA_DATE,
      SUM(metrics_conversions) AS _cv01
    FROM
      `<project>.<dataset>.ads_AdConversionStats_<CUSTOMER_ID>`
    WHERE
      segments_conversion_action_name = 'ターゲットCVアクション名称'
    GROUP BY
      campaign_id,
      _DATA_DATE
) AS cv
  ON
    (
      c.campaign_id = cv.campaign_id
    AND
      cs._DATA_DATE = cv._DATA_DATE
    )
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  ymd,
  campaign_id,
  campaign_advertising_channel_type,
  campaign_name,
  campaign_status,
  _cv01

広告グループ単位の取得

ドリルダウンして広告グループまでディメンションを切った上での指標取得。コードが長く長くなったので、こちらはCTEで記述。

WITH
  base_01 AS (
    SELECT
      c.campaign_id,
      c.campaign_advertising_channel_type,
      c.campaign_name,
      c.campaign_status,
      g.ad_group_id,
      g.ad_group_name,
      g.ad_group_status
    FROM
      `<project>.<dataset>.ads_Campaign_<CUSTOMER_ID>` AS c
    LEFT JOIN (
      SELECT
        campaign_id,
        ad_group_id,
        ad_group_name,
        ad_group_status
      FROM
        `<project>.<dataset>.ads_AdGroup_<CUSTOMER_ID>`
      WHERE
        _DATA_DATE = _LATEST_DATE  
    ) AS g
    ON
      c.campaign_id = g.campaign_id
    WHERE
      c._DATA_DATE = c._LATEST_DATE
  ),
  metrics_01 AS (
    SELECT
      gs._DATA_DATE AS ymd,
      b.campaign_id,
      b.campaign_advertising_channel_type,
      b.campaign_name,
      b.campaign_status,
      b.ad_group_id,
      b.ad_group_name,
      b.ad_group_status,
      SUM(gs.metrics_impressions) AS _imp,
      SUM(gs.metrics_clicks) AS _click,
      CAST(SUM(gs.metrics_cost_micros / 1000000) AS INT64) AS _cost,
      ROUND(SUM(gs.metrics_conversions), 2) AS _allcv
    FROM
      base_01 AS b
    LEFT JOIN
      `<project>.<dataset>.ads_AdBasicStats_<CUSTOMER_ID>` AS gs
    ON (
      b.ad_group_id = gs.ad_group_id
    AND
      gs._DATA_DATE BETWEEN DATE("2023-01-01") AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    )
    GROUP BY
      ymd,
      b.campaign_id,
      b.campaign_advertising_channel_type,
      b.campaign_name,
      b.campaign_status,
      b.ad_group_id,
      b.ad_group_name,
      b.ad_group_status
  ),
  targetConversion_01 AS (
    SELECT
      m.ymd,
      m.campaign_id,
      m.campaign_advertising_channel_type,
      m.campaign_name,
      m.campaign_status,
      m.ad_group_id,
      m.ad_group_name,
      m.ad_group_status,
      m._imp,
      m._click,
      m._cost,
      m._allcv,
      ROUND(COALESCE(cv._cv01, 0), 2) AS _cv_TargetCV
    FROM
      metrics_01 AS m
    LEFT JOIN (
      SELECT
        ad_group_id,
        _DATA_DATE,
        SUM(metrics_conversions) AS _cv01
      FROM
        `<project>.<dataset>.ads_AdGroupConversionStats_<CUSTOMER_ID>`
      WHERE
        segments_conversion_action_name = '特定のCVアクション'
      GROUP BY
        ad_group_id,
        _DATA_DATE
    ) AS cv    
    ON (
        m.ad_group_id = cv.ad_group_id
      AND
        ymd = cv._DATA_DATE
    )
  )

SELECT
 *
FROM
  targetConversion_01
ORDER BY
  ymd ASC

日付欠けに対応したSQLは以下を参考

WITH
  Base_01 AS (
    SELECT
      c.campaign_id,
      c.campaign_advertising_channel_type,
      c.campaign_name,
      c.campaign_status,
      g.ad_group_id,
      g.ad_group_name,
      g.ad_group_status
    FROM
      `<project>.<dataset>.ads_Campaign_<CUSTOMER_ID>` AS c
    LEFT JOIN (
      SELECT
        campaign_id,
        ad_group_id,
        ad_group_name,
        ad_group_status
      FROM
        `<project>.<dataset>.ads_AdGroup_<CUSTOMER_ID>`
      WHERE
        _DATA_DATE = _LATEST_DATE  
    ) AS g
    ON
      c.campaign_id = g.campaign_id
    WHERE
      c._DATA_DATE = c._LATEST_DATE
  ),
  -- 日付欠け対応
  DateRange AS (
    SELECT
      ad_group_id,
      generate_date,
      FORMAT_DATE('%A', generate_date) AS day_of_week
    FROM (
      SELECT
        ad_group_id,
        start_date,
        end_date      
      FROM (
        SELECT
          ad_group_id,
          MIN(_DATA_DATE) AS start_date,
          MAX(_DATA_DATE) AS end_date          
        FROM
          `<project>.<dataset>.ads_AdGroupBasicStats_<CUSTOMER_ID>`
        GROUP BY
          ad_group_id
      )
    ),
    UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) AS generate_date
    ORDER BY
      ad_group_id,
      generate_date ASC
  ),
  Metrics_01 AS (
    SELECT
      r.generate_date AS ymd,
      r.ad_group_id,
      r.day_of_week,
      COALESCE(SUM(gs.metrics_impressions), 0) AS _imp,
      COALESCE(SUM(gs.metrics_clicks), 0) AS _click,
      COALESCE(CAST(SUM(gs.metrics_cost_micros / 1000000) AS INT64), 0) AS _cost,
      COALESCE(ROUND(SUM(gs.metrics_conversions), 2), 0) AS _allcv
    FROM
      DateRange AS r
    LEFT JOIN
      `<project>.<dataset>.ads_AdGroupBasicStats_<CUSTOMER_ID>` AS gs
    ON (
      r.ad_group_id = gs.ad_group_id
    AND
      r.generate_date = gs._DATA_DATE
    AND
      gs._DATA_DATE BETWEEN DATE("2023-10-15") AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    )
    GROUP BY
      ymd,
      ad_group_id,
      day_of_week
  ),
  JoinMetrics_01 AS (
    SELECT
      m.ymd,
      m.day_of_week,
      b.campaign_id,
      b.campaign_advertising_channel_type,
      b.campaign_name,
      b.campaign_status,
      b.ad_group_id,
      b.ad_group_name,
      b.ad_group_status,
      m._imp,
      m._click,
      m._cost,
      m._allcv
    FROM
      Base_01 AS b
    LEFT JOIN
      Metrics_01 AS m
    ON b.ad_group_id = m.ad_group_id
  ),
  TargetCV_01 AS (
    SELECT
      m.ymd,
      m.day_of_week,
      m.campaign_id,
      m.campaign_advertising_channel_type,
      m.campaign_name,
      m.campaign_status,
      m.ad_group_id,
      m.ad_group_name,
      m.ad_group_status,
      m._imp,
      m._click,
      m._cost,
      m._allcv,
      ROUND(COALESCE(cv._cv01, 0), 2) AS _cv_TargetCV
    FROM
      JoinMetrics_01 AS m
    LEFT JOIN (
      SELECT
        ad_group_id,
        _DATA_DATE,
        SUM(metrics_conversions) AS _cv01
      FROM
        `<project>.<dataset>.ads_AdGroupConversionStats_<CUSTOMER_ID>`
      WHERE
        segments_conversion_action_name = '特定CVアクション'
      GROUP BY
        ad_group_id,
        _DATA_DATE
    ) AS cv    
    ON (
        m.ad_group_id = cv.ad_group_id
      AND
        ymd = cv._DATA_DATE
    )
  )

SELECT * FROM TargetCV_01
ORDER BY
  ad_group_id ASC, ymd ASC

広告単位の取得

広告に紐づくディメンションは多いので必要に応じて追加を行う。

WITH
  Base_01 AS (
    SELECT
      a.ad_group_ad_ad_id,
      a.ad_group_ad_ad_type,
      a.ad_group_ad_policy_summary_approval_status,
      a.ad_group_ad_status,
      c.campaign_id,
      c.campaign_name,
      c.campaign_status,
      c.campaign_advertising_channel_type,      
      g.ad_group_id,
      g.ad_group_name,
      g.ad_group_status
    FROM
      `<project>.<dataset>.ads_Ad_<CUSTOMER_ID>` AS a
    LEFT JOIN (
      SELECT
        campaign_id,
        campaign_name,
        campaign_status,
        campaign_advertising_channel_type,        
      FROM
        `<project>.<dataset>.ads_Campaign_<CUSTOMER_ID>`
      WHERE
        _DATA_DATE = _LATEST_DATE  
    ) AS c
    ON
      a.campaign_id = c.campaign_id
    LEFT JOIN (
      SELECT
        ad_group_id,
        ad_group_name,
        ad_group_status
      FROM
        `<project>.<dataset>.ads_AdGroup_<CUSTOMER_ID>`
      WHERE
        _DATA_DATE = _LATEST_DATE  
    ) AS g
    ON
      a.ad_group_id = g.ad_group_id

    WHERE
      a._DATA_DATE = a._LATEST_DATE
  ),
  -- 日付欠け対応
  DateRange AS (
    SELECT
      ad_group_ad_ad_id,
      generate_date,
      FORMAT_DATE('%A', generate_date) AS day_of_week
    FROM (
      SELECT
        ad_group_ad_ad_id,
        start_date,
        end_date      
      FROM (
        SELECT
          ad_group_ad_ad_id,
          MIN(_DATA_DATE) AS start_date,
          MAX(_DATA_DATE) AS end_date          
        FROM
          `<project>.<dataset>.ads_AdBasicStats_<CUSTOMER_ID>`
        GROUP BY
          ad_group_ad_ad_id
      )
    ),
    UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) AS generate_date
    ORDER BY
      ad_group_ad_ad_id,
      generate_date ASC
  ),
  Metrics_01 AS (
    SELECT
      r.generate_date AS ymd,
      r.ad_group_ad_ad_id,
      r.day_of_week,
      segments_ad_network_type,
      segments_device,
      segments_slot,
      COALESCE(SUM(ads.metrics_impressions), 0) AS _imp,
      COALESCE(SUM(ads.metrics_clicks), 0) AS _click,
      COALESCE(CAST(SUM(ads.metrics_cost_micros / 1000000) AS INT64), 0) AS _cost,
      COALESCE(ROUND(SUM(ads.metrics_conversions), 2), 0) AS _allcv
    FROM
      DateRange AS r
    LEFT JOIN
      `<project>.<dataset>.ads_AdBasicStats_<CUSTOMER_ID>` AS ads
    ON (
      r.ad_group_ad_ad_id = ads.ad_group_ad_ad_id
    AND
      r.generate_date = ads._DATA_DATE
    AND
      ads._DATA_DATE BETWEEN DATE("2023-10-15") AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    )
    GROUP BY
      ymd,
      ad_group_ad_ad_id,
      day_of_week,
      segments_ad_network_type,
      segments_device,
      segments_slot
  ),
  JoinMetrics_01 AS (
    SELECT
      m.ymd,
      m.day_of_week,
      b.ad_group_ad_ad_id,
      b.ad_group_ad_ad_type,
      b.ad_group_ad_policy_summary_approval_status,
      b.ad_group_ad_status,
      b.campaign_id,
      b.campaign_advertising_channel_type,
      b.campaign_name,
      b.campaign_status,
      b.ad_group_id,
      b.ad_group_name,
      b.ad_group_status,
      m.segments_ad_network_type,
      m.segments_device,
      m.segments_slot,
      m._imp,
      m._click,
      m._cost,
      m._allcv
    FROM
      Base_01 AS b
    LEFT JOIN
      Metrics_01 AS m
    ON b.ad_group_ad_ad_id = m.ad_group_ad_ad_id
  ),
  TargetCV_01 AS (
    SELECT
      m.ymd,
      m.day_of_week,
      m.ad_group_ad_ad_id,
      m.ad_group_ad_ad_type,
      m.ad_group_ad_policy_summary_approval_status,
      m.ad_group_ad_status,
      m.campaign_id,
      m.campaign_advertising_channel_type,
      m.campaign_name,
      m.campaign_status,
      m.ad_group_id,
      m.ad_group_name,
      m.ad_group_status,
      m.segments_ad_network_type,
      m.segments_device,
      m.segments_slot,
      m._imp,
      m._click,
      m._cost,
      m._allcv,
      ROUND(COALESCE(cv._cv01, 0), 2) AS _cv_TargetCV
    FROM
      JoinMetrics_01 AS m
    LEFT JOIN (
      SELECT
        ad_group_ad_ad_id,
        _DATA_DATE,
        segments_ad_network_type,
        segments_device,
        segments_slot,
        SUM(metrics_conversions) AS _cv01
      FROM
        `<project>.<dataset>.ads_AdConversionStats_<CUSTOMER_ID>`
      WHERE
        segments_conversion_action_name = 'ターゲットCVアクション名称'
      GROUP BY
        ad_group_ad_ad_id,
        _DATA_DATE,
        segments_ad_network_type,
        segments_device,
        segments_slot
    ) AS cv    
    ON (
        m.ad_group_ad_ad_id = cv.ad_group_ad_ad_id
      AND
        ymd = cv._DATA_DATE
      AND
        m.segments_ad_network_type = cv.segments_ad_network_type
      AND
        m.segments_device = cv.segments_device
      AND
        m.segments_slot = cv.segments_slot
    )
  )

SELECT
  *
FROM
  TargetCV_01

検索クエリ単位の取得

Protected Area

This content is password-protected. Please verify with a password to unlock the content.

PAGE TOP