Web Analytics

GA4×BigQueryでユーザー数・セッション数を集計する方法【GA4の画面と合わない理由も解説】

酒井 裕麻株式会社Bistro 代表取締役社長

GA4のデータをBigQueryで分析するとき、最初に出したくなるのが「ユーザー数」と「セッション数」です。ところが、GA4のエクスポートデータには「セッション」という列がなく、自分でSQLを書いて数える必要があります。

この記事では、ユーザー数・新規ユーザー数・セッション数・エンゲージメント率を集計するSQLと、GA4の管理画面と数字がぴったり合わない理由を解説します。

参考記事

GA4×BigQueryのevent_paramsをUNNESTで取り出す方法【初心者向けSQL】
SQLの集計関数の使い方【SUM・AVG・COUNT・MAX・MIN/GROUP BY】
SQL CASE式の使い方【条件で値を分ける・集計をクロス表にする】

※この記事の実行結果は、GA4のエクスポートと同じ構造で作ったサンプルデータ(架空のサイト、2026年3月1日〜7日)で集計したものです。

ユーザーとセッションの数え方

指標 数え方
ユーザー数 user_pseudo_id の種類の数
新規ユーザー数 first_visit イベントがあった user_pseudo_id の数
セッション数 user_pseudo_id と ga_session_id の組み合わせの種類の数
エンゲージのあったセッション数 session_engaged = '1' のイベントを含むセッションの数
エンゲージメント率 エンゲージのあったセッション数 ÷ セッション数

ポイントは、セッション数は ga_session_id だけでは数えられないことです。ga_session_id はセッションが始まった時刻(秒)をもとにした番号なので、別々のユーザーで同じ値になることがあります。必ず user_pseudo_id とつなげてから数えます。

期間全体の数字を出す

SELECT
  COUNT(DISTINCT user_pseudo_id) AS users
  , COUNT(DISTINCT IF(event_name = 'first_visit', user_pseudo_id, NULL)) AS new_users
  , COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
  , COUNT(DISTINCT IF(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1',
      CONCAT(user_pseudo_id, '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)),
      NULL)) AS engaged_sessions
  , COUNTIF(event_name = 'page_view') AS page_views
FROM
  `プロジェクトID.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'

実行結果

users new_users sessions engaged_sessions page_views
404 404 578 426 1117

1週間で404人のユーザーが578回訪問し、1,117回ページを表示しています。1人あたり約1.43セッションです。

※サンプルデータは3月1日から始まっているため、期間内のユーザー全員が新規ユーザーになっています。実際のサイトでは、新規ユーザー数はユーザー数より少なくなります。

COUNT(DISTINCT IF(条件, 値, NULL)) は、「条件に合うときだけ値を数え、合わないときはNULL(数えない)にする」という書き方です。CASE式の記事で紹介した COUNT(CASE WHEN …) と同じ考え方です。

WITH句で読みやすくする

サブクエリが何度も出てきて読みにくいので、最初に必要な列を取り出した表を作ってから集計する書き方に変えてみます。WITH 句で、名前の付いた一時的な表(ここでは ev)を作っています。

WITH ev AS (
  SELECT
    event_date
    , event_name
    , user_pseudo_id
    , CONCAT(user_pseudo_id, '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged
  FROM
    `プロジェクトID.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
)
SELECT
  event_date
  , COUNT(DISTINCT user_pseudo_id) AS users
  , COUNT(DISTINCT IF(event_name = 'first_visit', user_pseudo_id, NULL)) AS new_users
  , COUNT(DISTINCT session_key) AS sessions
  , COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL)) AS engaged_sessions
  , ROUND(COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL))
      / COUNT(DISTINCT session_key) * 100, 1) AS engagement_rate
FROM
  ev
GROUP BY
  event_date
ORDER BY
  event_date

実行結果

event_date users new_users sessions engaged_sessions engagement_rate
20260301 58 58 80 62 77.5
20260302 81 63 89 64 71.9
20260303 86 58 87 59 67.8
20260304 85 66 89 68 76.4
20260305 79 55 83 61 73.5
20260306 83 58 87 66 75.9
20260307 61 46 63 46 73

日別のユーザー数・セッション数・エンゲージメント率が出ました。GROUP BY event_date の部分を変えれば、ほかの切り口でも集計できます。

デバイス別に集計する

event_date の代わりに device.category で分けると、デバイス別の数字になります。

SELECT
  device.category AS device_category
  , COUNT(DISTINCT user_pseudo_id) AS users
  , COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
FROM
  `プロジェクトID.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
GROUP BY
  device_category
ORDER BY
  sessions DESC

実行結果

device_category users sessions
mobile 235 336
desktop 144 210
tablet 25 32

device のような、event_params ではない列(レコード型の列)は、device.category のようにドットでつないで取り出せます。UNNESTは必要ありません。

日別の合計と期間全体の数字は一致しない

日別のユーザー数を足すと 58+81+86+85+79+83+61=533人ですが、期間全体のユーザー数は404人でした。これは間違いではありません。同じ人が別の日にも来ると、日別では毎日1人ずつ数えられ、期間全体では1人と数えられるからです。

ユーザー数やセッション数のような「重複を除いて数える」指標は、日別の数字を足して週や月の数字にしてはいけないことを覚えておきましょう。週や月の数字が必要なときは、その期間でもう一度 COUNT(DISTINCT …) で集計します。

GA4の画面と数字が合わない主な理由

BigQueryで集計した数字は、GA4の管理画面の数字と少しずれるのが普通です。主な理由は次のとおりです。

理由 内容
GA4の画面は推定値を使っている GA4の画面のユーザー数・セッション数は、計算を速くするためにHyperLogLog++という推定の方法で数えている。BigQueryの COUNT(DISTINCT) は正確な数
ユーザーの識別のしかたが違う GA4の画面は、ログインID(user_id)やGoogleシグナルなども使ってユーザーをまとめることがある。BigQueryの user_pseudo_id はCookie単位
同意モードによるモデリング Cookieの同意をしなかったユーザーの行動を、GA4の画面では推定(モデリング)して補っていることがある。BigQueryには推定分は入らない
日付の区切り BigQueryのテーブルはGA4のプロパティのタイムゾーンで日付が分かれる。event_timestamp(UTC)で日付を作ると、日本時間とずれる
データの更新タイミング 当日・前日のデータは、まだすべて書き出されていないことがある
しきい値(データの非表示) GA4の画面では、ユーザーが特定されないように、少ない数字が隠されることがある

ずれは数%程度に収まることが多いですが、「BigQueryの数字とGA4の画面の数字のどちらを正とするか」をチームで決めておくことが大切です。社内のレポートでBigQueryの数字を使う場合は、「GA4の画面とは数%ずれることがある」と注記しておくと、問い合わせを減らせます。

まとめ

  • ユーザー数は COUNT(DISTINCT user_pseudo_id)
  • セッション数は、user_pseudo_id と ga_session_id をつないだものを COUNT(DISTINCT …) で数える
  • 新規ユーザーは first_visit、エンゲージのあったセッションは session_engaged = '1' で判定する
  • 重複を除いて数える指標は、日別の数字を足して期間の数字にしない
  • GA4の画面とBigQueryの数字は、推定方法やユーザーの識別のしかたの違いで少しずれる

ユーザー数・セッション数を自分で集計できるようになると、GA4の画面では作れない切り口(会員ランク別、広告接触別など)でも、同じ指標を出せるようになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。