[GA4+BigQuery]アクティブユーザー数を集計するSQLクエリ

本記事では、BigQueryにエクスポートしたウェブサイトのGA4データでアクティブユーザー数を集計するSQLクエリについて解説をおこなう。

アクティブユーザー数はGA4で新しく導入された指標のため、定義からしっかりと確認する必要がある。

SQLクエリ概要

Googleのアナリティクスヘルプを確認すると、アクティブユーザーの定義として以下のように記載されている。

エンゲージメントセッションが発生するか、アナリティクスで以下の情報が収集されると、アクティブユーザーとして認識されます。

ウェブサイトの first_visit イベントまたは engagement_time_msec パラメータ

Android アプリの first_open イベントまたは engagement_time_msec パラメータ

iOS アプリの first_open イベントまたは user_engagement イベン

（出典）

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

上記の定義に基づいて、ウェブサイトのアクティブユーザー数を集計するには「エンゲージメントセッションが発生」または「 first_visitイベントが発生」または「 engagement_time_msec パラメータが発生」という条件を満たしたユーザー数をカウントすれば良いということになる。

以下に、それぞれの条件の説明をする。

・「エンゲージメントセッションが発生」

「エンゲージメントセッションが発生」という条件は session_engeged パラメータを使用し、 session_engaged = ‘1’ となった場合にエンゲージメントセッションが発生したとみなす（※ 1は数値ではなく文字列なので要注意）。

・「 first_visitイベントが発生」

「 first_visitイベントが発生」という条件は event_name = ‘first_visit’ と定義する。

・「engagement_time_msec パラメータが発生」

「engagement_time_msec パラメータが発生」という条件は engagement_time_msec > 0 と定義する。

そして、上記の条件をCASE文で記述し、 user_pseudo_id を重複なしでカウントすることで、ウェブサイトにおけるアクティブユーザー数を集計している。

SQLクエリサンプル

今回のクエリサンプルは以下のようになる。

WITH prep AS (
  SELECT
    event_date,
    event_name,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')
			AS session_engaged_flag,
    (SELECT value.int_value FROM UNNEST(event_params) 
      WHERE key = 'engagement_time_msec') AS engagement_time_msec
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202210*`
  ORDER BY
    event_date
)

SELECT
  event_date,
  COUNT(DISTINCT CASE WHEN session_engaged_flag = '1' THEN user_pseudo_id
                      WHEN event_name = 'first_visit' THEN user_pseudo_id
                      WHEN engagement_time_msec > 0 THEN user_pseudo_id END) AS active_user_count
FROM
  prep
GROUP BY
  event_date
ORDER BY
  event_date
;

■ BigQueryでの集計結果

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