本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、ウィンドウ関数（LAG/LEAD）を使って特定ユーザーのページ遷移を、セッションごとに抽出するSQLクエリについて解説をおこなう。LTVが高いユーザーやロイヤルカスタマーが、サイト内でどのようなページ遷移をしているのかを把握したい時などに便利なSQLクエリである。
SQLクエリの概要
まずユニークなセッションIDの抽出及びイベントパラメータの抽出方法に関しては、以下の記事を参考にしていただきたい。
参考記事 : 日次のセッション数を集計するSQLクエリ | GA4QUERY
参考記事 : UNNESTでGA4のevent_params内のパラメータを取り出すSQLクエリ | GA4QUERY
ナビゲーション関数（ウィンドウ関数）であるLAG関数とLEAD関数を用いて、前後の page_location を取得する。セッションIDでパーティション（区切り）を作成し、 event_timestamp を昇順で並べることで同一セッション内のページ遷移を時系列で取得できる。
先行する 1 つの行の value_expressionの値を返します。offsetの値を変更すると、どれだけ前の行が返されるかが変更されます。デフォルト値は 1で、ウィンドウ枠内の直前の行を示します。offset が NULL または負の値の場合はエラーになります。
後続の 1 つの行の value_expressionの値を返します。offsetの値を変更すると、どれだけ後の行が返されるかが変更されます。デフォルト値は 1で、ウィンドウ枠内の直後の行を示します。offsetが NULL または負の値の場合はエラーになります。
下記のクエリサンプルでは、 user_pseudo_id が ‘2135809971.1674633399’であるユーザーのページ遷移を抽出している。
SQLクエリサンプル
今回のSQLクエリは、以下のようになる。
WITH prep AS (
SELECT
event_timestamp,
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,
FROM
`technogram-354804.analytics_291973511.events_intraday_202302*`
WHERE
event_name = 'page_view'
AND user_pseudo_id = '2135809971.1674633399'
)
SELECT
event_timestamp,
sid,
LAG(page_location) OVER(PARTITION BY sid ORDER BY event_timestamp) AS prev_page,
page_location,
LEAD(page_location) OVER(PARTITION BY sid ORDER BY event_timestamp) AS next_page,
FROM
prep
ORDER BY
event_timestamp,
sid
;BigQueryでの集計結果
今回のSQLクエリの実行結果は、以下のようになった。
ChatGPT（GPT-4モデル）によるSQL解説
ChatGPTでの解説を加えてみよう。
このSQLクエリは、指定されたデータセットから特定のユーザー（user_pseudo_idが’2135809971.1674633399’のユーザー）に関するページビューイベントの情報を抽出し、各ページビューイベントにおける前のページ（prev_page）と次のページ（next_page）を表示するものです。クエリは以下のステップで構成されています。
1.WITH句を用いて、prepという名前のサブクエリを作成します。このサブクエリでは、データセットからイベントのタイムスタンプ（event_timestamp）、セッションID（sid）、およびページのURL（page_location）を取得しています。イベント名（event_name）が「page_view」であり、ユーザーの擬似ID（user_pseudo_id）が「2135809971.1674633399」であるレコードに限定しています。
2.SELECT句を使って、prepサブクエリからイベントのタイムスタンプ（event_timestamp）、セッションID（sid）、前のページ（prev_page）、現在のページ（page_location）、および次のページ（next_page）を取得します。前のページ（prev_page）と次のページ（next_page）は、LAG関数とLEAD関数を使って、同じセッション内での前後のページビューイベントのページのURLを取得しています。これらの関数は、OVER句を使ってセッションIDでパーティションを分け、イベントのタイムスタンプで並べ替えて適用されています。
3.最後に、結果をイベントのタイムスタンプとセッションIDで並べて表示します。
このクエリを実行することで、特定のユーザーがどのようなページ遷移を行っているかを追跡し、ユーザーの行動やサイト内での移動パターンを把握することができます。これにより、ECサイト運営者はユーザーエクスペリエンスの改善やサイト構造の最適化を検討する際に役立てることができます。
今回の記事では、ウィンドウ関数（LAG/LEAD）を使って特定ユーザーのページ遷移をセッションごとに抽出するSQLクエリについて解説を行った。
LTVが高いユーザーやロイヤルカスタマーがサイト内でどのようなページ遷移をしているのかを把握したい時などに是非活用してほしい。
