GA4コホート分析をAIに書かせて検算:継続率の分母の違い

GA4 コホート分析の SQL を AI に書かせ、公開サンプル 92 日分で検算しました。初回を first_visit にするか最初のイベントにするか、観測できない週を分母から除くかで、週ごとの継続率がどれだけ変わるかと、質問文に書き足す 3 行を紹介します。

23分で読めます Data
週ごとに並んだ人の帯が右へ行くほど減り、右端の観測できない週が点線で区切られ、左端に以前からの人が混ざっている様子を表した抽象的なイラスト
この記事は AI が下書きを補助し、運営者が内容を確認して公開しています。

GA4 のデータを BigQuery に出し、「初めて来た人のうち翌週も来た人の割合」を AI に書かせた SQL で出している、EC サイトやメディアの事業担当の方向けの記事です。GA4 コホート分析の SQL を AI に 3 通りの質問文で書かせ、公開サンプルで検算しました。読み終わると、AI の SQL が初回と分母をどう決めたかを見分け、会議で説明できる継続率に直せるようになります。

この記事で分かること

  • AI に「週ごとの継続率」を頼むと、初回の日と分母をどう決めた SQL が返ってきたか
  • 初回の決め方・週の区切り・観測できない週の扱いで、継続率がそれぞれ何ポイント変わったか
  • データの最初の週と最後の週で数字が歪む理由と、質問文に書き足す 3 行

ざっくり結論:初回の決め方と、観測できない週の扱いで変わる

GA4 の公開サンプル(2020 年 11 月 1 日〜2021 年 1 月 31 日の 92 日分)で、筆者が 1 週後の継続率を数え方を変えて計算しました。基準の数え方では 3.80% で、数え方を 1 つ変えるだけで 3.52%〜4.29% の間で動きました。

1 週後で一番大きく動かしたのは初回の決め方で、比で見ると経過週が長いほど差が開いたのは、観測できない週(データの最終日までに、その経過週がまだ来ていないコホート)を分母から除くかどうかでした。観測できない週を分母から除かないと、継続率は経過週が長いほど低く出て、8 週後では本来の約 3 分の 1 になりました。

初回を「期間内の最初のイベント」にすると、反対に高く出ました(1 週後で +0.49 ポイント)。週を日曜始まりにするか月曜始まりにするかの差は、0.01 ポイントだけでした。

変えた条件1 週後の継続率基準との差
基準(first_visit・月曜・除く)3.80%0.00
観測できない週を除かない3.52%−0.28
初回を最初のイベントに4.29%+0.49
週を日曜始まりに3.81%+0.01

コホート分析と継続率の意味

コホート分析(同じ時期に始めた人をひとまとめにして、その後の行動を追う分析)では、初めて来た週ごとに人を分けます。分けた 1 つのまとまりを コホート と呼びます。

継続率(リテンションとも呼びます)は、コホートの人数のうち、n 週後にまた来た人の割合です。この記事では「コホートの週から n 週後の暦の週に、1 回でもイベントがあった人」を継続と数えます。初回から 7〜13 日後という数え方ではない点に注意してください。

GA4 の画面にも GA4 コホート探索(コホート データ探索)があります。公式ヘルプによると、コホートは「コホートに含まれる条件」(ユーザーをコホートに入れる条件)と「リピートの条件」(入れた人が引き続き残るための条件)で作ります(Google アナリティクス ヘルプ: コホート データ探索)。

同じヘルプでは、週単位のまとまりは連続する任意の 7 日間ではなく、日曜日から土曜日までと説明されています。BigQuery の SQL で同じことをする時は、この「入れる条件」と「週の区切り」を自分で書く必要があります。

図 1 は、継続率を出す手順を上から順に並べたものです。

継続率の計算手順を、初回の週を決める・n週後に来たかを数える・初回の週の人数で割る・観測できない週を除くの4段で縦に並べた流れ図

今回 AI に SQL を頼んだ時に変わったのは、1 段目(初回をどう決めるか)と 4 段目(観測できない週を除くか)でした。2 段目と 3 段目は、3 通りの質問文のどれでも同じ形でした。

使うデータと、ユーザーを数える単位

使ったのは、Google が公開している GA4 の公開サンプル(ga4_obfuscated_sample_ecommerce)です。2020 年 11 月 1 日から 2021 年 1 月 31 日までの、難読化した BigQuery のイベントデータが入っています(Google for Developers: GA4 の BigQuery サンプル データセット)。

難読化のため内部の整合性が若干制限される場合がある、と公式ページに書かれています。継続率が数%と低いのも、このサンプルの特徴かもしれません。自社のデータの水準とは比べないでください。

ユーザーは user_pseudo_id(ブラウザやアプリごとに割り当てられる、仮名化された ID)で数えました。公式の基本クエリ集も、user_id を返すか分からない場合は user_pseudo_id を数えるよう案内しています(Google for Developers: BigQuery の基本クエリ)。

筆者が実行した問い合わせで確かめた、データの全体像は次のとおりです。

項目値
日数(event_date)92 日
イベント4,295,584 件
ユーザー270,154 人
first_visit のある人257,314 人
first_visit の無い人12,840 人
うち期間より前に初訪問8,407 人

first_visit は、サイトに初めて来た時に記録されるイベントです。GA4 の「新規ユーザー」は、期間内に first_open か first_visit が記録された人数と定義されています(Google アナリティクス ヘルプ: ユーザー指標)。

first_visit の無い 12,840 人のうち 8,407 人(65.5%)は、初めて来た時刻(user_first_touch_timestamp)が期間の開始より前でした。筆者がこの 2 つの数から計算した割合です。

AI への質問文 3 通りと、返ってきた SQL

筆者が Claude Sonnet 5.5 に、条件の書き方を変えた 3 通りの質問文で SQL を書かせました。3 通りとも、データの場所と期間は質問の頭に書いています。

質問文書いた条件AI の初回週観測できない週
1書かない最初のイベント日曜始まり除かない
2初回と週first_visit月曜始まり除かない
32 + 除外first_visit月曜始まり除く

質問文 1 は「GA4 の BigQuery エクスポートで、初回訪問の週ごとの継続率を出す SQL を書いてください。」の 1 文だけです。返ってきた SQL は次のとおりです(要旨が変わらない範囲で整形)。

WITH e AS (
  SELECT user_pseudo_id AS u, PARSE_DATE('%Y%m%d', event_date) AS d
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),
cohort AS (SELECT u, DATE_TRUNC(MIN(d), WEEK) AS cw FROM e GROUP BY u),
act AS (SELECT DISTINCT u, DATE_TRUNC(d, WEEK) AS aw FROM e)
SELECT DATE_DIFF(a.aw, c.cw, WEEK) AS n,
       COUNT(DISTINCT c.u) AS retained,
       (SELECT COUNT(*) FROM cohort) AS base
FROM cohort c JOIN act a USING (u)
WHERE a.aw >= c.cw
GROUP BY n ORDER BY n

この SQL では、初回は期間内で最初にイベントがあった日(MIN(d))です。BigQuery の WEEK は日曜始まりなので、週も日曜で区切られます。

分母は (SELECT COUNT(*) FROM cohort) で、全員の 270,154 人という 1 つの数を、どの経過週にも使っています。データの最後の週に初めて来た人は、1 週後がデータの外にあるのに、分母には入ったままです。

質問文 2 では「初回は first_visit イベントの日とし、週は月曜始まりで区切ってください」と足しました。AI は、初回の日を次のように変えました。

-- 質問文 2 の答えで変わった部分(ほかは質問文 1 と同じ形)
DATE_TRUNC(MIN(IF(event_name = 'first_visit', d, NULL)), WEEK(MONDAY)) AS cw
-- first_visit の無い人は HAVING cw IS NOT NULL で除く

質問文 3 では、さらに「データの最終日までに、その経過週が全部入らないコホートは、その経過週の集計から除いてください」と足しました。AI は次の条件を足しました。

-- 質問文 3 の答えで足された条件
WHERE DATE_ADD(cw, INTERVAL (n + 1) * 7 - 1 DAY) <= (SELECT MAX(d) FROM e)

3 通りの答えは、記事の作成工程の中で、筆者側が作った質問文に 1 回ずつ答えさせたものです。普通の新しい会話で同じ質問をしても、違う SQL が返る可能性があります。質問文 1 で AI が first_visit を使わなかったのも、条件が無い時の 1 回の答えで、別のモデルや別の回では違う選び方になることがあります。

検算 SQL 4 通りで基準を作る

筆者が自分で検算の SQL を書き、bq query(BigQuery をコマンドで動かす道具)で実行しました。

検算初回の決め方観測できない週
(1)first_visit の日除かない
(2)期間内の最初のイベント除かない
(3)first_visit の日除く(基準)
(3’)期間内の最初のイベント除く

週はすべて月曜始まり、日付は event_date です。(3) を基準にした理由は、GA4 の新規ユーザーの定義(first_visit)に合わせられ、観測できない週の 0% が分母に入らないためです。

基準 (3) の SQL は次の形です。筆者が実行した形を、読みやすく書き直したものです(書き直した後の全文は、もう一度は実行していません)。

WITH e AS (
  SELECT user_pseudo_id AS u,
         PARSE_DATE('%Y%m%d', event_date) AS d,
         event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),
first_day AS (  -- 初回 = first_visit の日
  SELECT u, MIN(IF(event_name = 'first_visit', d, NULL)) AS fd
  FROM e GROUP BY u
),
cohort AS (
  SELECT u, DATE_TRUNC(fd, WEEK(MONDAY)) AS cw
  FROM first_day WHERE fd IS NOT NULL
),
act AS (SELECT DISTINCT u, DATE_TRUNC(d, WEEK(MONDAY)) AS aw FROM e),
last_day AS (SELECT MAX(d) AS md FROM e),
weeks AS (SELECT n FROM UNNEST(GENERATE_ARRAY(1, 8)) AS n),
sizes AS (SELECT cw, COUNT(*) AS size FROM cohort GROUP BY cw),
ret AS (
  SELECT c.cw, DATE_DIFF(a.aw, c.cw, WEEK(MONDAY)) AS n,
         COUNT(DISTINCT c.u) AS kept
  FROM cohort c JOIN act a USING (u)
  WHERE a.aw > c.cw
  GROUP BY c.cw, n
)
SELECT w.n,
       SUM(IFNULL(r.kept, 0)) AS kept,
       SUM(s.size) AS base,
       ROUND(100 * SUM(IFNULL(r.kept, 0)) / SUM(s.size), 2) AS rate_pct
FROM sizes s
CROSS JOIN weeks w
CROSS JOIN last_day l
LEFT JOIN ret r ON r.cw = s.cw AND r.n = w.n
-- 観測できない週を除く: n 週後の週の最終日がデータの最終日以前
WHERE DATE_ADD(s.cw, INTERVAL (w.n + 1) * 7 - 1 DAY) <= l.md
GROUP BY w.n
ORDER BY w.n

初回を最初のイベントにする時は、fd の式を MIN(d) に変えます。観測できない週を除かない時は、最後の WHERE を外します。

突き合わせ表:1 週後・2 週後・4 週後の継続率

検算 4 通りの結果は次のとおりです(単位は %)。

経過週(1)(2)(3) 基準(3’)
1 週後3.523.983.804.29
2 週後1.301.601.531.88
3 週後0.831.061.081.38
4 週後0.610.810.901.18
5 週後0.360.520.590.83
6 週後0.230.340.420.61
7 週後0.140.210.320.45
8 週後0.100.170.290.46

図 2 は、観測できない週を除いた 2 通り((3) と (3’))を、初回の決め方で比べた折れ線です。

経過週1〜8週の継続率を、初回を最初のイベントにした線とfirst_visitにした線で比べ、最初のイベントの方がどの週も高い折れ線グラフ

初回を最初のイベントにした (3’) は、どの経過週でも (3) より高く出ました。筆者が表から計算すると、比は 1 週後の 1.13 倍から、8 週後には 1.59 倍(0.46% と 0.29%)に広がりました。

経過週ごとの率は、違うコホートの集まりを比べている点にも注意が必要です。8 週後の分母は 89,871 人で、11 月ごろまでのコホートしか入りません。(3’) が 7 週後 0.45% から 8 週後 0.46% へ上がったのは、入るコホートが変わったためと考えられます。

AI の SQL を同じ表に並べる

AI の 3 つの SQL を、そのまま BigQuery で動かした結果を、基準 (3) と並べました。差の単位はポイントです。

質問文1 週後2 週後4 週後1 週後の差4 週後の差
14.001.620.82+0.20−0.08
23.521.300.61−0.28−0.29
33.801.530.900.000.00
基準 (3)3.801.530.90——

図 3 は、1 週後の差を棒で並べたものです。

1週後の継続率について、条件なしの質問文1はプラス0.20ポイント、初回と週を指定した質問文2はマイナス0.28ポイント、除外も指定した質問文3は差0と、検算の基準との差を比べた横棒グラフ

質問文 3 は、検算と同じ値になりました。質問文 2 は、検算 (1) と同じ条件の SQL で、同じ 3.52% でした。

質問文 1 の差が小さく見えた理由

質問文 1 の +0.20 は、3 つの条件が同時に違う結果です。基準から条件を 1 つずつ変えると、次の順で値が動きました(この順で変えた時の差で、条件ごとの独立した寄与ではありません)。

基準 (3)      3.80%  first_visit・月曜・除く
  ↓ 初回を最初のイベントに        +0.49
(3')          4.29%
  ↓ 観測できない週を除かない      −0.31
(2)           3.98%
  ↓ 週を日曜始まりに              +0.02
質問文 1      4.00%

高く出る誤りと低く出る誤りが打ち消し合い、1 週後だけを見ると基準に近く見えました。4 週後には差の向きが逆になり(−0.08)、誤りが無いから近いわけではないことが分かります。

データの端の問題:最初の週と最後の週

継続率が歪むのは、データの期間の両端です。図 4 は、92 日の期間の始まりと終わりで、それぞれ何が起きるかを示しています。

92日の期間の帯の下に、始まりの週では最初のイベントを初回にすると以前からの人が新規に混ざること、終わりの週では1週後がデータの外にあることを、上下2つの箱で示した構造図

最初の週:以前からの人が新規に混ざる

期間内の最初のイベントを初回にすると、期間より前から来ていた人も、期間の最初に現れた時に「初めて来た人」として数えられます。first_visit の無い人のうち 8,407 人は、初めて来た時刻が期間の開始より前でした。期間内の最初のイベントで初回を決めると、以前から来ていた人が新規として混ざるため、継続率は高く出ます。

期間の最初の 7 日(11 月 1 日〜7 日)に最初のイベントがある人は 20,078 人で、first_visit が 11 月 1 日〜7 日にある人は 16,926 人でした。2 つの数の差の 3,152 人(20,078 人の 15.7%)は first_visit の無い人などで、全員が以前からの人とは限りません。

なお、この 7 日は月曜始まりのコホートの週とは一致しません。11 月 1 日は日曜なので、月曜始まりでは 1 日だけの週(10 月 26 日の週、1,946 人)になります。

最後の週:観測が足りない

データの最終日は 2021 年 1 月 31 日(日曜)です。月曜始まりで最後の週(1 月 25 日の週)に初めて来た 18,665 人は、1 週後がデータの外にあります。

観測できない週を除かない SQL では、この人たちが「1 週後に来なかった人」として分母に入ります。筆者が人数から計算すると、外れる人の割合は 1 週後で 7.3%、4 週後で 32.0%、8 週後で 65.1% に増えます。経過週が長いほど (1) が (3) の何分の 1 かという比が小さくなるのは、このためです。

経過週継続人数除いた分母除かない分母除いた率除かない率
1 週後9,070238,649257,3143.80%3.52%
4 週後1,575174,963257,3140.90%0.61%
8 週後26489,871257,3140.29%0.10%

分子の継続人数は同じで、分母だけが違います。8 週後の除かない率は、除いた率の約 0.35 倍(約 3 分の 1)でした(筆者が人数から計算)。

コホートの週ごとの 1 週後の継続率

基準 (3) の 1 週後を、コホートの週ごとに分けると、2.36%(12 月 28 日の週)から 6.22%(10 月 26 日の週)まで幅がありました。人数で重みを付けて平均すると 3.80% になり、全体の値と一致します(筆者の計算)。

コホートの週人数1 週後
10/261,9466.22%
11/0216,5004.25%
11/0914,5905.68%
11/1616,9065.25%
11/2318,7344.59%
11/3021,1955.07%
12/0727,5583.11%
12/1424,2212.80%
12/2117,0132.43%
12/2816,3002.36%
01/0422,4583.16%
01/1121,0083.57%
01/1820,2203.99%

10 月 26 日の週は 1 日だけで、人数も少ないため、他の週より大きく振れやすいと考えられます。first_visit で初回を決めているので、以前からの人の混入とは関係しません。

質問文に書き足す 3 行

AI に継続率の SQL を頼む時は、次の 3 行を質問文に書き足します。今回の検算では、同じ 3 つの条件を書いた質問文 3 だけが基準と一致しました。下の 3 行は、質問文 3 で足した文を、条件ごとに 1 行ずつに分けたものです。

初回は first_visit イベントの日としてください。
週は月曜始まりで区切ってください。
データの最終日までに、その経過週が全部入らないコホートは、その経過週の集計から除いてください。

first_visit の無いユーザーをどう扱うかと、経過週を暦の週で数えるか初回から 7 日ずつで数えるかは、この 3 行では指定していません。今回の AI は first_visit の無い人を除き、暦の週で数えましたが、別の回では違う選び方をする可能性があるため、返ってきた SQL で確かめてください。

AI に継続率の SQL を頼む時は、初回の決め方・週の区切り・観測できない週の除き方の 3 つを質問文に書いてください。書かなかった条件は AI が自分で選ぶため、どれを選んだかを SQL から読み取る必要があります。

返ってきた SQL は、次の 3 か所を見れば確かめられます。

見る場所確かめること
初回の式MIN(d) か first_visit か
週の式WEEK か WEEK(MONDAY) か
分母経過週ごとに変わるか

分母が全経過週で同じ数なら、観測できない週を除いていません。AI に SQL を見直させる時の質問文は、AI に GA4 の SQL をレビューさせるプロンプトを試した記事も参考になります。

つまずきやすい点:タイムゾーン、ユーザーの識別子

タイムゾーンと週の区切り

event_date は、登録したタイムゾーンでの日付(YYYYMMDD の形)です。一方、event_timestamp は、GA4 がイベントを受け取った時刻を UTC(世界の基準の時刻)で記録したものです(Google アナリティクス ヘルプ: BigQuery Export スキーマ)。

このサンプルでは、event_date と event_timestamp の UTC の日付で、継続率は一致しました(どちらも 3.80%)。一致した理由は確認できていません。自社のデータでは、どちらの日付を使ったかを質問文に書き、両方で数えて比べてください。

週の始まりを日曜にしても月曜にしても、1 週後は 3.81% と 3.80% でほぼ同じでした。差が小さかったのは、このサンプルでの結果です。

ユーザーの識別子

公式の基本クエリ集は、ユーザー数は個別の user_id で数え、user_id が返るか分からない場合は user_pseudo_id で数えると案内しています(Google for Developers: BigQuery の基本クエリ)。自社で user_id を送っている場合は、どちらで数えるかも質問文に書いてください。

最新の 3 日はまだ増える

日次のテーブルには、その日のイベントが最長 3 日後まで追加で記録されます(Google アナリティクス ヘルプ: BigQuery Export スキーマ)。自社のデータで直近の週を数える時は、3 日たってから集計しないと、最後の週の継続が少なく出ます。

GA4 の画面の数値とは比べられない

公開サンプルには対応する GA4 の画面が無く、公式ページでも GA のデモアカウントのデータとは比較できないとされています。今回の検算は、SQL 同士の突き合わせです。自社のデータで画面の GA4 コホート探索 と比べる時は、週の区切り(画面は日曜〜土曜)と「コホートに含まれる条件」を SQL と揃えてください。

ほかの GA4 の集計で AI の SQL を検算した例は、AI に書かせた GA4 の BigQuery SQL を検算した記事や、GA4 のファネルの離脱率を AI に数えさせて検算した記事で紹介しています。

まとめ

  • 観測できない週を分母から除かないと、継続率は低く出ます。このサンプルでは 1 週後で 0.28 ポイント、8 週後では除いた値の約 3 分の 1(0.10% と 0.29%)でした。
  • 初回を期間内の最初のイベントにすると、以前から来ていた人が新規に混ざり、高く出ます。1 週後で 0.49 ポイント、8 週後で 1.59 倍でした。
  • 条件を書かない AI の SQL は、2 つの誤りが打ち消し合って 1 週後だけ近く見えました。初回・週・除外の 3 つを質問文に書き、分母が経過週ごとに変わるかを確かめます。

次にやること

継続率をダッシュボードでどう見せるかの参考には、ダッシュボードの一覧で、このサイトが公開データから作ったダッシュボードの例をご覧ください。AI が書いたコホート分析の SQL が、初回と分母を正しく扱っているかを第三者の目で確かめたい場合は、BigQuery SQL レビューパックもご利用いただけます。

参考にした一次情報