Looker Studio×BigQuery×スプレッドシートで爆速プロトタイピングする方法！

Transcript

1. 〜初級から魔改造まで〜 Looker Studio×BigQuery×スプレッドシート で爆速プロトタイピングする方法！ 株式会社キュービック テクノロジーエキスパートセンター Tech Lead/データエンジニア 尾﨑勇太 2025.04.18 開示範囲:公開ドキュメント 
 1
2. 自己紹介 2 株式会社キュービック 役割：Tech Lead/データエンジニア 名前： 尾﨑 勇太（おざき ゆうた） 覚え方：尾崎豊(おざきゆたか)と一字違い 業務領域 「データに関わる全て」
3. わたくしごとですが Tableau DATA Saber始めました 3 2025年4月1日からTry中🔥
4. LT内容 4 1.初級編：スプレッドシートで爆速プロトタイプ作成 2.中級編：カスタムクエリで独自データソース作成 3.上級編：クエリストリングでURLパラメータを渡してフィルタ 4.鬼編：UDFにパラメータを渡して動的に集計する
5. 最軽量のデータ連携 5 アーキテクチャ データ収集 データ可視化 軽量データであればデータ出しの段階でDWHにデータ蓄積しなくともアウトプットイメージや 分析軸の合意形成ができる データ蓄積
6. 6 これで済んだらデータエンジニ アはいらねぇんだよな
7. 幾多のSpread Sheet使いを挫折させてきた 7 ・何か妖精さんが毎日データをとってきてくれる ▷節子それ自動化やなくて手動や ・1000万セルの限界 ・表示まで5分以上かかるページ ・再読み込みしたら壊れるグラフ ・データ統合でしか結合できない ・計算フィールドの条件分岐に上限がある
8. LT内容 8 1.初級編：スプレッドシートで爆速プロトタイプ作成 2.中級編：カスタムクエリで独自データソース作成 3.上級編：クエリストリングでURLパラメータを渡してフィルタ 4.鬼編：UDFにパラメータを渡して動的に集計する
9. サーバレス化によるデータ連携 9 アーキテクチャ データ収集 データ可視化 GCPのサーバレスアーキテクチャなどでBigQueryへのデータ蓄積部分は自動化しつつ Looker Studioからカスタムクエリで独自データソースを作成することで生産性を向上 データ蓄積 BigQuery Cloud Run functions Cloud Scheduler
10. 10 ちゃんとBQ蓄積できると理想 系だけど、BIツールでETLせざ る得ないパターンもあるよね
11. どこでデータ加工するのが正解なのか？ 11 新規でデータ蓄積が 必要か？ SQLで 加工可能か？ YES YES NO BI上で動的な変更が あるか？ GAS YES NO Dataform カスタムSQL YES 冗長な収集が必要か？ (アカウント違いなど) スケジュール実行が 必要か？ NO NO Google Colaboratory BigQueryの既存デー タを加工するか？ サービスアカウント 認証は必要か？ NO NO Cloud Run functions Workflows NO YES BigQuery YES
12. ここで差がつく データエンジニアリング 12
13. 具体的なケースを考える 13 1.時系列の売上データを10%〜◯%まで増加を見れるようにしたい 2.最新のマスタで前日までの最新データが見たい
14. 14 BigQuery 集計値をテーブルにカラム追加 ▷仮で200%まで用意しておいて、後から考えよう 要望1：時系列の売上データを10%〜◯%まで増加を見れるようにしたい 集計パターンをそれぞれローデータとして蓄積 ▷5%刻みでテーブルを用意しよう 間違いではないが非合理的なソリューション例
15. 15 BigQuery Looker Studioの計算フィールドで動的に集計 ▷取得した売上データ×パラメータで計算フィールドを作成 要望1：時系列の売上データを10%〜◯%まで増加を見れるようにしたい カスタムクエリ中で、パラメータで動的にフィルタ ▷データソース生成時に再集計をかける シンプルなソリューション例 一工夫したソリューション例
16. 16 要望2：最新のマスタで前日までの最新データが見たい 間違いではないが非合理的なソリューション例 BigQuery 常に◯年分のデータを最新のマスタで洗い替え ▷めちゃくちゃ重い・・・ マスタ更新があった際に、過去まで遡求して更新 ▷過去のマスタが書き変わってもしょうがないよね 　▷本当に？？？
17. 17 要望2：最新のマスタで前日までの最新データが見たい シンプルなソリューション例 BigQuery 前日から１ヶ月間のデータを最新のマスタで洗い替え ▷ユーザー/運用者が見たいスコープを絞り、品質を担保する 品質が担保される期間を前提にモニタリングする ▷中長期のデータが参照したい場合と分析軸を分ける
18. どこでデータ加工するのが最適 なのかはもう一度考えよう 18
19. LT内容 19 1.初級編：スプレッドシートで爆速プロトタイプ作成 2.中級編：カスタムクエリで独自データソース作成 3.上級編：クエリストリングでURLパラメータを渡してフィルタ 4.鬼編：UDFにパラメータを渡して動的に集計する
20. Looker Studioを仮想的にAPI化 20 アーキテクチャ ページ内表示 パラメータをクエリストリングで連携することで、Looker Studioのカスタムクエリの集計条件 を描画前に調整し、ユーザーがみたい閾値で可視化が可能 BigQuery ページ参照 https://lookerstudio.google.com/u/0/reporting/54f a38e1-b459-4606-9a20-d86359802901/page/p_mnpnn34y nd?params={"ds76.roas_times_param":"0.5"} 表示要素演算 ユーザー フィルタ結果
21. イメージ 21 https://lookerstudio.google.com/u/0/r eporting/54fa38e1-b459-4606-9a20-d863 59802901/page/p_mnpnn34ynd?params={"d s76.roas_times_param":"0.5"} SELECT campaign_id, campaign_name, ad_group_id, ad_group_name, impressions, clicks, cost, revenue, revenue / NULLIF(cost, 0) AS roas FROM your_dataset.your_table WHERE revenue / NULLIF(cost, 0) >= @roas_times_param ORDER BY roas DESC ROASはデフォルト値 クエリストリングでデ フォルト値を更新 データソースを再集計
22. 22 動きを見ながらユーザーとアウ トプットを爆速で詰められそう
23. 23 まだだ、まだ足りない SQLの限界に挑戦したい
24. LT内容 24 1.初級編：スプレッドシートで爆速プロトタイプ作成 2.中級編：カスタムクエリで独自データソース作成 3.上級編：クエリストリングでURLパラメータを渡してフィルタ 4.👹鬼編：UDFにパラメータを渡して動的に集計する🔥
25. SQLで複雑な処理をUDFでjavascriptで書いてしまう 25 アーキテクチャ ページ内表示 計算フィールドやカスタムクエリ内でのメンテナンス性を考慮し、集計や分岐条件のコアロ ジックをUDFに切り出しつつ、結果のみを動的に可視化できるようにする BigQuery ページ参照 https://lookerstudio.google.com/u/0/reporting/54fa38e1-b4 59-4606-9a20-d86359802901/page/p_mnpnn34ynd?params ={"ds76.roas_times_param":"0.5"} 表示要素演算 ユーザー フィルタ結果 UDF(ユーザー定義関数の呼び出し)
26. イメージ 26 CREATE OR REPLACE FUNCTION `your-project.your_dataset.label_roas` (roas_time s_param FLOAT64, roas FLOAT64) RETURNS STRING LANGUAGE js AS R""" if (roas < roas_times_param ) { return `ROAS < ${Math.round( roas_times_param * 100)}%`; } else if (roas >= roas_times_param ) { return `ROAS ≧ ${Math.round( roas_times_param * 100)}%`; } else { return '不明'; } """; ルーティーンのクエリ BigQuery カスタムクエリ パラメータ閾値更新 SELECT campaign_id, campaign_name, ad_group_id, ad_group_name, impressions, clicks, cost, revenue, revenue / NULLIF(cost, 0) AS roas, `your-project.your_dataset.label_roas`( @roas_ times_param, revenue / NULLIF(cost, 0)) AS roas_label FROM your_dataset.your_table ORDER BY roas DESC
27. まとめ 27 今回は初級から鬼編までさまざまなアプローチを紹介しました。 本格的な運用やメタデータ管理の観点でよりbetterな方法論はたくさんあると思い ます。だけれども、そこに至るまでの課題が多いのが業界あるあるですよね。。。 常に考えたいこと：最小工数で成果を出す方法はないのか？ そのための、「プロトタイプ開発でQuick Winする方法」を 今回はご紹介しました。
29. データマート TROCCOがあればそんなにやり込まなくても良い説 29 BigQuery データ収集はノーコードでOK DWH連携だって朝飯前 頑張れば動的な変更も可能 BigQuery 整形もTROCCOのUI上で完結
30. 30 ご清聴、ありがとうございました