How to build BigCommerce dashboards in Metabase

{
  "mcpServers": {
    "bigcommerce": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://mcp.pipedream.net/v2"
      ]
    }
  }
}

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

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

Create a polished Metabase dashboard for BigCommerce 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,
catalog performance, and discounts/refunds from BigCommerce data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for BigCommerce tables and
  models). If durable BigCommerce 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 BigCommerce MCP server in read-only mode:
  orders, order products (line items), products, customers, and (if present) sales
  channels and inventory. 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:
BigCommerce CSV exports are usually flat and pre-aggregated (one row per order or
product, with columns like order total, refunded amount, units, and net revenue).
Warehouse tables are raw and order-grained (join orders to order_products for
line-item detail, filter by status_id for paid orders). 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
  BigCommerce — it reads a database or CLI-uploaded tables.
- 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.
- Count only completed/awaiting-fulfillment orders, not incomplete or cancelled.
- 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: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: BigCommerce 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. Catalog performance: Top products by net revenue; Units by SKU; Refund rate by
   product.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per customer;
   Revenue by acquisition month; Customer group mix.
5. Channels & margin: Revenue by sales channel; Discount depth; Refunds by week.

Filters: Date range, Sales channel, Product category, Customer group, Coupon.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_bc_orders, modeled_bc_order_products,
modeled_bc_products, and modeled_bc_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.

Order totals net of refunds over the last 30 days.

SELECT
  date_trunc('day', o.date_created)                   AS day,
  COUNT(*)                                            AS orders,
  SUM(o.total_inc_tax - COALESCE(o.refunded_amount, 0)) AS net_revenue
FROM orders o
WHERE o.status_id IN (10, 11)          -- Completed, Awaiting Fulfillment
  AND o.date_created >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

AOV computed at the order grain, on paid orders.

SELECT
  date_trunc('week', o.date_created) AS week,
  COUNT(*)                           AS orders,
  ROUND(
    SUM(o.total_inc_tax) / NULLIF(COUNT(*), 0),
  2)                                 AS aov
FROM orders o
WHERE o.status_id IN (10, 11)
GROUP BY 1
ORDER BY 1;

Units and gross revenue by product over 90 days.

SELECT
  op.name                          AS product,
  SUM(op.quantity)                 AS units,
  SUM(op.total_inc_tax)            AS gross_revenue
FROM order_products op
JOIN orders o ON o.id = op.order_id
WHERE o.status_id IN (10, 11)
  AND o.date_created >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY op.name
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 orders
  WHERE status_id IN (10, 11)
    AND customer_id IS NOT NULL
    AND customer_id > 0
  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;