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

関連記事

特集記事

A/Bテスト

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

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

無料ABテストツール（and,B）のマニュアルを作成しました
A/Bテスト

今日から無料で使えるABテストツールを作ってみた
Python

ECサイトのゴールデンルートを発見する方法 – Google Analytics 4とPythonを活用して（第2回）
トレジャーデータ

GA4 -> トレジャーデータ 連携術

コメント

  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!

  2. найти подругу в москве для секса порно видео японки негритянки московская яма
    секс порно фильмы андрея блэка

  3. Your point of view caught my eye and was very interesting. Thanks. I have a question for you.

  4. farmaci senza effetti collaterali in Italia Mabo Capodrise
    Preis von Medikamente

  5. migliori siti per l’acquisto di farmaci a Gent Arcana Zoug
    Betaalbare medicijnen zonder voorschrift online

  6. compra de somníferos medicamentos en España GeoLab Heerlerbaan Online-Kauf von Medikamente in Buenos Aires

  7. medicijnen kopen zonder doktersvoorschrift Egis Aesch vente en ligne de médicaments en France

  8. médicaments prix abordable stada Rohrbach prix du médicaments sur ordonnance

  9. яку молитву читати коли сниться
    небіжчик найкраща академія магії потрапила за власним 3 читати онлайн безкоштовно повністю
    таро кубок пентаклей сонник до
    чого сниться біла машина

  10. Trouver de la médicaments de qualité en ligne en France AbZ-Pharma Gómez Palacio farmaci con o senza prescrizione medica

  11. ойын карталарында төрт сәуегейлік, қазақ газеті баспасөз органы болған партия золотые слитки алматы цена, 100 грамм золота в тенге
    первая стадия производства серной кислоты, третья стадия производства серной кислоты костюм айли,
    тоймарт каталог алматы

  12. ойталқыға қатысатын адам саны, ойталқы латын тілінен аударғанда табиғаты
    таза болашақ, таза болса табиғат эссе құралай минус
    скачать, құралай текст мұхтар шымбулак рабочие дни, шымбулак билеты

  13. оқу практика бойынша қорытынды есеп,
    практика есебі қорытынды географиядағы негізгі әдістердің түрлері қандай, географиядағы салыстыру әдістері 10 сынып скачать
    песни руслана, песни руслан добрый скачать бесплатно менин каникулум сочинение, менин каникулум дилбаян

  14. валютные операции между резидентами и нерезидентами, валютные операции рк аварийная
    служба свет тараз, номер газовой службы тараз бмв сипаттамасы, бмв расшифровка оқушылардың қоршаған орта жағдайы, жаратылыстануды
    оқыту әдістемесіне л.с.севрук қандай үлес қосты?

  15. Wow, this post is pleasant, my sister is analyzing these things,
    therefore I am going to tell her.

  16. ж.аймауытов шыгармалары, жүсіпбек аймауытов кітаптары соляная
    шахта турда цена как отбелить
    белые вещи белизной, как отбелить белые вещи с помощью соды и перекиси арманым ай арманым текст, арманым
    ай сакен майгазиев текст

  17. I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.

  18. 對於跑步愛好者和日常運動的人士來說，Nike Air Zoom 是一雙備受推崇的運動鞋。nike zoom哪裡買最便宜

    • 888999
    • 2024.11.27 11:33am

    Adidas Originals的經典設計往往傳達出品牌對于自我表達和個性的注重，其圖標性的三葉草標志彰顯出品牌的複古與時尚相結合的風格。 adidas originals哪裏買最便宜？

    • abv
    • 2024.11.27 11:42am

    微笑項鍊 Tiffany：經典與時尚的完美融合tiffany哪裡買最便宜

    • per
    • 2024.11.27 12:19pm

    無論是搭配休閑裝還是正裝，Nike dunk鞋都能輕松駕馭，展現出不同的風格魅力。nike dunk哪裡買最便宜

    • chenchen123
    • 2024.11.29 11:13am

    探索經典魅力：Adidas Samba 與 Samba OG 不朽傳奇 adidas samba哪裡買最便宜？

  23. 재미있는 경험담과 함께 소통하는 것도 좋네요~기대됩니다!!! 방문하다 먹튀레이더

  24. Nice

    • per
    • 2024.12.20 4:13pm

    一次性小煙

    • per
    • 2024.12.20 4:15pm

    iqos 電子煙​

    • per
    • 2024.12.20 4:16pm

    拋棄 式 電子 菸​

    • per
    • 2024.12.20 4:18pm

    在時尚與舒適的交彙點，converse帆布鞋以其獨特的設計風格和卓越的品質，成爲了無數潮流愛好者的首選。converse哪裡買最便宜？

ランキング（週間）

  1. 1

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

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

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

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

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

    GA4とGoogle広告の連携・オーディエンス・ 広告用機械学習 | オンラインセミナー　アーカイブ動画＋テキスト起こし（2022年6月2日開催）
  7. 7

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

    GA4で設定したイベントの消し方（削除の仕方）
  9. 9

    Ads Data Hub（ADH） とはどんなものですか？GA4との関連は？
  10. 10

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

タグ

BigQuery_by_佐藤洋太 (4)

最近の記事

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

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

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

カテゴリー

アーカイブ
 
TOP