How to build Amazon seller dashboards in Metabase

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

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

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

Goal: Help sellers understand sales, average selling price, fees and profitability,
product/ASIN performance, and FBA inventory from Amazon SP-API data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Amazon/SP-API tables and
  models). If durable Amazon 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 Amazon MCP server in read-only mode:
  orders, order items, settlements/finances (fees, reimbursements),
  products/listings (ASIN/SKU), and FBA 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:
Amazon CSV exports are usually flat and pre-aggregated (one row per ASIN or day,
with columns like units, gross sales, fees, and net proceeds). Warehouse tables are
raw and order-grained (orders, order items, finances, FBA inventory). Inspect the
actual tables and column names first; do not assume exact names. Map the available
raw tables into these analytical concepts where possible: Orders, Order items,
Settlements/finances (fees, reimbursements), Products/listings (ASIN/SKU), and FBA
inventory.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Amazon — it reads a database or CLI-uploaded tables.
- Report NET proceeds: subtract Amazon referral and FBA fees, and refunds.
- Reconcile against settlement reports, not just order totals.
- Separate marketplaces (US, EU, etc.) and currencies.
- Use order-level grain for average selling price.
- 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: Amazon Seller Overview

Sections:
1. Executive summary (KPI cards): Net proceeds last 30 days; Units; Orders; Average
   selling price; Fees as % of sales.
2. Sales & demand: Net proceeds by day; Units by day; Revenue by marketplace.
3. Product performance: Top ASINs by net proceeds; Units by ASIN; Return rate.
4. Fees & profitability: Fees % of sales; FBA vs referral fees; Net margin by ASIN.
5. Inventory: FBA on hand vs sell-through; Days of cover; Stranded inventory.

Filters: Date range, Marketplace, ASIN/SKU, Fulfillment channel (FBA/FBM).

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_amazon_orders, modeled_amazon_order_items,
modeled_amazon_finances, and modeled_amazon_inventory.

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.

Shipped-order sales and units over the last 30 days.

SELECT
  date_trunc('day', o.purchase_date)                  AS day,
  COUNT(DISTINCT o.amazon_order_id)                   AS orders,
  SUM(oi.quantity_ordered)                            AS units,
  SUM(oi.item_price - COALESCE(oi.item_promo_discount, 0)) AS gross_sales
FROM orders o
JOIN order_items oi ON oi.amazon_order_id = o.amazon_order_id
WHERE o.order_status = 'Shipped'
  AND o.purchase_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

FBA vs. referral fees from the finances/settlement data.

SELECT
  date_trunc('month', f.posted_date) AS month,
  SUM(f.amount) FILTER (WHERE f.fee_type = 'FBAPerUnitFulfillmentFee') AS fba_fees,
  SUM(f.amount) FILTER (WHERE f.fee_type = 'Commission')              AS referral_fees,
  SUM(f.amount)                                                       AS total_fees
FROM finances f
GROUP BY 1
ORDER BY 1;

Units and gross sales by ASIN over 90 days.

SELECT
  oi.asin,
  oi.title,
  SUM(oi.quantity_ordered)  AS units,
  SUM(oi.item_price)        AS gross_sales
FROM order_items oi
JOIN orders o ON o.amazon_order_id = oi.amazon_order_id
WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '90 days'
  AND o.order_status = 'Shipped'
GROUP BY oi.asin, oi.title
ORDER BY gross_sales DESC
LIMIT 20;

FBA on-hand against recent daily sell-through.

SELECT
  i.asin,
  i.fulfillable_quantity                              AS on_hand,
  s.units_30d,
  ROUND(i.fulfillable_quantity
    / NULLIF(s.units_30d / 30.0, 0), 1)               AS days_of_cover
FROM fba_inventory i
JOIN (
  SELECT oi.asin, SUM(oi.quantity_ordered) AS units_30d
  FROM order_items oi
  JOIN orders o ON o.amazon_order_id = oi.amazon_order_id
  WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY oi.asin
) s ON s.asin = i.asin
ORDER BY days_of_cover ASC NULLS LAST;