How to build Shopify dashboards in Metabase

{
  "mcpServers": {
    "shopify-dev": {
      "command": "npx",
      "args": ["-y", "@shopify/dev-mcp@latest"]
    }
  }
}

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

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

Create a polished Metabase dashboard for Shopify 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 discounts/refunds from Shopify data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Shopify tables and
  models). If durable Shopify 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 Shopify MCP server in read-only mode:
  orders, order line items, products, customers, and refunds (and abandoned
  checkouts if available). 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:
Shopify CSV exports are usually flat and pre-aggregated (one row per order, with
columns like total price, discounts, and financial status). Warehouse tables are
raw and order-grained (orders, order_line_items, products, customers, refunds).
Inspect the actual tables and column names first; do not assume exact names or that
a given table (e.g. abandoned checkouts) exists.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Shopify — it reads a database or CLI-uploaded tables.
- 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.
- Report NET revenue: subtract refunds, discounts, and (per your policy) taxes and
  shipping. State the definition on the dashboard.
- Use order-level grain for AOV; do not average line-item prices.
- Only build cart-abandonment cards if abandoned-checkout data is actually present.
- Define a "repeat customer" once (2+ paid orders) and reuse it everywhere.

Dashboard title: Shopify Store Overview

Sections:
1. Executive summary (KPI cards): Net revenue last 30 days; Orders; AOV; Repeat-
   purchase rate; Refund rate; Cart abandonment (only if checkout data exists).
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; Refund rate
   by product.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per customer;
   Revenue by acquisition month.
5. Discounts & margin: Discount depth over time; Refunds by week.

Filters: Date range, Sales channel, Product type, Discount code, Customer type.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_shopify_orders, modeled_shopify_line_items,
modeled_shopify_products, modeled_shopify_customers, and modeled_shopify_refunds.

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.

Gross-after-discount and refunds by day over the last 30 days.

SELECT
  date_trunc('day', o.created_at)                     AS day,
  COUNT(*)                                            AS orders,
  SUM(o.total_price - COALESCE(o.total_discounts, 0)) AS gross_after_discount,
  SUM(COALESCE(r.refunded_amount, 0))                 AS refunds
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(amount) AS refunded_amount
  FROM refunds
  GROUP BY order_id
) r ON r.order_id = o.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND o.financial_status IN ('paid', 'partially_refunded')
GROUP BY 1
ORDER BY 1;

AOV computed at the order grain, on paid orders.

SELECT
  date_trunc('week', o.created_at) AS week,
  COUNT(*)                         AS orders,
  ROUND(
    SUM(o.total_price - COALESCE(o.total_discounts, 0)) / NULLIF(COUNT(*), 0),
  2)                               AS aov
FROM orders o
WHERE o.financial_status IN ('paid', 'partially_refunded')
GROUP BY 1
ORDER BY 1;

Share of customers with two or more paid orders.

WITH customer_orders AS (
  SELECT
    customer_id,
    COUNT(*) AS paid_orders
  FROM orders
  WHERE financial_status IN ('paid', 'partially_refunded')
    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;

Units and gross revenue by product over 90 days.

SELECT
  p.title                        AS product,
  SUM(li.quantity)               AS units,
  SUM(li.quantity * li.price)    AS gross_revenue
FROM order_line_items li
JOIN orders o   ON o.id = li.order_id
JOIN products p ON p.id = li.product_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
  AND o.financial_status IN ('paid', 'partially_refunded')
GROUP BY p.title
ORDER BY gross_revenue DESC
LIMIT 20;