作成したコンテンツが後日ユーザーの再訪問に貢献しているかを検証する指標を「ページ単位の再訪問率」という。GA4の指標でこのようなページ単位の再訪問率の指標はなく、探索での検証はできなくはないが手間がかかりすぎるため、より効率的に検証できるBigQueryを活用することが望ましい。
コンテンツの再訪問率はミドルファネルに位置付けられるコンテンツ評価の1つであるため、コンテンツを分析するための方法はこれが唯一の検証方法ということではなく、検証したい目的に合わせて分析手法を選択していく。
「ページ再訪問率」といった指標はGA4にデフォルトでは存在しない。名称からだいたいのイメージはできると思うが詳細に定義を解説する。ページ単位の再訪問率とは、ユーザーが初回訪問時に閲覧したページがトリガーとなり、後日再訪問するに貢献したページを評価する方法である。初回訪問時に閲覧したページはランディングページに限定しない。その理由としてはランディングページとして着地にしにくいページも平等に分析対象とするため。2回目の再訪問の判断はga_session_numberパラメータ値を利用し、数値が2の場合に2回目の再訪問と判断する。
ページ単位の再訪問率を算出するクエリ
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240310');
WITH
EventData AS (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
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'
),
visit_01 AS (
SELECT
ymd,
page_location,
user_pseudo_id,
COUNT(DISTINCT user_pseudo_id) OVER (PARTITION BY ymd, page_location) AS win_cnt_session_num_1
FROM
EventData
WHERE
ga_session_number = 1
GROUP BY
ymd,
page_location,
user_pseudo_id,
ga_session_number
),
visit_02 AS (
SELECT
ymd,
page_location,
user_pseudo_id,
FROM
EventData
WHERE
ga_session_number = 2
GROUP BY
ymd,
page_location,
user_pseudo_id
),
JOIN_visit_12 AS (
SELECT
v1.ymd AS ymd_1,
v1.page_location AS page_location_1,
v1.user_pseudo_id AS user_pseudo_id_1,
v1.win_cnt_session_num_1,
v2.ymd AS ymd_2,
v2.page_location AS page_location_2,
v2.user_pseudo_id AS user_pseudo_id_2,
COUNT(DISTINCT v2.user_pseudo_id) OVER (PARTITION BY v1.ymd, v1.page_location) AS win_cnt_re_user,
FROM
visit_01 AS v1
LEFT JOIN
visit_02 AS v2
ON
v1.user_pseudo_id = v2.user_pseudo_id
),
Master_01 AS (
SELECT
ymd_1 AS ymd,
page_location_1 AS page_location,
MIN(win_cnt_session_num_1) AS _first_uu,
MIN(win_cnt_re_user) AS _re_user,
FROM
JOIN_visit_12
GROUP BY
ymd_1,
page_location_1
),
Master_02 AS (
SELECT
page_location,
SUM(_first_uu) AS _first_uu,
SUM(_re_user) AS _re_user,
ROUND(SUM(_re_user) / SUM(_first_uu) * 100, 1) AS _Rate
FROM
Master_01
GROUP BY
page_location
)
SELECT
*
FROM
Master_02
クエリ解説
処理内容としては、ページごとの再訪問率を出すために日付とページごとにuser_pseudo_idでユーザーの紐付けを行っている。そのカウント方法ではウィンドウ関数を使い重複のないユーザー数を求めている。簡易的なクエリとしてはこれで問題なくページごとの再訪問率が出せるが、再訪問期間の条件指定が含まれていないので、期間ごとの再訪問率を平等に評価するためには上記のクエリに再訪問期間の条件指定も組み込む必要がある。その完成形のクエリは 『Visual SEM Report』の方で公開している。同一の完成形のクエリは下記参照