How to build QuickBooks dashboards in Metabase

# QuickBooks Online — Intuit's official server runs locally (stdio)
# Clone and run per the repo, then register it with your client:
claude mcp add quickbooks -- node /path/to/quickbooks-online-mcp-server/dist/index.js

{
  "mcpServers": {
    "quickbooks": {
      "command": "npx",
      "args": ["-y", "qbo-mcp-ts"],
      "env": {
        "QBO_CLIENT_ID": "YOUR_INTUIT_CLIENT_ID",
        "QBO_CLIENT_SECRET": "YOUR_INTUIT_CLIENT_SECRET",
        "QBO_COMPANY_ID": "YOUR_REALM_ID",
        "QBO_REFRESH_TOKEN": "YOUR_REFRESH_TOKEN",
        "QBO_ENVIRONMENT": "production"
      }
    }
  }
}

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

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

Create a polished Metabase dashboard for QuickBooks Online financial 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 invoiced revenue, accounts
receivable, cash flow, and profitability from QuickBooks Online data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for QuickBooks tables and
  models). If durable QuickBooks 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 QuickBooks Online MCP server using a
  read-only connection: customers, invoices, invoice line items, payments, items,
  and bills. 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: Customers, Invoices, Invoice line items,
Payments, Items (products/services), Bills, Vendors, Expenses/Purchases, and the
Chart of Accounts. Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  QuickBooks — it reads a database or CLI-uploaded tables.
- QuickBooks amounts are decimals in the major currency unit — do not divide by 100.
- Recognize revenue from invoices (or from the accounting method the business uses);
  do not double-count payments applied to invoices as extra revenue.
- Compute AR as unpaid invoice balances as of a date, and DSO from AR and revenue.
- Report in the company's home currency; if multi-currency, convert with a
  documented rate or caveat the mix.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: trends and AR-over-time need history,
  so build trend cards only if a warehouse sync or multiple uploads provide it.

Dashboard title: QuickBooks Revenue & AR Overview

Sections:
1. Executive summary (KPI cards): Invoiced revenue this month; AR balance; DSO;
   Overdue AR; Payments received this month; Gross margin (only if cost data exists).
2. Revenue & sales: Invoiced revenue by month; Revenue by product/service; Revenue
   by customer; Average invoice value.
3. AR & collections: AR aging buckets; DSO by month; Overdue invoices by customer;
   Collection rate.
4. Cash flow: Payments received vs. bills paid by month; Net cash movement;
   Expenses by category; Upcoming bills.
5. Profitability: Gross margin by month; Profit by product/service; Top customers
   by revenue and margin.

Filters: Customer, Product/service, Account, Class/Location (if used), Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_qbo_customers, modeled_qbo_invoices,
modeled_qbo_invoice_lines, modeled_qbo_payments, and modeled_qbo_ar_aging (an
as-of AR balance model).

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 QuickBooks'
P&L and AR aging reports. Keep it practical, dense, and executive-readable. Avoid
vanity metrics.

Invoice totals, counts, and average value over the last year.

SELECT
  date_trunc('month', i.txn_date) AS month,
  ROUND(SUM(i.total_amt), 2)      AS invoiced_revenue,
  COUNT(*)                         AS invoices,
  ROUND(AVG(i.total_amt), 2)       AS avg_invoice_value
FROM invoices i
WHERE i.txn_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Open invoice balances bucketed by days past due — the collections worklist.

SELECT
  CASE
    WHEN CURRENT_DATE - i.due_date <= 0  THEN 'Current'
    WHEN CURRENT_DATE - i.due_date <= 30 THEN '1-30'
    WHEN CURRENT_DATE - i.due_date <= 60 THEN '31-60'
    WHEN CURRENT_DATE - i.due_date <= 90 THEN '61-90'
    ELSE '90+'
  END                          AS aging_bucket,
  COUNT(*)                     AS open_invoices,
  ROUND(SUM(i.balance), 2)     AS ar_outstanding
FROM invoices i
WHERE i.balance > 0
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - i.due_date);

Revenue and open balance by customer, year to date.

SELECT
  c.display_name             AS customer,
  ROUND(SUM(i.total_amt), 2) AS revenue,
  ROUND(SUM(i.balance), 2)   AS open_balance
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.txn_date >= date_trunc('year', CURRENT_DATE)
GROUP BY c.display_name
ORDER BY revenue DESC
LIMIT 20;