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>" }
    }
  }
}

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a Crisp CSV export — creates a table AND a model
mb upload csv --file crisp-conversations.csv --collection root

# Refresh that same table later from a new export
mb upload replace <table-id> --file crisp-conversations.csv

Create a polished Metabase dashboard for Crisp live-chat support analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

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

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Crisp tables and
  models). If durable Crisp data is already present — synced from a warehouse or
  uploaded earlier — use it and skip to Step 2.
- If nothing is there, pull it with the Crisp MCP server using a read-only token:
  conversations, messages, people (contacts), and operators. Write each result to
  a CSV, then load it with the Metabase CLI — run "mb upload csv --file
  <export>.csv" so each upload creates a table and a ready-to-query model. Use
  "mb upload replace <table-id> --file <export>.csv" to refresh an existing table
  instead of creating duplicates.

Step 2 — Inspect before querying:
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. Inspect the actual tables and column
names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Crisp — it reads a database or CLI-uploaded 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.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: trends and time-in-state need history,
  so build trend cards only if a warehouse sync or multiple uploads provide it.

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.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable 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 data. 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;