かつてのユニバーサルアナリティクスには「ナビゲーション サマリー」というレポートがあり、指定したページの「前のページ」と「後のページ」を確認することができた。GA4では同様のレポートはデフォルトでは用意されておらず、探索を利用しても同じようなレポートを作成することはできない。「前のページ」などは探索で「ページの参照元 URL」のディメンションを使うことで確認することはできるが、都度のモニタリング用途としては不向きであり、分析用途としても現在のページを素早く切り替えていくことができないため利便性に欠ける。そもそもアドホック的に探索は利用するもので定常的に閲覧するレポートとしては適さない。
UA：ナビゲーション サマリー
しかし、このようなGA4のデフォルトで備わっている制限もBigQueryとLookerStudioを組み合わせて活用することで「ナビゲーション サマリー」レポートと同様、それ以上の機能を持つレポートを作成することが可能になる。
探索で「前のページ」を確認する
ディメンションには、ページロケーションとページの参照元URLを選択し、これだけでは外部ドメインからの流入も対象となってしまうので、フィルターにページの参照元URLに自身のドメインを設定する。ページロケーションには検証したいページのURLを指定する。
このようにすれば、指定したページの「前のページ」は調査することが可能ではあるが、都度検証したいページを指定する必要があるなど手間がかかる。
BigQueryで「ナビゲーション サマリー」を再現
BigQueryを活用することで、UAのナビゲーションサマリーを再現することが可能になる。以下のレポートはPCからは実際に操作が可能。レポートの見方は真ん中が現在のページで左が前のページ、右が次のページとなる。
ページ遷移をBigQueryから取得する問題点
このようなページ遷移をBigQueryから取得する方法としてよく見られるのが、page_locationをevent_timestamp順に並べてLAG関数とLEAD関数を用いて前後のページを取得するというものが散見される。しかし、この方法では同一ページ内で’page_view’イベントが付いてしまうと、それがページの前後ページ対象として集計されてしまうため、ページの前後ページを確認したい意図通りにならない不完全なデータが混ざってしまう問題が生じる。大まかに概要を把握できればよいのであれば問題ないが、より正確にページ前後のページを集計するにはクエリに人手間加える必要がある。
再帰CTEによる前後のページを取得するクエリ
再帰CTEによるSQLで正確にページの前後ページを集計している。例えばページAの中で’page_view’が何回付いたとしても、遷移先のページが見つかるか、離脱するかまで処理を続け前後ページを取得することが可能になる。再帰CTEの解説は別記事でもしている。ページ前後を取得する方法は1つではなく、レポートでは他のロジックで前後のページを取得している。再帰CTEでは再帰数がデフォルトでは上限が500であるため、この上限を超えるとエラーとなる。
GA4のページ遷移の取得に以下のようなLEAD関数が用いられることが多く、この方法でユーザーが遷移した2ページ目を取得することが可能になる。
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
WITH RECURSIVE
SessionData AS (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
LEAD(page_location) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) AS next_page_location,
LEAD(event_timestamp) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) AS next_event_timestamp,
1 AS depth
FROM (
SELECT
CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
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 = 'page_view'
)
),
RecursivePages AS (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
next_page_location,
next_event_timestamp,
depth
FROM
SessionData
UNION ALL
SELECT
rp.ssid,
rp.ymd,
rp.event_timestamp,
rp.page_location,
sd.next_page_location,
sd.next_event_timestamp,
rp.depth + 1
FROM
RecursivePages rp
INNER JOIN
SessionData sd
ON
rp.ssid = sd.ssid
AND
rp.ymd = sd.ymd
AND
rp.next_event_timestamp = sd.event_timestamp
WHERE
rp.page_location = rp.next_page_location
),
Master_01 AS (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
CASE
WHEN next_page_location IS NULL THEN '(Exit)'
ELSE next_page_location
END AS final_next_page
FROM
RecursivePages
WHERE
page_location != next_page_location
OR
next_page_location IS NULL
ORDER BY
ssid,
event_timestamp
),
PageRemove_01 AS (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
final_next_page,
LAG(page_location) OVER (PARTITION BY ssid ORDER BY event_timestamp) AS prev_page_location
FROM
Master_01
ORDER BY
ssid,
event_timestamp
),
PageRemove_02 AS (
SELECT
*,
CASE
WHEN page_location = prev_page_location THEN 'remove'
ELSE 'keep'
END AS removal_flag
FROM
PageRemove_01
ORDER BY
ssid,
event_timestamp
),
PageRemove_03 AS (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
final_next_page,
COALESCE(prev_page_location, '(entrance)') AS prev_page_location
FROM
PageRemove_02
WHERE
removal_flag = 'keep'
)
SELECT
*
FROM
PageRemove_03
