Skip to main content

Knowledge > Runbooks > Business Ops > Stripe Revenue and Subscription Health Review

Stripe Revenue and Subscription Health Review

Review active subscriptions, MRR, and subscription health to understand the current revenue state and identify any at-risk accounts.

Prerequisites​

  • Stripe CLI logged in
  • STRIPE_LIVE_SECRET_KEY from knowledge/.env
  • Access to Supabase SQL editor

Steps​

1. List Active Subscriptions by Product​

# Test mode (for development verification)
stripe subscriptions list --status active --limit 100

# Live mode (for actual revenue)
stripe subscriptions list --status active --limit 100 --api-key $STRIPE_LIVE_SECRET_KEY

Group by product/price to understand the distribution:

PlanMonthly PriceAnnual Price
Starter Chat$14.95$149.50/yr
Pro Chat$34.95$349.50/yr
Suite Chat$59.95$599.50/yr
Voice Starter$39.95—
Voice Pro$69.95—
Starter Bundle$49.95—
Pro Bundle$79.95—
Suite Bundle$99.95—
PewSearch Premium$9.95—
ITW Premium$9.95$99.50/yr
SermonWise Pro$19.95$199.50/yr

See C:\dev\PRICING.md for current Stripe price IDs.

2. Calculate MRR​

For each active subscription:

  • Monthly plan: add the monthly price directly
  • Annual plan: divide the annual price by 12

Sum all values to get MRR.

From the database:

SELECT
  plan,
  tier,
  count(*) as customers,
  -- MRR calculation varies by whether plan/price is stored; adjust to actual schema
  count(*) * CASE
    WHEN plan = 'starter' AND tier = 'chat' THEN 14.95
    WHEN plan = 'pro' AND tier = 'chat' THEN 34.95
    WHEN plan = 'suite' AND tier = 'chat' THEN 59.95
    WHEN plan = 'starter' AND tier = 'voice' THEN 39.95
    WHEN plan = 'pro' AND tier = 'voice' THEN 69.95
    WHEN plan = 'starter' AND tier = 'both' THEN 49.95
    WHEN plan = 'pro' AND tier = 'both' THEN 79.95
    WHEN plan = 'suite' AND tier = 'both' THEN 99.95
    WHEN plan = 'pro_website' THEN 9.95
    ELSE 0
  END as segment_mrr
FROM premium_churches
WHERE stripe_subscription_id IS NOT NULL
GROUP BY plan, tier
ORDER BY segment_mrr DESC;

3. Check Trial-to-Paid Conversion​

Identify subscriptions currently in trial:

stripe subscriptions list --status trialing --limit 100 --api-key $STRIPE_LIVE_SECRET_KEY

Identify subscriptions that started trialing in the last 30 days and converted (status moved to active):

  • Look at Stripe subscription event history for trial conversions
  • Target: >50% trial-to-paid conversion rate

4. Identify At-Risk Subscriptions​

Subscriptions with payment issues:

stripe subscriptions list --status past_due --limit 20 --api-key $STRIPE_LIVE_SECRET_KEY

past_due means: at least one payment failed, Stripe is retrying. If not resolved, subscription will be cancelled.

Action for each past_due:

  1. Check if the church has been notified (email from Stripe)
  2. If not resolved within 7 days: downgrade to free tier in premium_churches

5. Check for Subscription Anomalies​

Look for subscriptions where the database and Stripe are out of sync:

-- premium_churches with subscription IDs — verify each is still active in Stripe
SELECT church_id, plan, tier, stripe_subscription_id, updated_at
FROM premium_churches
WHERE stripe_subscription_id IS NOT NULL
ORDER BY updated_at DESC;

Cross-check each stripe_subscription_id is in the Stripe active list from Step 1.

6. Review Upcoming Renewals (Next 7 Days)​

Subscriptions renewing soon — ensure payment methods are valid:

# Check subscriptions with current_period_end in the next 7 days
stripe subscriptions list --status active --limit 100 --api-key $STRIPE_LIVE_SECRET_KEY
# Filter by current_period_end < now + 7 days

7. Note Growth Metrics​

Track these week-over-week:

  • Total active subscriptions
  • MRR
  • Trial subscriptions in the pipeline
  • Conversion rate

Verification​

  • Active subscription count in Stripe matches premium_churches table (within 1–2 for processing lag)
  • MRR calculation is consistent with expected plan distribution
  • No past_due subscriptions older than 7 days without a resolution plan

See Also​