Telegram Bot PostgreSQL分析ガイド：会話テーブルとイベントテーブルのスキーマ設計ベストプラクティス

自前でTelegram Botを構築してカスタマーサポートや運営を行う際、最も見落とされがちなのがデータ基盤です。多くのチームは初期段階でBotFatherとシンプルなWebhookを使ってメッセージを受信し、ログがファイルや単一のメッセージテーブルに散在してしまいます。「どのエージェントが最も速く返信したか？」「ユーザーが広告リンクから会話を開始するまでの平均時間は？」「今週クローズされた会話のうち、何件が転送されたか？」といった質問に答えようとしたとき、初めてデータが関連付けてクエリできないことに気づきます。

これこそ、自前のTelegram Botに構造化されたPostgreSQL分析スキーマが必要な理由です。適切なスキーマ設計により、「ログを見る」から「レポートを見る」へとレベルアップし、運営判断、カスタマーサポートの評価、コンバージョンアトリビューションに信頼性の高い基盤を提供します。本記事では、カスタマーサポート分析のシナリオを例に、実践可能な基本スキーマの考え方を、会話テーブル、イベントテーブル、そして一般的なレポートクエリとともに紹介します。

---

なぜ自前のTelegram BotにPostgreSQL分析スキーマが必要なのか？

Botが単純な自動応答のみを処理するなら、単一テーブルでメッセージを記録するだけでも十分かもしれません。しかし、有人エージェント、マルチチャネルからの誘導、会話の転送、ユーザーセグメンテーションが絡んでくると、データ要件は急速に複雑化します。

• ログの散乱：メッセージ、ステータス変更、分流リンクのクリックが混在し、会話単位で集計できません。
• 会話の統一的関連付けの欠如：ユーザーが1件メッセージを送り、エージェントが1件返信する、これらは同じ会話に属しますが、全体を貫く「会話ID」がありません。
• コンバージョンアトリビューションの困難：ユーザーが広告リンクをクリック → Botに遷移 → 自動応答 → 有人エージェントが対応、という一連の流れの中で、いずれかの段階でデータが欠落すると、チャネル効果を評価できません。

構造化されたPostgreSQLスキーマの役割は、2つのコアテーブル（conversationsとevents）を使って、散在したデータを分析可能なモデルに整理することです。その後のレポート、ユーザー属性分析、ファネル分析は、この2つのテーブルを基に展開します。

---

コアテーブル設計（1）：会話テーブル（conversations）——カスタマーサポート分析の基盤

conversationsテーブルは、カスタマーサポート分析の出発点です。ユーザーが会話を開始してから、エージェントが介入し、会話がクローズされるまでの、完全な人とボットの対話ライフサイクルを記録します。

テーブル作成SQL例

CREATE TABLE conversations (
    id BIGSERIAL PRIMARY KEY,
    bot_id BIGINT NOT NULL,          -- 关联 Bot 项目
    user_id BIGINT NOT NULL,         -- Telegram 用户 ID
    staff_id BIGINT,                 -- 最终处理的坐席 ID
    status VARCHAR(20) NOT NULL DEFAULT 'open',  -- open | closed | pending
    channel_source VARCHAR(50),      -- 来源渠道，如 diversion_link / direct
    start_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    end_at TIMESTAMPTZ,
    first_response_time INT,         -- 秒，用户首次发送到坐席首次回复的间隔
    last_message_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

会話ステートマシンと重要なタイムスタンプ

statusフィールドには通常、3つの列挙値が含まれます：

• open：会話進行中。エージェントまたはユーザーがまだメッセージを送信できます。
• closed：会話終了（例：エージェントが完了をマーク、またはユーザーがタイムアウト）。
• pending：ユーザーの返信待ち。エージェントが返信したが、ユーザーが未返信。

重要なタイムスタンプ：

• first_response_time：計算方法はstaff_first_reply_at - start_at。この値はエージェントの応答速度を直接反映し、カスタマーサポート評価のコア指標です。
• last_message_at：最新メッセージの時刻を記録し、会話がタイムアウトしたか（例：24時間以上メッセージがない場合は自動クローズ）を判断するために使用します。

外部キー関係とインデックス最適化

• user_idはユーザーテーブル（users）に関連付け、Telegramユーザーの基本情報（ユーザー名、言語コードなど）を格納します。
• staff_idはエージェントテーブル（staffs）に関連付け、エージェントの名前、グループなどを記録します。
• 推奨複合インデックス：
  • (bot_id, status)：特定のBotのオープン/クローズ会話数を迅速に集計。
  • (staff_id, created_at)：エージェント単位での過去の対応量を確認。
  • (start_at, status)：時間範囲で会話のトレンドを分析。

---

コアテーブル設計（2）：イベントテーブル（events）——詳細な行動追跡

会話テーブルは「結果」を記録し、イベントテーブルは「プロセス」を記録します。個々のメッセージ、状態変更、分流リンクのクリックはすべて、アトミックなイベントです。

テーブル作成SQL例

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations(id),
    event_type VARCHAR(50) NOT NULL,  -- message_sent, message_received, transfer, diversion_click, risk_alert
    payload JSONB,                    -- 事件详情，如消息内容、IP、UA
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

一般的なイベントタイプとペイロード構造

イベントタイプ	ペイロード例
message_sent	{"content": "您好，有什么可以帮您？", "staff_id": 123}
message_received	{"content": "我想咨询订单", "user_id": 456}
transfer	{"from_staff": 123, "to_staff": 456, "reason": "业务不匹配"}
diversion_click	{"ip": "203.0.113.1", "ua": "Mozilla/5.0 ...", "campaign": "ad_campaign_1"}
risk_alert	{"risk_word": "wallet_address", "staff_id": 123, "triggered": true}

ペイロードにJSONBを使用する利点は、テーブル構造を変更せずに任意のフィールドを追加できることです。例えば、diversion_clickイベントに後でreferrerフィールドを追加したい場合、JSONに直接書き込むだけで済みます。

イベントテーブルと会話テーブルのクエリシナリオの区別

• 会話テーブルを検索：「今日クローズされた会話はいくつか？」「どのエージェントの対応量が最も多いか？」を知りたい場合、conversationsテーブルを直接検索します。
• イベントテーブルを検索：「ユーザーが分流リンクをクリックしてから初回返信までの平均ステップ数」「特定ユーザーの操作パス」を分析したい場合、conversation_idでイベントを並べ替える必要があります。

設計原則：会話テーブルは状態のスナップショット（現在誰が処理中か、終了しているか）を保存し、イベントテーブルは変更のシーケンス（各ステップで何が行われたか）を保存します。両者を統合しないでください。そうしないと、データの冗長性によりクエリが遅くなります。

---

発展的分析：会話とイベントに基づくレポートクエリ例

以下のSQLは上記スキーマに基づき、データベースに実データが格納されていることを前提とします。

例1：日別エージェント対応量

SELECT
    DATE(start_at) AS day,
    staff_id,
    COUNT(*) AS total_conversations,
    COUNT(*) FILTER (WHERE status = 'closed') AS closed_conversations
FROM conversations
WHERE bot_id = 101
  AND start_at >= '2025-01-01'
GROUP BY DATE(start_at), staff_id
ORDER BY day, staff_id;

解説：このクエリにより、各エージェントの日々の対応負荷とクローズ率（closedの割合）を迅速に把握できます。特定のエージェントのクローズ率が長期間低い場合、会話転送が頻繁に行われていないか確認が必要です。

例2：ユーザー初回応答時間の分布

WITH response_times AS (
    SELECT
        user_id,
        first_response_time
    FROM conversations
    WHERE bot_id = 101
      AND first_response_time IS NOT NULL
)
SELECT
    CASE
        WHEN first_response_time < 30 THEN '0-30秒'
        WHEN first_response_time < 60 THEN '30-60秒'
        WHEN first_response_time < 300 THEN '1-5分钟'
        ELSE '5分钟以上'
    END AS response_bucket,
    COUNT(*) AS count
FROM response_times
GROUP BY response_bucket
ORDER BY response_bucket;

解説：初回応答時間をグループ化することで、カスタマーサポートチーム全体の応答効率を把握できます。ほとんどの会話が「5分以上」に集中している場合、エージェントのシフト調整や振り分けルールの最適化が必要です。

例3：分流リンクのコンバージョンファネル

WITH funnel AS (
    SELECT
        c.id AS conversation_id,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'diversion_click'
        ) AS has_click,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'message_received'
        ) AS has_message,
        EXISTS (
            SELECT 1 FROM events e
            WHERE e.conversation_id = c.id
              AND e.event_type = 'message_sent'
        ) AS has_reply
    FROM conversations c
    WHERE c.bot_id = 101
)
SELECT
    COUNT(*) AS total_conversations,
    COUNT(*) FILTER (WHERE has_click) AS clicked,
    COUNT(*) FILTER (WHERE has_click AND has_message) AS messaged,
    COUNT(*) FILTER (WHERE has_click AND has_message AND has_reply) AS replied
FROM funnel;

解説：このファネルは、誘導からカスタマーサポート対応までの各段階の離脱率を評価するのに役立ちます。例えば、分流リンクをクリックしたユーザーの70%がメッセージを送信したが、エージェントの返信を受けたのは40%だけだった場合、問題はエージェント不足か振り分けルールの不適切さにある可能性があります。

---

よくある設計の落とし穴と回避アドバイス

落とし穴 1：フィールドが多すぎて、早すぎる最適化

一部のチームはスキーマ設計段階で、user_name、staff_name のような冗長なフィールドを多数追加し、データ更新時に複数のテーブルを同期する必要が生じます。アドバイス：ID 外部キーのみを保持し、名前などの属性は JOIN クエリか、マテリアライズドビューによるキャッシュを利用しましょう。

落とし穴 2：データクリーンアップ戦略の欠如

events テーブルは最も急速に成長します。クリーンアップしなければ、1ヶ月で数百万件に達する可能性があります。アドバイス：

• 月単位のパーティションを設定し、直近6〜12ヶ月のイベントを保持します。
• 12ヶ月を超えたセッションは、コールドストレージ（CSVエクスポートやS3など）にアーカイブします。

落とし穴 3：複数Botプロジェクトの分離を考慮していない

プラットフォームで複数のBotを管理する場合、コアテーブルには bot_id フィールドを含め、bots テーブルへの外部キー関連付けを確立する必要があります。クエリ時は常に bot_id でフィルタリングし、データの混在を防ぎます。これはマルチテナント設計の基本要件です。

落とし穴 4：イベントテーブルに一意制約がない

同じイベントがリトライメカニズムにより2回挿入される可能性があります。アドバイス：events テーブルに (conversation_id, event_type, created_at) の一意制約を追加するか、アプリケーションレベルで冪等書き込みを実装します。

---

自社構築からSaaSへ：いつTG-Staffへの移行を検討すべきか

PostgreSQLスキーマの自社構築は柔軟で制御しやすいという利点がありますが、テーブル構造の設計、ビジネスロジックの記述、データベースパフォーマンスの維持に開発時間を費やす必要があるという代償もあります。チームが以下の状況に直面した場合、自社構築から成熟したプラットフォームへの移行を検討できます：

• メンテナンスコストが高い：データベース管理、スロークエリの最適化、データ整合性の処理に専任者が必要。
• リアルタイムカスタマーサービスパネルがない：自社構築のレポートは通常T+1であり、エージェントのステータスやセッションリストをリアルタイムで確認できない。
• 内部統制とリスク管理が必要：自社構築のコンテンツリスク管理（ウォレットアドレス監視など）には追加開発が必要で、リアルタイム性の確保が難しい。
• 多言語翻訳：自社構築の翻訳機能はAPI連携、クォータ管理、結果キャッシュが必要で、工数がかかる。

TG-Staff はこれらの分析機能を直接提供し、スキーマの自社構築は不要です：

• ユーザープロファイルとセッション記録は自動保存され、コンソールからエージェント、時間、ステータスでフィルタリング可能。
• 分流リンクの属性取得：IP、UA、URLパラメータを自動取得し、イベントテーブルを自前で設計する必要なし。
• コンテンツリスク管理：リスクワード検出とウォレットアドレス監視を内蔵し、監査ログをサポート。
• 自動翻訳：スタンダード版はAI翻訳、プロフェッショナル版はDeepL/Googleプロフェッショナル翻訳に対応。

まだ自社構築段階であれば、上記のスキーマが出発点として役立ちます。しかし、チームのリソースが限られている場合や、すぐにプロ仕様のカスタマーサポート機能を導入したい場合は、TG-Staffの無料トライアルをお試しください。

---

よくある質問

Q: 自社でPostgreSQLスキーマを構築するには、どのくらいのストレージ容量が必要ですか？
A: アクティブユーザー数とメッセージ量によります。10万イベントあたり約50～100 MBと見積もり、eventsテーブルは月単位でパーティション分割し、履歴データを定期的にアーカイブすることをお勧めします。

Q: セッションテーブルとイベントテーブルのデータ一貫性を保証するには？
A: アプリケーション層でトランザクション（BEGIN/COMMIT）を使用して両方のテーブルに同時に書き込むか、イベント駆動アーキテクチャを採用して、最初にeventsに書き込み、非同期タスクでセッション状態を集約することをお勧めします。

Q: このスキーマ設計は複数のBotプロジェクトをサポートしていますか？
A: はい。コアテーブルには bot_id フィールドを含め、botsテーブルへの外部キー制約を設定する必要があります。クエリ時には常にbot_idでフィルタリングし、データの混同を防ぎます。

Q: PostgreSQL上でリアルタイムのカスタマーサポートパネルを直接実装できますか？
A: PostgreSQLのLISTEN/NOTIFYやサードパーティツール（Grafanaなど）と組み合わせることで、ほぼリアルタイムのダッシュボードを実現できますが、リアルタイム双方向チャットにはWebSocket層が必要です。すぐに使えるソリューションをお求めの場合は、TG-StaffのWebコンソールをご参照ください。

Q: イベントテーブルのpayloadはJSONとJSONBのどちらを使うべきですか？
A: JSONBをお勧めします。インデックスとより効率的なクエリ（payload->>'ip'など）をサポートし、ストレージ容量も若干優れています。パフォーマンス低下を防ぐため、payloadフィールドのサイズは8 KB未満に制限することをお勧めします。

---

スキーマ設計をスキップして、すぐにプロ仕様のカスタマーサポート分析機能を利用したい場合は、TG-Staff無料トライアルに登録するか、@tgstaff_robotまでお問い合わせいただき、プランの詳細をご確認ください。また、ドキュメントでAPIと統合ソリューションをご覧いただけます。