GA4

【GA4 SQL】ページ再訪問率をBigQueryから集計

2024.07.15 2024.07.27

作成したコンテンツが後日ユーザーの再訪問に貢献しているかを検証する指標を「ページ単位の再訪問率」という。GA4の指標でこのようなページ単位の再訪問率の指標はなく、探索での検証はできなくはないが手間がかかりすぎるため、より効率的に検証できるBigQueryを活用することが望ましい。

コンテンツの再訪問率はミドルファネルに位置付けられるコンテンツ評価の1つであるため、コンテンツを分析するための方法はこれが唯一の検証方法ということではなく、検証したい目的に合わせて分析手法を選択していく。

ページ再訪問率の定義
ページ単位の再訪問率を算出するクエリ
- クエリ解説
Looker Studioでページ再訪問率を可視化

ページ再訪問率の定義

ページ単位の再訪問率 = 2回目の訪問ユーザー数 / ページ単位の初回訪問ユーザー数

「ページ再訪問率」といった指標は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』の方で公開している。同一の完成形のクエリは下記参照