※2024年7月~にセッション情報のエクスポートが開始されたため、セッションの参照元情報を取得できるようになりました。
参考：session_traffic_source_last_click
参考：session_traffic_source_last_click
目次
GA定義のセッションの参照元情報の取得が可能に
2024年10月頃にBigQueryにエクスポートされるデータにsession_traffic_source_last_clickのフィールドが追加されセッションの参照元情報が含まれるようになった。これによりGA4の探索・標準レポートの指標と同一のセッションの参照元・メディアのディメンションを扱えるようになったため、単純にGA4定義のセッションの参照元情報に合わせたければsession_traffic_source_last_clickの指標を扱えばよい。
session_traffic_source_last_clickは「直接セッション」ではない
UA時代から引き続きGA4のセッションの参照元情報も「直接セッション」とはなっておらず、そのユーザーの最後に確認された参照元情報に紐づく。結論から言えば、これを気にせずGA4の標準レポート・探索で表示される指標と合わせたければ「session_traffic_source_last_click」フィールドの値を活用すればよく、コード量も簡潔になるため私自身もこちらを今後は採用していく予定である。
では、そもそも「直接セッション」とは何かという点であるが、UA後期に出てきたディメンションであり、参照元がダイレクトなど直接的に訪問してきた際に、直接的に訪問したよねというフラグを確認することができるようになったものである。これにより、ダイレクト訪問であっても参照元が例えばオーガニックに付くことはなくなり、より直接的に参照元情報の貢献度が分かりやすくなったというメリットが得られるようになった経緯がある。
そのため、個人的にも「直接セッション」で判断した方がよいと思うが、「session_traffic_source_last_click」フィールドが扱えるようになったことで、複雑な参照元情報の取得式を模索しなくとも探索の値と合致できる点から、こちらの値でも実用上問題はないので「session_traffic_source_last_click」の値で集計してもよいと考えている。ただ、このようなBigQueryにエクスポートされるスキーマ情報は過去に遡らないため、過去の「session_traffic_source_last_click」情報はNULL値となることに注意は必要。
参考：【新機能】今までのGAの参照元は何だったの？ “本当の参照元”がわかる「直接セッション」
参考：[GA4] トラフィック ソースのディメンションのスコープ
セッションの参照元情報を取得するクエリ
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20241110');
WITH
SMC_01 AS (
SELECT
ssid,
-- セッションの手動参照元
CONCAT(manual_source,' / ', manual_medium) AS manual_source_medium,
manual_source,
manual_medium,
manual_campaign,
-- セッションの参照元
CONCAT(cross_source,' / ', cross_medium) AS cross_source_medium,
cross_source,
cross_medium,
cross_campaign
FROM (
SELECT
CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
-- セッションの手動参照元
session_traffic_source_last_click.manual_campaign.source AS manual_source,
session_traffic_source_last_click.manual_campaign.medium AS manual_medium,
session_traffic_source_last_click.manual_campaign.campaign_name AS manual_campaign,
-- セッションの参照元
session_traffic_source_last_click.cross_channel_campaign.source AS cross_source,
session_traffic_source_last_click.cross_channel_campaign.medium AS cross_medium,
session_traffic_source_last_click.cross_channel_campaign.campaign_name AS cross_campaign
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN date_from() AND date_to()
-- _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
event_name = 'session_start'
)
),
-- セッションの手動参照元
Manual_SMC_02 AS (
SELECT
*
FROM (
SELECT
ssid,
manual_source_medium AS source_medium,
manual_source AS source,
manual_medium AS medium,
manual_campaign AS campaign
FROM
SMC_01
)
GROUP BY
ssid,
source_medium,
source,
medium,
campaign
),
-- セッションの参照元
Cross_SMC_02 AS (
SELECT
*
FROM (
SELECT
ssid,
cross_source_medium AS source_medium,
cross_source AS source,
cross_medium AS medium,
cross_campaign AS campaign
FROM
SMC_01
)
GROUP BY
ssid,
source_medium,
source,
medium,
campaign
)
SELECT
*
FROM
--Manual_SMC_02
Cross_SMC_02
- session_traffic_source_last_click.cross_channel_
Google広告の自動タグが優先され、探索では[セッションの参照元]と同値になる。 - session_traffic_source_last_click. manual_campaign.
UTMパラメータが優先された探索では[セッションの手動参照元]と同値になる。
TipsセッションIDはデフォルトで30分間の非アクティブ状態で切り替わり、セッション開始はsession_startイベント刻みでセッション情報を取得すれば理論上は一意のセッション情報が取得できるはずであるが、ごく稀にsession_startイベントが重複してBigQueryに計測されてしまうことがあるため、重複を排除するためにGROUP BYでまとめる処理が必要になる。
以下、過去の情報
「直接セッション」の参照元情報を取得することを試みたクエリは以下となる。100％の完成形ではないが今後、修正していくため記載する。
セッションの直接の参照元情報を取得するクエリ
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
セッションの直接の参照元情報を取得するには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を返します。
