How to build Intercom support dashboards in Metabase

# Intercom (remote, OAuth in browser) — US-hosted workspaces
claude mcp add --transport http intercom https://mcp.intercom.com/mcp

{
  "mcpServers": {
    "intercom": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://mcp.intercom.com/mcp"]
    }
  }
}

# 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 an Intercom CSV export — creates a table AND a model
mb upload csv --file intercom-conversations.csv --collection root

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

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

Goal: Help support and CX leaders understand conversation volume, response time,
Fin AI resolution, CSAT, and teammate workload from Intercom data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Intercom tables and
  models). If durable Intercom 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 Intercom MCP server (US-hosted
  workspaces): conversations, conversation parts, contacts, admins, teams, and
  tags. 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, Conversation parts (messages),
Contacts, Admins (teammates), Teams, Tags, Segments, Companies, and conversation
statistics if available. Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Intercom — it reads a database or CLI-uploaded tables.
- Use medians (p50) and p90 for response and close times, never averages.
- Define "first response" as the first human teammate reply unless you are
  explicitly measuring Fin AI, in which case separate AI replies clearly.
- If conversation state-change 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: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: Intercom Support Overview

Sections:
1. Executive summary (KPI cards): Conversations opened last 7 days; Closed last 7
   days; Open backlog; Median time to first response; Median time to close;
   Fin resolution rate (only if AI resolution data exists).
2. Volume & backlog: Opened vs closed by day; Open by state; Backlog aging;
   Volume by channel.
3. Response time: First response time p50/p90 by week; Conversations over target
   by team; Time to close by week.
4. Fin AI & deflection: Fin resolution rate by week; AI vs human resolved;
   Handover rate; CSAT for AI vs human (only if rating data exists).
5. Teammate & team: Closed by teammate; Median handle time by team; Open assigned
   by teammate; Rating by team.

Filters: Team, Teammate, Channel, Tag, Segment, State, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_intercom_conversations,
modeled_intercom_conversation_parts, modeled_intercom_contacts,
modeled_intercom_admins, and modeled_intercom_companies.

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)) AS day,
  COUNT(*)                                       AS opened,
  COUNT(*) FILTER (WHERE c.state = 'closed')     AS closed
FROM conversations c
WHERE to_timestamp(c.created_at) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Median and p90 from the conversation's first-response field.

SELECT
  date_trunc('week', to_timestamp(c.created_at)) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY c.time_to_first_response / 60.0
  ) AS median_frt_min,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY c.time_to_first_response / 60.0
  ) AS p90_frt_min
FROM conversations c
WHERE c.time_to_first_response IS NOT NULL
GROUP BY 1
ORDER BY 1;

AI-handled conversations closed without handover. Adjust field names to your schema.

SELECT
  date_trunc('week', to_timestamp(c.created_at)) AS week,
  COUNT(*)                                          AS total_closed,
  COUNT(*) FILTER (WHERE c.ai_agent_participated)   AS ai_handled,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE c.ai_agent_participated AND NOT c.handed_over)
      / NULLIF(COUNT(*), 0),
    1
  ) AS fin_resolution_pct
FROM conversations c
WHERE c.state = 'closed'
GROUP BY 1
ORDER BY 1;

Where open conversations are piling up right now.

SELECT
  t.name             AS team,
  COUNT(*)           AS open_conversations
FROM conversations c
LEFT JOIN teams t ON t.id = c.team_assignee_id
WHERE c.state = 'open'
GROUP BY t.name
ORDER BY open_conversations DESC;