GA4+BigQueryでデバイス/OS/ブラウザ別のUU数を集計するSQL

今回は、Googleアナリティクスを用いたアクセス解析において確認頻度の高い、デバイス別、OS別、ブラウザ別のユニークユーザー数（UU数）を、BigQueryにエクスポートしたデータで集計するためのSQLクエリを紹介する。

SQLクエリ概要

まず、device フィールドに格納されているデータを使う。

このdevice フィールドは、列のデータ型が RECORD 型に設定されており、その中にネストされた値は device.category という形で取得することができる。

そして、device の中にはさらに web_info という RECORD 型のフィールドがあり、その中の値は device.web_info.browser という形で取得することができる。

（BigQuery公式ドキュメント参考）

テーブルスキーマでネストされた列と繰り返し列を指定する

ユニークユーザー数（UU数）の集計に関しては、こちらの記事で詳しく解説している。

日次のユニークユーザー数を集計するSQLクエリ | GA4QUERY

SQLクエリサンプル

１つ目の、デバイス別UU数集計のクエリサンプルは、以下のようになる。

デバイス別のユニークユーザー数（UU数）を集計するSQLクエリ

SELECT
  device.category AS device,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `technogram-354804.analytics_291973511.events_intraday_202210*`
GROUP BY
  device
ORDER BY
  user_count DESC
;

■ BigQueryでの集計結果

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

続いて２つ目の、OS別UU数集計のクエリサンプルは、以下のようになる。

OS別のユニークユーザー数（UU数）を集計するSQLクエリ

SELECT
  device.operating_system AS OS,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `technogram-354804.analytics_291973511.events_intraday_202210*`
GROUP BY
  OS
ORDER BY
  user_count DESC
;

■ BigQueryでの集計結果

BigQueryの集計結果は、以下のようになる。

続いて３つ目の、ブラウザ別UU数集計のクエリサンプルは、以下のようになる。

ブラウザ別のユニークユーザー数（UU数）を集計するSQLクエリ

SELECT
  device.web_info.browser AS browser,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `technogram-354804.analytics_291973511.events_intraday_202210*`
GROUP BY
  browser
ORDER BY
  user_count DESC
;

■ BigQueryでの集計結果

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

最後に４つ目の、デバイス/OS/ブラウザの組み合わせ毎のUU数集計のクエリサンプルは、以下のようになる。

デバイス/OS/ブラウザの組み合わせごとのユニークユーザー数（UU数）を集計するSQLクエリ

SELECT
  device.category AS device,
  device.operating_system AS OS,
  device.web_info.browser AS browser,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `technogram-354804.analytics_291973511.events_intraday_202210*`
GROUP BY
  device,
  OS,
  browser
ORDER BY
  user_count DESC
;

■ BigQueryでの集計結果

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

■ GA4（Googleアナリティクス4）探索画面での集計結果

また、GA4での探索画面での集計結果は、以下のようになる。

BigQueryの集計結果と、GA4の探索画面での集計結果が一致したことがわかる。