How to build Freshdesk support dashboards in Metabase

{
  "mcpServers": {
    "freshdesk": {
      "type": "http",
      "url": "https://<your-account>.freshdesk.com/mcp",
      "headers": { "Authorization": "<YOUR_FRESHDESK_API_KEY>" }
    },
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

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

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

First, inspect the schema and identify the available Freshdesk tables. Do not
assume exact table names. Map the available raw tables into these analytical
concepts where possible: Tickets, Conversations (replies and notes), Contacts,
Agents, Groups, Companies, Satisfaction surveys, and SLA fields.

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

Dashboard title: Freshdesk Support Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Resolved last 7
   days; Open backlog; Median first response time; Median resolution time;
   CSAT % (only if survey data exists).
2. Volume & backlog: Created vs resolved by day; Open by status; Backlog aging;
   Volume by source.
3. SLA & response time: First response and resolution SLA attainment; Overdue by
   group; Response time p50/p90 by week.
4. CSAT & quality: CSAT by week; Survey response rate; Reopened by week (only if
   activities exist); Volume by ticket type and priority.
5. Agent & group: Resolved by agent; Median resolution time by group; Open
   assigned by agent; Volume by source.

Filters: Group, Agent, Source, Ticket type, Priority, Status, Date range.

Before finalizing, create or recommend reusable Metabase models:
modeled_freshdesk_tickets, modeled_freshdesk_conversations,
modeled_freshdesk_contacts, modeled_freshdesk_agents, and
modeled_freshdesk_groups.

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 (status 4/5 = resolved/closed).

SELECT
  date_trunc('day', t.created_at) AS day,
  COUNT(*)                                            AS created,
  COUNT(*) FILTER (WHERE t.status IN (4, 5))          AS resolved
FROM tickets t
WHERE t.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Median and p90 from created to the stats resolved timestamp.

SELECT
  date_trunc('week', t.created_at) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (t.stats_resolved_at - t.created_at)) / 3600.0
  ) AS median_resolution_hours,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (t.stats_resolved_at - t.created_at)) / 3600.0
  ) AS p90_resolution_hours
FROM tickets t
WHERE t.stats_resolved_at IS NOT NULL
GROUP BY 1
ORDER BY 1;

Share of resolved tickets that did not escalate first response. Adjust to your SLA fields.

SELECT
  g.name             AS group_name,
  COUNT(*)           AS resolved_tickets,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE NOT t.fr_escalated) / NULLIF(COUNT(*), 0),
    1
  ) AS first_response_sla_pct
FROM tickets t
JOIN groups g ON g.id = t.group_id
WHERE t.status IN (4, 5)
GROUP BY g.name
ORDER BY resolved_tickets DESC;

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 (4, 5)
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - t.created_at::date);