GA4 のデータを BigQuery に出していて、集計の SQL（データベースからデータを取り出すための命令文）を ChatGPT や Claude に書かせ始めた方向けの記事です。AI に書かせた SQL の数字がどれだけずれるかを公開サンプルで実際に確かめ、自分のデータで検算する手順まで紹介します。
この記事で分かること
- 質問文だけから書いた GA4 の SQL は、エラーなく動いても数字が公式の定義とずれることがある
- 3 つの質問（セッション数・ユーザー数・購入したセッションの割合）のうち、どれがずれて、なぜずれたか
- 自分のデータで AI の SQL を検算するときの手順とチェックリスト
ざっくり言うと：SQL は動くが、数字が定義とずれることがある
AI に SQL を書かせると、たいていはエラーなく実行でき、それらしい数字が返ってきます。ただし「エラーが出ない」ことと「数字が定義どおり」であることは別の話です。GA4 のエクスポートには、セッションの数え方などに独特の決まりがあるためです。
筆者が公開サンプルで試したところ、3 問のうち 2 問で数字がずれました。ずれ幅は 1.3% と小さめですが、月次の報告や前月比の判断に使うなら見過ごせない大きさです。
| 質問 | AI の SQL | 検算用 SQL | 判定 |
|---|---|---|---|
| 1 日のセッション数 | 2,812 | 2,849 | ずれた（37 少ない） |
| 1 日のユーザー数 | 2,546 | 2,546 | 一致した |
| 購入したセッションの割合 | 0.640% | 0.632% | ずれた（高めに出た） |
この表の数字は、筆者が 2026-11-05 に bq query（BigQuery をコマンドで操作する道具）で実行した結果です。以下、どう調べたかを順に説明します。
使うデータと 3 つの質問
データ：GA4 の公開サンプル（1 日分）
使ったのは、Google が誰でも使えるように公開している ga4_obfuscated_sample_ecommerce（GA4 の e コマースのサンプルデータ）です。Google Merchandise Store（Google のグッズを売る通販サイト）の、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月分のイベントが入っています（Google for Developers）。
今回はそのうち最終日のテーブル events_20210131 だけを使います。行数は 26,489 で、筆者が bq show（テーブルの情報を見るコマンド）で確認しました。公開データなので、読者の方も同じ SQL で同じ数字を出せます。
料金：このサイズなら無料枠に収まる
BigQuery のオンデマンド料金（使った分だけ払う料金）は、クエリ（BigQuery に送る SQL の命令）が処理したデータ量で決まります。処理量は毎月 1 TiB（テビバイト。約 1 兆 1 千億バイト）まで無料です（Google Cloud の料金ページ）。
今回の 2 本の SQL は、dry run（実行せずに処理量だけを見積もる機能）でどちらも 5,798,375 バイト（約 5.8MB）でした。無料枠のおよそ 0.0005% です。
3 つの質問
AI に渡したのは、次の 3 つの質問の中身だけです。テーブルの構造（スキーマ）や GA4 の公式の定義は渡していません。
- 2021 年 1 月 31 日のセッション数はいくつか
- 同じ日のユーザー数はいくつか
- 同じ日に購入があったセッションの割合はいくつか
先にお断りしておくと、今回の「AI の答え」は ChatGPT などの画面で得た回答ではありません。この記事の作成で調査を担当した AI に、質問文だけを手がかりに書かせた SQL を 1 本だけ使っています。
プロンプト（AI に渡す指示文）の原文は保存していないため、ここでは質問の中身だけを示します。特定の AI サービスの実力を測る記事ではなく、「質問文だけで書いた SQL にどんな落とし穴があるか」を示す例として読んでください。
AI に書かせた SQL と結果
AI が書いた SQL は次のとおりです（筆者の手元の analysis/ai.sql）。
WITH e AS (
SELECT user_pseudo_id, event_name,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS sid
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`)
SELECT
COUNT(DISTINCT sid) AS sessions,
COUNT(DISTINCT user_pseudo_id) AS users,
SAFE_DIVIDE(COUNT(DISTINCT IF(event_name='purchase', sid, NULL)), COUNT(DISTINCT sid)) AS purchase_session_rate,
COUNTIF(event_name='purchase') AS purchase_events
FROM eここで出てくる用語を先に説明します。
| 用語 | 意味 |
|---|---|
| event_params | イベントに付く追加情報の入れ物。キー（項目名）ごとに 1 行ずつ繰り返される入れ子の形になっている |
| UNNEST | 入れ子になった値を、表の行として取り出す BigQuery の書き方 |
| ga_session_id | セッション ID。セッションが始まった時刻を秒単位で表した数字 |
| user_pseudo_id | ユーザーがサイトに初めてアクセスしたときなどに割り当てられる仮名の ID |
event_params が繰り返しの形であることは、GA4 の公式ヘルプのスキーマ（テーブルの構造の説明）に書かれています（BigQuery Export のスキーマ）。ga_session_id が秒単位のタイムスタンプであることも公式の説明です（GA4 のセッションについて）。
この SQL を実行した結果は、セッション数 2,812、ユーザー数 2,546、購入したセッションの割合 0.640% でした。エラーは出ず、見た目にはもっともらしい数字です。
定義どおりの検算用 SQL と結果
次に、公式の定義に沿って書いた検算用の SQL です（筆者の手元の analysis/ref.sql から、記事で使う列だけを残しました）。
WITH e AS (
SELECT user_pseudo_id, event_name,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS sid,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') AS eng_ms
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`),
s AS (
SELECT CONCAT(user_pseudo_id, '-', CAST(sid AS STRING)) AS skey, event_name
FROM e WHERE sid IS NOT NULL)
SELECT
(SELECT COUNT(DISTINCT skey) FROM s) AS sessions,
(SELECT COUNT(DISTINCT user_pseudo_id) FROM e) AS total_users,
(SELECT COUNT(DISTINCT user_pseudo_id) FROM e
WHERE eng_ms > 0 OR event_name = 'first_visit') AS active_users,
(SELECT COUNT(DISTINCT IF(event_name='purchase', skey, NULL)) FROM s) AS purchase_sessions,
(SELECT COUNT(DISTINCT IF(event_name='purchase', skey, NULL)) FROM s)
/ (SELECT COUNT(DISTINCT skey) FROM s) AS purchase_session_rate,
(SELECT COUNTIF(event_name='session_start') FROM e) AS session_start_events違いは、セッションを user_pseudo_id と ga_session_id の組 で数えている点です。公式ヘルプは、セッションを 1 つずつ見分けるには、ga_session_id を user_id（サイト側でログインした人に付ける ID）または user_pseudo_id と結び付けるよう勧めています（GA4 のセッションについて）。
ユーザー数は 2 種類を出しました。GA4 には「合計ユーザー数」と「アクティブ ユーザー数」という別々の指標があるためです（ユーザー指標の説明）。アクティブ ユーザーの条件は、公式の定義を SQL で近似したもので、GA4 の画面の数字とは照らし合わせていません。
2 本の SQL の検算結果
図 1 のとおり、セッション数だけが目に見えてずれ、ユーザー数は同じ値になりました。購入したセッションの割合は値が小さいので、1 万セッションあたりの件数に直して図 2 にしています。
| 指標 | AI の SQL | 検算用 SQL | 差 |
|---|---|---|---|
| セッション数 | 2,812 | 2,849 | 37（検算用の 1.3%） |
| 合計ユーザー数 | 2,546 | 2,546 | 0 |
| アクティブ ユーザー数（近似） | 出していない | 2,459 | 合計ユーザーより 87 少ない |
| 購入があったセッション | 18 | 18 | 0 |
| 購入したセッションの割合 | 0.640% | 0.632% | 0.008 ポイント |
| 同（1 万セッションあたり） | 64 | 63.2 | 0.8 |
差と割合は、筆者が実行結果から計算しました。AI の SQL の購入セッション数は、0.640% に 2,812 を掛けて逆算した値（18）です。
ずれの原因（3 問それぞれ）
図 3 は、3 問それぞれで何が起きて、どう直すかをまとめたものです。順に詳しく見ていきます。
問 1：セッション数は「ID だけ」で数えたので少なく出た
AI の SQL は ga_session_id だけを数えていました。ところが ga_session_id は「セッションが始まった秒」なので、同じ秒に始まった別の人のセッションは同じ番号になります。筆者が調べると、同じ ga_session_id を最大 3 人の user_pseudo_id が持っていました。
別の人のセッション 2 つ
user_pseudo_id = A, ga_session_id = 1612051200
user_pseudo_id = B, ga_session_id = 1612051200
↓ ga_session_id だけで数える
1 セッション（2 つが 1 つにまとまる）
↓ user_pseudo_id と組にして数える
2 セッション（正しく分かれる）上の ID の値は説明用の例です。この「まとまってしまう」分が合計 37 セッションあり、AI の SQL は 2,812、検算用は 2,849 になりました。
問 2：ユーザー数は一致したが、比べる指標で差が出る
ユーザー数は、AI の SQL も検算用も user_pseudo_id の重複を除いて数えたので、同じ 2,546 でした。これは GA4 の「合計ユーザー数」（期間中にサイトに来た人の合計）に当たります。
一方、GA4 には「アクティブ ユーザー数」（実際にサイトを利用した人の数）という別の指標があります（ユーザー指標の説明）。近似の条件で数えると 2,459 で、合計ユーザーより 87 少なくなりました。AI の SQL が正しくても、報告先がアクティブ ユーザーを見ているなら 3.4% の食い違いになります。
問 3：購入率は分母のずれがそのまま伝わった
公式ヘルプでは、セッションのキーイベント率（購入などの成果が出たセッションの割合）を「キーイベントが発生したセッションの数 ÷ セッションの総数」と定義しています（キーイベント率の説明）。購入したセッションの割合も同じ考え方で計算できます。
分子の「購入があったセッション」は、AI の SQL も検算用も 18 でした。違ったのは分母で、AI の SQL は問 1 と同じく 2,812 と少なく数えています。
分母が小さいぶん割合が大きくなり、0.640% と高めに出ました。つまり、問 1 を直せば問 3 も直ります。
検算の手順（自分のデータで試すときのチェックリスト）
図 4 の流れを、作業の手順に直すと次のようになります。
- 知りたいことを 1 文の質問にする
- AI に SQL を書かせて実行し、出た数字を控える
- 使っている指標の定義を、GA4 の公式ヘルプで確認する
- 定義どおりの検算用 SQL を自分で書き、同じテーブル・同じ日付で実行する
- 2 つの数字を並べて比べる
- ずれたら、下のチェックリストで原因を探し、直した SQL でもう一度比べる
| 確認すること | 見る場所 | 今回の結果 |
|---|---|---|
| セッションを user_pseudo_id と ga_session_id の組で数えているか | COUNT(DISTINCT …) の中身 | AI の SQL は ID だけだった |
| 割合の分母は「セッション数」で、分子は「成果のあったセッション数」か | 割り算の上と下 | 分子は正しく、分母が少なかった |
| 「ユーザー数」が合計ユーザーかアクティブ ユーザーか | 比べる相手の指標名 | 合計ユーザーで一致した |
| event_params を取り出すときに行が増えていないか | UNNEST の書き方 | 今回は増えていない |
| テーブルの日付と範囲は合っているか | FROM のテーブル名 | 2 本とも同じ 1 日分 |
つまずきやすい点
session_start の件数をセッション数にしてしまう
セッションが始まると session_start イベントが記録されます（GA4 のセッションについて）。そのため「session_start を数えればセッション数になる」と考えがちです。
ところが今回のテーブルでは、session_start は 2,760 件で、検算用のセッション数 2,849 より 89 少なくなりました。差が出たのは、前日から続いているセッションの session_start が、その日のテーブルには記録されていないためと考えられます。
購入イベントの件数で割ってしまう
purchase イベントは 19 件ありましたが、購入があったセッションは 18 でした。1 つのセッションで購入が 2 回以上記録された例があったと考えられます。イベント数をセッション数で割ると 0.667% となり、定義どおりの 0.632% より大きく出ます（図 2 の右端の棒）。
今回の AI の SQL はこの誤りを犯していませんでしたが、よく見かける書き方なので注意してください。
UNNEST を JOIN で書いて行が増える
event_params はキーごとに繰り返される形です（BigQuery Export のスキーマ）。テーブルと UNNEST(event_params) を JOIN（2 つの表を行どうしで結び付ける SQL の命令）でつなぐ書き方にすると、1 つのイベントがキーの数だけ行に増えます。その状態で COUNT(*) を使うと件数が膨らみます。
今回の 2 本の SQL は、必要なキーだけを 1 つ取り出す書き方なので、この問題は起きていません。
GA4 の画面の数字と完全には一致しない
GA4 の画面のセッション数は、HyperLogLog++ という計算方法で推定した値です。一方、BigQuery では正確な数を計算するため、この推定は使われません（GA4 のセッションについて）。画面と BigQuery の数字が少し違っても、それだけで SQL の誤りとは言えません。
また、今回の公開サンプルは難読化（元の値を分からないように加工すること）されています。公式の説明では、データの内部の整合性が少し損なわれている可能性があり、デモアカウントの画面とは比べられないとされています（Google for Developers）。
当日や直近のテーブルの数字は後から変わる
日次テーブル events_YYYYMMDD には、その日のイベントが最長 3 日後まで追加で記録されます（BigQuery Export のスキーマ）。直近の日付で検算すると、AI の SQL と検算用 SQL を実行した時刻の違いで数字がずれることがあります。2 本は続けて実行するか、3 日以上前の日付で比べると安心です。
まとめ
- 質問文だけから書いた GA4 の SQL は、エラーなく動いても数字がずれることがあります。今回はセッション数が 37（1.3%）少なく、購入率が高めに出ました
- ずれの元は、ga_session_id を user_pseudo_id と組にせずに数えたことです。分母のずれが購入率にもそのまま伝わりました
- AI の SQL は、公式の定義どおりの検算用 SQL と同じテーブルで並べて実行してから報告に使いましょう
次にやること
集計した数字をどう見せるかの参考には、ダッシュボードの一覧で、このサイトが公開データから作ったダッシュボードの例をご覧ください。AI に書かせた SQL が定義どおりか、第三者の目で確かめたい場合は、BigQuery SQL レビューパックもご利用いただけます。
参考にした一次情報
- Google アナリティクス ヘルプ：GA4 のセッションについて
- Google アナリティクス ヘルプ：BigQuery Export のスキーマ
- Google アナリティクス ヘルプ：ユーザー指標（合計ユーザー数とアクティブ ユーザー数）
- Google アナリティクス ヘルプ：キーイベント率
- Google for Developers：GA4 の e コマースのサンプルデータセット
- Google Cloud：BigQuery の料金
更新履歴
- 2026-11-09: 定期点検で、本文の11か所を直しました。