How to build Patreon membership dashboards in Metabase

# CRITICAL: every attribute must be named in fields[...] or the
# response comes back nearly empty — the classic silent-empty bug.
BASE=https://www.patreon.com/api/oauth2/v2
FIELDS='fields[member]=patron_status,currently_entitled_amount_cents,\
lifetime_support_cents,campaign_lifetime_support_cents,will_pay_amount_cents,\
pledge_cadence,pledge_relationship_start,last_charge_date,last_charge_status,\
next_charge_date,is_free_trial,is_gifted,is_follower,full_name'

curl -s -G "${BASE}/campaigns/${CAMPAIGN_ID}/members" \
  -H "Authorization: Bearer $PATREON_TOKEN" \
  --data-urlencode "$FIELDS" \
  --data-urlencode 'page[count]=500' \
  > patreon-members.json

# Stamp today's date so daily snapshots build real churn history
jq -r --arg d "$(date -u +%F)" '.data[] | [
  $d, .id, .attributes.patron_status,
  (.attributes.currently_entitled_amount_cents / 100),
  (.attributes.campaign_lifetime_support_cents / 100),
  .attributes.pledge_cadence, .attributes.pledge_relationship_start,
  .attributes.last_charge_date, .attributes.last_charge_status
] | @csv' patreon-members.json > patreon-creator-members.csv

# 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 members export — creates a table AND a model
mb upload csv --file patreon-members.csv --collection root

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

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

Goal: Help the team understand active members, new vs. churned, revenue by tier, churn by cohort, and net earnings after fees from Patreon data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for patreon tables and
  models). If durable Patreon data is already present — a warehouse sync, a
  direct database connection, or an earlier upload — use it and skip to Step 2.
- If nothing is there, pull a scoped, summarized export by scripting the Patreon Members API:
  members, plus campaigns and patron counts, pledge tiers and entitled amounts, charge dates and payment status.
  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 or column names. Inspect available fields, products,
locations or countries, currencies, and date ranges before creating trend cards.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Patreon — it reads a database or CLI-uploaded tables.
- Separate gross amounts, platform and processing fees, taxes, and refunds into
  distinct columns; never let one card silently mix gross and net.
- A single CSV is a point-in-time snapshot: only build trend cards if there is
  a usable date column or multiple periods have been uploaded.
- Member counts are point-in-time snapshots. Churn and net-member-change trends only work once you capture a snapshot per day or per month and append it.
- If a metric can't be computed from the data present, say so on the card
  instead of approximating it.

Dashboard title: Patreon Membership Overview

Sections:
1. Executive summary: Active members; Net member change; Churn rate;
   Monthly membership revenue; Average revenue per member.
2. Base: Active members trend; new vs. churned; members by tier.
3. Revenue: Membership revenue by month; revenue by tier; upgrades vs. downgrades.
4. Churn: Churn rate; churn by tenure cohort; survival by join cohort.
5. Earnings: Net after fees; effective fee rate; lifetime value by tier.

Filters: Date range, Tier, Patron status, Pledge cadence, Cohort.

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.

Monthly and annual pledges brought onto one comparable basis.

SELECT
  snapshot_date,
  COUNT(*) FILTER (WHERE patron_status = 'active_patron') AS active_patrons,
  COUNT(*) FILTER (WHERE patron_status = 'declined_patron') AS declined,
  ROUND(
    SUM(
      CASE
        WHEN patron_status <> 'active_patron' THEN 0
        -- cadence 12 means an annual pledge; spread it over the year.
        WHEN pledge_cadence = 12
          THEN currently_entitled_amount_cents / 12.0
        ELSE currently_entitled_amount_cents
      END
    ) / 100.0, 2
  ) AS mrr,
  ROUND(
    AVG(currently_entitled_amount_cents) FILTER (
      WHERE patron_status = 'active_patron'
    ) / 100.0, 2
  ) AS arpu
FROM creator_members
GROUP BY snapshot_date
ORDER BY snapshot_date DESC;

Genuine cancellations, with declined payments held out separately.

WITH month_end AS (
  SELECT DISTINCT ON (date_trunc('month', snapshot_date))
    date_trunc('month', snapshot_date)::date AS month,
    snapshot_date
  FROM creator_members
  ORDER BY date_trunc('month', snapshot_date), snapshot_date DESC
), status_by_month AS (
  SELECT m.month, c.member_id, c.patron_status
  FROM month_end m
  JOIN creator_members c ON c.snapshot_date = m.snapshot_date
)
SELECT
  s.month,
  COUNT(*) FILTER (WHERE p.patron_status = 'active_patron')
    AS active_at_start,
  COUNT(*) FILTER (
    WHERE p.patron_status = 'active_patron'
      AND s.patron_status = 'former_patron'
  ) AS churned,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE p.patron_status = 'active_patron'
        AND s.patron_status = 'former_patron'
    ) / NULLIF(COUNT(*) FILTER (
      WHERE p.patron_status = 'active_patron'
    ), 0), 2
  ) AS churn_rate_pct
FROM status_by_month s
JOIN status_by_month p
  ON p.member_id = s.member_id
 AND p.month = s.month - INTERVAL '1 month'
GROUP BY s.month
ORDER BY s.month DESC;

How long patrons stay, grouped by the month they first pledged.

WITH latest AS (
  SELECT * FROM creator_members
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM creator_members)
)
SELECT
  date_trunc('month', pledge_relationship_start)::date AS cohort_month,
  COUNT(*) AS patrons_ever,
  COUNT(*) FILTER (WHERE patron_status = 'active_patron') AS still_active,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE patron_status = 'active_patron')
    / NULLIF(COUNT(*), 0), 1
  ) AS retention_pct,
  ROUND(
    PERCENTILE_CONT(0.5) WITHIN GROUP (
      ORDER BY campaign_lifetime_support_cents / 100.0
    )::numeric, 2
  ) AS median_lifetime_support
FROM latest
GROUP BY cohort_month
ORDER BY cohort_month DESC;