BigQueryに格納したGA4独特の配列形式を操作しやすくする

TL;DR

• Firebaseで取得したログをBigQueryに転送すると、eventのパラメーターがkey-valueを配列形式にした状態で格納されるので、独特の処理を行う必要がある。
• GoogleがOSS提供している bqutil.fn という関数群を利用すると、valueを簡単に取得できて便利。
• bqutil.fn はBigQueryデータロケーションがUSでないと使えないが、UDFとして自分で定義すれば使える

背景

Firebaseを活用してアプリの行動データを分析する際、毎回面倒な配列展開処理を行なっていました。 一応 unnest などを使えばできますが、行が増えたり縦持ちを横持ちにしたりと、結構気をつけなければいけない印象です。

解決法

BigQueryのデータロケーションがUSの場合、 bqutil.fin というfunctionを活用することでkey-valueのvalueを取り出すことができます。

select
    event_name
,   user_pseudo_id
,   bqutil.fn.get_value("firebase_screen", event.event_params).string_value as firebase_screen
from
    `xxx.events_*`
where
    _TABLE_SUFFIX between 'aaa' and 'bbb'

上記方法で event_params の firebase_screen というkeyを指定してvalueを取得できるので、非常に楽になります。

ちなみに get_value 関数以外にも、こちらを見ると様々な関数が用意されています。

github.com

ただ上記の方法は、データロケーションがUSの場合でしか使えませんので、そうでない場合はクエリの上部に自身で定義してしまうことで同様の関数が使えるようになります。

create temp function get_value(get_key STRING, arr ANY TYPE) AS
(
  (SELECT value FROM UNNEST(arr) WHERE key = get_key)
);



select
    event_name
,   user_pseudo_id
,   get_value("firebase_screen", event.event_params).string_value as firebase_screen
from
    `xxx.events_*`
where
    _TABLE_SUFFIX between 'aaa' and 'bbb'

また上記は temp function なので該当クエリ内のみで使える状態ですが、custom functionとして登録してしまえば、該当projectで活用できるfunctionになります。

この辺りを見ていただければと思います。

cloud.google.com