Googleアナリティクス4のBigQueryエクスポート仕様

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

BigQueryへのエクスポート設定

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

テーブルの場所

  • 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_sourcetraffic_source.source
    • utm_mediumtraffic_source.medium
    • utm_campaigntraffic_source.name
  • パラメータがない場合はリファラに基づいて自動判別(UAと同じ)
  • セッションの開始ではない

utm_termutm_contentは対象外。

イベントごとの参照元

  • UTMパラメータ→イベントパラメータ
    • utm_sourcesource
    • utm_mediummedium
    • utm_campaigncampaign
    • utm_termterm
    • utm_contentcontent

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

デバイス関連

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) epUNNEST(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) epepは展開した部分の仮の名前

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

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

  1. すべてのパラメータ名と型を取得
  2. それに対して以下の書式で列を作る
    (SELECT ep.value.***_value FROM UNNEST(event_params) ep WHERE ep.key = '***')
  3. デバイスや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`;

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

[公開日:2021年3月11日]

アクセス解析 の記事一覧