How to build Zuora revenue dashboards in Metabase

{
  "mcpServers": {
    "zuora": {
      "url": "https://<your-zuora-mcp-endpoint>",
      "headers": {
        "Authorization": "Bearer <ZUORA_OAUTH_TOKEN>"
      }
    }
  }
}

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

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

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

Goal: Help finance and RevOps leaders understand recurring revenue (MRR/ARR),
churn, billings, accounts receivable, and revenue recognition from Zuora data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Zuora tables and
  models). If durable Zuora 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 Zuora MCP server using a read-only OAuth
  token: accounts, subscriptions, rate plans, rate plan charges, products,
  invoices, invoice items, payments, and (if present) revenue schedules. 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: Accounts, Subscriptions, Rate plans, Rate
plan charges, Products, Invoices, Invoice items, Payments, and (if present)
Revenue schedules. Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Zuora — it reads a database or CLI-uploaded tables.
- Compute MRR from active rate plan charges, normalizing every charge to a
  monthly amount (annual / 12, quarterly / 3, etc.).
- Separate one-time and usage charges from recurring charges so they don't
  inflate MRR.
- For revenue recognition, use recognized/deferred revenue schedules, not
  billings.
- Report in a single reporting currency; Zuora is multi-currency, so convert or
  split by currency.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: MRR movement and recognition need
  history, so build trend cards only if a warehouse sync or multiple uploads
  provide it.

Dashboard title: Zuora Revenue Overview

Sections:
1. Executive summary (KPI cards): MRR; ARR; Net new MRR; Gross revenue churn %;
   Net revenue retention; Accounts receivable.
2. MRR & ARR: MRR movement by month; ARR by product/rate plan; New vs. renewal
   bookings.
3. Churn & retention: Logo vs. revenue churn; Gross vs. net retention; Renewal
   rate by cohort.
4. Billings & AR: Invoiced revenue; AR aging; Failed payments and dunning.
5. Revenue recognition: Recognized vs. deferred revenue; Billings vs.
   recognized.

Filters: Product, Rate plan, Currency, Account segment, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_zuora_subscriptions, modeled_zuora_charges,
modeled_zuora_invoices, and modeled_zuora_mrr (a monthly per-charge 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
Zuora. Keep it practical, dense, and executive-readable. Avoid vanity metrics.

Sum a monthly MRR model built from active rate plan charges.

-- Requires a monthly MRR model built from active Zuora rate plan charges
SELECT
  ROUND(SUM(mrr), 2)       AS mrr,
  ROUND(SUM(mrr) * 12, 2)  AS arr,
  COUNT(DISTINCT subscription_id) AS active_subscriptions
FROM modeled_zuora_mrr
WHERE month = date_trunc('month', CURRENT_DATE);

Cancellations against subscriptions active at each month's start.

-- Cancellations against subscriptions active at each month's start
WITH months AS (
  SELECT generate_series(
    date_trunc('month', CURRENT_DATE - INTERVAL '11 months'),
    date_trunc('month', CURRENT_DATE),
    INTERVAL '1 month'
  ) AS month
)
SELECT
  m.month,
  COUNT(*) FILTER (
    WHERE date_trunc('month', s.cancelled_date) = m.month
  ) AS churned_subscriptions,
  COUNT(*) FILTER (
    WHERE s.subscription_start_date <= m.month
      AND (s.cancelled_date IS NULL OR s.cancelled_date > m.month)
  ) AS active_at_month_start
FROM months m
CROSS JOIN subscriptions s
GROUP BY m.month
ORDER BY m.month;

Open invoice balances bucketed by days past due.

-- AR aging from open invoices
SELECT
  CASE
    WHEN CURRENT_DATE - due_date <= 0  THEN 'current'
    WHEN CURRENT_DATE - due_date <= 30 THEN '1-30'
    WHEN CURRENT_DATE - due_date <= 60 THEN '31-60'
    ELSE '60+'
  END                                  AS aging_bucket,
  COUNT(*)                             AS invoices,
  ROUND(SUM(balance), 2)               AS open_balance
FROM invoices
WHERE status = 'Posted' AND balance > 0
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - due_date);