GA4 のデータを BigQuery に出していて、ファネル（商品閲覧から購入までの段階ごとの人数）を AI に SQL で出させたい方向けの記事です。GA4 のファネルを AI に数えさせると離脱率がどれだけずれるかを公開サンプルで検算し、読み終わると、AI の SQL を報告に使う前に自分で確かめる手順と、質問に足すべき定義が分かります。
この記事で分かること
- AI にファネルの SQL を書かせるときの質問の書き方（定義なしと、単位と順番を書いた場合の違い）
- AI の SQL が出した離脱率を、検算用の SQL 3 通りで確かめる手順
- 数え方（イベント単位かユーザー単位か、前の段階を踏んだ人だけか）で離脱率が何ポイント変わるか
ざっくり言うと：SQL は動くが、数え方で離脱率が変わる
筆者が 2026 年 11 月 10 日に、AI（claude-sonnet-4-5）へ質問を 2 種類渡してファネルの SQL を書かせ、BigQuery で実行しました。どちらの SQL もエラーなく動きましたが、離脱率は数え方によって大きく変わりました。
| 質問 | AI の SQL の数え方 | 基準とのずれ（最大） |
|---|---|---|
| A: 定義を書かない | ユーザー単位で、各段階を別々に数える | カート→購入手続きで 12.45 ポイント |
| B: 単位と順番を書く | ユーザー単位で、各イベントの最初の時刻どうしを比べる | 閲覧→カートで 2.71 ポイント |
ここでの「基準」は、筆者が書いた「ユーザー単位で順番を守って数える SQL」の結果です。どの質問も 1 回ずつしか書かせていないので、ずれの大きさは 1 例として読んでください。
ファネルとは：段階ごとの人数と離脱率
ファネル（漏斗の意味。段階が進むほど人数が減る様子を表す分析）は、商品を見た人のうち何人がカートに入れ、何人が購入まで進んだかを段階ごとに数えるものです。離脱率は、ある段階まで来た人のうち、次の段階に進まなかった人の割合です。
離脱率（%）＝（1 − 次の段階の人数 ÷ 前の段階の人数）× 100GA4 の画面にあるファネルデータ探索では、指定した順序どおりにステップを踏んだ場合だけ、ファネルを辿ったと判定されます。途中のステップを抜かしたユーザーは、後のステップを完了しても数えられません（Google アナリティクス ヘルプ: ファネルデータ探索）。
同じヘルプでは、期間内に同じユーザーが何度ステップを完了しても、最初の一連のステップだけが報告されると説明しています。つまり画面のファネルは「ユーザー単位」で「順番を守る」数え方です。BigQuery で SQL を書くと、この 2 点を自分で決めなければなりません。
使うデータ：GA4 の公開サンプルと 4 つのイベント
使ったのは BigQuery の公開データ bigquery-public-data.ga4_obfuscated_sample_ecommerce です。Google Merchandise Store の 2020 年 11 月 1 日から 2021 年 1 月 31 日までの、難読化（個人を特定できないよう値を加工すること）したイベントデータです（Google for Developers: GA4 の BigQuery サンプルデータセット）。
このうち 2021 年 1 月分だけを使いました。期間は _TABLE_SUFFIX（日付ごとに分かれたテーブル名の末尾。ここで絞ると読むデータが減る）で指定しています。ファネルの 4 段階は、GA4 の e コマースイベントのうち次の 4 つです。
| 段階 | イベント名 | 公式の説明 |
|---|---|---|
| 1. 商品閲覧 | view_item | 商品の詳細画面を閲覧するたびに送る |
| 2. カート追加 | add_to_cart | カートへの商品追加を測定する |
| 3. 購入手続き開始 | begin_checkout | 購入手続きの開始を測定する |
| 4. 購入 | purchase | 購入の発生を測定する |
説明は Google for Developers: e コマースの測定 によります。view_item は「閲覧するたび」に送られるので、1 人が何度も送ることがあります。この点が、後でイベント数とユーザー数の差になって表れます。
ユーザーを区別する列は user_pseudo_id（サイトに初めてアクセスしたときに割り当てられる仮名の ID）です（Google アナリティクス ヘルプ: BigQuery Export スキーマ）。
なお、このサンプルは難読化のため内部の整合性が少し制限されることがあり、GA4 のデモアカウントの画面の数字とは比べられないと公式に書かれています（前述のサンプルデータセットのページ）。そのため今回は画面との突き合わせはせず、SQL どうしで比べました。
AI に渡した質問 2 種類と、返ってきた SQL
図 1 の流れで進めました。まず質問を 2 種類用意し、AI（claude-sonnet-4-5、ツールなし）に各 1 回ずつ SQL を書かせています。
質問 A：定義を書かない
BigQuery の公開データ bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* の 2021 年 1 月分で、view_item、add_to_cart、begin_checkout、purchase のファネル（段階ごとの人数と、前の段階からの離脱率）を出す SQL を書いてください。SQL だけを返してください。返ってきた SQL は、ユーザーごとに「そのイベントが 1 回でもあったか」の印を付け、印の数を段階ごとに数える形でした。人数を数える部分は次のとおりです（筆者が検算のために足した列は除いています）。
WITH user_events AS (
SELECT
user_pseudo_id,
MAX(IF(event_name = 'view_item', 1, 0)) AS viewed,
MAX(IF(event_name = 'add_to_cart', 1, 0)) AS added_to_cart,
MAX(IF(event_name = 'begin_checkout', 1, 0)) AS began_checkout,
MAX(IF(event_name = 'purchase', 1, 0)) AS purchased
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
GROUP BY user_pseudo_id
)
SELECT
COUNTIF(viewed = 1) AS view_item_users,
COUNTIF(added_to_cart = 1) AS add_to_cart_users,
COUNTIF(began_checkout = 1) AS begin_checkout_users,
COUNTIF(purchased = 1) AS purchase_users
FROM user_events質問 B：単位と順番を書く
BigQuery の公開データ bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* の 2021 年 1 月分で、view_item、add_to_cart、begin_checkout、purchase のファネルを出す SQL を書いてください。数える単位は user_pseudo_id（ユーザー単位）、各ユーザーについて 4 つのイベントを event_timestamp の順に view_item → add_to_cart → begin_checkout → purchase の順番で踏んだ人だけを次の段階に数えてください。出力は段階ごとの人数と、前の段階からの離脱率です。SQL だけを返してください。返ってきた SQL は、ユーザーごとに各イベントの「最初の時刻」を 1 つずつ取り出し、その時刻どうしを比べる形でした。中心の部分を抜き出します（期間の絞り込みと、離脱率を計算する部分は省略しました）。
-- ユーザーごとに、各イベントの最初の時刻を 1 つだけ取り出す
MIN(CASE WHEN event_name = 'view_item' THEN event_timestamp END) AS view_item_ts,
MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS add_to_cart_ts,
-- （begin_checkout_ts と purchase_ts も同じ形）
-- 最初の時刻どうしを「より大きい」で比べて、次の段階に数える
COUNT(DISTINCT CASE WHEN add_to_cart_ts > view_item_ts
AND begin_checkout_ts > add_to_cart_ts
THEN user_pseudo_id END) AS begin_checkout_users検算用の SQL 3 通り：数え方の違い
AI の答えと比べるため、筆者が数え方の違う SQL を 3 通り書きました。違いは図 2 のとおり、「何を 1 と数えるか（単位）」と「前の段階を踏んだ人だけを次に数えるか」の 2 点です。
| 数え方 | 数える単位 | 前の段階を踏んだ人だけか | 時刻の順 |
|---|---|---|---|
| イベント件数 | イベント（1 人が何回でも） | 見ない | 見ない |
| ユーザー単位・順番不問 | ユーザー | 見ない | 見ない |
| ユーザー単位・順番を守る（基準） | ユーザー | 見る | 見る（同じ時刻も可） |
3 つ目の「順番を守る」SQL は次のとおりです。最初の view_item の後にある最初の add_to_cart、その後の最初の begin_checkout、その後の最初の purchase を順に探します。
WITH e AS (
SELECT user_pseudo_id, event_name, event_timestamp
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
),
s1 AS (
SELECT user_pseudo_id, MIN(event_timestamp) AS t1 FROM e WHERE event_name='view_item' GROUP BY 1
),
s2 AS (
SELECT s1.user_pseudo_id, s1.t1, MIN(e.event_timestamp) AS t2
FROM s1 JOIN e ON e.user_pseudo_id=s1.user_pseudo_id AND e.event_name='add_to_cart' AND e.event_timestamp>=s1.t1
GROUP BY 1,2
),
s3 AS (
SELECT s2.user_pseudo_id, s2.t2, MIN(e.event_timestamp) AS t3
FROM s2 JOIN e ON e.user_pseudo_id=s2.user_pseudo_id AND e.event_name='begin_checkout' AND e.event_timestamp>=s2.t2
GROUP BY 1,2
),
s4 AS (
SELECT s3.user_pseudo_id, MIN(e.event_timestamp) AS t4
FROM s3 JOIN e ON e.user_pseudo_id=s3.user_pseudo_id AND e.event_name='purchase' AND e.event_timestamp>=s3.t3
GROUP BY 1
)
SELECT 1 AS step, 'view_item' AS stage, COUNT(*) AS users FROM s1
UNION ALL SELECT 2,'add_to_cart',COUNT(*) FROM s2
UNION ALL SELECT 3,'begin_checkout',COUNT(*) FROM s3
UNION ALL SELECT 4,'purchase',COUNT(*) FROM s4
ORDER BY step時刻の比較に「以上（>=）」を使ったのは筆者の判断です。event_timestamp は同じリクエストで送られた複数のイベントが同じ時刻を共有できると公式に書かれているため、同じ時刻の次の段階も数えるようにしました（BigQuery Export スキーマ）。この SQL は「正解」ではなく、「順番の定義に沿って数えた基準」として扱います。
dry run（実行せずに読むデータ量だけを確かめる機能）では、この SQL が読むデータは 50,257,744 バイト（約 50.3MB）でした。読む量と無料枠の関係は、BigQuery の無料枠で GA4 エクスポートが何か月もつかを計算した記事で詳しく扱っています。
結果を比べる：段階ごとの人数と離脱率
5 つの SQL を 2021 年 1 月分で実行した人数（イベント件数の行は件数）が次の表です。「入れ子」は、順番は問わずに「前の段階もすべて踏んだ人だけ」を数えた参考の値です。
| 数え方 | view_item | add_to_cart | begin_checkout | purchase |
|---|---|---|---|---|
| イベント件数 | 86,971 | 15,522 | 11,034 | 1,204 |
| 順番不問（AI の答え A と同じ） | 19,629 | 3,832 | 1,924 | 1,069 |
| 入れ子（順番不問、参考） | 19,629 | 3,829 | 1,455 | 850 |
| 順番を守る（基準） | 19,629 | 3,827 | 1,445 | 848 |
| AI の答え B | 19,629 | 3,295 | 1,217 | 729 |
この人数から筆者が計算した離脱率が次の表と図 3 です。
| 数え方 | 閲覧→カート | カート→手続き | 手続き→購入 | 閲覧→購入の通過率 |
|---|---|---|---|---|
| イベント件数 | 82.15% | 28.91% | 89.09% | 1.38% |
| 順番不問（AI の答え A） | 80.48% | 49.79% | 44.44% | 5.45% |
| 入れ子（順番不問、参考） | 80.49% | 62.00% | 41.58% | 4.33% |
| 順番を守る（基準） | 80.50% | 62.24% | 41.31% | 4.32% |
| AI の答え B | 83.21% | 63.07% | 40.10% | 3.71% |
図 3 のとおり、閲覧→カートの離脱率はどの数え方でも 80〜83% に収まります。一方、カート→手続きは 28.91% から 63.07% まで、手続き→購入は 40.10% から 89.09% まで開きました。「どこで一番離脱しているか」という結論が、数え方しだいで入れ替わってしまう幅です。
AI の SQL はどの数え方だったか、なぜずれたか（検算）
基準の離脱率から、AI の答えの離脱率を引いた差（ポイント）が次の表と図 4 です。差は表に載せた小数 2 桁の値どうしで計算しています。
| 質問 | 閲覧→カート | カート→手続き | 手続き→購入 |
|---|---|---|---|
| A: 定義を書かない | 0.02 低い | 12.45 低い | 3.13 高い |
| B: 単位と順番を書く | 2.71 高い | 0.83 高い | 1.21 低い |
質問 A の SQL は「順番不問」の数え方だった
AI の答え A の人数は、筆者が書いた順番不問の SQL と 4 段階すべて同じでした。各段階を別々に数えているので、カートに入れていない人も「購入手続きを始めた人」に入ります。
筆者が数えたところ、begin_checkout があった 1,924 人のうち、add_to_cart も踏んでいたのは 1,455 人でした。残りの 469 人は前の段階を踏まずに数えられています。この 469 人がなぜカートを経ていないのか（難読化の影響か、別の経路か）は、今回は確認できていません。
注目したいのは「入れ子」の行です。順番を問わなくても、前の段階を踏んだ人だけに絞れば、基準との差は最大 0.27 ポイント（手続き→購入）まで縮みます。つまり A の 12.45 ポイントのずれは、時刻の順を見なかったことよりも、各段階を別々に数えたことでほぼ説明できます。
質問 B の SQL は「最初の時刻どうし」を比べていた
質問 B では、AI は時刻の順を見る SQL を返し、離脱率の差は最大 2.71 ポイントまで縮みました。ただし人数は基準より 532 人・228 人・119 人少なく、閲覧→カートのずれは A（0.02 ポイント）より大きくなっています。定義を書けば全部の段階でずれが減る、とは言えない結果です。
人数が少ない理由として、2 つの候補が考えられます。1 つは、各イベントの最初の時刻だけで比べるため、最初のカート追加が最初の閲覧より前にあると、その後で順番どおりに踏んでいても落ちることです。もう 1 つは、「より大きい（>）」で比べるため、同じ時刻のイベントを落とすことです。どちらが何人分かは、今回は分けて測っていません。
同じ公開サンプルで、セッション数・ユーザー数・購入率の 3 問を検算した AI に書かせた GA4 の BigQuery SQL を検算した記事 でも、SQL は動くのに定義が違うという同じ型のずれが出ていました。段階が複数あるファネルでは、そのずれが段階ごとに積み重なります。
検算の手順と、質問に足すべき 3 行
自分の GA4 データで AI にファネルを書かせるときは、次の順で確かめます。
- 期間を決め、4 つのイベントの件数とユーザー数を
COUNT(*)とCOUNT(DISTINCT user_pseudo_id)で出す。 - AI に質問を渡して SQL を書かせ、そのまま実行する。
- AI の各段階の人数を、手順 1 のユーザー数と比べる。同じなら「順番不問・各段階を別々に数える」型と分かる。
- 「前の段階も踏んだ人だけ」に絞った人数を出し、後ろの段階ほど人数が減っているかを見る。
- 順番を守る検算用 SQL を実行し、離脱率の差をポイントで出す。
- 差が大きい段階について、AI の SQL がどの条件（単位・入れ子・時刻の比べ方）で違うのかを読む。
質問には、次の 3 行を足すとずれの原因を減らせると考えられます。今回の質問 B は 1 と 2 を書いた形で、「最初の時刻どうし」の比べ方が返ってきました。3 を書かなかったことが一因と考えられますが、AI に書かせたのは 1 回だけなので、原因とは言い切れません。また、3 行すべてを足した質問は今回は試していません。
1. 数える単位は user_pseudo_id（ユーザー単位）。イベント件数ではない。
2. 前の段階を踏んだユーザーだけを次の段階に数える。
3. 時刻の比較は「前の段階の時刻以上」。各イベントの最初の時刻どうしではなく、前の段階より後にある最初のイベントを探す。AI に SQL のレビューまでさせたい場合は、AI に GA4 の SQL をレビューさせるプロンプト 4 本を検算した記事 の質問の型も使えます。
つまずきやすい点
順番を守らないと、後ろの段階が前より多くなることがある
各段階を別々に数える SQL では、後ろの段階の人数が前の段階より少なくなる保証がありません。今回は 3,832 人、1,924 人、1,069 人と減っていたので目立ちませんでしたが、中身には前の段階を踏んでいない 469 人が混じっていました。人数が減っているからといって、正しく数えたとは限りません。
イベント件数で離脱率を出すと、結論が逆になる
view_item は閲覧のたびに送られるため、件数で割ると 1 人が何度も見た分だけ分母が膨らみます。その結果、今回はカート→手続きの離脱率が 28.91% と一番低く、手続き→購入が 89.09% と一番高く出ました。順番を守った基準では 62.24% と 41.31% なので、改善すべき段階の判断が入れ替わります。
画面のファネルとは突き合わせられない
このサンプルは GA4 のデモアカウントの画面とデータが違うため、画面のファネルデータ探索の数字とは比べられません。自分のプロパティで試す場合は、画面のファネル（クローズド型。最初のステップを経たユーザーだけを数える）と SQL の人数を並べると、もう一段確かめられます。オープン型（途中のステップから始めたユーザーも数える）を選ぶと数え方が変わる点にも注意が必要です（ファネルデータ探索のヘルプ）。
AI の答えは 1 回の結果にすぎない
今回はどちらの質問も 1 回しか SQL を書かせていません。同じ質問をもう一度渡すと、別の数え方の SQL が返ってくる可能性があります。報告に使う前には、毎回、検算用の SQL と人数を比べてください。
まとめ
- AI に定義を書かずにファネルを頼むと、各段階を別々に数える SQL が返り、カート→購入手続きの離脱率が基準より 12.45 ポイント低く出ました。
- ずれの大半は「前の段階を踏んだ人だけを数えるか」で決まり、そこを絞れば差は 0.27 ポイント以内になりました。
- 単位と順番を書くと差は最大 2.71 ポイントに縮みましたが、時刻の比べ方まで書かないと人数は少なめに出ました。
次にやること
検算したファネルをどう見せるかの参考には、ダッシュボードの一覧で、このサイトが公開データから作ったダッシュボードの例をご覧ください。ファネルの SQL が定義どおりに数えているかを第三者の目で確かめたい場合は、BigQuery SQL レビューパックもご利用いただけます。
参考にした一次情報
- Google for Developers: Google アナリティクス 4 の BigQuery サンプル データセット（ウェブ e コマース）（2026-11-10 確認。期間、難読化、画面とは比べられないこと）
- Google for Developers: e コマースの測定（2026-11-10 確認。view_item・add_to_cart・begin_checkout・purchase の送り方）
- Google アナリティクス ヘルプ: ファネルデータ探索（2026-11-10 確認。順序どおりの判定、最初の一連のステップ、オープン型とクローズド型）
- Google アナリティクス ヘルプ: BigQuery Export スキーマ（2026-11-10 確認。event_timestamp と user_pseudo_id）