How to build Front support dashboards in Metabase

{
  "mcpServers": {
    "front": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://mcp.frontapp.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 a Front CSV export — creates a table AND a model
mb upload csv --file front-conversations.csv --collection root

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

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

Goal: Help support leaders understand inbox volume, response time, SLA, teammate
workload, and topic drivers from Front data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Front tables and
  models). If durable Front 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 Front MCP server (use a read-scoped
  connection): conversations, messages, comments, contacts, teammates, and
  inboxes. 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, Comments (internal
notes), Contacts, Teammates, Inboxes, Channels, and Tags. Inspect the actual
tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Front — it reads a database or CLI-uploaded tables.
- Use medians (p50) and p90 for reply times, never averages.
- Define "first reply" as the first outbound message from a teammate, excluding
  internal comments and automated messages.
- If conversation event history is missing, do not calculate time-in-status or
  reopen rate. 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: time-in-status and reopen rate need
  history, so build trend cards only if a warehouse sync or multiple uploads
  provide it.

Dashboard title: Front Inbox Overview

Sections:
1. Executive summary (KPI cards): Conversations opened last 7 days; Archived last
   7 days; Open backlog; Median time to first reply; Volume by channel.
2. Volume & backlog: Opened vs archived by day; Open by inbox; Backlog aging;
   Volume by channel.
3. Response time & SLA: First reply time p50/p90 by week; Over-target by inbox;
   Reopened by week (only if event history exists).
4. Teammate workload: Archived by teammate; Open assigned by teammate; Median
   handle time by teammate.
5. Topics & drivers: Volume by tag; Slowest tags; Volume by contact account.

Filters: Inbox, Teammate, Channel, Tag, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_front_conversations, modeled_front_messages,
modeled_front_contacts, modeled_front_teammates, and modeled_front_inboxes.

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

Median from the first outbound teammate message per conversation.

WITH first_outbound AS (
  SELECT
    m.conversation_id,
    MIN(m.created_at) AS first_reply_at
  FROM messages m
  WHERE m.is_inbound = false
    AND m.is_draft = false
  GROUP BY m.conversation_id
)
SELECT
  date_trunc('week', c.created_at) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (f.first_reply_at - c.created_at)) / 60.0
  ) AS median_first_reply_min
FROM conversations c
JOIN first_outbound f ON f.conversation_id = c.id
GROUP BY 1
ORDER BY 1;

Where open conversations are piling up right now.

SELECT
  i.name             AS inbox,
  COUNT(*)           AS open_conversations
FROM conversations c
JOIN inboxes i ON i.id = c.inbox_id
WHERE c.status = 'open'
GROUP BY i.name
ORDER BY open_conversations DESC;

Top topic drivers over the last 90 days.

SELECT
  tg.name            AS tag,
  COUNT(*)           AS conversations
FROM conversations c
JOIN conversation_tags ct ON ct.conversation_id = c.id
JOIN tags tg ON tg.id = ct.tag_id
WHERE c.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY tg.name
ORDER BY conversations DESC
LIMIT 20;