GA4で日次のセッション数を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、日次のセッション数を集計するSQLクエリについて解説をおこなう。WEBアクセス解析において最も一般的な指標の1つであり、使用頻度の高い基本クエリとなるのでぜひマスターしてほしい。また、ユニバーサルアナリティクス（旧Googleアナリティクス）からセッションの定義自体が微妙に変わっているので、そちらも合わせて確認いただきたい。

SQLクエリの概要

GA4におけるセッション数は user_pseudo_id と ga_session_id を連結した文字列をユニークセッションIDとして重複を除いた数をカウントする（ session_start イベント数との比較検証結果は記事後半に記載）。

セッション数の算出方法

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

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

ga_session_id は event_params 内に格納されていてそのままでは値を抽出できないため、 UNNEST 演算子を用いて以下のようなスカラーサブクエリを利用する。

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

user_pseudo_id と ga_session_id の連結は以下のように CONCAT 関数を利用する。

CONCAT(user_pseudo_id, ga_session_id)

参考 : CONCAT関数

UAとGA4におけるセッション定義の違い

ユニバーサルアナリティクス（UA）とGA4におけるセッションの定義の違いについては公式ヘルプに以下のように記載されている。このような仕様変更により、（一般的には）GA4のセッション数はUAのセッション数より少なくなることが想定されるので注意が必要である。

出典 : [UA→GA4] 指標の比較: Google アナリティクス 4 とユニバーサルアナリティクス

SQLクエリサンプル

SQLクエリのサンプルは、以下のようになる。

WITH prep AS (
  SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202305*` 
)

SELECT
  event_date,
  COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_count
FROM
  prep
GROUP BY
  event_date
ORDER BY
  event_date
;

BigQueryでの集計結果

BigQueryの集計結果は、以下のようになった。

GA4探索画面での集計結果

GA4探索画面での集計結果は、以下のようになった。

session_startイベントをカウントするとどうなるのか？

以下はユニークなセッションIDを重複なしでカウントした場合（session_id_count）と、単純に session_start イベントを重複なしでカウントした場合（session_start_event_count）の集計結果を比較したものである。

また、両者で微妙な数値の差異があることが見て取れる。数値の絶対数が増えるとさらに乖離が増えることが想定される。そして上記のGA4探索画面における集計結果と照合すると、ユニークなセッションIDを重複なしでカウントした数値がGA4探索画面の数値と一致していることがわかる。

※今回のサンプルではセッション数の絶対数が少ないため、GA4探索画面とBigQueryの集計結果が揃っているが、数値が大きくなると両者の集計結果に乖離が発生することは避けられない可能性が高い。そちらは以下の公式ドキュメントでも記載があるのでぜひ確認してほしい。

BigQuery には効率的な計算方法が適用されないため、BigQuery の計算結果と、標準レポート、カスタムレポート、データ探索ツール、Looker Studio の計算結果にはわずかな差異が生じることがあります。

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

また、上記の集計に利用したSQLクエリも以下に記載しておく。

WITH prep1 AS (
SELECT
  event_date,
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
FROM
  `technogram-354804.analytics_291973511.events_intraday_202305*` 
),

prep2 AS (
  SELECT
    event_date,
    COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_id_count
  FROM
    prep1
  GROUP BY
    event_date
),

prep3 AS (
  SELECT
    event_date,
    COUNTIF(event_name = 'session_start') AS session_start_event_count
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202305*` 
  GROUP BY
    event_date
)

SELECT
  event_date,
  session_id_count,
  session_start_event_count
FROM
  prep2
    LEFT JOIN prep3
      USING(event_date)
ORDER BY
  event_date
;

今回の記事では、日次のセッション数を集計するSQLクエリについて解説をおこなった。日時セッション数を集計する際の参考にしてほしい。

GA4で日次のセッション数を集計するSQLクエリ | GA4QUERY