How to build Jitbit help desk dashboards in Metabase

{
  "mcpServers": {
    "jitbit": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://yourcompany.jitbit.com/api/mcp",
        "--header",
        "Authorization:Bearer YOUR_JITBIT_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 Jitbit CSV export — creates a table AND a model
mb upload csv --file jitbit-tickets.csv --collection root

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

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

Goal: Help IT support leaders understand volume, responsiveness, SLA, category
drivers, and technician workload from Jitbit data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Jitbit tables and
  models). If durable Jitbit data is already present — synced from a warehouse,
  read directly from a self-hosted SQL Server replica, or uploaded earlier — use
  it and skip to Step 2.
- If nothing is there, pull it with the read-only Jitbit MCP server: search and
  read tickets, comments, and categories. 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, Comments, Users (technicians and end
users), Categories, Statuses, Priorities, Tags, and Departments. Inspect the
actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  the Jitbit SaaS API — it reads a database, a self-hosted SQL Server, or
  CLI-uploaded tables.
- Use medians (p50) and p90 for response and resolution times, never averages.
- Define "first response" as the first technician comment, excluding internal
  notes and automated messages.
- If status-change 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: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: Jitbit Help Desk Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Closed last 7 days;
   Open backlog; Median first response time; Volume by category.
2. Volume & backlog: Created vs closed by day; Open by status; Backlog aging;
   Volume by category.
3. SLA & response time: First response p50/p90 by week; Overdue by category;
   Reopened by week (only if history exists).
4. Technician performance: Closed by technician; Median resolution time by
   technician; Open assigned by technician; Volume by priority.
5. Categories & drivers: Volume by category over time; Top recurring issue types;
   Tickets by department; Repeat requesters.

Filters: Category, Technician, Priority, Status, Department, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse or
direct database) create reusable models: modeled_jitbit_tickets,
modeled_jitbit_comments, modeled_jitbit_users, and modeled_jitbit_categories.

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 (StatusID 3 = closed).

SELECT
  CAST(t.IssueDate AS date) AS day,
  COUNT(*)                                          AS created,
  COUNT(*) FILTER (WHERE t.StatusID = 3)            AS closed
FROM Tickets t
WHERE t.IssueDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY CAST(t.IssueDate AS date)
ORDER BY 1;

Median from the first non-internal technician comment per ticket.

WITH first_tech_comment AS (
  SELECT
    c.IssueID,
    MIN(c.CommentDate) AS first_reply_at
  FROM Comments c
  JOIN Users u ON u.UserID = c.UserID
  WHERE u.IsAdmin = true       -- technician
    AND c.ForTechsOnly = false
  GROUP BY c.IssueID
)
SELECT
  date_trunc('week', t.IssueDate) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (f.first_reply_at - t.IssueDate)) / 60.0
  ) AS median_first_reply_min
FROM Tickets t
JOIN first_tech_comment f ON f.IssueID = t.TicketID
GROUP BY 1
ORDER BY 1;

Top IT ticket drivers over the last 90 days.

SELECT
  cat.CategoryName   AS category,
  COUNT(*)           AS tickets
FROM Tickets t
JOIN Categories cat ON cat.CategoryID = t.CategoryID
WHERE t.IssueDate >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY cat.CategoryName
ORDER BY tickets DESC;

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

SELECT
  CASE
    WHEN CURRENT_DATE - t.IssueDate::date <= 1  THEN '0-1 days'
    WHEN CURRENT_DATE - t.IssueDate::date <= 3  THEN '2-3 days'
    WHEN CURRENT_DATE - t.IssueDate::date <= 7  THEN '4-7 days'
    ELSE '8+ days'
  END                AS age_bucket,
  COUNT(*)           AS open_tickets
FROM Tickets t
WHERE t.StatusID <> 3
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - t.IssueDate::date);