参考: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 ('20241101');
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,
session_traffic_source_last_click.cross_channel_campaign.source AS source,
session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
session_traffic_source_last_click.cross_channel_campaign.campaign_name AS campaign
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
AND
event_name = 'session_start'
),
SMC_02 AS (
SELECT
ymd,
ssid,
CONCAT(source,' / ', medium) AS source_medium,
source,
medium,
campaign
FROM
SMC_01
),
SMC_03 AS (
SELECT
source_medium,
source,
medium,
campaign,
COUNT(DISTINCT ssid) AS _ssuu
FROM
SMC_02
GROUP BY
source_medium,
source,
medium,
campaign
)
SELECT
*
FROM
SMC_03
以下、過去の情報
「直接セッション」の参照元情報を取得することを試みたクエリは以下となる。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を返します。