How to build commercetools dashboards in Metabase

{
  "mcpServers": {
    "commercetools": {
      "command": "npx",
      "args": ["-y", "@commercetools/mcp-essentials"],
      "env": {
        "CLIENT_ID": "YOUR_CLIENT_ID",
        "CLIENT_SECRET": "YOUR_CLIENT_SECRET",
        "PROJECT_KEY": "YOUR_PROJECT_KEY",
        "AUTH_URL": "https://auth.<region>.commercetools.com",
        "API_URL": "https://api.<region>.commercetools.com"
      }
    }
  }
}

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

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

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

Goal: Help commerce leaders understand GMV, average order value, repeat purchase,
catalog performance, and orders/payments from commercetools data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for commercetools tables
  and models). If durable commercetools 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 a commercetools MCP server in read-only mode:
  orders, line items, carts, products (product projections), customers, customer
  groups, and categories. 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:
commercetools CSV exports are usually flat (one row per order, line item, or
customer). Warehouse tables are raw and often carry nested JSON (line items,
money, custom fields) that must be flattened. Either way, money is stored as
centAmount with a currencyCode and fractionDigits — normalize it. Inspect the
actual tables and column names first; do not assume exact names. Map what you
find into these analytical concepts where possible: Orders, Line items, Carts,
Products (product projections), Product variants, Customers, Customer groups, and
Categories.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  commercetools — it reads a database or CLI-uploaded tables.
- Normalize centAmount using fractionDigits and keep currencyCode.
- Report NET revenue and GMV consistently; state the definitions.
- Count only completed/confirmed orders per your definition of paid.
- Use order-level grain for AOV.
- Handle multi-currency: convert to a reporting currency or split by currency.
- 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: commercetools Store Overview

Sections:
1. Executive summary (KPI cards): GMV last 30 days; Orders; AOV; Repeat-purchase
   rate; Discount depth.
2. Sales & channels: GMV by day; Orders by day; AOV trend; Revenue by store/channel.
3. Catalog performance: Top products by net revenue; Units by variant; Revenue by
   category.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per customer;
   Revenue by customer group.
5. Orders & payments: Order state funnel; Payment state mix; Discount depth.

Filters: Date range, Store/channel, Category, Customer group, Currency.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_ct_orders, modeled_ct_line_items,
modeled_ct_products, and modeled_ct_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.

Confirmed-order value over the last 30 days.

SELECT
  date_trunc('day', o.created_at)                       AS day,
  COUNT(*)                                              AS orders,
  SUM(o.total_price_cent_amount) / 100.0                AS gmv
FROM orders o
WHERE o.order_state = 'Confirmed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

AOV at the order grain, split by currency to stay honest.

SELECT
  date_trunc('week', o.created_at) AS week,
  o.currency_code                  AS currency,
  COUNT(*)                         AS orders,
  ROUND((SUM(o.total_price_cent_amount) / 100.0) / NULLIF(COUNT(*), 0), 2) AS aov
FROM orders o
WHERE o.order_state = 'Confirmed'
GROUP BY 1, 2
ORDER BY 1;

Units and gross revenue by product over 90 days.

SELECT
  li.name_en                        AS product,
  SUM(li.quantity)                  AS units,
  SUM(li.total_price_cent_amount) / 100.0 AS gross_revenue
FROM order_line_items li
JOIN orders o ON o.id = li.order_id
WHERE o.order_state = 'Confirmed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY li.name_en
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 order_state = 'Confirmed'
    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;