GA4で特定URLの前後のページ遷移数を集計するSQLクエリ

本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、特定のURLにおける前後のページ遷移パターンについて集計をおこなうSQLクエリについて解説をおこなう。

トップページやコンバージョンが多く発生する重要なURLにおいて、実際にどのようなページ遷移が前後で発生しているのかを定量化することで、より具体的にサイトの課題や改善点を把握することができる使い勝手の良いSQLクエリである。

SQLクエリの概要

まず、ユニークなセッションIDの抽出及びイベントパラメータの抽出方法に関しては、以下の記事を参考にしていただきたい。

参考記事 : UNNESTでGA4のevent_params内のパラメータを取り出すSQLクエリ | GA4QUERY

最初に、ナビゲーション関数（ウィンドウ関数）であるLAG関数とLEAD関数を用いて、前後の page_location を取得する。セッションIDでパーティション（区切り）を作成し、 event_timestamp を昇順で並べることで同一セッション内のページ遷移を時系列で取得できる。

LAG関数

先行する 1 つの行の value_expressionの値を返します。offsetの値を変更すると、どれだけ前の行が返されるかが変更されます。デフォルト値は 1で、ウィンドウ枠内の直前の行を示します。offset が NULL または負の値の場合はエラーになります。

LEAD関数

後続の 1 つの行の value_expressionの値を返します。offsetの値を変更すると、どれだけ後の行が返されるかが変更されます。デフォルト値は 1で、ウィンドウ枠内の直後の行を示します。offsetが NULL または負の値の場合はエラーになります。

以下のSQLクエリサンプルでは、 ‘https://www.technogram.co.jp/’ の前後のページ遷移について集計をおこなっている。

具体的には、「前のページ = prev_page」「現在のページ = page_location」「次のページ = next_page」を1つのセットとして、その組み合わせごとにセッション数をカウントすることで、どのような組み合わせ = 前後のページ遷移が多く発生しているかを集計している。

「前のページ = prev_page」が存在しない場合は、現在のページがランディングページということになるため、 prev_page には ( Landing… ) という値が入るようになっている。

また、「次のページ = next_page」が存在しない場合は、現在のページでセッションが終了 = 離脱ということなるため、 next_page には ( Exit… ) という値が入るようになっている。

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
    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
    prep1
)

SELECT
  IFNULL(prev_page, '( Landing... )') AS prev_page,
  page_location,
  IFNULL(next_page, '( Exit... )') AS next_page,
  COUNT(DISTINCT sid) AS session_count,
FROM
  prep2
WHERE
  page_location = 'https://www.technogram.co.jp/'
GROUP BY
  prev_page,
  page_location,
  next_page
HAVING
  page_location NOT IN (prev_page, next_page)
ORDER BY
  session_count DESC
;

BigQueryでの集計結果

今回のSQLクエリの結果は、以下のようになった。

ChatGPT（GPT-4モデル）によるSQL解説

今回もChatGPTによる、解説をつけてみよう。

このSQLクエリは、指定されたデータセットからページビューイベントの情報を抽出し、特定のページ（https://www.technogram.co.jp/）への遷移元ページ（prev_page）と遷移先ページ（next_page）を分析し、セッション数（session_count）をカウントするものです。クエリは以下のステップで構成されています。

1.WITH句を用いて、prep1という名前のサブクエリを作成します。このサブクエリでは、データセットからセッションID（sid）とページのURL（page_location）を取得しています。イベント名（event_name）が「page_view」であるレコードに限定しています。

2.次に、prep2という名前のサブクエリを作成します。このサブクエリでは、prep1からイベントのタイムスタンプ（event_timestamp）、セッションID（sid）、前のページ（prev_page）、現在のページ（page_location）、および次のページ（next_page）を取得します。前のページ（prev_page）と次のページ（next_page）は、LAG関数とLEAD関数を使って、同じセッション内での前後のページビューイベントのページのURLを取得しています。これらの関数は、OVER句を使ってセッションIDでパーティションを分け、イベントのタイムスタンプで並べ替えて適用されています。

3.最後に、SELECT句を使って、prep2から遷移元ページ（prev_page）、現在のページ（page_location）、および遷移先ページ（next_page）を取得し、セッション数（session_count）をカウントします。遷移元ページと遷移先ページが存在しない場合は、「( Landing… )」と「( Exit… )」という文字列を表示します。現在のページが’https://www.technogram.co.jp/‘であるレコードに限定しています。

4.GROUP BY句を使って、遷移元ページ（prev_page）、現在のページ（page_location）、および遷移先ページ（next_page）でグループ化します。さらに、HAVING句を使って、現在のページが遷移元ページや遷移先ページと異なるレコードに限定しています。

5.最後に、結果をセッション数（session_count）の降順で並べて表示します。

このクエリを実行することで、特定のページに対する遷移パターンを分析し、どのようなページから訪問され、どのようなページに遷移しているかを把握することができます。これにより、ウェブサイトの運営者はユーザーエクスペリエンスの改善やサイト構造の最適化を検討する際に役立てることができます。

また、特定のページへのアクセス元や遷移先ページの情報を分析することで、ウェブサイト内のユーザーの行動や移動パターンを把握し、コンバージョン率の向上や離脱率の低減につながる改善策を立案することができます。

今回の記事では、特定のURLにおける前後のページ遷移パターンについて集計をおこなうSQLクエリについて解説を行った。

重要なURLから、より具体的にサイトの課題や改善点を把握する際に活用してほしい。

GA4で特定URLの前後のページ遷移数を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリサンプル

BigQueryでの集計結果

ChatGPT（GPT-4モデル）によるSQL解説