[BigQuery]入門 GA4のイベントを外部のテーブルと結合してカンタン集計

この記事を読んで頂きたい方

GA4からBigQueryにエクスポートしたイベント データ テーブルをSQLで扱いたい方。
（SQLの書き方の正解は1つではありませんので、以下のSQL文は、こういうやり方がある、ということでご覧ください。）

GA4からBigQueryにエクスポートしたイベント データ

この記事では、「GA4」からエクスポートしたテーブルのうち、[event_params] で始まるカラムを取り上げます。

GA4からBigQueryにエクスポートしたデータの「テーブル スキーマ」を見ると、[event_params]の「種類」は「RECORD型」となっています。

[event_params]の「種類」は「RECORD型」

Google Developers Japan ブログの中に下記のような一文があります。（太字で強調したのは筆者）

BigQuery 表の各行は、単純なキーと値のペアである必要はありません。各行は単純なデータ（文字列、整数、浮動小数点数など）を含む JSON オブジェクトのようなものですが、これには配列、構造体、構造体の配列などのさらに複雑なデータも含めることができます。
出所：Google Developers Japan: BigQuery 活用術: UNNEST 関数

[event_params]のデータの「種類」は「RECORD型」ですが、それはすなわち、「構造体の配列」であることを意味しています。上記と同じ記事には次のように書かれています。

イベント パラメータが構造体の配列としてイベントの中に含まれています。
出所：Google Developers Japan: BigQuery 活用術: UNNEST 関数

「[event_params]は『構造体の配列』である」とは？

上記のキャプチャーでは、[event_params]で始まるカラムが見えます。

BigQueryにエクスポートしたGA4のデータのスキーマを見ると、[event_params]は下記のようになっています。

[event_params]
　┣[event_params.key]（イベントパラメータのKey）
　┗[event_params.value]（valueで終わるものは存在しない（注））
　　　┣[event_params.value.string_value]（データ型：文字列）
　　　┣[event_params.value.int_value]（データ型：整数）
　　　┣[event_params.value.float_value]（データ型：浮動小数点）
　　　┗[event_params.value.double_value]（データ型：浮動小数点）

（注）1つのカラムには1つのデータ型しか格納できないため、[event_params.value]のカラムは分かれています。

1 個の構造体の中に含まれれる値のことをメンバ（member）、それらの名前をメンバ名といいます。構造体のメンバの値を読み出したり、メンバに新しい値を代入したりするには、.（ドット）演算子を使います。まず構造体型の変数 event_params を宣言しています。次に各メンバに値を代入しています。（例）event_params.key

上記の[event_name]が「page_view」のデータを見ると、12行あって、一見12レコードあるように見えますが、左側のカラムがグレーに塗られているように、この12行で1レコードです。

イベントとパラメータ

1レコートが1つのイベントを格納しているわけですが、1レコードの中のテーブルが、イベントの属性であるパラメータを格納しています。

ここでたとえば、[event_params.key]＝[page_title]の値を取り出したいとします。[page_title]はカラムではないので、テーブルから直接SELECTすることができません。そこで、下記のように書きます。

下記の例は、イベント名「page_view」について、ページタイトルの一覧を出すというSQLです。

WITH kasou_table AS
(
SELECT event_timestamp, event_name, event_params
FROM `analytics_224349690.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20201110" AND "20210109"
AND event_name = "page_view"
)

SELECT event_timestamp,event_name, 
(
SELECT value.string_value FROM UNNEST(event_params)
WHERE key = "page_title"
)
AS page_title
FROM kasou_table
ORDER BY event_timestamp ASC

ちなみに、

FROM `analytics_224349690.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20201110" AND "20210109"

の部分について解説します。

GA4からエクスポートされるBigQuery上のテーブルは1日１テーブル、自動的に生成されます。複数の日付を対象にデータを取得したい場合は、複数のテーブルに対してSQLを実行する必要があります。上記の「_TABLE_SUFFIX」を使った書式で記述することで、複数の日付のテーブルを１つのテーブルのようにまとめて扱うことが可能になります。

FROM句の末尾にある（*）はワイルドカードで、それにどのような値を入れるかを事業のWHERE句で指定しています。「_TABLE_SUFFIX」はテーブルの接尾辞を示す変数で、テーブルの末尾に来る値を指定します。

「FROM `analytics_224349690.events_*`」の部分ですが、ワイルドカード テーブル名には特殊文字（*）が含まれているため、ワイルドカード テーブル名はバッククォート（`）文字で囲む必要があります。

結果はこうなります。

上記の実行結果を見ると、元々、テーブルに[page_title]というカラムがあったかのように見えますが、下記のデータにあるように、[page_title]はカラムではありません。

[page_title]はカラムではないのに、それでも、カラムのように扱えるのは、上記のSQL文に、

SELECT value.string_value FROM UNNEST(event_params)
WHERE key = "page_title") AS page_title

のUNNEST関数の記述があるからです。

UNNEST(event_params)という記述で、「RECORD型」である「event_params」を処理しています。

上記のSQLの結果は、イベント名「page_view」について集計したもので、実行結果は、

1329レコードでした。ということは、PV数は1329PVであるはずです。これを簡単に確認するには、たとえば、下記のようなSQLを書きます。

SELECT COUNT(*) AS pageview
FROM `analytics_224349690.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20201110" AND "20210109"
AND event_name = "page_view"

実行すると、下記のような結果となります。

PV数が1329PVであることは確認できました。

続いて、ページタイトル別のPV数を集計します。ここでもUNNEST関数を使っています。

SELECT page_title
, COUNT(*) AS pageview
FROM (
SELECT
(
SELECT value.string_value FROM UNNEST(event_params)
WHERE key = "page_title") AS page_title
FROM `analytics_224349690.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20201110" AND "20210109"
AND event_name = "page_view"
)
GROUP BY page_title
ORDER BY pageview DESC

結果はこうなりました。

ここまでの話ですと、わざわざBigQueryを操作する意味が無いと思われるかもしれません。そこで、BigQueryを使うと、どのように便利かという一例をご紹介します。

BigQueryなら「テーブルの結合」が簡単

たとえば、下記のような「URL」と「コンテンツのジャンル」の対照表がテーブル（CSVファイル：genre.csv）として管理されていたとします。このコンテンツの「ジャンル毎にPV数を集計」したいとなったとします。

GA4のテーブルと対照表のテーブル（CSVファイル：genre.csv）が共通して持つフィールド（今回の場合は、[page_location]）があれば、「ジャンル毎にPV数を集計」は、簡単に実現できます。

SELECT genre
, COUNT(*) AS pageview
FROM (
SELECT
(
SELECT value.string_value FROM UNNEST(event_params)
WHERE key = "page_location") AS page_location
FROM `analytics_224349690.events_*`
WHERE _TABLE_SUFFIX BETWEEN "20201110" AND "20210109"
AND event_name = "page_view"
)
INNER JOIN for_note.ga4_genre
USING (page_location)
GROUP BY genre
ORDER BY 2 DESC

上記のSQL文では、テーブルの結合を行っています。

INNER JOIN for_note.ga4_genre
USING (page_location)

の部分で、「URL（フィールド名：page_location）」と「コンテンツのジャンル（フィールド名：genre）」の対照表をGA4のテーブル（上記でのテーブル名は「ga4_genre」）と結合しています。

テーブルの結合方法は、上記で用いた「INNER JOIN」以外にも、複数の方法があります。詳しくは、Googleのドキュメントをご参照ください。

Google Cloud  BigQuery > ドキュメント > リファレンス
標準 SQL のクエリ構文
JOIN 演算

実行結果はこのようになり、ジャンル別のPV数の集計ができています。

3つ以上のテーブルを連結することも、もちろん可能です。

GA4のデータとテーブルとして管理されている別のデータを組み合わせて解析したい場合、BigQueryは非常に便利なツールです。

------------------------------------
弊社への「GA4の導入設計、設定」等のご相談はサイトよりお願いいたします。
▼お問い合わせ
and,a（https://and-aaa.com/）
------------------------------------