GA4×BigQueryでユーザー数・セッション数を集計する方法【GA4の画面と合わない理由も解説】
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の画面では作れない切り口(会員ランク別、広告接触別など)でも、同じ指標を出せるようになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
