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

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

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

Create a polished Metabase dashboard for Freshdesk 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 Freshdesk data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Freshdesk tables and
  models). If durable Freshdesk 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 a Freshdesk MCP server (use a scoped API key):
  tickets (with stats), conversations, contacts, agents, and groups. 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, Conversations (replies and notes),
Contacts, Agents, Groups, Companies, Satisfaction surveys, and SLA fields.
Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Freshdesk — it reads a database or CLI-uploaded 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.
- 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: 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.

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