はじめに
WED株式会社でデータエンジニアをしているryuya-matsunawaです。
弊社では、レシート買取アプリONEを運営しており、ユーザー情報や購買情報などのデータをBigQueryで管理しています。
BigQueryで管理しているデータを用いて、営業から「特定の店舗でのユーザー数や購買数を比較したい」といった分析要件が寄せられることがあります。
その際、SQLクエリを手動で作成するのは時間がかかり、より深い分析に時間を割くことが難しいです。
そこで今回は、ClaudeとBigQueryを連携させて、Notionに記載された分析要件から自動的にSQLクエリを生成する仕組みを試してみました。その結果と知見をまとめて紹介します。
構成とセットアップ
使用したツール
- Claude Desktop: 自然言語でのやり取りとクエリ生成
- BigQuery: データウェアハウス
- Notion: 分析要件の管理
セットアップ手順
ClaudeでBigQueryとNotionを使用するには、MCP（Model Context Protocol）サーバーの設定が必要です。
BigQueryの設定
参考記事：https://dev.classmethod.jp/articles/claude-bigquery-mcp/
Notionの設定
参考記事：https://note.com/tsubuan_sun/n/n0806658b11f1
claude_desktop_config.json
{
"mcpServers": {
"bigquery": {
"command": "npx",
"args": [
"-y",
"@ergut/mcp-bigquery-server",
"--project-id",
"your-project-id",
"--location",
"us"
]
},
"notionApi": {
"command": "npx",
"args": [
"-y",
"@notionhq/notion-mcp-server"
],
"env": {
"OPENAPI_MCP_HEADERS": "{\"Authorization\": \"Bearer your_notion_token\", \"Notion-Version\": \"2022-06-28\"}"
}
}
}
}
実際の使用例
分析要件の設定
Notionページに以下のような分析要件を記載しました：
| 項目 | 内容 |
|---|---|
| 抽出内容・カラム | ユーザーID数 |
| 対象店舗・流通 | 東京駅店、新宿駅店 |
| 対象エリア | 東京都 |
| 対象商品 | シャンプーシリーズ |
| 対象期間 | 2023/1/1-2023/12/31 |
| デモグラ | 指定なし |
要求内容：
- UU数、購入数のTOP10の店舗を出す
- デモグラ（性別や年代）で東京駅店と新宿駅店を比較する
Claudeによる自動クエリ生成
事前指示
今回は、あらかじめ以下のような指示をClaudeに与えています：
BigQueryでクエリを作成する際、以下の点を考慮してクエリを作成してください：
- スキーマを確認し、パーティションキーがあれば確実に使用してください
- 実際に使用するテーブルにあるカラムを使用してください
※最低限の指示しか入れていないので、まだ改善の余地があります。
実際の生成プロセス
-
Notionページの内容取得
- ClaudeがNotionAPIを使用してページの内容を取得
- 分析要件を理解し、必要なデータ構造を把握
-
BigQueryスキーマの確認
-
INFORMATION_SCHEMAを参照 - 実際に存在するテーブル・カラムを確認
- パーティションキーの確認
-
SELECT
table_name,
column_name,
data_type,
is_nullable,
is_partitioned
FROM `sample_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'purchases'
-
クエリの段階的生成
- パーティションキーを活用した効率的なクエリ構造を生成
- 事前指示に従った適切なフィルタリング条件を適用
- 最終的にほぼ正しいクエリが完成
生成されたクエリ例
SELECT
p.purchase_id,
p.user_id,
p.store_name,
IFNULL(u.gender, 'その他') as gender,
CASE
WHEN u.age_range = 10 THEN '10代'
WHEN u.age_range = 20 THEN '20代'
WHEN u.age_range = 30 THEN '30代'
WHEN u.age_range = 40 THEN '40代'
WHEN u.age_range = 50 THEN '50代'
WHEN u.age_range = 60 THEN '60代'
WHEN u.age_range = 70 THEN '70代'
ELSE 'その他'
END as age_range,
sample_dataset.get_region(u.prefecture) as region,
u.household_income_range_start,
'東京駅店' as facility_type
FROM `sample_dataset.purchases` p
LEFT JOIN `sample_dataset.users` u ON p.user_id = u.user_id
WHERE DATE(p.purchase_date) >= '2023-01-01' -- パーティションキーを使用
AND DATE(p.purchase_date) <= '2023-12-31' -- パーティションキーを使用
AND p.prefecture = '東京都'
AND p.chain_name = '東京駅店' -- 事前指示に従ったフィルタリング
AND p.is_canceled IS NOT TRUE
実際に使ってみた感想
良かった点
-
スキーマ情報の自動取得と活用
-
INFORMATION_SCHEMAを参照して実際のカラム名やデータ型を確認 - パーティション情報を正しく認識し、効率的なクエリを生成
- 事前指示に従った適切な条件文の生成
-
-
段階的な改善
- 最初は概要レベルのクエリから開始
- プロンプトの調整により段階的に詳細化
-
基本的なクエリ構造の生成
- JOINやWHERE句の基本的な構造は適切に生成
- 日付範囲やフィルタ条件も正しく反映
課題と制限
-
実際のデータとの乖離
- 「東京駅店」の実際の表記が「TOKYO_STATION」など異なる場合がある
- Notionに記載されていない詳細な仕様の把握が困難
-
ドメイン知識の必要性
- 業務固有のデータ構造や命名規則への理解不足
- 人間によるレビューと修正が必要
-
フォーマットの統一
- Notionの記載フォーマットをBigQuery寄りに整備すると、より精度の高いクエリが生成可能
今後の展望
-
rawデータの活用
- 外部システムから送られてくるrawデータを読み込ませて、分析に適したテーブル設計の提案
-
非技術者への展開
- 営業などのSQLをあまり書かない層が、テーブル構造を気軽に質問できる環境の構築
-
フォーマットの標準化
- Notion等での要件記載フォーマットをより構造化し、クエリ生成精度の向上
まとめ
ClaudeとBigQueryの連携によるクエリ自動生成は、簡易的な分析クエリのベース作成という観点では十分に実用的でした。
一方で、業務固有の詳細な仕様や実際のデータ構造については、人間による確認と修正が必要です。完全自動化ではなく、分析作業の効率化ツールとして活用するのが現実的でしょう。
特に以下のようなケースで効果を発揮しそうです：
- 定型的な分析パターンのクエリ作成
- 新しいテーブルの構造理解
- SQLに慣れていないメンバーへの学習支援
技術の進歩により、今後はさらに精度の高いクエリ生成が期待できそうです。皆さんもぜひ試してみてください！
Discussion