Subscription Churn Instrumentation on Paystack Data

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);

// 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);
}

-- 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;

This guide is part of the Paystack recurring billing series.

Sign up for the McTaba newsletter