How to build Medusa dashboards in Metabase

{
  "mcpServers": {
    "medusa": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://docs.medusajs.com/mcp",
        "--header",
        "Authorization: Bearer YOUR_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 Medusa CSV export — creates a table AND a model
mb upload csv --file medusa-orders.csv --collection root

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

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

Goal: Help store operators understand sales, average order value, repeat purchase,
product performance, and fulfillment from Medusa data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase. If the Medusa Postgres database is
  already connected — or Medusa tables/models were uploaded earlier — use that data
  and skip to Step 2.
- If nothing is there, pull it with the Medusa MCP server in read-only mode: orders,
  line items, products, and customers. 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:
Medusa CSV exports are usually flat and pre-aggregated (one row per order or product,
with columns already summarized). The Medusa database is raw: identify the Medusa
version and table layout first. Medusa v2 uses module tables such as "order",
"order_line_item", "product", "product_variant", "customer", and "cart"; v1 differs.
Note that "order" is a reserved word — quote it. Some money fields are stored in
minor units (e.g. cents) — normalize them. Inspect the actual tables and column names
first; do not assume exact names.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Medusa — it reads the connected SQL database or CLI-uploaded tables.
- Query a read replica, not the production writer.
- Report NET revenue: subtract refunds and discounts; state the definition.
- Count only completed/captured orders per your definition of paid.
- Use order-level grain for AOV.
- Define a "repeat customer" once (2+ paid orders) and reuse it everywhere.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: connect the database directly or upload
  multiple periods before building trend cards.

Dashboard title: Medusa Store Overview

Sections:
1. Executive summary (KPI cards): Net revenue last 30 days; Orders; AOV; Repeat-
   purchase rate; Refund rate.
2. Sales & demand: Net revenue by day; Orders by day; AOV trend; New vs returning
   revenue.
3. Product performance: Top products by net revenue; Units by variant; Return rate.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per customer;
   Revenue by acquisition month.
5. Fulfillment & payments: Order status funnel; Payment capture; Refunds by week.

Filters: Date range, Region, Sales channel, Product, Customer type.

Reuse the models Metabase auto-created from uploaded CSVs, or (for the database)
create reusable models: modeled_medusa_orders, modeled_medusa_line_items,
modeled_medusa_products, and modeled_medusa_customers.

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.

Completed-order totals over the last 30 days.

-- Medusa v2 module tables; money often in minor units
SELECT
  date_trunc('day', o.created_at)     AS day,
  COUNT(*)                            AS orders,
  SUM(o.total) / 100.0                AS net_revenue
FROM "order" o
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

AOV computed at the order grain, on completed orders.

SELECT
  date_trunc('week', o.created_at) AS week,
  COUNT(*)                         AS orders,
  ROUND((SUM(o.total) / 100.0) / NULLIF(COUNT(*), 0), 2) AS aov
FROM "order" o
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY 1;

Units and gross revenue by product over 90 days.

SELECT
  p.title                          AS product,
  SUM(li.quantity)                 AS units,
  SUM(li.quantity * li.unit_price) / 100.0 AS gross_revenue
FROM order_line_item li
JOIN "order" o          ON o.id = li.order_id
JOIN product_variant v  ON v.id = li.variant_id
JOIN product p          ON p.id = v.product_id
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.title
ORDER BY gross_revenue DESC
LIMIT 20;

Share of customers with two or more paid orders.

WITH customer_orders AS (
  SELECT customer_id, COUNT(*) AS paid_orders
  FROM "order"
  WHERE status = 'completed'
    AND customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT
  ROUND(100.0 * COUNT(*) FILTER (WHERE paid_orders >= 2)
    / NULLIF(COUNT(*), 0), 2) AS repeat_purchase_rate_pct
FROM customer_orders;