Google広告もGA4と同様にBigQueryにデータをエクスポートすることができる。これによりGA4と同様にSQLを使用して高度な分析が可能になる。またモニタリングレポートとしてもAPI接続よりも高速になるため単純な指標の取り出しにもSQLで抽出する意味はある。ここではBigQueryにエクスポートされたGoogle広告の基本的な指標を取得するSQLのサンプルクエリを提示したい。
GA4とのテーブル構造の違い
GA4はすべてのデータが非正規化して格納されているのに対して、Google広告のテーブルは正規化されている。つまり、データカテゴリーごとに分類されており、数十ものテーブルに格納されている点が大きな違いとなる。そのため、分析SQLではテーブルを非正規化する必要があるが、GA4ではそもそも最初からテーブルが非正規化されていたためこの処理は不要であった。対してGoogle広告では必要なデータをつなぎ合わせて非正規化する処理が必要になる。
GA4と同様にGoogleが公式に設計したスキーマであるため、特段の理由がなければGoogle広告のBigQueryへのデータ格納はGoogleが用意した転送サービスを使用することを推奨する。またこの点について考慮点を挙げると、実際のWEB広告はGoogle広告以外の媒体も活用されることが多い。そのため、Google広告だけで完結できるものではない。ではあるが、Google広告がWEB広告のメイン媒体であることは多くなるため、他のELTツールを使うにしても分析に特化した用途としての活用も使えるためBigQueryと連携しておいた方がよいだろう。
基本指標の取得：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
