はじめに「新規ユーザー数」の取得については探索と一致する結果が得られるSQLを提示できるが、「リピーター数」については探索との値とかなりズレが生じており原因についてはまだ調査中である。何か分かり次第、追記するが現時点での見解はGA4のリピーターの定義に則りカウントしているためBigQueryの値を採用するに問題ないとみている。

新規ユーザーのセグメントを適用させる方法については別記事にて解説している。

【GA4 SQL】新規ユーザーのユーザーセグメントを日次で適用 – Visual SEM Report

GA4の分析において新規とリピーターを分けて分析したいケースがよくある。GA4の新規ユーザーの定義では初回訪問は新規ユーザーとしてカウントされるが、その日の2回目以降のセッションではリピーターとしてカウントされてしまう。

新規ユーザー数の日次カウント

SELECT
  PARSE_DATE("%Y%m%d", event_date) AS ymd,
  COUNT(DISTINCT user_pseudo_id) AS new_uu
FROM
  `<project>.<dataset>.events_*`
WHERE
    event_name = 'first_visit'
  AND
    _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
GROUP BY
  event_date
ORDER BY
  event_date

参考：基本的なイベントクエリ > ユーザー数と新規ユーザー数

上記リンクGoogleアナリティクスの公式ヘルプにも新規ユーザー数をカウントするSQLが掲載されている。

新規ユーザー数の定義

新規ユーザー数のカウントは”first_visit”イベントが発火したユニークユーザー数となるため分かりやすい（アプリの場合は”first_open”）。またリピーター数も同様であるが、アクティブユーザーである必要もないため、アクティブユーザーとしての条件を加える必要もない。ただ、必ずしもGA4の定義に従う必要もないため、アクティブユーザーとしての条件を加えても問題ない。

「新規ユーザー数」: 指定した期間にサイトまたはアプリにアクセスしたユーザーのうち、過去にサイトまたはアプリにアクセスしたことがないユーザーの数。

https://support.google.com/analytics/answer/12253918?hl=ja

指定した期間に first_open イベントまたは first_visit イベントがログに記録された、新しいユニーク ユーザーの数。

https://support.google.com/analytics/answer/12253918?hl=ja

リピーター数の日次カウント

※注意：探索の「リピーター数」と値が大きくズレる

WITH ReUU AS (
  SELECT
    ymd,
    COUNT(DISTINCT uuid) AS _reuu
  FROM (
    SELECT
      *
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        user_pseudo_id AS uuid,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    WHERE
        ga_session_number > 1
  )
  GROUP BY
    ymd
)

SELECT
  *
FROM
  ReUU

リピーター数の定義

過去に1度でもセッションがあったユーザーが2回目以降の訪問でリピーターとなる。そのため、セッションの訪問回数を記録している’ga_session_number’パラメータの値が「2」以上であるユニークユーザー数がリピーターとなるはずであるが、探索との値とはズレが生じる。様々なクエリを検証してみたがいずれにおいても合致することはなかった。ちなみに公式ヘルプにも記載があるように、エンゲージメントセッションであるかは関係がないため、この条件を加えると探索との値は近くなるにしても値がズレる原因ではないと思われる。

Users who have initiated at least one previous session.

日本語訳：過去に少なくとも1回セッションを開始したことのあるユーザー。

GA4探索より

「リピーター」: 指定した期間にサイトまたはアプリにアクセスしたユーザーのうち、過去にサイトまたはアプリにアクセスしたことがあるユーザーの数。

https://support.google.com/analytics/answer/12253918?hl=ja

指定した期間に、過去のセッションを 1 回以上開始したユニーク ユーザーの数。過去のセッションがエンゲージメント セッションだったかどうかは関係ありません。

https://support.google.com/analytics/answer/12253918?hl=ja

そもそもリピーター数を算出する必要があるのか？

探索の値と合致するリピーター数の正確な算出方法が不明ではあるが、そもそもリピーター数を出す必要があるのか？SQLを用いてレポートを作成する際の定石として不必要なコーディングを減らし、既にある指標で算出できるのであればそれを用いてリピーター数を計算すればよいのではないかと思うし、実際の業務ではそのようにしている。すなわちリピーター数の算出式は以下の通りになる。

新規ユーザー数はその日の訪問が新規訪問であれば、その日は何回セッションが変わったとしても新規ユーザーとして扱う。この定義にすれば、新規ユーザー数とリピーター数の合算が必ずアクティブユーザー数の合計と合致するため、レポートの読み手としても不要な誤解を与えることもない。

必ずしもGA4の定義に則る必要もなく、指標の意味と整合性が取れていればカスタマイズできるのがBigQueryのデータ活用の強みでもあるので柔軟性を持って対応していくことも必要である。