本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、ウィンドウ関数（ FIRST_VALUE 関数）を活用した各ページごとの離脱数を集計するSQLクエリについて解説をおこなう。サイトに訪れたユーザーが最終的にどのページでセッションを終えているのかを数値化することで、望ましいユーザー行動が起こっているかどうかを定量的に把握することができる。
SQLクエリの概要
まず離脱数に関しては、公式ヘルプで以下のように定義されている。
離脱数は、あるページまたは画面における 1 回のセッション内で発生した、最後のイベントの回数です。離脱率は、あるページまたは画面で終了したセッションの割合です（離脱数 ÷ セッション数として計算されます）。
出典 : [GA4] 閲覧開始数と離脱数
以下のクエリサンプルでは、「最後にページビューが発生」したページを離脱ページとして定義している。そして、セッション内の最後のページビューを特定するために、 FIRST_VALUE というウィンドウ関数を活用する。 sid 単位で区切り、 event_timestamp を降順で並び替えることで、 同一セッション内で最後にページビューイベントが発生した page_location を抽出することができる。
★オススメ★
LAST_VALUE を使えばいいのでは？という疑問が当然発生するが、 FIRST_VALUEと DESC を組み合わせて使うほうが記述が少なく済むため、こちらの方法をオススメしている。
参考記事 : Window関数のLAST_VALUEの罠にハマった
セッション数の集計方法に関しては以下の記事を参考にしてほしい。
参考記事 : 日次のセッション数を集計するSQLクエリ | GA4QUERY
SQLクエリサンプル
今回のSQLサンプルは、以下のようになる。
WITH prep1 AS (
SELECT
CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS sid,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
event_timestamp,
FROM
`technogram-354804.analytics_291973511.events_intraday_202302*`
WHERE
event_name = 'page_view'
),
prep2 AS (
SELECT
sid,
page_location,
event_timestamp,
FIRST_VALUE(CONCAT(page_location, event_timestamp)) OVER(PARTITION BY sid ORDER BY event_timestamp DESC) AS exit_page_location,
FROM
prep1
)
SELECT
CASE WHEN CONCAT(page_location, event_timestamp) = exit_page_location THEN page_location ELSE NULL END AS exit_page_location,
COUNT(DISTINCT sid) AS exit_count
FROM
prep2
GROUP BY
exit_page_location
HAVING
exit_page_location IS NOT NULL
ORDER BY
exit_count DESC
;BigQueryでの集計結果
今回のSQLクエリの実行結果は、以下のようになった。
GA4（Googleアナリティクス4）探索画面での集計結果
また、GA4での探索画面での集計結果は、以下のようになった。
両者の数値は概ね一致していることがわかる。
BigQueryでの集計結果とGA4の探索画面での集計結果に、微妙な差異が発生する件については、以下の記事を参考にしていただきたい。
（参考記事）
ChatGPT（GPT-4モデル）によるクエリ解説
ChatGPTを使い、今回のSQLクエリについて解説を加えてみたい。
このSQLクエリは、指定されたデータセットから各ページの離脱回数（exit_count）をカウントし、降順に並べて表示するものです。クエリは以下のステップで構成されています。
1.WITH句を用いて、prep1という名前のサブクエリを作成します。このサブクエリでは、データセットからセッションID（sid）、ページのURL（page_location）、およびイベントのタイムスタンプ（event_timestamp）を取得しています。イベント名（event_name）が「page_view」であるレコードに限定しています。
2.prep1サブクエリの結果を用いて、prep2という名前のサブクエリを作成します。このサブクエリでは、セッションID（sid）、ページのURL（page_location）、イベントのタイムスタンプ（event_timestamp）を取得し、FIRST_VALUE関数を使って各セッションにおける最後のページのURLとタイムスタンプを連結した値（exit_page_location）を取得しています。これにより、各セッションの離脱ページを特定できます。
3.SELECT句を使って、prep2サブクエリから離脱ページのURL（exit_page_location）と離脱回数（exit_count）を取得します。各行において、ページのURLとタイムスタンプの連結が離脱ページのURLとタイムスタンプの連結と一致する場合、そのページのURLを離脱ページのURLとして取得します。それ以外の場合は、NULLを取得します。
4.離脱ページのURLでグループ化し、結果を離脱回数の降順で並べて表示します。この際、HAVING句を用いて、離脱ページのURLがNULLでないものに限定しています。
このクエリを実行することで、各ページの離脱回数を取得し、ECサイト運営者はユーザーがどのページでサイトを離れる傾向があるかを把握し、ページの改善や最適化を検討することができます。
今回は、ウィンドウ関数（ FIRST_VALUE 関数）を活用した各ページごとの離脱数を集計するSQLクエリについて解説を行った。そして、サイトに訪れたユーザーが最終的に、どのページでセッションを終えているのかを数値化することで、望ましいユーザー行動が起こっているかどうかを定量的に把握した。
また、実行結果に加え、ChatGPTでのSQL解説も加えているので、SQLクエリの学習、あるいは実際に活用する際の参考にしてみてほしい。
