🤖

セッション数、ページ遷移のクエリ例　GA4 データを BigQuery で自在に扱う

2024/12/19に公開

!この記事は、社内マニュアルとして機能することを第一の目的としています。

そのため、弊社特有の運用例が登場しますが、あくまで一例・参考ケースとしてご覧ください。

誤りや不明点、質問がありましたら、コメントでお知らせいただけると幸いです。

社内外問わず、どなたからの質問にも回答したいと思います。

 はじめにこの記事では、分析要望に対して、GA4 のどのデータを使って、どのようにクエリを書けばいいのかを具体的に解説します。
たとえば、以下のような要望に対して、データに基づいた意思決定をサポートできるようになることを目指します。
特定のページのセッション数を知りたい
ページ 1 からページ 2 の遷移率を把握したい
特定のクリックイベントが発生したユーザー数を知りたい
ランディングページが特定のページグループに属するセッションで、特定のイベントが発生した割合を知りたい
この記事を読むことで、チームメンバーが施策について議論する際に、データ分析の観点から具体的な提案ができるようになるでしょう。

 GA4 データ分析の基本GA4 データを BigQuery で分析する際の基本を理解しましょう。

 イベント駆動のデータ構造GA4 のデータは、イベント駆動で生成されます。これは、ユーザーの行動やシステムの状態変化が「イベント」として記録され、そのイベントごとにデータが生成されるということです。BigQuery にエクスポートされた GA4 データは、このイベントを基点とした構造を持っています。
ページにランディングした、クリックした、画面の 60%スクロールした。初めてサイトに訪れた、ページ 1 からセッションが始まった、など細かい動きがイベントとして一つ一つレコードに登録されていきます。
重要なフィールドは以下の通りです。

event_date: イベントが発生した日付

event_name: イベントの名前（例: page_view, click）

event_params: イベントに関連するパラメータ（例: page_location, ga_session_id）
一つ一つのイベントに対して、イベントに関する様々な情報がネスト構造でevent_paramsに格納されています。この中から、分析に必要なデータを取り出す必要があります。

 メトリクス（数値）とディメンション（属性）GA4 データを分析する際には、メトリクスとディメンションという概念を理解することが重要です。

メトリクス: 数値で表される指標（例: イベント数、セッション数、ユーザー数）

ディメンション: 分析の軸となる属性（例: page_location、ga_session_id、日付）
「ディメンション」は「データを分類するための“ものさし”」

→ データを、性別・年代・デバイス種類・ページ URL といった“分け方”で整理するための基準となるもの。

「ディメンション」は「情報を切り分ける“タグ（ラベル）”」

→ たとえば「ユーザーが使っている国名」や「閲覧しているページ名」などのラベルを使って、数値データ（メトリクス）を整理・仕分けしていくイメージ。

「ディメンション」は「データの“軸”」

→ グラフで考えると、縦軸が数値（メトリクス）で、横軸がディメンション（国、ページ、期間など）。ディメンションを変えると、同じ数値データが違った切り口で見える。
例えば、

event_name = 'page_view' のレコードから COUNT(*) で PV 数（メトリクス）を集計し、日付（ディメンション）でグループ化する

event_params から page_location（ディメンション） を取り出し、ページごとので PV 数 （メトリクス）を確認する

event_params から ga_session_id（ディメンション） を取り出し、セッション数（メトリクス）を集計する
といった具合です。

 分析の視点：点での計測と線での計測GA4 データの分析には、大きく分けて 2 つの視点があります。

点での計測: 特定の時点でのイベント数や PV 数など、単純な集計を行います。
例：特定のページの PV 数、特定のイベントの発生回数


線での計測: セッションを軸に、ユーザーの行動の流れを分析します。
例：ページ遷移、特定の条件を満たすイベントの発生率

どういうディメンションでどのメトリクスを使うのかを考えることで、SQL のクエリがなんとなーく頭に浮かぶようになれば ok!

 実践的な分析シナリオ
 1. 点での計測（イベントや PV の単純集計）すでに設定済みの、特定のイベントの発火数を知りたい場合は、

下記のように where 句で event_name を絞り込みをして、select 句で COUNT をします。
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS event_date,
  COUNT(*) AS event_count
FROM
  `<your_project>.<your_dataset>.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH))
                      AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
    AND event_name = 'event_A'
GROUP BY
  event_date
ORDER BY
  event_date
セッション内でユニークなイベントの数を取り出したい場合は、COUNT(*) AS event_count のところを、

COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS unique_session_count

に変えます。これはセッション ID が重複しているレコードを除外し、残ったレコードをカウントしています。

 2. 点での計測（ページの PV 数やセッション数の単純集計）次は特定のページ（/example-page）のセッション数を知りたい場合のクエリです。ページ単位で絞り込む時は event_params 内の key page_locationを使って絞り込みます。
WITH extracted AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    RTRIM(REGEXP_REPLACE( (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), '[?].+', '' ), '/') AS page_location, -- urlのパラメータ、末尾の/を削除
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH))
                      AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
    AND event_name = 'page_view'
)
まず with 句を使用して、url とセッションを識別する ID が展開された pv イベントデータを抽出します。

具体的には、event_paramsからpage_locationとga_session_idを抽出し、パラメータを削除する処理をextractedという名前の CTE にまとめます。CTE とはテーブルを整形してクエリ内だけで使える一時的なテーブルのことです。
extracted CTE は、以下のようなデータを出力します。（WITH 句の中の SELECT 文を実行した結果です）


event_date
page_location
ga_session_id


2024-11-24
https://example.com/page1
1732433595

2024-11-24
https://example.com/page2
1732440577

2024-11-24
https://example.com/page3
1732440577

...
...
...

このデータに対して、メインクエリでは、page_location ごとに ga_session_id の重複を除いた数をカウントすることで、各ページのセッション数を算出します。
WITH extracted AS (
    ~~
)

SELECT
  event_date,
  page_location,
  COUNT(DISTINCT ga_session_id) AS session_count
FROM
  extracted
WHERE
  page_location LIKE '%/example-page'
GROUP BY
  event_date,
  page_location
ORDER BY
  event_date;
メインクエリの出力例は以下の通りです。


event_date
page_location
session_count


2024-11-24
https://example.com/example-page
123

2024-11-25
https://example.com/example-page
150

2024-11-26
https://example.com/example-page
180

...
...
...

※ ページごとのセッション数について

ページビューはあくまで閲覧数に過ぎませんが、「ユニークなセッション数」を見れば、そのページが実際にどれほど訪問行動に組み込まれているかが明確になります。多くの PV があっても、セッションにほとんど関与していないページは実質的に読まれていないのと同じです。
クエリのポイント
CTE（WITH 句）の活用:

extracted CTE を使用して、event_params から page_location や ga_session_id を抽出する処理を事前にまとめています。
これにより、メインクエリがシンプルになり、見通しが良くなります。


正規表現でクエリパラメータ除去:

REGEXP_REPLACE 関数を使って、page_location から ? 以降のパラメータ部分を削除しています。そして RTRIM で末尾の/を削除しています。
これにより、パラメータ付きの URL を 1 つのページとして集計できます。
例：https://example.com/page/?param=123 → https://example.com/page


UNNEST の最小化:

event_params 内の値抽出は CTE 部分で行い、メインクエリでは既に整形済みの列を参照しています。
これにより、冗長な UNNEST やサブクエリ指定を回避し、クエリがよりシンプルになります。


セッション数の明確な取得方法:

ga_session_id を COUNT(DISTINCT ga_session_id) でカウントすることで、重複したセッション ID を除外し、セッション数を計算しています。


拡張性・保守性の向上:
データ抽出ロジック（UNNEST、REGEXP_REPLACE など）を CTE に集約することで、後から条件や対象カラムを増やす際も CTE 部のみ修正すればよく、クエリの保守・拡張が容易です。
例えば、別のパラメータを抽出したい場合でも、extracted CTE の SELECT 文を修正するだけで済みます。


PV 数の算出について:
このクエリではセッション数を算出していますが、extracted CTE を利用して、メインクエリの集計関数を COUNT(*) に変更することで、ページごとの PV 数を算出することも可能です。
PV 数であれば COUNT(DISTINCT ga_session_id) AS session_count のところを COUNT(*) AS pv_count に変えれば OK です。


 3. 線での計測（セッションスコープ、ページ遷移など）セッションを軸に、ユーザーがどのようなページ遷移を行ったのかを分析することも可能です。

ここでは、特定のランディングページ（例：/landing-page）から「不特定の次のページ」へ実際に遷移したセッション数を求める例を示します。
ポイントは、セッション内で発生した複数の page_view イベントを時系列で並べ、先頭がランディングページかどうか、さらに 2 ページ目以降が存在するかをチェックすることです。

 手順イメージランディングページの特定

entrance = 1 の page_view イベントが、そのセッションで最初に訪れたページビューを示します。

これにより、行番号を振る必要なく、簡潔にランディングページを特定できます。
次ページの取得

LEAD(page_location) 関数を用いて、ランディングページの次に訪れたページを取得します。

OVER(PARTITION BY ga_session_id ORDER BY event_timestamp) でセッション内のイベントを時系列順に並べ、LEAD()により次ページを参照します。
次ページへの遷移判定

ランディングページ（entrance=1）が存在し、かつ LEAD()で取得した next_page が NULLでなければ、

「不特定の次のページへ遷移した」とみなします。

これを日別・ランディングページ別・次ページ別に集計することで、各ランディングページからどのページへユーザーが流れているかを把握できます。
WITH base AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    REGEXP_REPLACE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
      r"\?.*",
      ""
    ) AS page_location,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS entrance_flag,
    event_timestamp
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH))
                      AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
    AND event_name = 'page_view'
),

-- 次ページを特定するために、セッション内で時系列順に並べて、次ページをLEAD関数で取得
numbered AS (
  SELECT
    ga_session_id,
    event_date,
    page_location,
    entrance_flag,
    event_timestamp,
    LEAD(page_location) OVER(PARTITION BY ga_session_id ORDER BY event_timestamp) AS next_page
  FROM
    base
)

SELECT
  event_date,
  page_location AS landingpage,
  next_page AS secondpage,
  COUNT(DISTINCT ga_session_id) AS session_count
FROM
  numbered
WHERE
  entrance_flag = 1        -- entrance=1がランディングページを示す
  AND next_page IS NOT NULL -- 次ページが存在するセッションのみ
GROUP BY
  event_date,
  landingpage,
  secondpage
ORDER BY
  event_date,
  session_count DESC;

解説:

base CTE で、必要な情報を抽出します。

numbered CTE で、LEAD 関数を使って次のページを取得します。
メインクエリで、ランディングページから次のページへの遷移数を集計します。

 4 正規表現で表せない複数ページのグループ分け葬儀の口コミでは市区町村のページ、葬儀社のページ、など、ページのグループ分けがあります。正規表現で表すよりも、テーブルで管理したほうがわかりやすい、クエリも書きやすい。ということでサイトのページグループを格納するテーブルが 2 つあります。
<project_id>.<dataset_id>.URL_grouping テーブル：URL とページグループの対応関係


group_id
url


1
URL1

2
URL2

3
URL3

...
...

<project_id>.<dataset_id>.group_name テーブル：グループ ID と名前の対応関係


id
group_name


1
グループ A

2
グループ B

3
グループ C

...
...

これらのテーブルを JOIN することで、URL 単位ではない、グループ単位での分析が楽になります。
まずは URL_grouping と group_name テーブルを JOIN する CTE の作成。

そして、ga4 の page_location と URL_grouping を JOIN してグループ情報を付与するクエリを作成します。

今回はページグループ A のセッション数を求めるクエリを作成します。
WITH
  URL_grouping AS (
    SELECT
      url_group.url,
      group_name.name
    FROM
      `<project_id>.<dataset_id>.URL_grouping` AS url_group
    INNER JOIN
      `<project_id>.<dataset_id>.group_name` AS group_name
    ON
      url_group.group = group_name.group
    WHERE
      group_name.name = 'ページグループA'
  ),
  extracted AS (
    SELECT
      event_date,
      RTRIM(REGEXP_REPLACE( (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), '[?].+', '' ), '/') AS page_location,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM
      `<project_id>.<dataset_id>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH))
                        AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
      AND event_name = 'page_view'
  )
SELECT
  e.event_date,
  u.name,
  COUNT(DISTINCT e.ga_session_id) AS sessions
FROM
  extracted AS e
INNER JOIN
  URL_grouping AS u
ON
  e.page_location = u.url
GROUP BY
  e.event_date,
  u.name
ORDER BY
  e.event_date;
出力例は以下の通りです。


event_date
page_location
session_count


2024-11-24
ページグループ A
23

2024-11-25
ページグループ A
25

2024-11-26
ページグループ A
28

...
...
...

解説:

URL_grouping CTE で、URL とグループ名の対応表を作成します。

extracted CTE で、GA4 のイベントデータから必要な情報を抽出します。
メインクエリで、URL_grouping と extracted を JOIN し、ページグループごとのセッション数を集計します。

 まとめGA4 データを BigQuery で分析する際に、以下のポイントを理解しておくと、様々な分析要望に対応できるようになります。

データ構造の理解: GA4 のデータはイベント駆動であり、event_name、event_date、event_params などのフィールドを適切に扱う必要があります。特に、event_params はネスト構造になっているため、UNNEST を活用して必要な情報を抽出することが重要です。

メトリクスとディメンション: 分析の軸となるディメンション（例: page_location, ga_session_id）と、集計するメトリクス（例: イベント数、セッション数）を明確に区別して考えることが、正しい分析を行うための基本となります。

点での計測と線での計測:

点での計測: 特定の時点でのイベント数や PV 数などを集計します。event_name や page_location で絞り込み、COUNT 関数を使って集計します。

線での計測: セッションを軸にユーザーの行動を分析します。ga_session_id をキーにセッション内の行動を時系列順に並べ、LEAD 関数などを用いて遷移を分析します。またランディングページの特定は、page_view イベントの entrances キーを使います。


CTE（WITH 句）の活用: 複雑なデータ抽出や加工処理は CTE にまとめておくことで、クエリの見通しが良くなり、保守性も向上します。

正規表現や関数を駆使したデータの前処理: URL から不要なパラメータを削除したり、末尾のスラッシュを削除したりするなど、データを集計しやすい形に加工する処理は、分析の精度を高めるために重要です。

外部テーブルとの JOIN: ページグループのような複雑な分類を行う場合は、別途テーブルを作成し、GA4 データと JOIN することで、より柔軟な分析が可能になります。
これらのポイントを踏まえ、記事中で紹介したクエリ例を参考にすることで、以下のような分析ができるようになります。

ページ単位の分析: page_location をディメンションとして、基本的な指標（PV 数、セッション数）を取得する。

複数ページをまとめたグループ単位での分析: URL_grouping や group_name テーブルと JOIN することで、URL のグループ単位での分析を実現する。

点での計測（単純なイベントカウント）: イベントや PV をシンプルにカウントし、ディメンションでグルーピングすることで意味を深める。

線での計測（セッション内での流れ）: セッション ID を用いてページ遷移や特定条件下のイベント発火率を算出し、ユーザーの行動フローを把握する。
これらの要素を組み合わせることで、「こういう要望があったら、GA4 のテーブルのどこを見て、どのようにクエリを組めばよさそうか」を具体的にイメージできるようになり、データに基づいた意思決定を促進することが可能になります。

 参考文献【GA4 SQL】ランディングページのセッション数を集計 

event_date	page_location	ga_session_id
2024-11-24	https://example.com/page1	1732433595
2024-11-24	https://example.com/page2	1732440577
2024-11-24	https://example.com/page3	1732440577
...	...	...

event_date	page_location	session_count
2024-11-24	https://example.com/example-page	123
2024-11-25	https://example.com/example-page	150
2024-11-26	https://example.com/example-page	180
...	...	...

event_date	page_location	session_count
2024-11-24	ページグループ A	23
2024-11-25	ページグループ A	25
2024-11-26	ページグループ A	28
...	...	...

はじめに

GA4 データ分析の基本

イベント駆動のデータ構造

メトリクス（数値）とディメンション（属性）

分析の視点：点での計測と線での計測

実践的な分析シナリオ

1. 点での計測（イベントや PV の単純集計）

2. 点での計測（ページの PV 数やセッション数の単純集計）

クエリのポイント

3. 線での計測（セッションスコープ、ページ遷移など）

手順イメージ

4 正規表現で表せない複数ページのグループ分け

まとめ

参考文献

Discussion