JavascriptのUDFでBigQueryのSQLはさらにパワーアップ

UDFとは

UDFとはUser Defined Functionの略で、日本語だと「ユーザー定義関数」と呼ばれます。Googleの公式サイトでは、以下のように説明されています。

上記説明が掲載されているGoogleの公式サイトのページはこちらです。本記事と合わせて参照していただくと良いと思います。

上記の説明の通り、UDFは、SQL文、または、JavaScriptコードで記述することが可能です。BigQueryを利用する人は、当然SQLは記述できるので、SQL文でのUDFはなんとなくイメージが湧くかと思います。

一方、SQLは書けても、JavaScriptは書けないという方もたくさんいらっしゃると思います。私もその一人ですが、先日、弊社のチーフ・テクノロジー・オフィサーの山田が執筆したこちらのブログ記事を見返す機会がありました。

そこでJavaScriptによるUDFがとても強力だということを認識し、自分でも少し書いてみたので紹介します。私自身がエンジニアではないので、きっと「動くことは動くがこなれていない」コードだとは思いますが、本記事を通じて、SQL文のUDFの違いについてざっくりとイメージをお持ちいただければうれしいです。

お題：地域の時差を考慮してユーザーがサイトを利用した時間を調整する

こんなお題を考えてみたいと思います。日本はタイムゾーンが一つしかないですが、米国本土には、4つあります。すると、例えば東海岸のニューヨーク州と西海岸のカリフォルニア州は3時間の時差があることになります。例えば、ニューヨークが朝9時のとき、カリフォルニアはまだ朝6時です。

一方、GA4がBigQueryにエクスポートしたテーブルにおけるイベント発生時刻は、UTC(協定世界時、coordinated universal time）で記録されています。カラム名は、event_timestamp、データ型は整数型で、UNIX時でマイクロ秒まで記録されています。

そのUTCで記録された時刻を、ニューヨークはニューヨークのタイムゾーンに合わせて、カリフォルニアはカリフォルニアのタイムゾーンに合わせて取得したい。というのをお題にします。

大きな方針と、SQL文のUDFの場合

大きな方針としては、地域(geo.region）を投入すれば、UTCとの時差が返ってくるようなUDFを記述し、帰ってきた時差の数値を、BigQueryがもともと持っている関数に挿入して、州別の時刻を取得する。とします。まぁ、妥当な方針だと思います。

このお題を解決するためにSQLのUDFであれば、大量のCASE文を書く必要があることが（SQLがそれほど詳しくない人でも）なんとなく理解して頂けると思います。

例えば、ニューヨーク州、テキサス州、コロラド州、カリフォルニアの4州だけであっても、以下のようなCASE文が必要になります。(THENの後ろにあるマイナスの数値はUTCとの時差です。）

しかし、実際には51（ワシントンDCを有する、 District of Colombiaも含めれば52）も州があるので、CASE文の記述も、それなりに面倒です。

JavaScriptのUDF

一方JavaScriptのUDFを利用する場合、大きな方針は変わりませんが、例えば以下の記述で解決することができます。

具体的には、作成した timeZoneAdjustmentという関数に、文字列型のregionの値を入力すると、timezoneにUTCとの時差が整数で出力されます。全部で24行ありますが、大部分は配列を記述しているだけですので、いわゆるプログラミング部分は18行目から23行目のわずか6行です。

しかも、配列部分は、ChatGPTに作ってもらえます。以下の画面をご覧ください。コーテーションマークが大サービスされていますが、修正は簡単です。

JavaScriptのUDFを利用したSQL文

では、実際にJavaScriptで記述した上記のUDFをSQL文の中で利用し、州ごとに調整された時刻を取得してみましょう。

紫色で記述した場所が、SQL文からUDFを呼び出しているところです。引数として、geo.regionを渡しているのが確認できます。戻り値は整数ですが、時差を考慮した出力をしてくれるdatetime関数の第2引数は文字列型である必要があるのでcast関数で型変換を行っています。

取得した結果

以下が取得した結果（一部）です。
赤枠で示した6行目と7行目をご覧ください。UTCで記録された時刻はわずか6分差ですが、ニューヨークのユーザーは夜の7時、カリフォルニアのユーザーは夕方4時だったことが分かります。

それによって、ユーザーが実際に生活している場所での時間に合わせて分析ができるようになります。

プリンシプルでは、GA4がエクスポートしたデータを含むBigQuery上のテーブルについて、データプレパレーションからダッシュボード作成を一貫してご支援しております。興味のある方は、お気軽にご連絡ください。