現状GA4にはユーザー単位であれば、経路データ探索を用いることによりデータ可視化することができるが、セッション単位で確認することはできない。また、モニタリング用途として日次で遷移数を確認したいケースがほとんどなので、指定した日付の遷移数を可視化できたとしても、それ単体では良いのか悪いのか評価することができない。そのため、単体でページ遷移数を取得したとしても、そこから何か示唆を得ることは難しい。
BigQueryを使うことでこのような問題を解決することができ、日次でページ間のセッション単位での遷移数を確認できるデータマートを作成しておくことが望ましい。
セッション単位のページAからページBへの遷移数を日次で取得するクエリ
WITH
URL_A AS (
SELECT
ymd,
MIN(event_timestamp) AS min_event_timestamp,
CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid,
FROM (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
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 '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)
WHERE
page_location = 'Page_A'
GROUP BY
ymd,
ssid
),
URL_B AS (
SELECT
ymd,
MIN(event_timestamp) AS min_event_timestamp,
CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid,
FROM (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
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 '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)
WHERE
page_location = 'Page_B'
GROUP BY
ymd,
ssid
),
URL_A_to_B AS (
SELECT
a.ymd,
a.ssid
FROM
URL_A AS a
INNER JOIN
URL_B AS b
ON
a.ymd = b.ymd
AND
a.ssid = b.ssid
AND
a.min_event_timestamp < b.min_event_timestamp
),
calc AS (
SELECT
a.ymd,
COALESCE(_ssuu_A, 0) AS _ssuu_A,
COALESCE(_ssuu_B, 0) AS _ssuu_B
FROM (
SELECT
ymd,
COUNT(DISTINCT ssid) AS _ssuu_A
FROM
URL_A
GROUP BY
ymd
) a
LEFT JOIN (
SELECT
ymd,
COUNT(DISTINCT ssid) AS _ssuu_B
FROM
URL_A_to_B
GROUP BY
ymd
) b
ON
a.ymd = b.ymd
ORDER BY
ymd ASC
)
SELECT
*
FROM
calc
クエリ解説
ランディングページからフォーム入力ページへの遷移のように、逆行があまり起こり得ないケースであれば、GA4の探索などを用いて遷移率に近いものを出すことは可能ではあるが、正確な遷移数を取得することはできない。そのため、event_timestampの値を比較することで時系列順のページ訪問ページを割り出す必要がある。
セッションの集計にあたってはeventを’page_view’でより限定的に条件を指定してもよい。その場合には以下のように記述する。
WHERE
_TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
event_name = 'page_view'「数」から「率」への変換はBIツール側で行う
上記クエリでページ遷移数を取得することができたら、BigQuery側では「率」の計算までは行わない。率を算出する際は合算値に対して計算する必要があるため、単一行で率を求めてしまうと結果の値が不正確になる。そのため、Looker StudioなどのBIツールを連携するのであれば日次の遷移数まで求めたデータマートを作成し、それをLooker Studio側で読み込み、SUM関数で行の値を合算し率を計算するのがセオリーとなる。
