How to build Zendesk support dashboards in Metabase

{
  "mcpServers": {
    "zendesk": {
      "command": "npx",
      "args": ["-y", "@fruggr/zendesk-mcp-server"],
      "env": {
        "ZENDESK_SUBDOMAIN": "your-company",
        "ZENDESK_EMAIL": "you@example.com",
        "ZENDESK_API_TOKEN": "your-api-token"
      }
    },
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

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

Goal: Help support leaders understand volume, responsiveness, SLA attainment,
CSAT, and agent workload from Zendesk data.

First, inspect the schema and identify the available Zendesk tables. Do not assume
exact table names. Map the available raw tables into these analytical concepts
where possible: Tickets, Ticket comments/audits, Ticket metrics, Users (agents and
end users), Organizations, Groups, Tags, Satisfaction ratings, and SLA policies.

Important:
- Treat MCP data access as exploratory only.
- Build the dashboard from durable database/warehouse tables.
- Use medians (p50) and p90 for reply and resolution times, never averages.
- Define "first reply" as the first public agent comment, excluding automated
  messages and internal notes.
- If ticket audit/status history is missing, do not calculate reopen rate or
  time-in-status. Use a caveat instead.
- Do not claim Metabase connects natively to Zendesk unless that is explicitly
  true in this environment.

Dashboard title: Zendesk Support Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Tickets solved
   last 7 days; Open backlog; Median first reply time; Median full resolution
   time; CSAT % (only if satisfaction ratings exist).
2. Volume & backlog: Created vs solved by day; Open tickets by status; Backlog
   aging by days-open bucket; Volume by channel.
3. SLA & response time: SLA attainment by policy (only if SLA fields exist);
   Breached tickets by reason; Reply time p50/p90 by week.
4. CSAT & quality: CSAT by week; Rating response rate; Bad ratings by tag;
   Reopen rate (only if audits exist).
5. Agent & team: Solved by agent; Median handle time by group; Open assigned by
   agent; Volume by group.

Filters: Group, Assignee, Channel, Tag, Priority, Status, Date range.

Before finalizing, create or recommend reusable Metabase models:
modeled_zendesk_tickets, modeled_zendesk_comments, modeled_zendesk_users,
modeled_zendesk_organizations, modeled_zendesk_satisfaction, and
modeled_zendesk_sla (only if SLA data exists).

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', t.created_at) AS day,
  COUNT(*)                                          AS created,
  COUNT(*) FILTER (WHERE t.status IN ('solved', 'closed')) AS solved
FROM tickets t
WHERE t.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Median and p90 from the pre-computed ticket_metrics durations.

SELECT
  date_trunc('week', t.created_at) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY m.reply_time_in_minutes
  ) AS median_first_reply_min,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY m.reply_time_in_minutes
  ) AS p90_first_reply_min
FROM tickets t
JOIN ticket_metrics m ON m.ticket_id = t.id
WHERE m.reply_time_in_minutes IS NOT NULL
GROUP BY 1
ORDER BY 1;

Good ratings as a share of rated tickets.

SELECT
  date_trunc('week', s.created_at) AS week,
  COUNT(*) FILTER (WHERE s.score = 'good') AS good,
  COUNT(*) FILTER (WHERE s.score IN ('good', 'bad')) AS rated,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE s.score = 'good')
      / NULLIF(COUNT(*) FILTER (WHERE s.score IN ('good', 'bad')), 0),
    1
  ) AS csat_pct
FROM satisfaction_ratings s
GROUP BY 1
ORDER BY 1;

Open tickets bucketed by how long they've been waiting.

SELECT
  CASE
    WHEN CURRENT_DATE - t.created_at::date <= 1  THEN '0-1 days'
    WHEN CURRENT_DATE - t.created_at::date <= 3  THEN '2-3 days'
    WHEN CURRENT_DATE - t.created_at::date <= 7  THEN '4-7 days'
    ELSE '8+ days'
  END                AS age_bucket,
  COUNT(*)           AS open_tickets
FROM tickets t
WHERE t.status NOT IN ('solved', 'closed')
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - t.created_at::date);