How to build Gorgias support dashboards in Metabase

{
  "mcpServers": {
    "gorgias": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://mcp.gorgias.com/mcp?gorgias_subdomain=YOUR-SUBDOMAIN"
      ]
    },
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

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

Goal: Help ecommerce CX leaders understand volume, responsiveness, support-driven
revenue, CSAT, and contact drivers from Gorgias data.

First, inspect the schema and identify the available Gorgias tables. Do not assume
exact table names. Map the available raw tables into these analytical concepts
where possible: Tickets, Messages, Customers, Users (agents), Tags, Satisfaction
surveys, and any linked order/revenue data (e.g. Shopify) if present.

Important:
- Treat MCP data access as exploratory only.
- Build the dashboard from durable database/warehouse 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.
- Only build support-driven revenue cards if linked order data is actually present.
- If ticket event history is missing, do not calculate reopen rate or
  time-in-status. Use a caveat instead.
- Do not claim Metabase connects natively to Gorgias unless that is explicitly
  true in this environment.

Dashboard title: Gorgias Support Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Closed last 7 days;
   Open backlog; Median first response time; CSAT % (only if survey data exists);
   Support-influenced revenue (only if order data exists).
2. Volume & backlog: Created vs closed by day; Open by status; Backlog aging;
   Volume by channel.
3. Support-driven revenue: Tickets linked to orders; Top return drivers by tag;
   Conversion after support (only if order data exists).
4. Response time: First response p50/p90 by week; Volume by hour; Reopened by week.
5. CSAT & drivers: CSAT by week; Bad ratings by tag; Volume by tag; Repeat
   contacts.

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

Before finalizing, create or recommend reusable Metabase models:
modeled_gorgias_tickets, modeled_gorgias_messages, modeled_gorgias_customers,
modeled_gorgias_users, and modeled_gorgias_satisfaction.

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.

SELECT
  date_trunc('day', t.created_datetime) AS day,
  COUNT(*)                                          AS created,
  COUNT(*) FILTER (WHERE t.status = 'closed')       AS closed
FROM tickets t
WHERE t.created_datetime >= 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.created_datetime) AS first_reply_at
  FROM messages m
  WHERE m.from_agent = true
    AND m.is_internal_note = false
  GROUP BY m.ticket_id
)
SELECT
  date_trunc('week', t.created_datetime) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (f.first_reply_at - t.created_datetime)) / 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;

Top contact drivers — returns, shipping, product — over 90 days.

SELECT
  tg.name            AS tag,
  COUNT(*)           AS tickets
FROM tickets t
JOIN ticket_tags tt ON tt.ticket_id = t.id
JOIN tags tg ON tg.id = tt.tag_id
WHERE t.created_datetime >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY tg.name
ORDER BY tickets DESC
LIMIT 20;

Customers who wrote in three or more times in the last 90 days.

SELECT
  c.email,
  COUNT(*)           AS ticket_count
FROM tickets t
JOIN customers c ON c.id = t.customer_id
WHERE t.created_datetime >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY c.email
HAVING COUNT(*) >= 3
ORDER BY ticket_count DESC
LIMIT 50;