BigQuery
GA4×BigQuery

【BigQuery×GA4】時系列でセッション数を出すSQLクエリ

【BigQuery×GA4】時系列でセッション数を出すSQLクエリ BigQuery
2026.01.18

本記事では、GA4におけるセッション数の考え方を整理したうえで、BigQueryにエクスポートしたGA4のデータから、年次・月次・日次・時間別でセッション数を出すSQLクエリについて紹介する。

セッション数を出すクエリの概要

前提として、GA4のセッション数は「user_pseudo_id」と「ga_session_id」を組み合わせたユニークセッションIDを、重複を除いてカウントする。

セッション数の算出方法
アナリティクスでは、ユニーク セッション ID の数を推定することで、サイトまたはアプリで発生したセッションの数を算出します。

セッション数の算出方法 – アナリティクスのセッションについて

なぜ「user_pseudo_id」と「ga_session_id」を組み合わせるのかと言うと、「ga_session_id」はユーザーが訪問したタイムスタンプをもとに生成されているため、まったく同じタイミングでアクセスがあった場合、同じ「ga_session_id」が異なる複数のユーザーに紐づいてしまう可能性があるためである。
そこで、「user_pseudo_id」と連結させることでより厳密なセッション数となるというわけだ。

セッション ID は、セッションの開始時に秒単位のタイムスタンプとして自動的に生成されます。Google アナリティクス以外の異なるセッションを分析するには、user_id または user_pseudo_id と session_id を結び付けて、各セッションの固有識別子を取得することをご検討ください。

イベントがセッション ID とセッション番号に関連付けられる仕組み – アナリティクスのセッションについて

SQLで書くと以下のようになる。

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

「ga_session_id」は、「event_params」フィールド内にネストされた状態で格納されているので、「UNNEST(event_params)」を用いて取り出す必要がある。

CONCAT関数は2つの値を連結させる関数であり、「user_pseudo_id」と「ga_session_id」をつなげる役割をもっている。

CONCAT(文字列1, 文字列2, …)

これらを「COUNT(DISTINCT ···」で、重複を除いてカウントする。

時系列でセッション数を出すSQLクエリ

年次・月次・日次・時間別ごとにSQLサンプルクエリを紹介し、結果テーブルとGA4画面上での整合性確認を行っていく。
ただし「年次セッション数」のみ、データはGoogleマーチャンダイズストアのサンプルデータセットを利用しており、GA4ではさかのぼれないほど古いデータなので、結果テーブルとGA4画面の突合ができないことだけはご了承いただきたい。

年次セッション数

SQL
SELECT
  FORMAT_DATE("%Y", DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), YEAR)) AS year,
  COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id"))) AS sessions
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
WHERE
  _table_suffix BETWEEN "20200101" AND "20211231"
GROUP BY
  year
ORDER BY
  year;

PARSE_DATE関数は、STRING型の文字をDATE型に変換するもので、STRING型であるevent_dateが第二引数にきている。
第一引数であるフォーマット文字列は、STRING型のフィールドがどのような形で年月日を記録しているかで変わのだが、event_dateフィールドは「20201101」のような形で記録されるため「”%Y%m%d”」となる。
もしこれが、「2020/11/01」のような形で記録されるフィールドであれば、「”%Y/%m/%d”」のようになる。

PARSE_DATE(フォーマット文字列, STRING型のフィールド名)

DATE_TRUNC関数は、日付を丸めるもので、小さい日付単位を大きい日付単位にまとめられる。例えば、「2020年11月10日」を月・四半期・年で丸めていくと、

  • 月(MONTH)→2020年11月1日
  • 四半期(QUARTER)→2020年10月1日
  • 年(YEAR)→2020年1月1日

となる。今回は年次セッション数なので、デイトパートは「YEAR」となり、第一引数は先ほどのPARSE_DATE関数を持ってくる。

DATE_TRUNC(DATE型の値, デイトパート)

FORMAT_DATE関数は日付の表示形式を変更するもので、人間が見やすいような文字列に置き換えられる。先ほどのDATE_TRUNC関数までの段階だと、年次（YEAR）でまとめた場合、クエリの結果テーブルには、「2020–01-01」のように表示される。
これだと2020年1月1日のセッション数だと勘違いしてしまう恐れがあるため、「2020」とするために、第一引数は「”%Y”」となる。
第二引数は、DATE_TRUNC関数までの部分をごっそり持ってくる。

FORMAT_DATE(表示形式を指定するキーワード, DATE型の値)

月次セッション数

SQL
SELECT
  FORMAT_DATE("%Y%m", DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), MONTH)) AS year_month,
  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 "20240801" AND "20241130"
GROUP BY
  year_month
ORDER BY
  year_month;

左が結果テーブル、右がGA4画面である。

「年次セッション数」のところで解説した内容を月次に置き換えるだけである。
DATE_TRUNC関数の第二引数は「MONTH」にし、FORMAT_DATE関数の第一引数は「”%Y%m”」のようにしてあげる。

FORMAT_DATE関数がなくてもセッション数としては正しい数値になるのだが、DATE_TRUNC関数だけでクエリを実行すると、日付の見た目が「2020-11-01」となり誤解が生まれる可能性があるため、「202011」となるよう使用したほうがよいだろう。

日次セッション数

SQL
SELECT
  event_date,
  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 "20241101" AND "20241110"
GROUP BY
  event_date
ORDER BY
  event_date;

左が結果テーブル、右がGA4画面である。

年次と月次では、たくさんの関数を使って日付を丸めたり、表示形式を変更したりしてきたが、日次に関してはevent_dateがもともと「20201110」のような表記であるため、そのままフィールド名である「event_date」をSELECT句で呼び出している。

時間別セッション数

SQL
SELECT
  EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE "Asia/Tokyo") AS hour,
  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_20240909` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
GROUP BY
  hour
ORDER BY
  hour;

左が結果テーブル、右がGA4画面である。

TIMESTAMP_MICROS関数は、マイクロ秒単位で記録されたUNIX時間をUTC時間に変換するものである。
ユーザーがサイト内で起こすイベントは、「event_timestamp」によりマイクロ秒単位で取得しているのだが、「event_timestamp」はUNIX時間となっているため、TIMESTAMP_MICROS関数でUTC時間に戻す作業を行っている。

TIMESTAMP_MICROS(マイクロ秒単位で記録されたUNIX時の整数)
UNIX時間やUTC時間とは？

UNIX時間は、コンピューター上での時刻表現のことで、UTC時間でいう1970年1月1日午前0時からの経過秒数で表される。
一方で、UTC時間とは「協定世界時」とも呼ばれ、世界で統一された基準時刻として使われる。UTCからマイナス9時間が日本時間となる。

「TIMESTAMP_MICROS(event_timestamp)」のあとに「AT TIME ZONE “Asia/Tokyo”」とすれば、UTC時間が日本時間となるため、日本に住んでいる我々にとっては正確な時刻となる。

EXTRACT関数は日付や時刻の一部の値を抽出するもので、今回は時間別でセッション数を知りたいので、第一引数は「HOUR」となる。
FROMのあとは、先ほどのTIMESTAMP_MICROS関数以降を持ってくる。

EXTRACT(デイトパートorパート FROM DATE型orDATETIME型の値)

BigQueryとGA4のセッション数は必ずしも一致しない

GA4では2021年10月から、セッション数のカウント効率や精度を向上させるために、「HyperLogLog++ アルゴリズム」という計算方法が用いられているのだが、BigQueryではそのようなアルゴリズムは適用されていないため、必ずしも両ツールのセッション数が合致するわけではない。

セッション数の差異
2021 年 10 月、Google アナリティクスでは、セッション数のカウントにおける効率と精度の向上、エラー率の低下を実現するため、標準レポート、カスタム レポート、データ探索ツール、Looker Studio でセッション指標の計算方法を変更しました。使用される計算方法は HyperLogLog++（HLL++）と呼ばれ、アクティブ ユーザー数やセッション数など、よく使用される指標の正確なカウントを推定します。
そのため、従来の計算方法で表示されるセッション数とはわずかな差異が生じることがあります。

セッション数の差異 – アナリティクスのセッションについて

なお、差異があった場合にどちらを信用すればよいかだが、BigQueryは生データであるため、BigQueryが正しい数値となる。

BigQuery には効率的な計算方法が適用されないため、BigQuery の計算結果と、標準レポート、カスタム レポート、データ探索ツール、Looker Studio の計算結果にはわずかな差異が生じることがあります。
データを確認する方法を選ぶ際は、次のヒントを参考にしてください。

  • 元データからより正確な結果を取得したい場合は、BigQuery で結果を確認してください。
  • より効率的に結果を取得したい場合は、標準レポート、カスタム レポート、データ探索ツール、Looker Studio で結果を確認してください。
BigQuery の検討事項 – アナリティクスのセッションについて

おわりに

本記事では、GA4のセッション数をBigQueryで算出する考え方と、BigQueryにエクスポートしたGA4のデータから時系列軸でセッション数を出すSQLクエリについて解説した。

セッション数は、サイト分析において最も使用する指標の1つだと思うので、ぜひマスターしておくとよいだろう。

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