How to build Chargebee revenue dashboards in Metabase

{
  "mcpServers": {
    "chargebee": {
      "command": "npx",
      "args": [
        "-y", "mcp-remote",
        "https://YOUR-SITE.mcp.chargebee.com/data_lookup_agent",
        "--header", "Authorization:Bearer YOUR_CHARGEBEE_API_KEY"
      ]
    }
  }
}

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

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

Create a polished Metabase dashboard for Chargebee revenue 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 recurring revenue, churn,
retention, failed payments, and cohort economics from Chargebee data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Chargebee tables and
  models). If durable Chargebee 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 Chargebee MCP server using a read-only
  API key: customers, subscriptions, plans/item prices, invoices, and
  transactions. 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, Subscription items,
Plans/Item prices, Invoices, Invoice line items, Transactions, Credit notes, and
Payment sources. Inspect the actual tables and column names first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Chargebee — it reads a database or CLI-uploaded tables.
- Compute MRR from active subscriptions, normalizing every plan to a monthly
  amount (divide annual by 12, etc.) and converting amounts from the smallest
  currency unit where applicable.
- Report revenue in a single reporting currency; if multiple currencies exist,
  convert with a documented rate or caveat the mix.
- Separate voluntary churn from involuntary (failed-payment) churn.
- Exclude one-time charges, add-on non-recurring lines, and taxes from MRR unless
  explicitly asked.
- 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 cohorts need history,
  so build trend cards only if a warehouse sync or multiple uploads provide it.

Dashboard title: Chargebee Revenue Overview

Sections:
1. Executive summary (KPI cards): MRR; ARR; Active subscriptions; Net new MRR this
   month; Gross revenue churn %; Net revenue retention (only if MRR-movement data
   can be derived).
2. MRR movement: New, expansion, contraction, and churned MRR by month.
3. Churn & retention: Customer vs. revenue churn by month; Gross vs. net retention;
   Cancellations by reason; Trial-to-paid conversion.
4. Failed payments & dunning: Failed transactions and $ at risk; Dunning recovery
   rate; Declines by gateway/error; Subscriptions in dunning by age.
5. Cohorts & LTV: Revenue retention by signup-month cohort; Cumulative LTV by
   cohort; ARPU by plan.

Filters: Plan/Item price, Billing period, Currency, Customer segment, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_chargebee_customers,
modeled_chargebee_subscriptions, modeled_chargebee_invoices,
modeled_chargebee_transactions, and modeled_chargebee_mrr (a monthly
per-subscription 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 Chargebee's
RevenueStory. Keep it practical, dense, and executive-readable. Avoid vanity metrics.

Normalize active subscriptions to a monthly amount and sum.

SELECT
  ROUND(SUM(
    CASE p.period_unit
      WHEN 'year'  THEN s.plan_amount / 12.0 / NULLIF(p.period, 0)
      WHEN 'month' THEN s.plan_amount / NULLIF(p.period, 0)
      WHEN 'week'  THEN s.plan_amount * 52.0 / 12.0 / NULLIF(p.period, 0)
    END * s.plan_quantity
  ) / 100.0, 2) AS mrr_now
FROM subscriptions s
JOIN plans p ON p.id = s.plan_id
WHERE s.status IN ('active', 'in_trial', 'non_renewing');

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', to_timestamp(s.cancelled_at)) = m.month
  ) AS churned_subscriptions,
  COUNT(*) FILTER (
    WHERE to_timestamp(s.created_at) <= m.month
      AND (s.cancelled_at IS NULL OR to_timestamp(s.cancelled_at) > m.month)
  ) AS active_at_month_start
FROM months m
CROSS JOIN subscriptions s
GROUP BY m.month
ORDER BY m.month;

Declined transactions by week and error code — the dunning worklist.

SELECT
  date_trunc('week', to_timestamp(t.date)) AS week,
  COUNT(*)                                  AS failed_transactions,
  ROUND(SUM(t.amount) / 100.0, 2)           AS dollars_at_risk,
  t.error_code
FROM transactions t
WHERE t.status = 'failure'
  AND to_timestamp(t.date) >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, t.error_code
ORDER BY 1, dollars_at_risk DESC;