GA4リピーターをAIに書かせて検算:数え方で人数が3.8倍

GA4 のリピーターと新規ユーザーの数を出す BigQuery の SQL を AI に書かせ、公開サンプルで検算しました。リピーターは数え方で 4,346〜16,598 人に分かれ、新規と両方に入る人が 11,725 人いました。質問文に書く項目も紹介します。

21分で読めます Data
新規とリピーターを表す 2 つの円が一部重なり、重なりに入った人のアイコンを虫めがねで確かめている抽象図
この記事は AI が下書きを補助し、運営者が内容を確認して公開しています。

GA4 の新規ユーザーとリピーターの数を、BigQuery のデータから AI に集計させたいマーケターの方向けの記事です。GA4 のリピーターを数える SQL を AI に書かせ、公開サンプルで 3 通りの数え方と比べて検算しました。読み終わると、AI の SQL がどの項目で新規とリピーターを分けたかを見分け、質問文に書き足す項目を決められるようになります。

この記事で分かること

  • AI に「新規ユーザーとリピーターの数」を頼んだ時に返った SQL と、判定に使った項目
  • 数え方(first_visit、セッション番号、初回の日時)で人数と購入率がどれだけ変わるか
  • 新規とリピーターの合計がユーザー数と合わない理由と、質問文に書くとよい項目

ざっくり結論:新規はほぼ同じ、リピーターは 3.8 倍ずれた

公開サンプルの 2021 年 1 月分(ユーザー 94,790 人)で、3 通りの数え方と、AI に定義を書いて頼んだ SQL の結果を並べました。人数はすべて筆者が実行した問い合わせの結果です。

3 通りの数え方と AI の SQL で、新規は約 8.9 万〜9.0 万人とほぼ同じなのに、リピーターは 4,346 人から 16,598 人まで変わることを比べた横棒グラフ

数え方新規リピーター合計
A first_visit の有無88,8305,96094,790
B セッション番号90,22116,598106,819
C 初回の日時88,8064,34693,152
AI 質問 2・388,83016,598105,428

図 1 のとおり、新規の人数は数え方を変えても 1,415 人しか動きません(筆者計算)。一方でリピーターは、最も少ない C の 4,346 人と最も多い B の 16,598 人で 3.8 倍の差がありました(筆者計算)。C の合計が 94,790 人に届かないのは、初回の日時が空のユーザー 1,638 人をどちらにも入れていないためです。

差の大部分は、「1 月に初めて来て、1 月のうちにもう一度来た人」をどう扱うかで生まれていました。AI の SQL でリピーターを数える時は、新規とリピーターを重ならないように分けるのか、重なりを許すのかを質問文で決めてください。

なお、AI の答えは、この記事の調査を担当した Claude Sonnet 5.5 のセッション自身が書いたものです。公式ヘルプと基本クエリ集を読んだ後、検算 SQL を見る前に、質問文ごとに 1 回ずつ書きました。先に読んだ資料の影響を受けた可能性があり、どの AI でも同じ SQL が返るとは限りません。

GA4 公式の新規ユーザーとリピーターの定義

Google アナリティクスのヘルプでは、2 つのユーザー指標(人数を数える指標)を次のように定めています(GA4 のユーザー指標のヘルプ)。

指標公式の定義(要約)
新規ユーザー数期間内に first_open か first_visit が記録された人
リピーター期間内に、過去のセッションを 1 回以上開始した人

first_visit(ウェブサイトに初めて来た時に記録されるイベント)と first_open(アプリを初めて開いた時のイベント)は、イベントの名前で判定できます。一方でリピーターの定義は「過去のセッション」で書かれていて、BigQuery のどの項目で判定するかはヘルプに書かれていません。

また、同じ人が新規とリピーターの両方に数えられるかどうかも、ヘルプには書かれていません。そのため、定義の文だけを渡しても、重なりをどう扱うかは決まりません。

BigQuery で使える 3 つの項目

BigQuery(Google の大量データ向けのデータベース)に GA4 のデータを書き出すと、1 行が 1 イベント(ページ表示や購入などの 1 回の動き)になります。新規とリピーターの判定に使える項目は、主に次の 3 つです。

  • event_name が first_visit か first_open か。公式の基本クエリ集は、この方法で新規を判定しています(BigQuery の基本クエリ)。
  • event_params の中の ga_session_number(筆者の理解では、その人にとって何回目のセッションかを表す番号。今回参照したヘルプには説明が見当たりませんでした)。今回のサンプルでは、1 月の 1,210,147 件すべてのイベントに入っていました(筆者が実行した問い合わせ)。
  • user_first_touch_timestamp(その人が初めてサイトに来た時刻。マイクロ秒単位の整数)。エクスポートのスキーマのヘルプに載っています(BigQuery Export スキーマ)。

1 人のユーザーが、first_visit の有無、セッション番号、初回の日時のどれで判定するかによって新規かリピーターに分かれ、セッション番号で見る時だけ両方に入りうる流れ図

図 2 のとおり、A と C は 1 人を新規とリピーターの両方に入れることはありません。B だけは、1 月にセッション番号 1 と 2 以上の両方があった人を、新規とリピーターの両方に入れます。

使ったデータ:GA4 の公開サンプル

使ったデータは、Google が公開している GA4 の BigQuery サンプル bigquery-public-data.ga4_obfuscated_sample_ecommerce です。Google Merchandise Store のデータを難読化(個人が分からないよう値を加工すること)したもので、2020 年 11 月 1 日から 2021 年 1 月 31 日の 3 か月分が入っています(サンプル データセットの説明)。

今回はそのうち 2021 年 1 月の 31 日分を使い、ユーザーは user_pseudo_id(ブラウザごとの匿名の ID)で数えました。公式の基本クエリ集でも、user_id が無い場合は user_pseudo_id の個別の数を数えるとしています(BigQuery の基本クエリ)。1 月のイベントは 1,210,147 件、ユーザーは 94,790 人、first_visit は 88,873 件で、first_open は 0 件でした(筆者が実行した問い合わせ)。

AI に渡した 3 通りの質問文と返ってきた SQL

質問文は次の 3 通りです。AI には、筆者の検算 SQL を見せる前に書かせました。

質問質問文に足したこと
質問 1何も足さない
質問 2公式の定義を文で書く
質問 3質問 2 + 合計の確認 SQL

質問 1:定義を書かない

GA4 の BigQuery エクスポートの公開サンプル(bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*)の
2021 年 1 月分で、新規ユーザーとリピーターの人数と、それぞれの購入率を出す BigQuery の SQL を書いてください。

返った SQL の判定の部分を抜き出すと、次の 3 行です。上の 2 行がユーザーごとにまとめる部分、最後の 1 行が集計で区分を付ける部分です。first_visit がある人を新規にし、それ以外の人をすべてリピーターに入れています。

MAX(IF(event_name = 'first_visit', 1, 0)) AS is_new,
MAX(IF(event_name = 'purchase', 1, 0)) AS purchased
IF(is_new = 1, '新規', 'リピーター') AS segment,

質問 2:公式の定義を文で書く

質問 1 の文に、次の 4 行を足しました。

定義は GA4 のヘルプに合わせてください。
- 新規ユーザー: 指定した期間に first_open イベントまたは first_visit イベントがログに記録されたユーザー
- リピーター: 指定した期間にアクセスしたユーザーのうち、過去のセッションを 1 回以上開始したことがあるユーザー
購入率は、購入(purchase イベント)をしたユーザー数 ÷ 各区分のユーザー数 とします。

返った SQL は、新規とリピーターを別々の項目で判定しました。

WITH users AS (
  SELECT
    user_pseudo_id,
    MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new,
    MAX(IF(
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') > 1,
      1, 0)) AS is_returning,
    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
  '新規' AS segment,
  COUNTIF(is_new = 1) AS users,
  COUNTIF(is_new = 1 AND purchased = 1) AS purchasers,
  SAFE_DIVIDE(COUNTIF(is_new = 1 AND purchased = 1), COUNTIF(is_new = 1)) AS purchase_rate
FROM users
UNION ALL
SELECT
  'リピーター',
  COUNTIF(is_returning = 1),
  COUNTIF(is_returning = 1 AND purchased = 1),
  SAFE_DIVIDE(COUNTIF(is_returning = 1 AND purchased = 1), COUNTIF(is_returning = 1))
FROM users

質問 3:合計がユーザー数と合うかも確かめさせる

質問 2 の文に、さらに次の 2 行を足しました。

あわせて、「新規ユーザー数 + リピーター数」が期間内の全ユーザー数と一致するかを確かめる SQL も書いてください。
一致しない場合に、同じ人が両方に数えられている人数も出してください。

1 本目の SQL は質問 2 と同じでした。2 本目に、重なりを数える確認の SQL が付きました。次はその抜粋で、前半の users の部分は質問 2 と同じ(purchased の列が無いだけ)なので省いています。

SELECT
  COUNT(*) AS total_users,
  COUNTIF(is_new = 1) AS new_users,
  COUNTIF(is_returning = 1) AS returning_users,
  COUNTIF(is_new = 1) + COUNTIF(is_returning = 1) AS sum_of_both,
  COUNTIF(is_new = 1 AND is_returning = 1) AS counted_in_both,
  COUNTIF(is_new = 0 AND is_returning = 0) AS in_neither
FROM users

質問文を定義なし・定義あり・確認も依頼の 3 通りに変えた時に、AI の SQL が新規とリピーターをどの項目で判定し、検算のどの数え方と一致したかを並べた比較図

図 3 のとおり、定義を書くとリピーターの判定がセッション番号に変わりました。質問 3 では SQL の判定は変わらず、合計が合わない理由が結果に出るようになりました。

3 通りの検算 SQL と人数・購入率の比較

検算には、ユーザーごとに 1 行にまとめて 3 通りの区分を並べる SQL を使いました。次は実行した SQL から、人数を数える列だけを残した抜粋です(購入者を数える列などは省いています)。

WITH u AS (
  SELECT user_pseudo_id,
    MAX(IF(event_name IN ('first_visit','first_open'),1,0)) fv,
    MAX(IF((SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_number')=1,1,0)) s1,
    MAX(IF((SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_number')>1,1,0)) gt1,
    MIN(user_first_touch_timestamp) ft,
    MAX(IF(event_name='purchase',1,0)) p
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131' GROUP BY 1)
SELECT COUNT(*) total, SUM(p) purch,
 SUM(fv) fv_new, COUNTIF(fv=0) fv_ret,
 SUM(s1) s1_new, SUM(gt1) gt1_ret,
 COUNTIF(s1=1 AND gt1=1) s1_gt1_both, COUNTIF(fv=1 AND gt1=1) fv_gt1_both,
 COUNTIF(ft>=UNIX_MICROS(TIMESTAMP "2021-01-01")) ft_new, COUNTIF(ft<UNIX_MICROS(TIMESTAMP "2021-01-01")) ft_ret,
 COUNTIF(ft IS NULL) ft_null,
 COUNTIF(fv=1 AND ft<UNIX_MICROS(TIMESTAMP "2021-01-01")) fv_but_ft_before
FROM u

購入者(purchase イベントのある人)の数と購入率を、数え方ごとに並べると次のとおりです。

数え方新規の購入率リピーターの購入率
A first_visit の有無0.871%4.950%
B セッション番号0.871%4.910%
C 初回の日時0.869%6.328%
AI 質問 10.871%4.950%
AI 質問 2・30.871%4.910%

AI 質問 1 の SQL を実行した結果は、数え方 A と人数・購入者数とも一致しました。AI 質問 2・3 の SQL は、新規が A と、リピーターが B と一致しました。どの検算とも全体は一致しない、2 つの数え方を組み合わせた形です。

リピーターの購入率は、どの数え方でも新規の 5.6〜7.3 倍で、「リピーターの方がよく買う」という向きは変わりません(筆者計算)。ただし C の 6.328% と B の 4.910% には 1.4 ポイントの差があり、会議で率そのものを比べる時には効いてきます。

差が出た理由:両方に数えられる人と、期間の外の初回訪問

新規とリピーターの両方に入る 11,725 人

AI 質問 3 の確認 SQL を実行すると、新規とリピーターの合計は 105,428 人で、ユーザー数より 10,638 人多くなりました。両方に数えられた人が 11,725 人、どちらにも入らない人が 1,087 人いたためです。

筆者はこの結果から、94,790 人を重ならない 4 つの群に分けました。

AI 質問 2・3 の判定で 94,790 人を分けると、新規だけが 77,105 人、両方に入る人が 11,725 人、リピーターだけが 4,873 人、どちらでもない人が 1,087 人になることを示す横棒グラフ

群人数購入者購入率
新規だけ77,1052480.32%
両方に入る11,7255264.49%
リピーターだけ4,8732895.93%
どちらでもない1,08760.55%

重ならない 4 群の購入率を比べると、新規だけは 0.32% と低く、両方に入る人は 4.49%、リピーターだけは 5.93% と高いことを示す横棒グラフ

人数と購入者数は、筆者が実行した問い合わせの値の差から計算しました(例: 新規だけ = 88,830 − 11,725)。図 4 のとおり、AI の定義でのリピーター 16,598 人のうち 70.6% は、1 月に first_visit が記録された人でした(筆者計算)。

図 5 を見ると、新規の購入率 0.871% は、月内に戻ってきた人に支えられています。新規の購入者 774 人のうち 526 人は両方に入る人で、戻らなかった新規だけの購入率は 0.32% でした(筆者計算)。

数え方 B でも、両方に入る人は 12,029 人いました。B ではどちらにも入らない人が 0 人なので、両方に入る人のうち購入者は 786 + 815 − 1,069 = 532 人と計算できます(筆者計算)。

この形の重なりは、流入元別の集計でも起きます。筆者がこのサイトの運用で試した記録では、ユーザー 94,790 人に対して「ユーザー × 流入元」の組み合わせが 110,873 件あり、同じ人に複数の区分が付いて合計が増えていました。詳しくは GA4 の流入元別集計を AI に書かせて検算した記事で紹介しています。

A と C の差は、初回の日時が空の 1,638 人

A のリピーター 5,960 人と C のリピーター 4,346 人の差 1,614 人は、次の 2 つの差し引きで説明できました(筆者計算)。

  • user_first_touch_timestamp が空の 1,638 人は、全員が first_visit の無い側にいました。A ではリピーター、C ではどちらにも入りません。
  • first_visit があるのに、初回の日時が 2021 年 1 月より前の人が 24 人いました。A では新規、C ではリピーターです。

1,638 − 24 = 1,614 人で、差と一致します。空の 1,638 人の購入率は 1.343% と低く、A のリピーターに混ざることで、A のリピーターの購入率は C より低くなっていました。空になる理由は確認できていません。

自社のデータでも、初回の日時が空の人が何人いるかは数え方によって扱いが変わります。リピーターの購入率を比べる前に、初回の日時が空のユーザーを別の行に分けて数えてください。

質問文に書くとよい項目

今回の結果から、筆者は次の 4 項目を質問文に書くことを提案します。このうち試したのは 1 と 4 で、2 と 3 の書き方で AI がどんな SQL を返すかは試していません。

項目書く内容の例
1. 判定の項目新規は first_visit、リピーターは何で決めるか
2. 重なりの扱い両方に入れるか、どちらか 1 つにするか
3. 項目が空の人空の人をどちらに入れるか、外すか
4. 合計の確認新規+リピーターとユーザー数を並べる

4 の確認を頼んだ質問 3 では、SQL の判定は変わらなかったものの、合計が合わない理由が数字で見えました。新規とリピーターの合計がユーザー数と合うかを確かめる SQL を一緒に頼むと、重なって数えられた人数が結果に出ます。

SQL の検算の進め方は、AI に書かせた GA4 の SQL をセッション数・ユーザー数・購入率で検算した記事でも紹介しています。初回訪問の月で人を分けて継続率を見る場合は、GA4 のコホート分析を AI に書かせて検算した記事が参考になります。

つまずきやすい点

サンプルの数字は GA4 の画面と比べられない

公開サンプルは、Google Merchandise Store の GA4 デモアカウントとは別のデータで、画面の数字とは比べられません(サンプル データセットの説明)。そのため、3 通りのどれが GA4 の画面の値に近いかは、今回は確かめていません。自社のデータでは、3 通りの値を GA4 の画面の値と並べて、近い数え方を選んでください。

期間の始めより前の初回訪問は first_visit では見えない

first_visit は初めて来た時のイベントなので、期間より前に初めて来た人には、ふつう期間内の first_visit がありません。ただし今回のサンプルでは、first_visit があるのに初回の日時が期間より前の人も 24 人いました(筆者が実行した問い合わせ)。公開サンプルは 2020 年 11 月 1 日から始まるため、それより前の初回訪問はイベントでは確かめられません(サンプル データセットの説明)。

「新規でない人 = リピーター」とは限らない

AI 質問 1 の SQL は、新規でない人をすべてリピーターにしました。公式の定義では、リピーターは「過去のセッションを開始した人」で、新規の反対として決めてはいません(GA4 のユーザー指標のヘルプ)。実際、AI 質問 3 では、どちらにも入らない人が 1,087 人いました。

難読化で数字の整合が少し崩れることがある

公開サンプルは難読化の影響で、データの内部整合性が若干制限される可能性があると説明されています(サンプル データセットの説明)。どちらにも入らない 1,087 人や、初回の日時が空の 1,638 人が難読化の影響かどうかは確認できていません。

AI に SQL を見直させる質問文は、AI に GA4 の SQL をレビューさせるプロンプトを試した記事も参考になります。

まとめ

  • 新規の人数は数え方を変えても 88,806〜90,221 人とほぼ同じでしたが、リピーターは 4,346〜16,598 人と 3.8 倍ずれ、購入率も 4.910%〜6.328% に分かれました。
  • 公式の定義を書いて頼んだ AI の SQL は、新規を first_visit、リピーターをセッション番号で判定し、11,725 人を両方に数えました。公式の定義の文だけでは重なりの扱いが決まらないため、AI の SQL は重なりを許す数え方になりました。
  • 質問文には、判定の項目・重なりの扱い・空の人の扱い・合計の確認の 4 項目を書くことを提案します(試したのは判定の項目と合計の確認の 2 つです)。

次にやること

新規とリピーターの推移をどう見せるかの参考には、ダッシュボードの一覧で、このサイトが公開データから作ったダッシュボードの例をご覧ください。AI が書いた新規とリピーターの SQL が、狙った定義で重なりを扱えているかを第三者の目で確かめたい場合は、BigQuery SQL レビューパックもご利用いただけます。

参考にした一次情報