GA4のBigQueryエクスポートの仕様とデータの扱い方を説明する。GA4のログはネストされたレコードなど特殊な形式になっており、そのままでは扱いにくいのでいくつかテクニックがある。

BigQueryへのエクスポート設定

GA4の管理画面で設定するが、手順はこちらの記事を参照。

marketechlabo

Googleアナリティクス4のデータをBigQuery出力する | marketechlabo

https://www.marketechlabo.com/ga-app-web-property-to-bigquery/

テーブルの場所

GA4のBigQueryエクスポート設定で指定したプロジェクト
データセット「analytics_999999999」（「999999999」の部分はプロパティID）
テーブル名
- 前日までのデータ（日付別に）「events_20210101」
- 「20210101」の部分は日付
- 今日のデータ、前日未集計分のデータ「events_intraday_20210102」

出力時刻は午前7時ごろ。レポートのタイムゾーンに依存するため、米国のままにしていると夜11時ごろに出力される。GA4ではなくFirebase Analyticsの場合はUTC。タイムゾーン設定がない。

日付でシャーディングされたテーブル

GA4のBigQueryエクスポートでは日単位でテーブルが分かれている
- 前日まで：events_20210101, events_20210102, …, , events_20210127
- 当日：events_intraday_20210128
クエリをかける際、日単位で対象のテーブルを絞り込めばメモリの節約ができる

シャーディングとは分割するという意味。このようなクエリで期間指定抽出ができる。

SELECT
  ...
FROM
  `prj.analytics_999999999.events_*`
WHERE
  _table_suffix BETWEEN '20210101' AND '20210110'

以下は同じ

FROM `prj.analytics_999999999.events_*` WHERE _table_suffix = '20210101'
FROM `prj.analytics_999999999.events_20210101`

FROMの中でテーブル名にワイルドカードを使うと、それにマッチするテーブル名すべてを対象にできる。
そしてそれを絞り込むのにWHEREの中で_table_suffixという部分。これでテーブルの末尾の文字列を指定できる。単純にFROMの*だけでWHEREを使わないと全期間のデータが対象になるので課金で死ぬ。

主な項目

ユーザ識別子
イベント名: event_name
タイムスタンプ: event_timestamp（Unix time形式）
イベントパラメータ: event_params.***
ユーザープロパティ: user_properties.***
トラフィック: traffic_source.***
デバイス関連: device.***
地域: geo.***
eコマース関連変数
- ecommerce.***
- items.***
ストリームID: stream_id
（ウェブ、アプリなど1つのGA4プロパティで複数のストリームがある場合に識別する）

https://support.google.com/analytics/answer/7029846

ユーザの識別子

user_id: タグ／Firebase SDKで明示的に送ったユーザID
user_pseudo_id: 自動計測される端末／ブラウザのID（アプリの場合Instance ID、ウェブの場合はcookie）

セッションを特定するIDはイベントパラメータにある

トラフィック

そのユーザを最初に獲得した媒体

UTMパラメータ→列
- utm_source → traffic_source.source
- utm_medium → traffic_source.medium
- utm_campaign → traffic_source.name
パラメータがない場合はリファラに基づいて自動判別（UAと同じ）
セッションの開始ではない

utm_termとutm_contentは対象外。

イベントごとの参照元

UTMパラメータ→イベントパラメータ
- utm_source → source
- utm_medium → medium
- utm_campaign → campaign
- utm_term → term
- utm_content → content

セッション単位で見たい場合はクエリで抽出する必要がある

デバイス関連

Firebase（アプリ）のブラウザ情報とウェブのブラウザ情報は違う列に入る

デバイスカテゴリ: device.category（アプリ／ウェブ）
OS名: device.operating_system（アプリ／ウェブ）
OSのバージョン: device.operating_system_version（アプリ／ウェブ）
ブラウザ名
- device.browser（アプリ）
- device.web_info.browser（ウェブ）
ブラウザのバージョン
- device.browser_version（アプリ）
- device.web_info.browser_version（ウェブ）

イベントパラメータに含まれる主な変数

通算訪問回数: ga_session_number
セッションID: ga_session_id
ページのURL: page_location
ページのタイトル: page_title
リファラ: page_referrer

※ユーザープロパティにはデフォルトで計測されるものがないので、自分で追加

データの形式

イベントパラメータは特殊な形式になっており、このまま列名を指定してSELECTしても欲しい変数を取れない

これがGoogleアナリティクス＋BigQueryの厄介な形式。ユニバーサルアナリティクスのGA360でも似た構造だった。

ネスト（入れ子）されたデータ形式

データの親子構造をそのまま一つのテーブルに格納する。

一般的なRDBでは1個のセルには1個の値しか入らない

BigQueryではセルにテーブルが入ったような形式がある

一般的なRDBでは別テーブルにして正規化するところを、BigQueryではテーブルをテーブルの中に入れて済ませてしまう。入れ子になるテーブルは1行の場合もあれば、複数行になる場合もある（REPEATED）。BigQueryなど一部の分析用DBに固有の形式で、一般的なRDBにはあまりない。

GA4の場合

イベントパラメータ、ユーザープロパティ、eコマースの商品などがネストされている

このままでは使えないので、以下のように変換すると列名を指定して変数を取得できる

→ネストの部分を展開して通常のレコードにする：UNNEST（階層構造のフラット化）

UNNEST

たとえばページのURL（page_location）を扱う際、以下のようにクエリを書きたい

SELECT
  event_name,
  page_location
FROM
  `prj.analytics_999999999.events_20210127`;

SELECT
  user_pseudo_id,
FROM
  `prj.analytics_999999999.events_20210127`
WHERE
  page_location = 'https://example.com/products.php';

しかしこれはできない

ページのURLをSELECTしたい、もしくはURLを条件にユーザを抽出したい場合

クエリ中の

page_location

の部分を

(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location')

に置き換える。

ep.value.***_value
パラメータによって型が異なり、取得する列が異なる
- ep.value.string_value: 文字列型
- ep.value.int_value: 整数型
- ep.value.double_value: 浮動小数点小数型
ep.key = '***'

イベントパラメータの値を取得する（SELECT）

SELECT
  event_name, -- 通常の列
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') -- ネストされた列
FROM
  `prj.analytics_999999999.events_20210127`

イベントパラメータの値で絞り込む（WHERE）

SELECT
  user_pseudo_id
FROM
  `prj.analytics_999999999.events_20210127`
WHERE
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location')
   = 'https://example.com/products.php'

ユーザープロパティの場合は以下のように書き換えればいい

UNNEST(event_params) ep → UNNEST(user_properties) up
ep. → up.

イディオムで覚えておけばいい。

（例題）event_nameが「first_visit」「session_start」以外のイベントについて

user_pseudo_id
event_name

とともに

page_title
ga_session_number
link_url

を抽出

SELECT
  user_pseudo_id,
  event_name,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_title'   ) page_title,
  (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') ga_session_id,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'link_url'     ) link_url
FROM
  `prj.analytics_999999999.events_20210127`
WHERE
  event_name NOT IN ('first_visit', 'session_start');

UNNESTのもう一つのパターン（たまに使う）

セルの結合を解除して単純な行列にするイメージ。赤字は上の行の繰り返し

（例）各イベントヒットに対して計測されたパラメータ名の一覧を取得する

こんなテーブルを作る

1行目から5行目はパラメータ名以外同じ（前ページの赤字部分）

SELECT
  event_name,
  event_timestamp,
  ep.key,
FROM
  `prj.analytics_9999.events_20210127`, UNNEST(event_params) ep

FROMの中でテーブル, UNNEST(展開する対象)
展開する対象は
- event_params
- user_properties
- items
UNNEST(event_params) epのepは展開した部分の仮の名前

カンマはCROSS JOINという珍しいJOINの種類

（課題）当日データについて、以下の形式のテーブルを作る

すべてのパラメータ名と型を取得
それに対して以下の書式で列を作る
(SELECT ep.value.***_value FROM UNNEST(event_params) ep WHERE ep.key = '***')
デバイスやOSの列名は？

取得しているパラメータ

SELECT
  ep.key,
  SUM(case when ep.value.string_value is not null then 1 else 0 end) type_str,
  SUM(case when ep.value.int_value    is not null then 1 else 0 end) type_int,
  SUM(case when ep.value.double_value is not null then 1 else 0 end) type_double
FROM
  `prj.analytics_999999999.events_intraday_20210128`, UNNEST(event_params) ep
GROUP BY
  key

event_params.value.string_valueに値があれば文字列型、int_valueに値があれば整数型、double_valueに値があれば浮動小数点小数型→その数をカウント

答え

SELECT
  user_pseudo_id,
  event_timestamp,
  event_name,
  device.category,
  device.operating_system,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location'    ) page_location,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_title'       ) page_title,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_referrer'    ) page_referrer,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'source'           ) source,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'medium'           ) medium,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'campaign'         ) campaign,
  (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id'    ) ga_session_id,
    :
FROM
  `prj.analytics_999999999.events_intraday_20210128`;

実際にログを使った分析はこちらの記事を参照。

marketechlabo