Big QueryのUNNESTについて

Big Query上では、ほかのSQLなどでは使えない配列やREPEATEDなカラムなどを扱うことができる。Google Analyticsには、REPEATEDなカラムを持っている。

下の図を例に挙げて説明すると、下の図の全範囲で1行という扱いになり、event_paramsが配列になっている。

しかし、この配列の要素の値を直接取り出そうとすると、下記のようなエラーがでる。

エラー内容

簡潔に和訳すると、配列の値は取得できないと書かれている。

実は、Big Query上の配列やREPEATEDなカラムの要素は、上記のようにそのまま値を取り出すことはできず、UNNESTを使う必要がある。

上の二つの図からもわかる通り、UNNESTをしない実行結果とUNNESTを使用した実行結果が同じということがわかる。

UNNESTを使用すると、今回の場合はunnestをevent_paramsカラムにかけたため、epは、event_paramsカラムの要素のものしか持っていない。

UNNESTをすることで、配列が1行ずつ展開され、配列やREPEATEDなカラムの要素の中身を取り出すことができるようになる。

UNNESTの注意点

データの内容をそのまま表示したい場合は上記の方法で問題なく行えるが、UNNESTを使用したカラムで条件を指定するうえでは気を付けなければいけないことがある。

対処法には、2つのパターンが存在する

GA4の場合、上記のevent_paramsがこれにあたる。event_paramsは、keyの値に応じて、valueが表す内容が変わってくる。

ここでは、event_paramsのvalueの値に条件をかけることを最終目標とする。

まず、event_paramsのkeyに条件をかける場合について考えてみる。

event_paramsのkeyに条件をかける場合、例えば、keyがpage_title, page_locationのときだけという条件を付けたい場合、先程の方法で行うと、

上図からもわかる通り、表示するデータはないと表示されてしまう。

理由としては、UNNESTを使うことで、今まで配列でひとくくりだったものが一つ一つに分解されているからだ。

この問題を解決するには、select句にUNNESTを用いることが考えられる。

上図のようにうまく取り出すことができた。

次に、上のクエリを利用して、event_paramsのvalueに条件をかけてみる。

今回は、with句を使うことで、対応した。

上の実行結果を見てわかる通り、page_titleがHomeのものだけ結果として出力されるようになっている。

まとめると、条件をかけたいものが各行に応じて表す内容が異なる場合は、

GA4のデータでは、これはitemsが相当する。itemsは、各行に応じて表す内容は異ならない。(item_idというカラムでは商品のID…)

この場合も、event_paramsのようにunnestをしたものにそのまま条件をかけるのでは、UNNESTをした後、一つの配列がバラバラになってしまうため、例えば商品Aと商品Bを購入したユーザーを求めたいものが求められない。

この場合は、

GA4の場合、ユーザーはuser_pseudo_idで一意に指定することができるので、内部結合をする条件として、使う。

itemAとitemBを両方とも購入したユーザーを表示することができた。

Follow me!

DMP構築のご相談

お気軽にお問い合わせください