How to build VTEX dashboards in Metabase

{
  "mcpServers": {
    "vtex-dev": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://developers.vtex.com/mcp"]
    }
  }
}

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

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

Create a polished Metabase dashboard for VTEX 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 order flow from VTEX data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for VTEX tables and
  models). If durable VTEX 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 VTEX MCP server in read-only mode:
  orders (OMS), order items, products/SKUs, clients, sales channels, and sellers.
  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:
VTEX CSV exports are often flat and pre-aggregated (one row per order or SKU, with
columns like order value, units, and status). Warehouse tables are raw and
order-grained (join orders to order items and filter by order status). Inspect the
actual tables and column names first; do not assume exact names or that a
particular table exists. Map what you find into these analytical concepts where
possible: Orders (OMS), Order items, Products/SKUs, Clients (customers), Sales
channels, and Sellers (for marketplace).

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  VTEX — it reads a database or CLI-uploaded tables.
- Report NET revenue and GMV consistently; state the definitions.
- Use VTEX order status to define which orders count (e.g. invoiced).
- Separate marketplace/seller orders from own-inventory orders where relevant.
- 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: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: VTEX Store Overview

Sections:
1. Executive summary (KPI cards): GMV last 30 days; Orders; AOV; Repeat-purchase
   rate; Cancellation rate.
2. Sales & channels: GMV by day; Orders by day; AOV trend; Revenue by channel/seller.
3. Catalog performance: Top SKUs by net revenue; Units by SKU; Revenue by category.
4. Customers & retention: Repeat-purchase rate by cohort; Orders per client;
   Revenue by region.
5. Order flow: Status funnel; Cancellation rate; Payment method mix.

Filters: Date range, Sales channel, Seller, Category, Region.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_vtex_orders, modeled_vtex_order_items,
modeled_vtex_skus, and modeled_vtex_clients.

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.

Invoiced-order value over the last 30 days.

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

AOV computed at the order grain, on invoiced orders.

SELECT
  date_trunc('week', o.creation_date) AS week,
  COUNT(*)                            AS orders,
  ROUND((SUM(o.value) / 100.0) / NULLIF(COUNT(*), 0), 2) AS aov
FROM orders o
WHERE o.status = 'invoiced'
GROUP BY 1
ORDER BY 1;

Units and gross revenue by product over 90 days.

SELECT
  i.name                            AS product,
  SUM(i.quantity)                   AS units,
  SUM(i.selling_price * i.quantity) / 100.0 AS gross_revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'invoiced'
  AND o.creation_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY i.name
ORDER BY gross_revenue DESC
LIMIT 20;

Share of clients with two or more paid orders.

WITH client_orders AS (
  SELECT client_email, COUNT(*) AS paid_orders
  FROM orders
  WHERE status = 'invoiced'
    AND client_email IS NOT NULL
  GROUP BY client_email
)
SELECT
  ROUND(100.0 * COUNT(*) FILTER (WHERE paid_orders >= 2)
    / NULLIF(COUNT(*), 0), 2) AS repeat_purchase_rate_pct
FROM client_orders;