How to build Lago billing dashboards in Metabase

# Lago (local agent toolkit — build/run per the repo README)
claude mcp add lago \
  --env LAGO_API_KEY=<YOUR_LAGO_API_KEY> \
  --env LAGO_API_URL=https://api.getlago.com \
  -- <command-from-lago-agent-toolkit-readme>

{
  "mcpServers": {
    "lago": {
      "command": "<command-from-lago-agent-toolkit-readme>",
      "env": {
        "LAGO_API_KEY": "<YOUR_LAGO_API_KEY>",
        "LAGO_API_URL": "https://api.getlago.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 Lago CSV export — creates a table AND a model
mb upload csv --file lago-invoices.csv --collection root

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

Create a polished Metabase dashboard for Lago usage-based billing analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help finance and product leaders understand usage-based revenue, recurring
MRR, invoices, and prepaid credit (wallet) consumption from Lago data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Lago tables and
  models). If durable Lago data is already present — synced from a database or
  uploaded earlier — use it and skip to Step 2.
- If nothing is there, pull it with Lago's agent-toolkit MCP server using a
  read-only API key: customers, subscriptions, plans, billable metrics, events,
  invoices, fees, and wallets. 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, Subscriptions, Plans, Billable
metrics, Events (usage), Invoices, Fees, Wallets/Credits, and Coupons. Inspect
the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Lago — it reads a database or CLI-uploaded tables.
- Separate recurring subscription revenue (MRR) from usage-based (metered)
  revenue; document the rating logic.
- Report usage against included allowances so overage is visible.
- Track wallet (prepaid credit) balances and consumption separately.
- Report in a single reporting currency.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: usage trends and MRR movement need
  history, so build trend cards only if a database sync or multiple uploads
  provide it.

Dashboard title: Lago Billing Overview

Sections:
1. Executive summary (KPI cards): MRR; Usage revenue this month; Invoiced
   revenue; Outstanding wallet balance; Net new MRR; Overage revenue.
2. Usage & billable metrics: Usage by metric over time; Usage revenue by metric;
   Overage vs. included.
3. Recurring & total revenue: MRR from recurring charges; Recurring vs. usage
   split; Revenue by plan.
4. Invoices & fees: Invoiced revenue; Fees by type; AR.
5. Wallets & credits: Wallet balances; Top-ups vs. consumption; Burn rate.

Filters: Plan, Billable metric, Customer, Currency, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a database)
create reusable models: modeled_lago_usage, modeled_lago_invoices,
modeled_lago_wallets, and modeled_lago_mrr (a monthly recurring-MRR 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
Lago. Keep it practical, dense, and executive-readable. Avoid vanity metrics.

Rated metered consumption, grouped by metric.

-- Metered usage revenue by billable metric
SELECT
  date_trunc('month', f.created_at) AS month,
  bm.name                           AS billable_metric,
  ROUND(SUM(f.amount_cents) / 100.0, 2) AS usage_revenue
FROM fees f
JOIN charges c          ON c.id = f.charge_id
JOIN billable_metrics bm ON bm.id = c.billable_metric_id
WHERE f.fee_type = 'charge'
GROUP BY 1, bm.name
ORDER BY 1, usage_revenue DESC;

From subscription fees, excluding usage charges.

-- Recurring MRR from subscription fees (excludes usage charges)
SELECT
  ROUND(SUM(mrr), 2)       AS mrr,
  ROUND(SUM(mrr) * 12, 2)  AS arr,
  COUNT(*)                 AS active_subscriptions
FROM modeled_lago_mrr
WHERE month = date_trunc('month', CURRENT_DATE);

Prepaid credit outstanding and how much has been used.

-- Prepaid credit balance and consumption
SELECT
  w.customer_id,
  ROUND(w.balance_cents / 100.0, 2)                       AS balance,
  ROUND(SUM(wt.credit_amount) FILTER (
    WHERE wt.transaction_type = 'outbound') / 100.0, 2)   AS consumed_credits
FROM wallets w
LEFT JOIN wallet_transactions wt ON wt.wallet_id = w.id
WHERE w.status = 'active'
GROUP BY w.customer_id, w.balance_cents
ORDER BY balance ASC;