BigQueryではじめるWeb解析入門 ② 〜Google Analytics4(GA4)のデータ構造〜
前回はSQLの最小限の構文とBigQueryの画面の見方など概要を説明しました。次に、今後のSQLの説明を分かりやすくするため、Google アナリティクス（以降GA4）のデータはどのように格納されているかご紹介します。
GA4 BigQuery Export とは
前回、GA4のサンプルデータをクエリしましたが、皆様の中にはなぜBigQueryでGAのデータが見れるのか不思議に思った方もいるかもしれません。実は、GAにはBigQuery Exportという、生ログに近いデータを自動でエクスポートしてくれる機能があります。データを転送する特別な準備やコーディング、前処理は不要で、GAの管理画面で設定しておくだけで、サンプリングや集計されていないログをBigQueryに自動抽出して、複雑なセグメンテーションを適用したり自社のデータと統合して高度な分析に活用できるわけです。これはマーケターや分析担当の方にはとても嬉しい機能ではないでしょうか。
今回使うサンプルデータは実際のWebサイトで収集されたGA4のデータなので、このシリーズを読み終えた方は、ご自身のGAアカウントで試して同様の分析を頂くことも可能です。
GA4のデータ構造
こちらのURLを開くと、以下のように前回クエリーしたGA4のテーブル情報が表示されます。このスキーマというタブでは、テーブルに含まれるフィールド（列）名と種類（データ型）、モード（空白値を許可するかなど）が確認できます。
ここで、列の種類として、STRING（文字）やINTEGER（整数）のフィールドの他に、event_paramsのように「RECORD」という種別があるのにお気付きの方もいらっしゃるかと思います。このように、種類が「RECORD」でモードが「REPEATED」の列は構造体と呼ばれる特殊なデータ型で、内部に配列などさまざまなフィールドを内包しています。この構造体を組み合わせると、あたかも複数のテーブルを一つに結合しているかのように使えるため、個別のテーブルをJOINしてクエリーするよりも高速に複雑なデータを分析できるというメリットがあります。構造体のクエリ方法に関しては、後ほどご案内します。GA4のデータ構造について詳細はこちらをあわせて参照ください。
実際にどのようなデータが入っているかは「プレビュー」タブから参照できます。プレビューは料金がかからないので、まずデータ全体像を確認したい場合におすすめの機能です。
このevents_表は画面上部分に「2021–01-31」と日付が表示されていることに注目ください。BigQueryは、実は日付ごとにデータを分割して格納するシャーディングという機能があり、この機能を使うことで検索の速度やコストを改善することが可能です。この日付でデータを分割する機能は、GA4やFirebaseのようなアプリやWeb行動ログ、IoTなど大量のログが発生するユースケースではとても便利です。
GA4は、各行がサイト内の一イベントに対応しています。すべてのデータがeventとeventパラメータで構成されています。例えば、あるユーザがサイトを初回訪問するとfirst_visitというイベントが送信され一行追加されるイメージとなります。GA4でデフォルトで収集されるイベントの種類はこちらを参照ください。
また、event_paramsという列に下矢印が表示されてるのにお気づきでしょうか。上でご説明したように、これは構造体と呼ばれるデータ型です。GA4のデータ構造はやや特殊で、ExcelやCSVのような単純な表形式と違い、入れ子のような階層構造になっています。構造体の中にはさまざまなフィールドが内包されており、例えば、event_paramsという列の中にはkeyとvalueから構成される配列が格納され、イベントの詳細な情報が格納されています。
BigQueryでこのようなネストされた形式を参照するにはUNNESTという関数を使います。FROM句の中で表名とともにUNNEST（配列）を指定することにより、行が配列の数だけ複製され、配列の内容が展開されたような状態で内容を参照することが可能になります。表示内容を分かりやすくするため、BigQueryコンソール上部の「エディタタブを非表示にする」という項目を選択してから以下のSQLを実行してみてください。
SELECT event_params FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`このように、日付分割された表に対して、2021年1月31日のデータだけ検索したい場合、以下のようにテーブル名の末尾をyyyy(年)mm(月)dd(日)の日付形式で指定します。event_paramsをそのまま検索すると、一つの行の中に以下のように複数の配列が表示されています（行が1と表示されているのに注目ください）。
では、次に以下のようにUNNEST(event_params)をSQLで指定してみましょう。
SELECT params
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,
UNNEST(event_params) as params
すると、1行だったデータが複数行に分割され、配列のkeyとvalueが表示されていることがわかります。
まとめ
今回はGA4のデータ構造概要と、GAのデータをクエリする時に重要な日付分割表とネストされたデータのクエリについてご紹介しました。SQLが始めての方には少し分かりにくい点もあると思いますが、後々GA4のデータを分析する時に知っておくと便利な概念なので、以下の三点だけ頭のどこかにとどめて頂けると幸いです。
- GAのデータは日付ごとに分割されてデータが格納されている
- GA4のデータはイベント単位で行が記録されている
- GA4のデータは一つのイベント（行）の中にevent_paramsという配列が含まれている
次回は、GA4のユーザ数やPVといった主要な指標を参照する方法をご紹介します。
