6. BigQueryのUDFの使い方や使用例の紹介（SQL編）

今回はBigQueryのUDF（user-defined functions、ユーザー定義関数）の使い方をご紹介します。
UDFはSQLとJavascriptで作成することができますが、今回はSQLに絞って説明します。

UDFの使用例

・INT型で記録された情報の可読性向上
北海道を0、青森県を1など単語を数字に置き換えているものを数字から単語に変換するのに便利です。

CASE 
     WHEN area = 0 THEN 'Hokkaido'
     WHEN area = 1 THEN 'Aomori'
     ELSE 'other'
END

・パラメータ付きURLの集約
「hogehoge.jp/top」と「hogehoge.jp/top?utm_source=google」などを「top」として集約して、閲覧数を集計したいときに便利です。

CASE 
     WHEN page_location LIKE'%hogehoge.jp/top%' 0 THEN 'top'
     ELSE 'other'
END

よく利用するCASE文や変換などをUDFとして用意しておくことで、以下のようなメリットがあります。
・クエリを書く時間を短縮
・社内で統一されたクエリを書ける結果、意図しないミスが減る

UDFの作り方

まず、UDFを管理しやすくするために、Dataset（udf_firebase）を作成します。Datasetを分けておくことで、おいおいのUDF管理が楽になります。

以下のようなクエリを実行して、UDFをudf_firebaseに保存します。

CREATE OR REPLACE FUNCTION `udf_firebase.cast_page_location`(page_location STRING) AS (
CASE 
     WHEN page_location LIKE "%hogehoge.jp/top%" THEN 'top'
     WHEN page_location LIKE "%hogehoge.jp/search%" THEN 'search'
     ELSE 'other'
 END
);

UDFの内容を更新したいときは、UDFのページを開いた後に編集を行います。
①「EDIT PERSISTENT FUNCTION」を選択
②CASE文の追加
③「Run」をクリックすることで、更新されます。

UDFの使い方

※以下はあくまで例で、結果はno resultになります。

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE('2018-10-03'));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE('2018-10-03'));


SELECT
  PARSE_DATE("%Y%m%d", event_date) AS date
  -- UDFを用いて変換（STRINGの引数を受け取るUDFのため、STRINGで渡す）
  , udf_firebase.cast_page_location((SELECT value.string_value FROM UNNEST(event_params)) AS params WHERE params.key = 'firebase_screen_class') AS firebase_screen_class
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
  AND event_name = 'screen_view'

最後に

上記のやり方を参考に様々なUDFを用意することで、同じクエリを書く手間を省けます。
また、URLや属性値など元データの変更にあわせてUDFを更新することを忘れないようにしましょう。

お仕事の依頼

データ分析やSEO、UI/UX改善のコンサルティングなどのお仕事をご相談する際はTwitterからご連絡お願いいたします。

このマガジンの記事

1. アプリの各画面を閲覧したユーザー数をBigQueryで集計する方法
2. Google Analytics 4の各イベントについての説明(page_view, screen_viewなど)
3. event_paramsについて（BigQueryのGoogle Analytics 4プロパティ）
4. GAをBigQueryで分析する際の注意点（WebとAppの違いやPlatformの絞り込み）
5. BigQueryでGoogle Analyticsを分析するときに参照したいドキュメント一覧
6. BigQueryのUDFの使い方や使用例の紹介（SQL編）
7. BigQueryのクエリのスケジューリングの設定方法や使い方