UNNESTを知らないとBigQueryを使えない？

GoogleアナリティクスのデータをBigQueryで集計・分析可能に！

GA4がリリースされて、無料版のWebデータ（イベントなど）をBigQueryに格納することができるようになりました。以前はGA360（月額100万円以上）を契約しないとできなかったのができるようになったのはとても大きいです。

「SQLを使って購入時のユーザーIDと商品IDを抽出しよう！」

SELECT event_date, user_pseudo_id, items.item_id 
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` 
WHERE event_name ='purchase'
;

エラーが出て実行できない。。。

BigQueryには配列が入っている

上記のエラーは「配列内の要素（item_id）にはアクセスできません」ということです。

「配列？？」「テーブルに配列なんてあるの？？」

実は、BigQueryのカラムには配列も入れることが可能で、GA4やFirebaseのデータには多くの配列が含まれています。（※配列だけでなく構造型も入っていますがここでは省略します）

下記の赤い部分で1行となり、event_params (event_params.key, event_params.value.string_value)が配列となっています。

一方、event_dateやevent_timestamp, event_nameは配列ではないため、上の画像の緑色の部分が空白になっています。ですが、データが無いわけではなく、ざっくり表現すると下記のリストのようにevent_paramsの中にさらにデータが入っているという感じになるので、表形式で表現すると上の画像の緑色のような空白ができてしまいます。

• event_date
• event_timestamp
• event_name
• event_params
  • event_params1
  • event_params2
  • event_params3

配列の中身を取るにはUNNEST

「BigQueryには配列がある」ということを説明しましたが、最初のエラーにもあったように、配列の中身をそのまま参照することができません。

配列の中身を参照するには、いったん配列を展開する必要があります。それをできるのがUNNEST関数です。

下記のようなSQLを実行すると、先程はevent_dateやevent_timestamp, event_nameが空白になっていたところが、埋め尽くされていて、行番号も各行に振られているのがわかります。

SELECT event_date, event_timestamp, event_name, ep.key, ep.value.string_value
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
,UNNEST(event_params) ep
ORDER BY 1,2,3,4,5

先ほどのUNNESTをしない実行結果と比べると、赤枠で囲んだ部分が同じであることがわかります。

UNNEST(配列)とすることで、配列が1行ずつに展開されます。また、単に「配列」と書きましたが、配列の各要素が複数のカラムを持つSTRUCT型となっているものが多いです。つまり、GA4からエクスポートされるテーブルもevent_paramsカラムは下記のように、key、value.string_value、value.int_value、value.double_value、value.float_valueというカラムを内包しています。

• event_date
• event_timestamp
• event_name
• event_params
  • event_params （1要素目）
    • key
    • value.string_value
    • value.int_value
    • value.double_value
    • value.float_value
  • event_params（2要素目）
    • key
    • value.string_value
    • value.int_value
    • value.double_value
    • value.float_value
  • event_params（3要素目） ※以下略

UNNSETの問題点

「UNNESTしたらもうデータは取れるから問題ないね」と思うかもしれませんが、そう簡単にはいきません。データを取得する際に気をつけなければならない点があります。

配列内の複数の要素を条件に入れる場合

下記の結果からもわかるように、UNNESTは配列の要素を1行ずつ書き出しています。そのため、「page_title = “matplotlib.pyplotの’str’ object is not callableエラー | 4番は司令塔” AND page_referrer = “https://www.google.com/” 」のように同じ配列に入っていた要素を一緒に使おうとしても結果を抽出できません。（SQLはエラーではありませんが、結果が0件になります）

SELECT event_date, event_timestamp, event_name, ep.key, ep.value.string_value
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
,UNNEST(event_params) ep
WHERE page_title = 'matplotlib.pyplotの'str' object is not callableエラー | 4番は司令塔' AND page_referrer = 'https://www.google.com/'

購入時に商品Aと商品BigQueryを買った購入者を抽出したい場合

また、特定の複数の商品を購入した会員を抽出する場合も、UNNESTをしただけでは抽出できません。

SELECT event_date, event_timestamp,user_pseudo_id, i.item_id
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
UNNEST(items) i
WHERE event_name ='purchase'
;

配列の対処法

BigQueryの配列の対処法は大きく分けて2つあります。

1. 配列内の要素が同じ場合
2. 配列内の要素が異なる場合

配列内の要素が同じ場合

GA4からエクスポートされるテーブル内のitemsカラムのように、配列内の各要素が同じ（商品ID、商品名…）という場合、テーブルに対してUNNESTを行い、必要な情報をそれぞれ抽出していきます。

例えば、商品Aと商品Bを購入したユーザーを抽出したい場合、下記のような方法が一番わかりやすいです。

1. GA4のテーブルに対してUNNEST
2. 商品Aを購入したユーザーを抽出
3. 商品Bを購入したユーザーを抽出
4. 上記2と3のサブクエリをINNER JOINで連結 （INNER JOINは両方に含まれるものだけ抽出します）

with dataset AS(
SELECT event_date, event_timestamp,user_pseudo_id, i.item_id
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
UNNEST(items) i
WHERE event_name ='purchase'
)
, itemA AS(
SELECT *
FROM dataset
WHERE item_id = 'itemA'
)
, itemB AS(
SELECT *
FROM dataset
WHERE item_id = 'itemB'
)
SELECT DISTINCT user_pseudo_id
FROM itemA
INNER JOIN itemB USING(user_pseudo_id)
ORDER BY 1
;

対象の商品が多数となる場合、上記の方法だとサブクエリをたくさん作るのが面倒なので、下記のようにWHERE句で対象商品を限定した後、GROUP BYとHAVINGで購入商品種類が2種類以上という条件を追加することで、抽出することができます。

with dataset AS(
SELECT event_date, event_timestamp,user_pseudo_id, i.item_id
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
UNNEST(items) i
WHERE event_name ='purchase'
)
SELECT user_pseudo_id
FROM dataset
WHERE item_id IN( 'itemA', 'itemB')
GROUP BY user_pseudo_id
HAVING COUNT(DISTINCT item_id) >= 2
;

配列内の要素が異なる場合

event_paramsのように、配列内の各要素が異なる場合、UNNESTでそのまま抽出して1つのカラムにまとめてもあまり意味がありません。その場合は、SELECT部分にUNNESTを用いることで、各要素ごとに別のカラムとして抽出します。

そうすることで、page_title = ‘abcde’ AND page_referrer = ‘xyz’のような条件も追加することが可能になります。

SELECT event_timestamp, event_name
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_title') AS page_title
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') AS page_location
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_referrer') AS page_referrer
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
;

with dataset AS (
SELECT event_timestamp, event_name
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_title') AS page_title
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') AS page_location
,(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_referrer') AS page_referrer
FROM `xxxxxx.analytics_1XXXXXX7.events_20201125` e
)
SELECT *
FROM dataset
WHERE page_title = 'abcde' AND page_referrer = 'xyz'
;

まずは配列のパターンとUNNESTの使い方を暗記

SQLの経験があってもUNNESTや配列を初めて使う人はかなり戸惑うと思います。実際私の周りでもSQLの経験がある人でもGAのデータを扱うのになれるまで3ヶ月かかったりしています。

完全に理解しようとすると時間がかかるので、細かいことは抜きにして、UNNESTを使う時に前述の2パターンのどちらに当てはまるか？を考えて、当てはめてみることをおすすめします。