How to build Square sales dashboards in Metabase

# Square (remote, OAuth in browser)
claude mcp add square -- npx -y mcp-remote https://mcp.squareup.com/sse

{
  "mcpServers": {
    "square": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://mcp.squareup.com/sse"]
    }
  }
}

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

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

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

Goal: Help founders and finance leaders understand gross and net sales,
products, refunds, disputes, fees, and payouts from Square data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Square tables and
  models). If durable Square 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 Square MCP server using read-only scopes:
  payments, orders, order line items, catalog items, customers, refunds, disputes,
  locations, and payouts. 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:
Do not assume exact table names. Map the available raw tables into these
analytical concepts where possible: Payments, Orders, Order line items, Catalog
items, Customers, Refunds, Disputes, Locations, and Payouts. Inspect the actual
tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Square — it reads a database or CLI-uploaded tables.
- Separate gross sales from net (after Square processing fees and refunds).
- Compute payment success rate on a consistent denominator.
- Handle multi-location data: allow filtering and comparison by location.
- Keep tips and taxes separate from product revenue.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: sales and refund trends need history,
  so build trend cards only if a warehouse sync or multiple uploads provide it.

Dashboard title: Square Sales Overview

Sections:
1. Executive summary (KPI cards): Gross sales; Net sales; Fees %; Refund rate;
   Average order value; Payments success rate.
2. Sales & net revenue: Gross sales by day; Gross vs. net; Sales by location.
3. Products & catalog: Top items/categories; Units sold; Sales by hour/day.
4. Refunds & disputes: Refund rate; Disputes by month; Refund reasons.
5. Fees & payouts: Fees as a share of sales; Payouts and settlement timing; Net
   settlement.

Filters: Location, Item/category, Payment method, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_square_payments, modeled_square_orders, and
modeled_square_refunds.

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. Reconcile totals against
Square Dashboard. Keep it practical, dense, and executive-readable. Avoid vanity
metrics.

Gross sales and what lands after Square processing fees.

SELECT
  date_trunc('day', created_at)                       AS day,
  COUNT(*)                                            AS payments,
  ROUND(SUM(amount_money) / 100.0, 2)                 AS gross_sales,
  ROUND(SUM(processing_fee) / 100.0, 2)               AS fees,
  ROUND(SUM(amount_money - processing_fee) / 100.0, 2) AS net_sales
FROM payments
WHERE status = 'COMPLETED'
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Compare stores and registers on volume and average order value.

SELECT
  l.name                                      AS location,
  COUNT(*)                                    AS orders,
  ROUND(SUM(p.amount_money) / 100.0, 2)       AS gross_sales,
  ROUND(AVG(p.amount_money) / 100.0, 2)       AS avg_order_value
FROM payments p
JOIN locations l ON l.id = p.location_id
WHERE p.status = 'COMPLETED'
GROUP BY l.name
ORDER BY gross_sales DESC;

Refunded payments and the amount returned.

SELECT
  date_trunc('month', created_at)                     AS month,
  COUNT(*) FILTER (WHERE status = 'COMPLETED')        AS payments,
  COUNT(*) FILTER (WHERE refunded_amount > 0)         AS refunded_payments,
  ROUND(SUM(refunded_amount) / 100.0, 2)              AS refunded_amount
FROM payments
GROUP BY 1
ORDER BY 1;