GCP/Firebase/アクセス解析

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

FacebookTwitterLineHatena共有

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」の部分は日付
      • GA4のBigQueryエクスポート設定画面の「エクスポートタイプ」で「毎日」のチェックを入れている場合に出力される
    • 今日のデータ、前日未集計分のデータ「events_intraday_20210102」
      • 「20210102」の部分は日付
      • エクスポートタイプ設定で「ストリーミング」のチェックを入れている場合に出力される
      • 「ストリーミング」と「毎日」を両方オンにしている場合は毎日のテーブルが生成されるとこのテーブルが削除される

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

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

GA4のBigQueryエクスポートでは日単位でテーブルが分かれている。

  • 前日まで：events_20210101, events_20210102, …, , events_20210127
  • 当日：events_intraday_20210128

このように一つの内容（エンティティ）を表すテーブルを実体としては異なる複数のテーブルに分けて分散格納することをシャーディング（sharding）という。BigQueryでは共通の接頭辞で、末尾が_YYYYMMDD形式の日付で異なる名前のシャーディングテーブルはウェブ画面上では一つのテーブルであるかのようにまとめて表示される。

シャーディングテーブル

シャーディングされたテーブル群ではクエリをかける際、日単位で対象のテーブルを絞り込めばメモリの節約ができる

またクエリでも以下のようにfrom句でテーブル名を指定する際にワイルドカード*を付けると、そのワイルドカード条件にマッチするテーブル名、つまり前方一致でマッチするテーブル名のすべてを対象にできる。

select
  ...
from
  `prj.analytics_999999999.events_*`

そしてwhere句で_table_suffixを使ってワイルドカードの部分の文字列を指定することができる。

select
  ...
from
  `prj.analytics_999999999.events_*`
where
  _table_suffix between '20210101' and '20210110';

上はテーブル名末尾の日付文字列部分が20210101から20210110の間（2021年1月1日から2021年1月10日までのログ）を対象にしたクエリだが、結果は以下と同じ。

select ... from `prj.analytics_999999999.events_20210101`
union all
select ... from `prj.analytics_999999999.events_20210102`
union all
  :
select ... from `prj.analytics_999999999.events_20210110`

_table_suffixはアスタリスクの部分にマッチする文字列を表す。つまり下の2つのfromは同じものを意味する。

from `prj.analytics_999999999.events_*` where _TABLE_SUFFIX = '20210101'

from `prj.analytics_999999999.events_20210101`

_table_suffixを指定してクエリ対象のテーブルを絞り込むことによって、処理するデータ量つまりクエリコストの節約ができる。単純にfromでワイルドカードを指定したまま_table_suffixを使った絞り込みを行わないと全期間のテーブルが対象がクエリの対象になり、そのデータサイズに対して課金される。クエリを実行する際は必ず_table_suffixで期間を絞り込もう。一方でパーティショニングも同様にデータを分けて格納しているのですが、テーブルの実体としては一つである。シャーディングもパーティショニングもクエリを実行する際、日付単位で対象のテーブルを絞り込めばメモリの節約ができる。

なおシャーディングテーブルは基本的に同じ列構成になるのだが、GA4の場合、同じシャーディングテーブル群であってもある日を境に列が追加されることがある。その場合select *をすると列が一致しないというエラーが発生するので、列名もワイルドカードではなく個別に指定しなければならない。

主な項目

GA4のログは1行1イベントを表す履歴データである。1行の中にネストされたデータ構造を持つ列もある。

  • ユーザー
    • ユーザー識別子
    • ユーザープロパティ: user_properties.***
  • イベント
    • イベント名: event_name
    • タイムスタンプ: event_timestamp
    • バッチ処理関連
      • ページビューの識別子: batch_page_id
      • 同一ページ内での何番目の計測パケット送信なのか: batch_ordering_id
      • 同じ計測パケットの中でのイベントの順序: batch_event_index
    • イベントパラメータ: event_params.***
  • トラフィック
    • ユーザーの最初の流入元: traffic_source.***
    • 各イベント行における流入元: collected_traffic_source.***
    • セッションの流入元: session_traffic_source_last_click.***
  • デバイス関連: device.***
  • 地域: geo.***
  • eコマース関連変数
    • 取引に関する情報: ecommerce.***
    • 商品に関する情報: items.***
  • 同意関連: privacy_info.***
  • ストリームID: stream_id（ウェブ、アプリなど1つのGA4プロパティで複数のストリームがある場合に識別する）

ユーザー

ユーザの識別子として2種類の列がある。

  • user_pseudo_id: 自動計測される端末／ブラウザのID（アプリの場合Instance ID、ウェブの場合はcookieに記録されるクライアントID）
  • user_id: JavaScriptのタグ／Firebase SDKで明示的に送ったユーザID（計測者が指定した値）

user_pseudo_idは必ず値が入る（nullにはならない）のに対して、user_idはSDK側で明示的に取得設定をしない限り値が入らない（nullとして格納される）。

計測者が任意に指定したユーザ属性であるユーザープロパティはuser_properties.***に入る。ネストされた構造になっている。ユーザープロパティは最大で36文字しか格納できない。37文字以降は削除されて出力されるので、長い文字列のユーザー属性値を扱う場合は気を付けよう。たとえばSHA256でハッシュ化した16進文字列は64文字となるので、格納できません。

イベント

イベントに関連する変数としてイベント名はevent_nameに、イベントの発生したタイムスタンプはUnix timeのマイクロ秒として（timestamp型ではなくint64型）でevent_timestampに入る。

ページビューを特定するIDがbatch_page_idである。同じページビューで発生したイベントはこの値が同じになる。次にページの読み込み処理が発生するまでは同一の値が保持される。同じページビューで発生したイベントをグルーピングするのに使うことができるので、「あるページでバナーを閲覧→そのバナーをクリックした」などを把握することができる。

GA4では計測パケットを1イベントずつ送信するとは限らず、同一ページビュー内で発生した複数のイベントを後でまとめて送信することがある。これをGA4での「バッチ処理」とはこのまとめて送信することを表す。一つのページビューの中で何回目のパケット送信なのかを示す数値がbatch_ordering_idである。複数のイベントをまとめて送信したパケットでは、一つのbatch_ordering_idの中に複数のイベントレコードが含まれる。この一つのバッチの複数のイベントの中で何番目に発生したイベントなのかを表す数値がbatch_event_indexである。

batch_page_id、batch_ordering_id、batch_event_indexの関係は以下の図のようになっている。

GA4のバッチイベント送信処理

イベントとともに送られるイベント属性はイベントパラメータevent_paramsに入る。パラメータには明示的に設定せずに設定で自動的に計測されるものと、計測者によるタグ設定で送られるものがある。主なイベントパラメータには

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

などがある。セッションIDはこれ単体でセッションをユニークに識別することを意図したものではなく、ユーザの識別子（user_idまたはuser_pesudo_id）と組み合わせてセッションをユニークに特定する。

分析時に使いたい多くの変数がevent_paramsに入るはずである。event_paramsもuser_propertiesと同様のネストされた構造になっている。

トラフィック

GA4にはトラフィック関連のディメンションが多数ある。探索機能のディメンション一覧の「トラフィック ソース」の欄には多数の項目があるのを確認できるだろう。数は多いが、

  1. ユニバーサルアナリティクス時代からあるUTMパラメータベースの参照元（source）／メディア（medium）／キャンペーン（campaign）／キーワード（term）／広告コンテンツ（content）の種類
  2. イベント行単位での流入元／セッション単位の流入元／ユーザー単位の流入元という粒度の違い
  3. Google広告や検索広告360などと自動タグ連携をしている場合、連携元ツールの種類

というさまざまな切り口での分類になる。BigQueryエクスポートにもトラフィック変数の列が複数あり、画面のディメンションと対応しているものとしていないものがある。BigQueryエクスポートの列とGA4ディメンションの対応関係は以下のようになっている（1はsource、medium、campaign_nameのみ掲載）。

GA4のトラフィック変数

traffic_source.***

ユーザの初回訪問時の流入元を表す列がtraffic_source.***である。URLにUTMパラメータが含まれる場合は

  • utm_sourceの値がtraffic_source.source
  • utm_mediumの値がtraffic_source.medium
  • utm_campaignの値がtraffic_source.name（traffic_source.campaignではない）

に入る。これ以外のUTMパラメータであるutm_termとutm_contentは対象外となる。URLにUTMパラメータが含まれない場合はリファラに基づいて自動判別される。これらの列はセッションの流入元を表すものではない。なおストリーミング出力する場合のevents_intraday_***ではこれらの列の値は出力されない（すべてnullになる）。

collected_traffic_source.manual_***

各イベント行における流入元を表す列がcollected_traffic_source.manual_***である。

  1. URLにUTMパラメータが含まれればその値が採用される
  2. URLにUTMパラメータが含まれなければリファラに基づいて自動判定された値が入る

サイト内からの遷移であればすべてnullになる。

  • utm_sourceの値がcollected_traffic_source.manual_source
  • utm_mediumの値がcollected_traffic_source.manual_medium
  • utm_campaignの値がcollected_traffic_source.manual_campaign_name（ollected_traffic_source.manual_campaignではない）

になる。

アプリからの流入を見る場合など、実際にトラフィックについて調べる際はUTMパラメータの値に加えてリファラも同時に見るケースがあるが、リファラはイベントパラメータpage_referrerという違うところに入る点は留意しておこう。

collected_traffic_source.***id

各イベント行においてURLにGoogle広告やGoogleマーケティングプラットフォームから流入した際の連携用クリックIDが含まれる場合、それらの値を格納するのがcollected_traffic_source.***idになる。

  • Google広告の場合、collected_traffic_source.gclidに広告のgclid
  • キャンペーン マネージャー360やディスプレイ＆ビデオ360の場合、collected_traffic_source.dclidにDoubleClickのクリックID
  • Googleマーチャントセンターの場合、collected_traffic_source.srsltidにマーチャントセンターのクリックID

が格納される。

session_traffic_source_last_click.***

当該セッションの流入元を表す列がsession_traffic_source_last_click.***である。ここにはsession_traffic_source_last_click.manual_campaign.***とsession_traffic_source_last_click.google_ads_campaign.***があり、それぞれ流入元判定における手動タグ連携と自動タグ連携の変数が格納される。なおストリーミング出力する場合のevents_intraday_***ではこれらの列の値は出力されない（すべてnullになる）。

session_traffic_source_last_click.manual_campaign.***

当該セッションの流入時に

  1. URLにUTMパラメータが含まれればその値が採用される
  2. URLにUTMパラメータが含まれなければリファラに基づいて自動判定された値が入る

Googleアナリティクス画面のディメンション「セッションの手動参照元」「セッションの手動メディア」などが対応する。Googleアナリティクスの仕様としてノーリファラの場合は直近の流入にさかのぼって流入元が適用され、さかのぼる流入元がない場合にのみ参照元に(direct)、メディアに(none)が入るが、BigQueryエクスポートでも同様の動作になる。

session_traffic_source_last_click.google_ads_campaign.***

Google広告との自動タグ連携に基づく広告側の情報が入る。重要な列として

  • 広告アカウントのお客様ID（ディメンション「セッションのGoogle広告お客様ID」に該当）はsession_traffic_source_last_click.google_ads_campaign.customer_id
  • キャンペーン名（ディメンション「セッション – Google 広告キャンペーン」に該当）がsession_traffic_source_last_click.google_ads_campaign.campaign_name
  • 広告グループ名（ディメンション「セッションの Google 広告グループ名」に該当）がsession_traffic_source_last_click.google_ads_campaign.ad_group_name

に入る。Google広告に対してクリックコンバージョンをアップロードする場合は、session_traffic_source_last_click.google_ads_campaign.customer_idからお客様IDを取得し、collected_traffic_source.gclidをキーとしたコンバージョンデータを作成することになる。

Google広告の自動タグ連携と手動タグ連携を併用している場合（Googleアナリティクスと広告の管理画面で自動タグ連携をオンにしつつ、URLにUTMパラメータも付けている状態）にはsession_traffic_source_last_click.manual_campaign.***にはUTMパラメータの値が入り、session_traffic_source_last_click.google_ads_campaign.***には連携している広告アカウントの情報が入る。Google広告で手動タグ連携を使わず（URLにUTMパラメータを付けず）自動タグ連携のみの場合は、session_traffic_source_last_click.manual_campaign.***にはリファラに基づく値が入り、session_traffic_source_last_click.google_ads_campaign.***には連携している広告アカウントの情報が入る。したがってGoogle広告流入なのにsession_traffic_source_last_click.manual_campaign.sourceではyoutube、session_traffic_source_last_click.manual_campaign.mediumではreferralなどということもある。この後の自動タグ連携の列も見ないと真の流入はわからない。

session_traffic_source_last_click.sa360_campaign.***

検索広告360との自動タグ連携に基づく検索広告側の情報が入る。重要な列として

  • 参照元（ディメンション「セッションの SA360 参照元」に該当）がsession_traffic_source_last_click.sa360_campaign.source
  • キャンペーン名（ディメンション「セッションの SA360 キャンペーン」に該当）がsession_traffic_source_last_click.sa360_campaign.campaign_name

に入る。

session_traffic_source_last_click.cm360_campaign.***

キャンペーン マネージャー 360との自動タグ連携に基づくキャンペーン マネージャー 360側の情報が入る。

session_traffic_source_last_click.dv360_campaign.***

ディスプレイ＆ビデオ 360との自動タグ連携に基づくディスプレイ＆ビデオ 360側の情報が入る。

session_traffic_source_last_click.cross_channel_campaign.***

これらの自動タグ連携・手動タグ連携に基づいた最終的な「セッションの参照元」などの情報が入る。参照元、メディア、キャンペーンに加えてデフォルトチャネルグループの値も入ります。termとcontentは入らない。デフォルトチャネルグループでは、Google広告の自動タグ連携における検索広告のPaid SearchとP-MAXキャンペーンなどのCross-networkの違いも反映される。

トラフィックディメンションの関係

このようにGA4にはトラフィック関連の列が多数あるが、基本的にはsession_traffic_source_last_click.cross_channel_campaign.***を参照しておくといい。ただし前述したとおりノーリファラ流入が一部置き換えられるので、その点は留意する必要がある。分析の目的によってはcollected_traffic_source.manual_***と併用するのがいい。

デバイス・ブラウザ

ブラウザ情報は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（ウェブ）

デバイスカテゴリdevice.categoryでは、モバイルはmobile、PCはノートPCも含めてdesktop、タブレットはtabletという値になる。まれにコネクテッドTVがsmart tvとして入る。

eコマース

eコマースイベントの場合のイベント情報はecommerce.***、商品情報はitems.***に入る。1回のeコマースイベントで複数の商品が操作された（表示された、購入されたなど）場合は、itemsがネストされた構造になる。さらに各商品に対して任意の商品属性をカスタムディメンションとして指定することができるのだがそれらはitemsの下のitems.item_params.***に入る。item_paramsもevent_paramsやuser_propertiesと同様のネストされた構造になっている。

BQエクスポート全体図

公式のスキーマ情報（随時更新されているので）

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

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

イベントパラメータevent_params、ユーザープロパティuser_properties、eコマースの商品itemsはネストされているレコードになっている。BigQueryのテーブルスキーマでは種類が「RECORD」、モードが「REPEATED」になっている。

ネストのスキーマ定義

BigQuery UIではこのようにデータが表示されている。

ネストされている、入れ子になっているというのは、データの親子構造をそのまま一つのテーブルに格納した形である。一般的なRDBでは1個のセルには1個の値しか入らない。

一般的なテーブル

ネストされたデータはセルにテーブルが入ったような形式になっている。

テーブルのネスト

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

このまま列名を指定してSELECTしても欲しい変数を取れない。これがGoogleアナリティクス＋BigQueryの厄介な形式。ユニバーサルアナリティクスのGA360でも似た構造だった。

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の種類

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

  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`;

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

marketechlabo
 
ウェブログからSQLで指標を計算する8構文～GA4のBigQueryを題材に | marketechlabo
https://www.marketechlabo.com/weblog-metrics-ga4/
[公開日：2021年3月11日]

GCP/Firebase の記事一覧

, , , ,
FacebookTwitterLineHatena共有
詳細…