How to build Paddle revenue dashboards in Metabase

# Paddle (live) — replace with sandbox host for testing
claude mcp add --transport http paddle https://mcp.paddle.com/mcp \
  --header "Authorization:Bearer YOUR_PADDLE_API_KEY"

{
  "mcpServers": {
    "paddle": {
      "command": "npx",
      "args": [
        "-y", "mcp-remote",
        "https://mcp.paddle.com/mcp",
        "--header", "Authorization:Bearer YOUR_PADDLE_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 Paddle CSV export — creates a table AND a model
mb upload csv --file paddle-subscriptions.csv --collection root

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

Create a polished Metabase dashboard for Paddle 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 net revenue from Paddle data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Paddle tables and
  models). If durable Paddle 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 Paddle MCP server using a read-only API
  key (use the sandbox endpoint to test): customers, subscriptions, subscription
  items, products, prices, transactions, and adjustments. 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, Products, Prices, Transactions, Transaction line items, Adjustments
(refunds/credits), and Discounts. Inspect the actual tables and column names
first.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Paddle — it reads a database or CLI-uploaded tables.
- Compute MRR from active subscription items, normalizing every price to a monthly
  amount and converting amounts from the smallest currency unit (amounts are stored
  as integer minor units, sometimes as strings).
- Paddle is a merchant of record: separate gross billed amount from net revenue
  after tax and Paddle fees. Do not report tax collected as your revenue.
- 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.
- 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: Paddle 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;
   Scheduled cancellations.
4. Failed payments & dunning: Failed transactions and $ at risk; Recovery rate;
   Past-due subscriptions by age.
5. Net revenue & cohorts: Gross vs. net revenue by month; Revenue retention by
   signup-month cohort; Cumulative LTV by cohort; ARPU by product.

Filters: Product, Price, Billing interval, Currency, Country, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_paddle_customers, modeled_paddle_subscriptions,
modeled_paddle_transactions, and modeled_paddle_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 the Paddle
dashboard and payouts. Keep it practical, dense, and executive-readable. Avoid
vanity metrics.

Normalize active subscription items to a monthly amount and sum.

WITH monthly AS (
  SELECT
    si.subscription_id,
    SUM(
      CASE p.billing_interval
        WHEN 'year'  THEN p.unit_price_amount / 100.0 / 12.0
        WHEN 'month' THEN p.unit_price_amount / 100.0
        WHEN 'week'  THEN p.unit_price_amount / 100.0 * 52.0 / 12.0
        WHEN 'day'   THEN p.unit_price_amount / 100.0 * 365.0 / 12.0
      END * si.quantity / NULLIF(p.billing_frequency, 0)
    ) AS mrr
  FROM subscription_items si
  JOIN prices p        ON p.id = si.price_id
  JOIN subscriptions s ON s.id = si.subscription_id
  WHERE s.status IN ('active', 'past_due')
  GROUP BY si.subscription_id
)
SELECT ROUND(SUM(mrr), 2) AS mrr_now,
       ROUND(SUM(mrr) * 12, 2) AS arr_now
FROM monthly;

Merchant-of-record breakdown: billed total, tax collected, and your earnings.

SELECT
  date_trunc('month', t.billed_at)          AS month,
  ROUND(SUM(t.details_totals_total)  / 100.0, 2) AS gross_billed,
  ROUND(SUM(t.details_totals_tax)    / 100.0, 2) AS tax_collected,
  ROUND(SUM(t.details_totals_earnings) / 100.0, 2) AS net_earnings
FROM transactions t
WHERE t.status = 'completed'
  AND t.billed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Past-due and canceled transactions by week — the dunning worklist.

SELECT
  date_trunc('week', t.created_at) AS week,
  COUNT(*)                          AS failed_transactions,
  ROUND(SUM(t.details_totals_total) / 100.0, 2) AS dollars_at_risk
FROM transactions t
WHERE t.status IN ('past_due', 'canceled')
  AND t.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;