TableauのパラメータとBigQueryのUDFを利用して効率よく「必要な期間だけ」のデータを取得する

今、2023年5月上旬ですので、2022年8月15日に「TableauからGA4に接続するにはBigQuery一択」というブログ記事をリリースしてから、そろそろ9ヶ月経過しようとしています。しかし、いまだ、その状況は変わっておらず、TableauからGA4に接続するにはBigQuery一択という状況は継続しているように思います。

だとすると、TableauからカスタムSQLを書いて、BigQueryに投げることになりますが、いつも面倒に思うのが、期間の選択です。BigQueryのGA4データは、日別のテーブルに分かれて記述されているため、例えば、3月15日から5月3日までのデータを分析しようと思うと、赤の下線（_table_suffix BETWEEN ‘20230315’ AND ‘20230503’）を書かなければいけません。

一度なら、まぁ、構わないのですが、分析の途中でもっと長期のデータが必要になった場合には、いちいちTableauの「データソース」タブから、カスタムSQLを編集して、上記の下線部を書き直す必要があります。それでは面倒だから・・・と最初から長期のデータを取得すると、1) Tableauにもしかすると分析に利用しない期間のデータも取り込むことになる。2) 分析に利用しない期間についてもBigQueryの料金がかかる。と、あまりよろしくありません。

それを解決するのが、Tableauのパラメータを利用した「必要な期間だけ」のデータを取得する方法です。パラメータ、つまりTableau DesktopのUIから「開始日」、「終了日」を指定するだけで、その期間を取得することができ、データソースからカスタムSQLを書き直す必要はなくなります。Tableauのパラメータを使ってBigQueryにSQLを投げることを「パラメタイズドクエリ」と呼んだりしますが、それを使います。

パラメータイズドクエリについての記事は、以下がありますので、関連する記事を見てみたいという方はジャンプしてみてください。

また、効率よくパラメータイズドクエリを利用するためにBigQueryの「ユーザー定義関数（User Defined Function | UDF）」を利用します。「パラメタイズドクエリは知っているけど、UDFはそうでもない」という方は、UDFのユースケースとしてお持ち帰りください。

パラメタイズドクエリを利用した「必要な期間だけ」データを取得する原理

パラメタイズドクエリを利用して「必要な期間だけ」のデータを取得は、次の太字の、日付が入っている部分（開始日と終了日の2つ）をTableauのパラメータに差し替えることで行います。

_table_suffix BETWEEN ‘20230315’ AND ‘20230503’

ここで気をつけなければいけないのは、上記の2つの日付部分のデータ型です。シングルコーテーションマークで囲まれていることから分かる通り、開始日も終了日も「文字列型」で指定する必要があります。

Tableauのパラメータを「日付型」で作成するとエラーに

Tableauのパラメータは以下の通りに「日付型」で作成したいですね。テキスト型で作ればBigQueryのSQLは問題なく解釈してくれますが、その場合、パラメータから日付選択するときにカレンダーが使えません。すると、たとえば「3月の第二週の月曜日から始まる1週間」を指定したい場合、「月曜日って何日だったっけ？」といちいちカレンダーを調べなければいけません。また、3月第二週の月曜日が2023年3月6日だった場合、文字列型のパラメータでは、ユーザーに、2023006と打鍵してもらわなければなりません。日付型にすれば、カレンダー上でワンクリックですみますね。

「日付型」で作成したTableauのパラメータ「開始日」

上記で作成した日付型のパラメータを、以下のようにカスタムSQLに「差し込んで」パラメタイズドクエリにすると・・・一見良さそうなのですが、実は、エラーになります。

エラーの理由は、「BigQueryに差し込む、期間を指定する引数は文字列型でなければいけないのに、パラメータがクエリに渡した値は日付型だったから」です。

日付型のパラメータを文字列型に変えるUDF

エラーを解消するには、BigQueryの、データ取得先のテーブルを指定する引数に「文字列型」を渡せば良いのでパラメータの「日付型」を「文字列型」に変換しましょう。と、ここで「待てよ、この【日付型のパラメータを文字列型に変換する】というのはパラメータイズドクエリを利用する場合にいつも使うんじゃないか？」と思いつきます。

そんなときに利用するのがユーザー定義関数（UDF）です。UDFの公式ヘルプはこちらです。

ユーザー定義関数は、一旦作ってしまえば、同一プロジェクトの別データセットはもちろん、プロジェクトが異なるデータセット配下のテーブルにクエリを掛ける場合にも利用可能です。ただし、検証したところデータセットの「データのロケーション」が異なっていると利用できませんでした。つまり、UDFを作成したデータセットのロケーションがUSだとして、そのUDFを asia-northeast1をロケーションとするデータセット配下のテーブルを対象としては利用できませんでした。

しかし、その「データのロケーション」の違いにさえひっかからなければ多くのデータセットに対して作成したUDFを利用できます。これは相当便利です。

作成したユーザー定義関数（UDF）

今回作成した「パラメータが渡す日付型の値をBigQueryのテーブル指定の引数に使うためテキストに変換する」UDFは以下の通りです。

赤の下線は、作成した関数名です
オレンジの下線はその関数に食わせる引数名とそのデータ型です。
緑枠が関数の実態です。日付型で渡された set_dateを年、月、日に分解し、月、日が一桁の場合には先頭にゼロを加えて連結し、文字列として取得しています。
紫枠は、作成したユーザー定義関数が、データセットは以下に「ルーチン」として保存されていることを示しています。

使いたい方がいるかもしれないので、関数の実体部分をコピーしやすいようにテキストで貼り付けておきますね。

concat(
  cast(extract(year from set_date) as string),
  case 
  when extract(month from set_date) <=9 then concat("0",cast(extract(month from set_date) as string))
  else cast(extract(month from set_date) as string) end,
  case
  when extract(day from set_date) <=9 then concat("0",cast(extract(day from set_date) as string))
  else cast(extract(day from set_date) as string) end
)

作成したユーザー定義関数の検証

作成したユーザー定義関数の検証は簡単です。日付型のデータを関数に食わせ、テキストとしてもどってくることを確認すればよいですね。以下の通りに検証しています。日付型として食わせた 2023-04-09が、20230409として帰ってきています。ユーザー定義関数がうまく動いています。

Tableauのデータ接続に記述するカスタムSQL

Tableauのデータ接続では、BigQueryに対して以下のカスタムSQLを記述します。パラメータは日付型ですが、_table_suffix between A and Bの、A、Bともにユーザー定義関数が文字列に変換してくれます。SQLの本体部分はここでは何でも良いと思いますので、日別のユーザー数を取得しているだけです。

パラメータを変更した時の挙動は動画（33秒）でご覧ください。

宣伝

以下は宣伝です。

おお、Tableau Desktopってそんなことできるんだ。すげーや。俺も（私も）勉強したいな。という方、Tableauを学べる、評判の良いUdemyの講座があります。
SQLの勉強をしたい！今こそやらねば。という方、BigQueryを学習の基盤として使ったUdemyの講座があります。え、動画は苦手？だったら書籍があります。
そろそろ真面目にGA4に取り組まないと、今年は2023年はまずいぞ。という方、GA4のレポートを極めるUdemyの講座があります。レポートも学びたいが、そもそも設定が、、、という方は、Google アナリティクス認定資格の対策にもなるこちらの講座はどうでしょう？なるほど、GA4も書籍を手元において学びたい派ですか、そんなあなたにはこちらの書籍をおすすめします。
というか、そもそも「データ分析」を体系だって学んだことがないんだよね。いつも、見様見真似で・・・という方、Udemyでデータ分析を基礎から学んでみませんか？
いやー、私はTableauじゃなくて、Looker Studioをメインに使っているんだよね。という方で、最新機能にキャッチアップしていないな。という方は、Looker Studioの最新の機能を網羅したこちらの講座はどうでしょう？2023年4月に実装された「ボタン」とか「サンキーチャート」ってチェック済ですか？