How to build LiveAgent support dashboards in Metabase

{
  "mcpServers": {
    "liveagent": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://YOUR_ACCOUNT.ladesk.com/public/api/mcp"
      ]
    }
  }
}

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

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

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

Goal: Help support leaders understand multi-channel volume, responsiveness,
department health, and agent workload from LiveAgent data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for LiveAgent tables and
  models). If durable LiveAgent 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 LiveAgent MCP server: tickets, messages,
  agents, contacts, and departments. 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, Messages, Agents, Contacts,
Departments, Tags, and Channels (email, chat, call, social). Inspect the actual
tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  LiveAgent — it reads a database or CLI-uploaded tables.
- Use medians (p50) and p90 for response times, never averages.
- Define "first response" as the first outbound agent message, excluding internal
  notes and automated messages.
- If ticket event 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: LiveAgent Support Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Resolved last 7
   days; Open backlog; Median first response time; Volume by channel.
2. Volume & backlog: Created vs resolved by day; Open by status; Backlog aging;
   Volume by channel.
3. Response time & SLA: First response p50/p90 by week; Open by department;
   Reopened by week (only if history exists).
4. Agent performance: Resolved by agent; Median handle time by agent; Open
   assigned by agent; Calls by agent.
5. Channels & drivers: Volume by channel over time; Volume by tag; Volume by hour;
   Repeat contacts.

Filters: Department, Agent, Channel, Tag, Status, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_liveagent_tickets, modeled_liveagent_messages,
modeled_liveagent_agents, modeled_liveagent_contacts, and
modeled_liveagent_departments.

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

Median from the first outbound agent message per ticket.

WITH first_outbound AS (
  SELECT
    m.ticket_id,
    MIN(m.date_created) AS first_reply_at
  FROM messages m
  WHERE m.type = 'A'        -- agent message
    AND m.is_note = false
  GROUP BY m.ticket_id
)
SELECT
  date_trunc('week', t.date_created) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (f.first_reply_at - t.date_created)) / 60.0
  ) AS median_first_reply_min
FROM tickets t
JOIN first_outbound f ON f.ticket_id = t.id
GROUP BY 1
ORDER BY 1;

Multi-channel mix over the last 30 days.

SELECT
  t.channel,
  COUNT(*)           AS tickets
FROM tickets t
WHERE t.date_created >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY t.channel
ORDER BY tickets DESC;

Where open tickets are piling up right now.

SELECT
  d.name             AS department,
  COUNT(*)           AS open_tickets
FROM tickets t
JOIN departments d ON d.id = t.department_id
WHERE t.status <> 'R'
GROUP BY d.name
ORDER BY open_tickets DESC;