Zenn
🐋

BigQueryでGA4のイベント集計

2022/12/14に公開
Google Analytics
データ分析
BigQuery
GA4
tech

はじめに

Google Analytics 4プロパティ(以下GA4)をBigQueryに連携(BigQuery Export)すると、GA管理画面上のレポートより詳細・柔軟な分析を高速に行うことができます。
この記事ではGA4のイベント集計をBigQueryではじめる方向けに役立ちそうなことについて書いてみます。

BigQueryでデータを確認する

テーブル

BigQueryプロジェクトへエクスポートされたデータは、analytics_{プロパティID} という名前のデータセットで保存されます。エクスポートの頻度を毎日に設定している場合、このデータセット内にevents_YYYYMMDD という日付毎のテーブルが毎日作成されます。

イベントテーブルのフィールド

各events_YYYYMMDD テーブルには、ユーザーの個々のイベントが1行ずつ記録されています。
テーブルは次のようなフィールドを列に含んでいます：

フィールド(抜粋) 型 メモ
event_date STRING イベントが記録された日付
event_name STRING イベント名(session_start やpage_viewなど)
user_pseudo_id STRING GA4側で自動で作成されるユーザーの仮の識別子。
GA4 [探索]レポート > ユーザーエクスプローラ確認できる「アプリ インスタンスID」のこと。
event_params RECORD イベントの各パラメータを格納した繰り返しレコード。
traffic_source RECORD ユーザーの初回来訪時(≠ セッション開始時)の流入元の情報を格納したレコード。

[GA4] BigQuery Export スキーマ

event_params 列には配列が格納されている

GA4では一つのイベントに複数のパラメータを記録できますが、これらはevent_params にオブジェクト配列として格納されています。

実際にBigQuery上でイベントデータの中身を見てみると、session_startイベントを表す一行に、複数のevent_paramsが格納されていることがわかります。

これはは次のようなオブジェクトと考えるとわかりやすいです。

{
	"event_name":"session_start",
	"event_params":[
		{"key":"page_referrer", "string_value":"https://www.bing.com/"},
		{"key":"ga_session_id", "int_value":1670474519},
		{"key":"page_location", "string_value":"https://exammple.com/page1"},
		{"key":"page_title", "string_value":"page1"},
		・・・(省略)
	]
}

event_paramsの中身に関わる集計を行う場合は、注意が必要です。

ga_session_id について

GA4ではga_session_id という識別子を計測時に生成し、ユーザーの異なるセッションを識別しています。

セッション ID は、イベントが発生したセッションを示します。たとえば、ユーザーがお客様のサイトで 2 つのセッションを別々に実施した場合、2 つの異なるセッション ID が生成されます。(アナリティクス ヘルプ)

ga_session_id はセッション内で発生した各イベントに関連づけられ、パラメータとしてevent_params の中に格納されます。

クエリの組み立て方

ここでは、GA4のイベント集計を行うクエリの作成時に役立つことSQLと併せて紹介します。

イベント数の集計

各種イベント数の集計は、イベントに該当するレコードの行数をカウントすることで行うことができます。

COUNTIF 関数を使う方法と、グループ集計による方法を紹介します。

その他集計したいイベント名は公式のヘルプから調べることができます。

方法1 `COUNTIF`関数を使う方法
  • 3行目で、ユーザー識別子の個数を数えることでユーザー数を算出しています(Cookieベース)。
  • COUNTIF 関数を用いて、イベント名ごとのイベント数を集計しています。
SELECT
　　　　COUNT(DISTINCT user_pseudo_id) AS users,
　　　　COUNTIF(event_name = 'session_start') as sessions,
　　　　COUNTIF(event_name = 'page_view') as page_views
FROM
　　　　`{project name}.analytics_{ID}.events_*`
WHERE
　　　　_TABLE_SUFFIX BETWEEN '20221101' AND '20221130'

結果

行 users sessions page_views
1 292 422 483
方法2 グループ集計を使う方法

event_name 列に関するグループ集計によっても集計可能です。

SELECT
　　　　event_name,
　　　　count(*) as events
FROM
　　　　`{project name}.analytics_{ID}.events_*`
WHERE
　　　　event_name IN ('page_view', 'session_start')
　　　　AND _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
GROUP BY event_name

結果

行 event_name events
1 session_start 422
2 page_view 483

event_params 列の中身を列に展開する

event_params はkey-value型オブジェクトの配列が格納されているため、集計時には展開する必要があります。

中身を展開するには、UNNEST 演算子とCROSS JOINを組み合わせます。

/**
 * イベントテーブルにevent_paramsの中身のkey, valueを展開した列をつける
 */
SELECT
  key,
  value.string_value,
  value.int_value,
  value.float_value,
  value.double_value
FROM
  `{project name}.analytics_{ID}.events_*`
  CROSS JOIN
    UNNEST(event_params)
WHERE
  _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'

配列の操作 | BigQuery | Google Cloud

使用例：ページURLごとにユーザー数、セッション数、PV数を集計する

ページURLは, event_params 配列のpage_location キーの文字列値を参照します。

SELECT
　　　　　　　　value.string_value AS page_URL,
    count(DISTINCT user_pseudo_id) AS users,
    countif(event_name = 'session_start') as sessions,
    countif(event_name = 'page_view') as page_views
FROM
　　　　　　　　`{project name}.analytics_{ID}.events_*`
	CROSS JOIN
	UNNEST(event_params)
WHERE
　　　　　　　　key = 'page_location' -- ページURL
　　　　　　　　AND _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
GROUP BY
　　　　　　　　value.string_value

WITH句

WITH句を使うとクエリ結果に名前をつけて参照ができ、可読性が高まります。セグメント毎のイベント集計のような集計を行う際に役立ちます。

使用例：初回来訪とリピーターにセグメント分けを行い、ユーザー数、セッション数、PV数を集計
/**
* 初回来訪/リピーターでセグメント分け(初回来訪(is_new_user = 1)/
* リピーター(is_new_user = 0))し、UserInfoと名前をつけ、イベントテーブルと
* 左外部結合を行う。
*/
WITH 
UserInfo AS (
SELECT
  user_pseudo_id,
  MAX(IF(event_name = 'first_visit', 1, 0)) AS is_new_user
FROM
  `{project name}.analytics_{ID}.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
GROUP BY user_pseudo_id
)
SELECT
    UserInfo.is_new_user,
    COUNT(DISTINCT UserInfo.user_pseudo_id) AS user_count,
    COUNTIF(event_name = 'session_start') AS sessions,
    COUNTIF(event_name = 'page_view') AS page_views
FROM
    UserInfo
    LEFT JOIN (
        SELECT
	    user_pseudo_id,
	    event_name
	FROM
	    `{project name}.analytics_{ID}.events_*`
	WHERE
	    _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
    ) AS T
    ON UserInfo.user_pseudo_id = T.user_pseudo_id
GROUP BY
    is_new_user

結果

行 is_new_user user_count sessions page_views
1 1 270 352 390
2 0 22 70 93

ユーザー単位の集計、セッション単位の集計

ユーザー単位の集計を行いたい場合、user_pseudo_id でグループ化を行います。

使用例：ユーザーを最初に獲得した流入元、メディア別のセッション数、PV数をユーザー単位で集計
/**
* ユーザーを最初に獲得した流入元、メディア別のセッション数、PV数をユーザー単位で集計
* ユーザー単位の集計 -> user_pseudo_idでグループ化
*/
SELECT
    user_pseudo_id,
    traffic_source.source, 
    traffic_source.medium,
    COUNTIF(event_name = 'page_view') AS page_views,
    COUNTIF(event_name = 'session_start') AS sessions
FROM
    `{project name}.analytics_{ID}.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
GROUP BY
    user_pseudo_id, traffic_source.source, traffic_source.medium

またセッション単位で集計を行いたい場合、user_pseudo_id とga_session_id の両方でグループ化を行います。ga_session_id はevent_params に格納されているので、列として展開して取り出すことで集計できます。

使用例：デバイスカテゴリ×各セッションごとにPV数を集計
/**
 * デバイスカテゴリ(desktop, mobile, tablet)ごとに各セッション
 * のPV数を取得する
 */
SELECT
  user_pseudo_id,
  value.int_value as ga_session_id,
  device.category as device_category,
  COUNTIF(event_name = 'page_view') as page_views
FROM
  `{project name}.analytics_{ID}.events_*`
  CROSS JOIN
    UNNEST(event_params)
WHERE
  key = 'ga_session_id'
  AND _TABLE_SUFFIX BETWEEN '20221101' AND '20221130'
GROUP BY
  user_pseudo_id, value.int_value, device.category

まとめ

GA4のデータをBigQueryでクエリ集計するために必要な基礎知識を整理してみました。
その他、分析クエリの組み立てパターン等是非アドバイスください！

参考
Google アナリティクス 4 のイベントデータのエクスポートに関する基本的なクエリ
GA4用のBigQuery クエリ集(GA4 Guide)

Discussion