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

# 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 Zendesk CSV export — creates a table AND a model
mb upload csv --file zendesk-tickets.csv --collection root

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

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

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

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Zendesk tables and
  models). If durable Zendesk 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 Zendesk MCP server using a read-only API
  token: tickets, ticket comments, ticket metrics, users, and satisfaction
  ratings. 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: Tickets, Ticket comments/audits, Ticket
metrics, Users (agents and end users), Organizations, Groups, Tags, Satisfaction
ratings, and SLA policies. Inspect the actual tables and column names first.

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

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.

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