Skip to main content

Knowledge > Runbooks > Business Ops > Monthly Stripe Revenue Reconciliation

Monthly Stripe Revenue Reconciliation

Reconcile Stripe revenue against expected MRR at the end of each month to catch discrepancies, failed payments, and data consistency issues between Stripe and the database.

Prerequisites​

  • Stripe CLI logged in
  • STRIPE_LIVE_SECRET_KEY from knowledge/.env for live mode
  • Access to Supabase SQL editor
  • Prior month's MRR baseline (from last reconciliation or weekly metrics)

Steps​

1. Pull All Charges for the Month​

Replace [MONTH_START] and [MONTH_END] with Unix timestamps for the month boundaries.

# Get the Unix timestamp for month boundaries
# Example: March 2026
# Start: 2026-03-01T00:00:00Z → 1740787200
# End:   2026-03-31T23:59:59Z → 1743465599

stripe charges list \
  --created[gte]=1740787200 \
  --created[lte]=1743465599 \
  --limit 100 \
  --api-key $STRIPE_LIVE_SECRET_KEY

Tally:

  • Gross revenue (sum of amount for status=succeeded)
  • Refunds (sum of amount_refunded)
  • Net revenue = Gross - Refunds
  • Stripe fees = approximately 2.9% + $0.30 per charge

2. Calculate Expected MRR​

Cross-reference active subscriptions with pricing:

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

Multiply each subscription's price by the quantity and sum. Compare to PRICING.md for plan amounts.

From the database:

SELECT plan, tier, count(*) as count
FROM premium_churches
WHERE stripe_subscription_id IS NOT NULL
GROUP BY plan, tier;

Expected MRR = sum of (count × monthly price) for each plan/tier combination. See PRICING.md for current prices.

3. Identify Discrepancies​

Common discrepancies and their causes:

DiscrepancyLikely Cause
Actual < ExpectedFailed payments, refunds, mid-month cancellations, prorations
Actual > ExpectedProrations from upgrades, one-time charges, manual invoices
DB count ≠ Stripe countWebhook processing failure (subscription updated in Stripe but not in DB)

For any discrepancy > $5: investigate before closing the reconciliation.

4. Check for Failed Payments​

stripe invoices list --status open --limit 20 --api-key $STRIPE_LIVE_SECRET_KEY

Open invoices indicate payment failures. Stripe will retry automatically (3 times over 7 days by default). Check if any churches should be downgraded to free tier due to non-payment.

Also check invoice.payment_failed events were processed:

SELECT * FROM ops_error_reports
WHERE route ILIKE '%stripe%'
  AND message ILIKE '%payment_failed%'
  AND created_at > date_trunc('month', now() - interval '1 month');

5. Verify Database Reflects Stripe State​

For each active Stripe subscription, the corresponding premium_churches row should exist:

-- Check for premium_churches records without a valid subscription
SELECT id, church_id, plan, tier, stripe_subscription_id, updated_at
FROM premium_churches
WHERE stripe_subscription_id IS NULL
  AND plan != 'free'
ORDER BY updated_at DESC;

Any non-free church without a stripe_subscription_id may have been cancelled in Stripe but not updated in the DB (webhook failure). Investigate each case.

6. Verify MailerLite Subscriber Count vs Stripe Customer Count​

Stripe customers (paying or trialing) should have a corresponding MailerLite subscriber in the appropriate segment. Significant discrepancies may indicate onboarding email failures.

Check MailerLite via REST API:

curl -H "Authorization: Bearer $MAILERLITE_API_KEY" \
  "https://connect.mailerlite.com/api/groups?limit=25"

7. Document the Reconciliation​

Add a brief note to business ops records or DECISION_LOG.md:

- 2026-03-31: Monthly reconciliation complete. Gross: $X, Net: $X, MRR: $X, X active subscriptions. [Note any discrepancies and resolutions.]

Verification​

  • Gross revenue matches the sum of successful Stripe charges for the month
  • Active subscription count in Stripe matches premium_churches table count
  • All open invoices (failed payments) have been identified and addressed
  • No unprocessed webhook events left in the Stripe dashboard

See Also​