Build a renewal cohort ledger that explains retention

Define the cohort at the renewal opportunity

A renewal cohort contains subscriptions that had a real opportunity to renew in the same period under comparable terms. Do not put new subscriptions, complimentary access, future annual renewals, or already-canceled accounts in the denominator. Record the cohort date, billing interval, price, currency, tier, discount, tenure band, and whether access continues during a grace period.

Keep people, subscriptions, invoices, and payments distinct. One institution may pay one invoice for many seats; one person may hold two subscriptions; one renewal may need several attempts. The ledger's primary key should let the operator follow the contractual subscription while separate fields retain payer and reader counts.

Track transitions instead of one churn label

Useful states are eligible, renewal invoice created, paid on first attempt, payment failed, recovered during grace, voluntarily canceled, ended after failed recovery, refunded after renewal, disputed, and unresolved. Stripe's subscription object distinguishes active, past due, canceled, unpaid, paused, and other states. Map provider states to the publisher's ledger explicitly; do not assume a dashboard's active count equals cash retained.

Voluntary cancellation answers a different operating question from payment failure. Refunds and disputes happen after an apparent renewal and reduce retained cash. Unresolved invoices must remain unresolved rather than being forced into retained or churned to make a period close neatly.

Source notes: The Subscription object

Calculate retention with a reconciliation row

Hypothetical example: 120 monthly subscriptions are eligible on 1 September. Ninety-six pay on the first attempt and remain retained, eight pay after a retry and remain retained, one additional paid renewal is later refunded, six cancel voluntarily, seven end after payment failure, and two remain unresolved at the measurement cutoff. Gross renewal payment success, including the later-refunded renewal, is 105 divided by 120, or 87.5%. Known retained renewals after the refund are 104, or 86.7%. Known ended subscriptions are 13, or 10.8%. The refunded share is 0.8% and the unresolved rate is 1.7%. These mutually exclusive outcome rows reconcile to 120.

If each renewal invoice is a hypothetical $12, successful billings before the refund are $1,260 and retained billed amount after the refund is $1,248, before fees, tax, later disputes, or currency effects. Count-based retention and cash retention answer different questions. Label the cutoff, because the two unresolved subscriptions may later recover or end.

Percentages are rounded. The rows reconcile to the eligible cohort rather than the current active total.

Compare cohorts only after aligning terms

Compare monthly with monthly and annual with annual. Separate renewal month, acquisition source, tier, price cohort, tenure, discount, and migration status when those differences are material. A new-price cohort may retain differently because of the price, benefits, acquisition mix, or season; the ledger identifies the difference but does not establish its cause.

Choose both an operational cutoff, such as seven days after due date, and a mature cutoff after the recovery window. Recalculate older cohorts when late recoveries, refunds, or disputes land. Publish no retention benchmark from this template: it is a local measurement system whose definitions must travel with every chart.

• Freeze the eligible cohort before counting outcomes.
• Keep voluntary cancellation, failed payment, and refund separate.
• Show unresolved renewals at every interim cutoff.
• Reconcile subscription count and retained cash independently.

Source notes: The Subscription object