How to build WooCommerce dashboards in Metabase

{
  "mcpServers": {
    "woocommerce": {
      "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 WooCommerce CSV export — creates a table AND a model
mb upload csv --file woocommerce-orders.csv --collection root

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

Create a polished Metabase dashboard for WooCommerce 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 WooCommerce data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (the connected WooCommerce store
  database, or WooCommerce tables and models uploaded earlier). If durable
  WooCommerce data is already present, use it and skip to Step 2.
- If nothing is there, pull it with a WooCommerce MCP server in read-only mode:
  recent orders, products, customers, and coupon usage. 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:
WooCommerce CSV exports are usually flat and pre-aggregated (one row per order or
product). The WooCommerce database is raw: identify how orders are stored — the
legacy schema (orders in wp_posts with post_type 'shop_order' and fields in
wp_postmeta) or High-Performance Order Storage (HPOS) with dedicated tables such as
wp_wc_orders and wp_wc_order_stats. Prefer the WooCommerce Analytics lookup tables
when present: wp_wc_order_stats, wp_wc_order_product_lookup, and
wp_wc_customer_lookup. Inspect the actual tables and column names first; do not
assume exact names or the table prefix.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  WooCommerce — it reads the underlying SQL database or CLI-uploaded tables.
- Build durable dashboards from database tables (a read replica if live).
- Report NET revenue: subtract refunds and discounts; state the definition.
- Count only paid/processing/completed orders, not pending, failed, or trash.
- 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: WooCommerce 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 product; Refund rate
   by product.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per customer;
   Revenue by acquisition month.
5. Discounts & operations: Coupon/discount depth; Refunds by week; Order status
   funnel.

Filters: Date range, Product category, Coupon, Payment method, Customer type.

Reuse the models Metabase auto-created from uploaded CSVs, or (for the database)
create reusable models: modeled_woo_orders, modeled_woo_order_items,
modeled_woo_products, and modeled_woo_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.

Directly from the High-Performance Order Storage orders table.

-- High-Performance Order Storage (HPOS) schema
SELECT
  date_trunc('day', o.date_created_gmt)               AS day,
  COUNT(*)                                            AS orders,
  SUM(o.total_amount)                                 AS gross,
  SUM(COALESCE(o.discount_total_amount, 0))           AS discounts
FROM wp_wc_orders o
WHERE o.type = 'shop_order'
  AND o.status IN ('wc-processing', 'wc-completed')
  AND o.date_created_gmt >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Using the WooCommerce reporting lookup tables — the friendliest source.

-- WooCommerce Analytics lookup tables (recommended)
SELECT
  date_trunc('week', s.date_created) AS week,
  COUNT(DISTINCT s.order_id)         AS orders,
  ROUND(
    SUM(s.net_total) / NULLIF(COUNT(DISTINCT s.order_id), 0),
  2)                                 AS aov
FROM wp_wc_order_stats s
WHERE s.status IN ('wc-processing', 'wc-completed')
GROUP BY 1
ORDER BY 1;

Units and net revenue by product over 90 days.

SELECT
  p.post_title                       AS product,
  SUM(pl.product_qty)                AS units,
  SUM(pl.product_net_revenue)        AS net_revenue
FROM wp_wc_order_product_lookup pl
JOIN wp_wc_order_stats s ON s.order_id = pl.order_id
JOIN wp_posts p          ON p.ID = pl.product_id
WHERE s.status IN ('wc-processing', 'wc-completed')
  AND s.date_created >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.post_title
ORDER BY net_revenue DESC
LIMIT 20;

Share of customers with two or more paid orders.

WITH customer_orders AS (
  SELECT
    customer_id,
    COUNT(DISTINCT order_id) AS paid_orders
  FROM wp_wc_order_stats
  WHERE status IN ('wc-processing', 'wc-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;