BigQueryテクニック集 – BigQuery UtilsでSQLをもっと楽に

BigQuery

and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太

BigQueryを触っていて配列の操作などの取り回しが入ってくると途端に記述量が増え、データの扱いが大変になって来ることが多々あります。今回はBigQueryに用意されているBigQuery Utilsを使ってSQLをもっと楽に開発してみましょう。

BigQuery Utilsとは?

BigQuery UtilsとはBig Query上で使用できる便利な関数を組み込んだライブラリのようなものです。
EXCELでいう数式をもっと簡単に扱えるようにしたコードの集合体の認識でOKです。

Githubリポジトリ
https://github.com/GoogleCloudPlatform/bigquery-utils

導入する手間は一切なく、

bqutil.fn

とBigQueryのエディタに記述するだけですぐに任意の関数を呼び出すことができます。

SELECT 
  bqutil.fn.typeof("アイウエオ"), /* 結果は STRING */
  bqutil.fn.typeof(0), /* 結果は INT64 */
  bqutil.fn.typeof(DATE("2022-01-01")), /* 結果は DATE */

上記は引数の型を読み取る例です。
特に難しいこともなくスムーズに理解頂けると思います。

※注意事項

BigQuery UtilsはBigQueryがサンドボックスモードだと、下記のようなエラーが出てしまい動作しないのでBillingを設定し、サンドボックスモードを解除してお使いください。

Not found: Dataset ga4-quick:analytics_302974976 was not found in location US

早速使ってみる

GA4でevent_paramsの配列内を取得して操作する際のよくあるこの形。

SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM `ga4-quick.analytics_302974976.events_*`
  WHERE
  _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'

これをBigQueryUtilsで書くと

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
  CONCAT(
    user_pseudo_id,
    CAST(bqutils.fn.get_value('ga_session_id', event_params).int_value as STRING)
  ) AS sid,
FROM `ga4-quick.analytics_302974976.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'

サブクエリが減って少しスッキリしました。
event_params, user_propertiesの配列で頻出するクエリで使えそうです。

他にも使い所を考えてみましょう。
例えば、サイト上の全てのpage_locationから、クエリパラメーターをURL毎にkey/valueで出力するとこのようになります。

WITH locations AS (
 /* GAからpage_locationsを取得する処理 */
)
SELECT url, key, bqutil.fn.url_param(t1.url, key) AS val
FROM (
  SELECT url, bqutil.fn.url_keys(url) AS keys
  FROM locations
) AS t1, UNNEST(t1.keys) AS key

サンプルのURLで試した結果:

urlkeyval
1https://www.google.com/search?q=bigquery+udf&device=pcqbigquery+udf
2https://www.google.com/search?q=bigquery+udf&device=pcdevicepc
3https://www.google.com/search?q=analytics&device=spqanalytics
4https://www.google.com/search?q=analytics&device=spdevicesp
5https://www.google.com/search?q=firebase&device=tablet&test=qfirebase
6https://www.google.com/search?q=firebase&device=tablet&test=devicetablet
7https://www.google.com/search?q=firebase&device=tablet&test=test

生のSQLだと正規表現などを使って文字列を判定する必要が出てくるのでコンパクトになるのはかなり嬉しいですね。

いかがでしたでしょうか。
今回紹介した以外にもまだまだBigQuery Utilsでは様々な関数が用意されています。使い所次第ではかなり有用なものとなりそうです

詳細はこちら
https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs/community

関連記事

特集記事

コメント

  1. I seriously love your blog.. Pleasant colors & theme.
    Did you make this amazing site yourself? Please reply back as I’m hoping to create my own personal site and want to find out where you got this
    from or just what the theme is named. Cheers!

ランキング(週間)

  1. 1

    GA4 「ページ ロケーション」と「ページ パス と スクリーン クラス」何が違う?ドメインの有無以外にもパラメータの扱いに違いあり

  2. 2

    GA4のレポート画面の数値データは、イベント発生時から何時間後に確定するのか?

  3. 3

    GA4の探索で「ページ別訪問数」を見ることは可能か?

  4. 4

    GA4の指標「総ユーザー数」VS「アクティブ ユーザー数」

  5. 5

    【祝・GPT-4 が ChatGPT plus に登場記念!】アクセス解析コンサルタントが、GPT-4にCSVデータを渡して、データ解析結果のコメントを書いてもらう方法

  6. 6

    GA4 拡張計測機能の新顔「フォームの操作」 form_start, form_submit

  7. 7

    YouTubeの概要欄に貼られたリンクをクリックしてサイトを訪れたユーザーを計測するために

  8. 8

    GA4 クロスドメイン設定では、リンク先に「_gl」パラメータが付く。勝手なパラメータを付けるとエラーになるページは要注意

  9. 9

    GA4で異なるドメイン(サブドメイン)の同一ページパスをドメイン付きで表示する

  10. 10

    GA4では、utm_term, utm_content はどうなったのか?

最近の記事

  1. 生成AIを調整してABテストを実装したら想像以上に早かった

  2. 無料で使えるABテストツールのバージョンアップを行いました

  3. 無料ABテストツール(and,B)のマニュアルを作成しました

カテゴリー

 
TOP