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

# Metabase built-in MCP (replace with your instance URL)
claude mcp add --transport http metabase https://your-metabase.example.com/api/metabase-mcp

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

Create a polished Metabase dashboard for Intercom support analytics using the
available Intercom tables in this database.

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

First, inspect the schema and identify the available Intercom tables. 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.

Important:
- Treat MCP data access as exploratory only.
- Build the dashboard from durable database/warehouse 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.
- Do not claim Metabase connects natively to Intercom unless that is explicitly
  true in this environment.

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.

Before finalizing, create or recommend reusable Metabase 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 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)) 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;