本記事ではBigQueryにエクスポートしたGA4データを用いて、初回訪問の年月ごとにユーザー数を集計するSQLクエリについて解説をおこなう。

こちらのクエリは、どれくらいのユーザーを、どれくらいの期間リテンションできているか、というのを定量的に把握したい時に有用な集計方法である。

SQLクエリ概要

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計を行なっている。そのため、下記のSQLクエリサンプルをコピペして、そのままBigQueryで実行して頂ければ、今回の記事と同じ結果を得ることができるようになっている。

Google Merchandise Storeは Google ブランドの商品を販売するオンライン ストアです。このサイトでは、Google アナリティクス 4 の標準のウェブ e コマースの実装と拡張計測機能が使用されます。BigQuery 一般公開データセット プログラムを通じて利用可能な ga4_obfuscated_sample_ecommerce データセットには、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月間の難読化した BigQuery イベント エクスポート データのサンプルが含まれています。

（出典）

Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプル データセット

BigQueryにエクスポートしたGA4データのスキーマにおいて、ユーザーの初回訪問の情報は user_first_touch_timestamp カラムにUNIX時間で格納されている（データ型は INT64となっている）。

また、TIMESTAMP_MICROS関数を使ってUNIX時間（INT64）から TIMESTAMP にデータを変換することができる。

さらに、EXTRACT関数を使ってタイムスタンプから、年と月の値を取得することができる。この時のデータの戻り値は、数値型のINT64となっている。

SQLクエリサンプル（年月別集計）

今回のSQLクエリサンプルを見てみよう。

２つのクエリを紹介する。

まずは、年月別集計のクエリを紹介する。

SELECT
  EXTRACT(YEAR FROM TIMESTAMP_MICROS(user_first_touch_timestamp)) AS first_touch_year,
  EXTRACT(MONTH FROM TIMESTAMP_MICROS(user_first_touch_timestamp)) AS first_touch_month,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
  first_touch_year,
  first_touch_month
HAVING
  first_touch_year IS NOT NULL
  AND first_touch_month IS NOT NULL
ORDER BY
  first_touch_year,
  first_touch_month
;

■ BigQueryでの集計結果

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

SQLクエリサンプル（年別集計）

続いて、年月別ではなく年別で集計する場合には、以下のようなクエリとなる。

SELECT
  EXTRACT(YEAR FROM TIMESTAMP_MICROS(user_first_touch_timestamp)) AS first_touch_year,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
  first_touch_year
HAVING
  first_touch_year IS NOT NULL
ORDER BY
  first_touch_year
;

■ BigQueryでの集計結果

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

ユーザーとそのリテンションの程度を定量的に把握したい時に、上記記事は参考になるのではないか。

また、今回の記事の関連記事を以下に挙げておくので、合わせて参考にされたい。

▼ユーザーのアクティブ頻度とその構成比を集計する方法に関する記事はこちら

▼アクティブユーザー数を集計する方法に関する記事はこちら