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

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

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

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

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

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Gorgias tables and
  models). If durable Gorgias 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 Gorgias MCP server (keep it read-only):
  tickets, messages, customers, users (agents), tags, and satisfaction surveys.
  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, Customers, Users (agents),
Tags, Satisfaction surveys, and any linked order/revenue data (e.g. Shopify) if
present. Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Gorgias — 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.
- 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.
- 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: 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.

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