BigQuery
GA4×BigQuery

【BigQuery×GA4】ページごとの離脱率・直帰率を出すSQLクエリ

【BigQuery×GA4】ページごとの離脱率・直帰率を出すSQLクエリ BigQuery

本記事では、ページごとの離脱率・直帰率を出すSQLクエリを紹介する。

各指標の定義とカウント方法

まずは、離脱率と直帰率の定義とカウント方法について確認する。

離脱率

離脱率は以下のように求められる。

離脱率　＝　離脱数　÷　セッション数

旧GA（ユニバーサルアナリティクス）では、式の分母は“PV数”だったのだが、GA4になってからは“セッション数”になっていることに注意が必要だ。

なぜ分母がセッション数になったのかというと、“離脱数”の定義が旧GAとGA4で変わったからだ。
旧GAでは、離脱数の定義は「セッション内でそのページを最後に見た数」だったのに対して、GA4では「セッション内で最後のイベントが発生した数」を表している。つまりGA4では、“離脱”をページ閲覧単位ではなくセッションの終点として捉える設計に変わり、「そのページを含むセッションがそこで終了したかどうか」を判断の軸にしている。
そのため、分母をセッションに統一することで、離脱を体験の結果として一意に扱えるようになり、より実態に近い評価が可能となっている。

上記離脱率の式は、もともとGoogle公式ヘルプページでも言及されていた。

離脱率の定義　Google公式ヘルプ

今は少し文章が変わり、離脱率について詳しく言及されなくなってしまっているが、定義が変わったわけではないだろう。

この指標について – [GA4] 閲覧開始数と離脱数

直帰率

直帰率の定義も旧GAとは異なり、GA4では以下のように求められる。

直帰率　＝　1　-　エンゲージメント率

つまり、既存指標である「エンゲージメント率」の逆数であり、どちらかの指標があれば実質2つもいらないと言える。
そこで、本記事で述べる直帰率は、旧GAのときと同じく以下のように定義する。

直帰率　＝　直帰数　÷　セッション数

1ページの閲覧だけでサイトから離脱した割合を把握することも、ランディングページ分析においては今でも十分使えると思うので、今回はこちらの旧GAの定義の直帰率を採用したい。

ページごとの離脱率・直帰率を出すSQLクエリ

ページごとの離脱率・直帰率を出すSQLクエリは以下のようになる。

SQL
WITH vt1 AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS page_location,
    COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id"))) AS sessions
  FROM
    `analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
  WHERE
    _TABLE_SUFFIX BETWEEN "20240901" AND "20240930" -- ご自身で抽出したい期間に変更する
  GROUP BY
    page_location
),
vt2 AS (
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS page_location,
    CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id")) AS session_id
  FROM
    `analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
  WHERE
    event_name = "page_view" AND _TABLE_SUFFIX BETWEEN "20240901" AND "20240930" -- ご自身で抽出したい期間に変更する
),
vt3 AS (
  SELECT
    page_location,
    session_id,
    CASE
      WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp) = 1 THEN 1
      ELSE 0
    END AS entrance_flag,
    CASE
      WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS exit_flag,
    CASE
      WHEN COUNT(1) OVER (PARTITION BY session_id) = 1 THEN 1
      ELSE 0
    END AS bounce_flag
  FROM
    vt2
),
vt4 AS (
  SELECT
    page_location,
    SUM(entrance_flag) AS entrances,
    SUM(exit_flag) AS exits,
    SUM(bounce_flag) AS bounces
  FROM
    vt3
  GROUP BY
    page_location
)

SELECT
  vt1.page_location AS page_location,
  vt1.sessions AS sessions,
  vt4.entrances AS entrances,
  vt4.exits AS exits,
  vt4.bounces AS bounces,
  ROUND(SAFE_DIVIDE(vt4.exits, vt1.sessions), 3) AS exit_rate,
  ROUND(SAFE_DIVIDE(vt4.bounces, vt4.entrances), 3) AS bounce_rate
FROM
  vt1
JOIN
  vt4
USING
  (page_location)
ORDER BY
  sessions DESC;

クエリ結果は下のようになる。

下は、GA4で集計したときのデータである。先ほどのクエリで出した離脱率（exit_rate）は「離脱数÷セッション」で算出しており、直帰率（bounce_rate）は「直帰セッション÷閲覧開始数」で算出しているが、数値がきちんと合っている。

クエリ解説

全体の流れとしては以下のようになる。

  1. ページごとのセッション数（ユニーク訪問数）を出す（vt1）
  2. ページとセッションIDの組み合わせに、閲覧開始・離脱・直帰のフラグを立てる（vt2、vt3）
  3. ページごとに閲覧開始数・離脱数・直帰数を合計する（vt4）
  4. vt1とvt4を結合して、ページごとの離脱率・直帰率を算出する

１．ページごとのセッション数（ユニーク訪問数）を出す

(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS page_location

上は、ページを抽出するクエリ。ページは「page_location」というイベントパラメータに格納されており、「page_location」は「event_params」フィールド内の「value.string_value」カラムにあるため上記のような書き方となる。

COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id"))) AS sessions

上は、セッション数を抽出するクエリ。セッション数は、「user_pseudo_id」と「ga_session_id」を連結し、その固有数を数えることでカウントするため上記のような書き方となる。

セッション数の定義については下記の記事で詳しく解説している。

【BigQuery×GA4】時系列でセッション数を出すSQLクエリ
本記事では、GA4におけるセッション数の考え方を整理したうえで、BigQueryにエクスポートしたGA4のデータから、年次・月次・日次・時間別でセッション数を出すSQLクエリについて紹介する。セッション数を出すクエリの概要前提として、GA4...
webanalytics-tatsujin.com

２．ページとセッションIDの組み合わせに、閲覧開始・離脱・直帰のフラグを立てる

vt2で、「page_view」イベントに絞ったイベント発生時間とページ、セッションIDを抽出し、その情報をもとに、v3で閲覧開始・離脱・直帰のフラグを立てる流れとなる。

CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp) = 1 THEN 1
  ELSE 0
END AS entrance_flag

上は、閲覧開始のフラグを立てるクエリ。閲覧開始は通常、サイト訪問の一番はじめの「page_view」イベントが発生したときに計測されるため、ROW_NUMBERのウィンドウ関数を使ってセッションIDをイベント発生時間の昇順で並べたときの1行目を「1」、それ以外の行を「0」としている。

CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) = 1 THEN 1
  ELSE 0
END AS exit_flag

上は、離脱のフラグを立てるクエリ。離脱は本記事の冒頭で定義を確認したとおり、「セッション内で最後のイベントが発生した数」なので、ROW_NUMBERのウィンドウ関数を使ってセッションIDをイベント発生時間の降順で並べたときの1行目を「1」、それ以外の行を「0」としている（閲覧開始の逆順）。

CASE
  WHEN COUNT(1) OVER (PARTITION BY session_id) = 1 THEN 1
  ELSE 0
END AS bounce_flag

上は、直帰のフラグを立てるクエリ。セッションIDが1つしかない場合は1ページの閲覧のみで離脱したということだから、COUNTのウィンドウ関数を使ってセッションIDが1つしかない場合は「1」、それ以外の場合は「0」としている。

３．ページごとに閲覧開始数・離脱数・直帰数を合計する

SUM(entrance_flag) AS entrances,
SUM(exit_flag) AS exits,
SUM(bounce_flag) AS bounces

上は、ステップ2でフラグを立てた閲覧開始・離脱・直帰の「1」の数を合計している。

４．vt1とvt4を結合して、ページごとの離脱率・直帰率を算出する

SELECT
  vt1.page_location AS page_location,
  vt1.sessions AS sessions,
  vt4.entrances AS entrances,
  vt4.exits AS exits,
  vt4.bounces AS bounces,
  ROUND(SAFE_DIVIDE(vt4.exits, vt1.sessions), 3) AS exit_rate,
  ROUND(SAFE_DIVIDE(vt4.bounces, vt4.entrances), 3) AS bounce_rate
FROM
  vt1
JOIN
  vt4
USING
  (page_location)

上は、これまでのステップでつくった2つの仮想テーブル、vt1とvt4の共通カラム「page_location」をキーにして結合し、今回出したかった指標を抽出している。
具体的には、vt1からは「page_location（ページ）」・「sessions（セッション数）」を、vt4からは「entrances（閲覧開始数）」・「exits（離脱数）」・「bounces（直帰数）」を、それらを使った計算で「exit_rate（離脱率）」・「bounce_rate（直帰率）」を出している。

SAFE_DIVIDEは、分母が0のときでもエラーにならないように割り算する関数である。この関数を使えば、そういったときに「NULL」を返すようにしてくれる。
ROUNDは、値を指定した桁数で丸める関数であり、小数点第何位まで表示させたいかを決めることができる。今回は、桁数を「3」としているため、小数点第4位で四捨五入している。

おわりに

本記事では、BigQuery上でページごとの離脱率・直帰率を算出するSQLクエリを紹介した。特に直帰率については、GA4になって以降、旧GAの定義で分析することが難しくなったが、BigQueryを使えば自分の分析目的に合わせて柔軟に再現できるため非常におすすめである。記事のクエリや考え方をベースに、ぜひ自サイトに合った分析へと発展させてみてほしい。

タイトルとURLをコピーしました