見出し画像

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

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

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

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

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

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

画像6

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

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

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

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

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

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

画像1

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

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

画像11

[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

画像9

上記の[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日1テーブル、自動的に生成されます。複数の日付を対象にデータを取得したい場合は、複数のテーブルに対してSQLを実行する必要があります。上記の「_TABLE_SUFFIX」を使った書式で記述することで、複数の日付のテーブルを1つのテーブルのようにまとめて扱うことが可能になります。

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

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

結果はこうなります。

画像9

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

画像8

[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」について集計したもので、実行結果は、

画像8

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

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

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

画像9

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

結果はこうなりました。

画像8

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

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

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

画像9

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数の集計ができています。

画像10

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

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

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





この記事が気に入ったらサポートをしてみませんか?