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

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

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

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

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

[GA4] アナリティクスのセッションについて

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 ···」で、重複を除いてカウントするばセッション数になるというわけだ。

なお、「user_pseudo_id」と「ga_session_id」を連結させる必要がある理由は、「ga_session_id」はユーザーが訪問したタイムスタンプを元に生成されているため、まったく同じタイミングでアクセスがあった場合、同じ「ga_session_id」が異なる複数のユーザーに紐づいてしまう可能性があるためである。
「user_pseudo_id」を連結すれば、ユニークセッションIDとなる。

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

[GA4] アナリティクスのセッションについて

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

以下より、実際に私が運営しているサイトのデータセットを使ってサンプルクエリ・結果テーブル・GA4画面との整合性確認を行っていく。
ただし、BigQueryにエクスポートされているデータ期間は2024年8月27日～11月26日までなので（3か月間の無料トライアル期間にエクスポートしたため）、「年次セッション数」に限ってはGoogleマーチャンダイズストアのサンプルデータセットを利用している。

なお、Googleマーチャンダイズストアのサンプルデータセットも、エクスポートされている期間は2020年11月1日～2021年1月31日までの3か月間であり、その期間のデータはGA4デモアカウントでは見ることができないので、クエリの結果テーブルとGA4画面との整合性確認ができない点だけご了承いただきたい。

年次セッション数

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_*`
where _table_suffix between "20200101" and "20211231"
group by year
order by year;

以下はクエリの結果テーブルである。

parse_date関数は、STRING型の文字をDATE型に変換するもので、今回の場合はSTRING型であるevent_dateが第二引数にきている。

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

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

date_trunc関数は、日付を丸めるもので、小さい日付単位を大きい日付単位にまとめることができる。

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

例えば「2020年11月10日」という日単位まで明確な日付を、月・四半期・年で丸めていくと、

となる。

今回は年次セッション数なので、デイトパートは「year」となり、第一引数は先ほどSTRING型からDATE型にしたpurse_date関数を持ってくる。

format_date関数は日付の表示形式を変更するもので、人間が見やすいような文字列に置き換えるものである。

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

先ほどのdate_trunc関数までの段階だと、年次（year）でまとめた場合、クエリの結果テーブルには、「2020–01-01」のように表示される。
これだと2020年1月1日のセッション数だと勘違いしてしまう恐れがあるため、「2020」とするために、第一引数は「“%Y”」としている。

第二引数は、date_trunc関数までの部分をごっそり持ってくる。

月次セッション数

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_*`
where _table_suffix between "20240801" and "20241130"
group by year_month
order by year_month;

以下はクエリの結果テーブルとGA4で見た画面である。
数値に若干差異があるが、これはGA4の仕様によるものであり、詳しくは「BigQueryとGA4のセッション数は必ずしも一致しない！」で解説している。

クエリは「年次セッション数」のところで解説した内容を月次に置き換えるだけである。

date_trunc関数の第二引数は「month」にし、format_date関数の第一引数は「“%Y%m”」のようにしてあげる。

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

日次セッション数

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_*`
where _table_suffix between "20241101" and "20241110"
group by event_date
order by event_date;

以下はクエリの結果テーブルとGA4で見た画面である。

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

時間別セッション数

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`
group by hour
order by hour;

以下はクエリの結果テーブルとGA4で見た画面である。
GA4では、時間の昇順で並び替えようとするとおかしな順番になるため見づらいが、数値は合っている。

timestamp_micros関数は、マイクロ秒単位で記録されたUNIX時間をUTC時間に変換するものである。

timestamp_micros(マイクロ秒単位で記録されたUNIX時の整数)

ユーザーがサイト内で起こすイベントは、「event_timestamp」によりマイクロ秒単位で取得しているのだが、「event_timestamp」はUNIX時間となっているため、timestamp_micros関数でUTC時間に戻す作業を行っている。
「timestamp_micros(event_timestamp)」のあとに「at time zone “Asia/Tokyo”」とすれば、UTC時間が日本時間となるため、日本に住んでいる我々にとっては正確な時刻となる。

extract関数は、日付や時刻の一部の値を抽出するもので、以下が構文となる。

extract(デイトパートorパート from DATE型orDATETIME型の値)

今回は時間別でセッション数を知りたいので、パートは「hour」、fromのあとにつづくのは、先ほどのtimestamp_micros関数以降を持ってくる。

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

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

なお、差異があった場合にどちらを信用すればよいかだが、BigQueryが精緻となる。

データを確認する方法を選ぶ際は、次のヒントを参考にしてください。

• 元データからより正確な結果を取得したい場合は、BigQuery で結果を確認してください。
• より効率的に結果を取得したい場合は、標準レポート、カスタム レポート、データ探索ツール、Looker Studio で結果を確認してください。

[GA4] アナリティクスのセッションについて

おわりに

本記事では、BigQueryにエクスポートしたGA4のデータから時系列軸でセッション数を出すSQLクエリについて解説した。セッション数は、サイト分析において最も使用する指標のうちの1つだと思うので、ぜひマスターしておくとよいだろう。