How to build Crisp chat dashboards in Metabase

{
  "mcpServers": {
    "crisp": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://api.crisp.chat/mcp/",
        "--header",
        "Authorization:Bearer ${CRISP_MCP_SERVER_TOKEN}"
      ],
      "env": { "CRISP_MCP_SERVER_TOKEN": "<your-crisp-mcp-server-token>" }
    },
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

Create a polished Metabase dashboard for Crisp live-chat support analytics using
the available Crisp tables in this database.

Goal: Help support leaders understand chat volume, responsiveness, resolution, and
contact drivers from Crisp data.

First, inspect the schema and identify the available Crisp tables. Do not assume
exact table names. Map the available raw tables into these analytical concepts
where possible: Conversations, Messages, People (contacts), Operators (agents),
Segments, and Sessions.

Important:
- Treat MCP data access as exploratory only.
- Build the dashboard from durable database/warehouse tables.
- Use medians (p50) and p90 for response times, never averages.
- Define "first response" as the first outbound operator message, excluding
  automated/bot messages and internal notes.
- Chat is concurrent and fast — measure response in minutes, and consider missed
  chats explicitly.
- If conversation state history is missing, do not calculate time-in-state. Use a
  caveat instead.
- Do not claim Metabase connects natively to Crisp unless that is explicitly true
  in this environment.

Dashboard title: Crisp Chat Overview

Sections:
1. Executive summary (KPI cards): Conversations opened last 7 days; Resolved last
   7 days; Open backlog; Median time to first response; Volume by hour.
2. Volume & backlog: Opened vs resolved by day; Open by state; Aging unresolved;
   Volume by hour.
3. Response time: First response p50/p90 by week; Missed chats by day; Reopened by
   week (only if history exists).
4. Operator performance: Resolved by operator; Open assigned by operator; Median
   handle time by operator; Concurrent chats.
5. Segments & drivers: Volume by segment; Volume by page/trigger; New vs returning
   visitors; CSAT by week (only if ratings exist).

Filters: Operator, Segment, State, Date range.

Before finalizing, create or recommend reusable Metabase models:
modeled_crisp_conversations, modeled_crisp_messages, modeled_crisp_people, and
modeled_crisp_operators.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available schema. Keep it practical, dense,
and executive-readable. Avoid vanity metrics.

The basic volume trend over the last 30 days.

SELECT
  date_trunc('day', to_timestamp(c.created_at / 1000)) AS day,
  COUNT(*)                                              AS opened,
  COUNT(*) FILTER (WHERE c.state = 'resolved')          AS resolved
FROM conversations c
WHERE to_timestamp(c.created_at / 1000) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Median from the first non-automated operator message per conversation.

WITH first_outbound AS (
  SELECT
    m.conversation_id,
    MIN(m.created_at) AS first_reply_at
  FROM messages m
  WHERE m.from = 'operator'
    AND m.is_automated = false
  GROUP BY m.conversation_id
)
SELECT
  date_trunc('week', to_timestamp(c.created_at / 1000)) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY (f.first_reply_at - c.created_at) / 60000.0
  ) AS median_first_reply_min
FROM conversations c
JOIN first_outbound f ON f.conversation_id = c.session_id
GROUP BY 1
ORDER BY 1;

Unresolved conversations by state right now.

SELECT
  c.state,
  COUNT(*)           AS conversations
FROM conversations c
WHERE c.state <> 'resolved'
GROUP BY c.state
ORDER BY conversations DESC;

When chats arrive, so you can staff for peaks.

SELECT
  EXTRACT(HOUR FROM to_timestamp(c.created_at / 1000)) AS hour_of_day,
  COUNT(*)                                              AS conversations
FROM conversations c
WHERE to_timestamp(c.created_at / 1000) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;