ga_session_numberで日次の新規ユーザー数を集計するSQLクエリ

今回は前回に引き続き、日次の新規ユーザー数を集計する方法を紹介する。

前回記事と併せてお読み頂きたい。

■GA4+BigQueryで日次の新規ユーザー数を集計する①（first_visitイベントを使う）

https://www.ga4query.com/blog/ga4bigquery%e3%81%a7%e6%97%a5%e6%ac%a1%e3%81%ae%e6%96%b0%e8%a6%8f%e3%83%a6%e3%83%bc%e3%82%b6%e3%83%bc%e6%95%b0%e3%82%92%e9%9b%86%e8%a8%88%e3%81%99%e3%82%8b%e2%91%a0%ef%bc%88first_visit%e3%82%a4/

SQLクエリの概要

GA4の探索画面では、新規ユーザー数の定義として「サイトと初めて接触した、またはアプリを初めて起動したユーザーの数（発生イベント : first_open）」という記述がある。

BigQueryにエクスポートしたGA4データのevent_params カラムの中に ga_session_number というパラメータがあり、これは「あるユーザーが現在のセッションまでに開始したセッションの数」を示している。つまり、 ga_session_number が1であるということは初回訪問を示しており、新規ユーザーとして定義できる。

ga_session_numberパラメーター

実際のSQLクエリを見てみよう。

WITH prep AS (
  SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202209*`
)

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS new_users
FROM
  prep
WHERE
  ga_session_number = 1
GROUP BY
  event_date
ORDER BY
  event_date
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下になった。

GA4管理画面（探索機能）での集計結果

続いて、GA4の探索機能を活用した集計結果は、以下になった。

BigQueryとGA4の集計結果は、ほぼ一致した。

■ GA4公式ドキュメント

[GA4] セッションについて

https://support.google.com/firebase/answer/9191807?hl=ja

[UA→GA4] 指標の比較: Google アナリティクス 4 とユニバーサルアナリティクス

https://support.google.com/analytics/answer/11986666?hl=ja&ref_topic=10737980#users