AIサポート機能を備えた業務SQL管理システム
はじめに
とあるマーケティング企業の方から「顧客に向けたBigQueryの管理ができないか」という相談を受けました。
詳しく話を聞いてみるとその企業では顧客のマーケティングの成果分析のためにGCPのBigQueryを使っており、その結果を共有しているようです。
クエリの発行についてはある程度は顧客にまかせており、クエリ自体の生成は知識が必要なため企業のマネージャー職が作成し、日々の実行は顧客に任せている、とのことでした。
顧客もクエリから得られる情報の傾向分析などはできており、基本的にはCSVでダウンロードした上でExcelに食わせ、日々の変化の傾向、施策の影響などを確認できるようになっているとのことでした。
ただクエリの生成には専門的な知識が必要で、マネージャー間でも共有などはSlackなどを始めとした社内チャットテキストベースだったりで行われており、そのクエリの共有化を行えばより便利になるのではないか、とのことでした。
今回はそういったことをきっかけに
- クエリの管理
- 顧客でのクエリの実行
ができるようなシステムの構築を行いました。
なおセキュリティ上の懸念点となりそうなGCPの権限周り、具体的にはGCPサービスアカウントを顧客ごとに割り振ったりするか、また実行をそのサービスアカウントごとに行うかどうかについては、ある程度信頼された顧客のみを招待して運用を始める段階を想定しており、まずは一つのサービスアカウントのみで行いましょう、との結論となりました。
ただし将来的にはそういった顧客ごとのサービスアカウントの分離も行えるように考慮はしておきました。そうすることでクエリを発行しすぎた場合に従量課金部分が跳ね上がっていないかを確認したり、不要に見られてはいけない情報にはアクセスできないするようなどといったことを想定しています。
graph TD
A[会社] --> B[広告効果測定部門]
A --> C[データ分析部門]
A --> D[顧客サポート部門]
B --> E[広告チーム]
B --> F[レポーティングチーム]
C --> G[分析チーム]
C --> H[BI/ダッシュボードチーム]
D --> I[カスタマーサクセスチーム]
目的
今回のシステムのもくてきは業務の効率化と品質向上によるコスト削減です。マネージャー間、マネージャー顧客間でシステマティックにクエリの共有ができ、実行ができることでコスト削減につながると想定されました。
また顧客としても新しいクエリをシステム上で随時実行できるようにすることで企業としての顧客満足度が上がり、収益の向上を狙う事ができると考えられました。
graph LR
A[システム化] --> B[業務効率化]
A --> C[品質向上]
A --> D[顧客満足度向上]
B --> E[コスト削減]
C --> E
D --> F[収益向上]
要件定義
システムとしての概要は以下のようになります。
graph TD
A[ユーザー] --> B[Webアプリケーション]
B --> C[クエリ管理]
B --> D[実行管理]
B --> E[権限管理]
C --> F[BigQuery]
D --> F
E --> G[ユーザー管理]
また主な機能は下記の通りです。
- ユーザー管理機能
- ユーザー登録・認証
- メールアドレスによるサインアップ
- ロール管理
- クエリ管理機能
- クエリの作成・編集
- SQLエディタ
- チーム内共有
- クエリ実行機能
- 結果のプレビュー
- CSVダウンロード
ユースケース
graph TD
A[管理者] --> B[ユーザー管理]
A --> C[システム設定]
D[マネージャー] --> E[チーム管理]
D --> F[クエリ管理]
G[一般ユーザー] --> H[クエリ作成]
G --> I[クエリ実行]
G --> J[レポート作成]
詳細設計
システム自体はSupabaseを利用し、フロントにはReactをベースとしたRefine、Viteの構成としました。
graph TD
A[クライアント] --> B[フロントエンド]
B --> C[バックエンド]
C --> D[データベース]
C --> E[BigQuery]
subgraph Frontend
B[フロントエンド/React]
end
subgraph Backend
C[Supabase]
end
subgraph Database
D[PostgreSQL]
end
subgraph External
E[BigQuery]
end
Supabaseを利用することでスムーズなユーザー管理を行うことができました。また今回は積極的に利用していませんが、例えばレポート結果を保持しておくような機能を追加する際にマルチテナントのような機能をRLSをつかって実現できる、と見込んでいます。
DB設計
今回のDB設計はかなりシンプルなものとしました。下記のような構成となります。
erDiagram
profiles ||--o{ queries : "owns"
profiles ||--o{ settings : "has"
profiles {
uuid id PK
string email
string role
timestamp created_at
timestamp updated_at
}
queries {
uuid id PK
uuid profile_id FK
string title
text query
timestamp created_at
timestamp updated_at
}
settings {
uuid id PK
uuid profile_id FK
string key
text value
timestamp created_at
timestamp updated_at
}
ただし settings テーブルには管理者のみしかアクセスできないなどRLSで制限を加えている項目があります。
サイトマップ
graph TD
A[ログイン] --> B[ダッシュボード]
B --> C[クエリ一覧]
B --> D[プロフィール]
B --> E[設定]
C --> F[クエリ作成]
C --> G[クエリ編集]
C --> H[クエリ詳細]
D --> I[プロフィール編集]
E --> J[設定編集]
システム
システムの基本的な機能はクエリの管理、実行、CSVのダウンロードとなります。
AIアドバイザー
追加の機能としてクエリのAIアドバイザーを設置しました。これは現在書かれているクエリを最適化したり、クエリに関するいろいろなことをチャットで相談したりする機能です。
特にエラーが出た時に「このエラーを直して！」といった感じですぐに修正させることができます。
修正案もすぐに反映させることができます。いやー便利ですね。現在はやっていませんが、RAGのようにテーブル構造をある程度読ませられるようになると、「〇〇から〇〇を抜き出すクエリを書いて」とか言えばクエリを書いてくれる様になりそうです。
おわりに
KaterではDX支援事業として、DX化、システム化に困っている方々を支援したいと思っています。
今回紹介した事例はセキュリティ上の理由により非公開となっていますが、このシステムのデモ版を使ってみたい方、このシステムを自社向けにさらに改良したい方には お問い合わせ からお問い合わせください。
Katerで制作するデモ版はあくまでPoCにむけたMVPというもので、改良を前提として作成されています。ですので購入していただいた方にはコードをそのままお渡しし、自社もしくは他社でそのコードを改良、改善していっていただくという想定です。そのために必要なドキュメント類も付属しています。
またデモ版でPoCのための運用を行いたいがそのためのデプロイが行えない方に向けては弊社にてデプロイ作業代行も行っております。 そういった場合も含めてどうぞお気軽に お問い合わせ ください。
