GA4

【GA4SQL】新規ユーザー数・リピーター数の取得

2024.04.28 2024.05.15

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

新規ユーザー数の日次カウント
- 新規ユーザー数の定義
リピーター数の日次カウント
- リピーター数の定義

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

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」以上であるユニークユーザー数がリピーターとなるはずであるが、探索との値とはズレが生じる。様々なクエリを検証してみたがいずれにおいても合致することはなかった。ちなみに公式ヘルプにも記載があるように、エンゲージメントセッションであるかは関係がないため、この条件を加えると探索との値は近くなるにしても値がズレる原因ではないと思われる。