BigQueryにエクスポートされたGA4のデータには現時点でセッションレベルでの参照元情報は含まれていない。トラフィックソースが記録されるのは最初の訪問の際に記録されるユーザーレベルとイベントレベルであるため、何かしらの手段を用いてセッションレベルでの参照元情報を取得する必要がある。
ここで提示する方法はセッションの最初に記録された参照元をSQLを用いて取得する方法であり、私自身が取り入れている方法でもあり推奨するが、必ずしもこの解決方法が唯一ではないことを断っておく。
GAの定義に基づくセッションの参照元情報の複製は困難
GAの仕様として、直接の訪問で開始されたセッションは、そのユーザーの最後に確認された参照元／メディアに関連付けられるという決まりがある。そのため、この定義に則らず集計した場合、標準レポートとの数値のズレが生じることとなる。では、このGAのルールに基づいて参照元情報を遡り取得すればよいのではあるが、現実的に非常に難解であり一筋縄ではいかないクエリが必要になる。
参照元情報を遡り取得することは技術的には無理ではないが、果たしてそこまでGA4のトラフィック ソースレポートのレプリケーションを作成することに意味があるのかを検討する必要がある。
セッションの最初に記録された参照元情報を採用する
参照元情報を遡って取得することが困難であるため、他のルールで参照元情報を取得する必要がある。ここで推奨する方法はそのセッションの流入につながった直接の参照元情報を採用するものである。またGA4では途中で参照元が変わったとしてもセッションは切り替わらないため、この点も考慮する必要がある。つまり、最初の参照元情報を採用するか、最後の参照元情報を採用するかの選択になるが、ここでは最初の参照元情報を採用した解説となる。
UA「直接セッション」と同義
そのセッションの直接の流入元となった参照元情報は、ユニバーサル アナリティクスの時代にも「直接セッション」というディメンションで存在していた。直接獲得につながった参照元情報がみたいというニーズに応えるものでもあったかと思うが、このようなディメンションがあることからも、そのセッションの直接の参照元情報を採用することになんら問題はないことが分かる。
参考：【新機能】今までのGAの参照元は何だったの？ “本当の参照元”がわかる「直接セッション」の使いどころ
セッションの直接の参照元情報を取得するクエリ
WITH
SMC_01 AS (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
),
SMC_02 AS (
SELECT
ymd,
ssid,
ARRAY_AGG(
(
CASE
WHEN REGEXP_CONTAINS(page_location, r'gclid') THEN 'google'
WHEN REGEXP_CONTAINS(page_location, r'yclid') THEN 'yahoo'
ELSE source
END
) IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS source,
ARRAY_AGG(
(
CASE
WHEN REGEXP_CONTAINS(page_location, r'gclid') THEN 'cpc'
WHEN REGEXP_CONTAINS(page_location, r'yclid') THEN 'cpc'
ELSE medium
END
) IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS medium,
ARRAY_AGG (
campaign IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS campaign,
FROM
SMC_01
GROUP BY
ymd,
ssid
),
SMC_03 AS (
SELECT
ymd,
ssid,
CONCAT(COALESCE(source, '(direct)'), ' / ', COALESCE(medium, '(none)')) AS source_medium,
COALESCE(source, '(direct)') AS source,
COALESCE(medium, '(none)') AS medium,
COALESCE(campaign, '(not set)') AS campaign,
FROM
SMC_02
),
SMC_04 AS (
SELECT
ymd,
ssid,
source_medium,
source,
medium,
CASE
WHEN medium = 'cpc' AND campaign = '(organic)' THEN '(not set)'
ELSE campaign
END AS campaign
FROM
SMC_03
ORDER BY
ymd ASC
)
SELECT
*
FROM
SMC_04
ChatGPTによるクエリ解説
セッションの直接の参照元情報を取得するにはARRAY_AGG関数を利用する。ARRAY_AGG関数は指定した列の値を集約して配列として返す関数であるが、オプション指定によりセッションの最初の参照元情報のみを返す処理を行っている。
- 条件分岐（CASE文）:
page_locationにgclidまたはyclidが含まれている場合、これらはGoogle AdsやYahoo!広告などの有料検索キャンペーンに由来するパラメータです。これらが含まれていれば、媒体を「cpc」（コストパークリック）として認識します。それ以外の場合は、そのセッションのmediumフィールドの値を使用します。- ARRAY_AGG()関数: この関数は条件分岐の結果を集約して配列に格納します。しかし、ここでの目的は配列を作ることではなく、セッションの最初のイベントでの
medium値を取得することです。そのため、event_timestampの昇順（ASC）で並べ替えて、最初に登場する値を取得します。- IGNORE NULLS: このオプションは、NULL値を無視して非NULLの値のみを集約することを指定します。これにより、何らかの理由で
mediumが欠落している場合に無視され、利用可能な最初の値が採用されます。- LIMIT 1: これは集約された値の中から最初の1つだけを取得することを指定します。この場合、最初の参照元情報だけが必要なため、セッションの開始時点の
medium値のみを抽出します。- [SAFE_OFFSET(0)]: ARRAY_AGGで作成された配列から最初の要素を安全に取得します。この操作により、配列の最初の要素（このクエリでは最初の参照元情報）が返されます。もし何らかの理由で結果が空の場合、
SAFE_OFFSETはNULLを返します。
