McTaba Labs logo
Bonaventure OgetoBy Bonaventure Ogeto|

Subscription Churn Instrumentation on Paystack Data

Instrument churn by handling three Paystack events: subscription.disable (voluntary churn), invoice.payment_failed (involuntary churn from declined card), and charge.success (recovered). Store each event in a subscription_events table with timestamp and reason. Churn rate = cancellations this month / active subscriptions at start of month. Involuntary churn is recoverable — send an email with a card update link within 24 hours of a failed charge.

Subscription Events Table

CREATE TABLE subscription_events (
  id SERIAL PRIMARY KEY,
  subscription_code TEXT NOT NULL,
  customer_email TEXT NOT NULL,
  plan_code TEXT,
  plan_name TEXT,
  plan_amount NUMERIC(12, 2),
  event_type TEXT NOT NULL,
  -- 'created' | 'renewed' | 'payment_failed' | 'cancelled' | 'reactivated'
  churn_type TEXT,
  -- 'voluntary' | 'involuntary' | null
  failure_reason TEXT,
  -- gateway_response for payment_failed events
  occurred_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sub_events_email ON subscription_events(customer_email);
CREATE INDEX idx_sub_events_type ON subscription_events(event_type, occurred_at);

Instrumenting from Webhook Events

// In your webhook handler

if (event.event === 'subscription.create') {
  await db.subscriptionEvents.create({
    subscription_code: event.data.subscription_code,
    customer_email: event.data.customer.email,
    plan_code: event.data.plan.plan_code,
    plan_name: event.data.plan.name,
    plan_amount: event.data.plan.amount / 100,
    event_type: 'created',
    occurred_at: event.data.created_at,
  });
  // Increment MRR
  await db.mrr.increment(event.data.plan.amount / 100);
}

if (event.event === 'charge.success' && event.data.plan) {
  await db.subscriptionEvents.create({
    subscription_code: event.data.subscription_code,
    customer_email: event.data.customer.email,
    plan_amount: event.data.amount / 100,
    event_type: 'renewed',
    occurred_at: event.data.paid_at,
  });
}

if (event.event === 'invoice.payment_failed') {
  await db.subscriptionEvents.create({
    subscription_code: event.data.subscription.subscription_code,
    customer_email: event.data.customer.email,
    plan_code: event.data.subscription.plan.plan_code,
    plan_amount: event.data.subscription.plan.amount / 100,
    event_type: 'payment_failed',
    churn_type: 'involuntary',
    failure_reason: event.data.transaction?.gateway_response,
    occurred_at: event.data.created_at,
  });
  // Start dunning — send card update email immediately
  await sendCardUpdateEmail(event.data.customer.email);
}

if (event.event === 'subscription.disable') {
  await db.subscriptionEvents.create({
    subscription_code: event.data.subscription_code,
    customer_email: event.data.customer.email,
    plan_amount: event.data.plan.amount / 100,
    event_type: 'cancelled',
    churn_type: 'voluntary',
    occurred_at: event.data.created_at,
  });
  // Decrement MRR
  await db.mrr.decrement(event.data.plan.amount / 100);
}

Calculating Churn Rate and MRR

-- Monthly churn rate
WITH month_start AS (
  SELECT COUNT(*) as active_start
  FROM subscriptions
  WHERE status = 'active' AND created_at < '2026-07-01'
),
churned AS (
  SELECT COUNT(*) as churned_count
  FROM subscription_events
  WHERE event_type = 'cancelled'
  AND occurred_at BETWEEN '2026-07-01' AND '2026-07-31'
)
SELECT
  churned.churned_count::float / NULLIF(month_start.active_start, 0) * 100 AS churn_rate_pct
FROM month_start, churned;

-- Involuntary vs voluntary churn breakdown
SELECT
  churn_type,
  COUNT(*) as count,
  SUM(plan_amount) as mrr_lost
FROM subscription_events
WHERE event_type = 'cancelled'
  AND occurred_at BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY churn_type;

-- Top failure reasons for involuntary churn
SELECT
  failure_reason,
  COUNT(*) as occurrences
FROM subscription_events
WHERE event_type = 'payment_failed'
GROUP BY failure_reason
ORDER BY occurrences DESC
LIMIT 10;

Learn More

Key Takeaways

  • ✓Separate voluntary churn (subscription.disable) from involuntary churn (invoice.payment_failed). They need different responses.
  • ✓Involuntary churn from declined cards is recoverable — 30-40% of failed charges succeed on retry within 7 days.
  • ✓Track MRR by summing active subscription plan amounts. Decrement on subscription.disable, increment on new subscription.create.
  • ✓Build a subscription_events table to capture every status change with timestamp and reason for longitudinal analysis.
  • ✓Send a card update email immediately after invoice.payment_failed. Every day of delay reduces recovery rate.
  • ✓Segment churn by card type, billing cycle, and plan tier to identify which customers churn most.

Frequently Asked Questions

What is a healthy subscription churn rate for an African SaaS?
Monthly churn rates under 2% are generally healthy for B2B SaaS. For B2C, under 5% monthly is acceptable. African markets have higher involuntary churn due to card expiry and insufficient balance — which is why dunning sequences are especially important. Track voluntary and involuntary churn separately.
How do I recover a customer whose subscription was cancelled due to failed payment?
Send an email immediately with a link to update their card. If they do not respond in 3 days, send an SMS. After 7 days, offer a one-time discount or a grace extension. Customers who cancelled due to failed payment (involuntary) are much more likely to reactivate than those who actively cancelled.
Should I store MRR in the database or calculate it on the fly?
Calculate on the fly from your subscriptions table for accuracy. Storing MRR as a running counter risks drift when events are missed or replayed. A simple query summing active subscription plan amounts gives you real-time MRR without synchronization issues.
How do I track expansion MRR (upgrades) and contraction MRR (downgrades) with Paystack?
Paystack does not have native plan upgrade/downgrade events. You need to model this yourself: cancel the current subscription (subscription.disable) and create a new one at the higher or lower plan. Store both events with the old and new plan amounts to calculate expansion/contraction MRR in your analytics.

Ready to build real-world apps?

Join the McTaba Labs full-stack marathon (4 months full-time · 6 months part-time). Learn M-Pesa, USSD, and WhatsApp engineering while shipping 8 production apps.

Apply to the McTaba Marathon