Skip to main content

Subscription Health Report for Retention and Churn Insights

Pelcro ProductAccounting

Problem Statement

Pelcro clients are not able to project their subscriber behavior into the future because they don't understand historical numbers and current projected number.

User Story

  1. As a Pelcro client, I want to understand how many subscriptions were retained and churned in a given period

DoD

  1. Create a Subscription Health report on Pelcro platform. Pelcro client can select their required date range and interval. The output will be a table based on every interval valid in the selected date range:

  2. If user selects a date range of January 1, 2024 to December 31, 2024 with an interval of Monthly, then the table below will be January 1-January 31, February 1 - Feb 29, etc..

  3. We generate a table with the variable below based on database queries

Name

Variable

Description

Opening Actives

OA

Subscriptions active at the start of period

New Starts

NS

Subscriptions created during period

Churned (Opening Cohort)

CO

Subscriptions that were active at the start but ended during the period and did not renew

Churned (In-Period)

CI

Subscriptions that started and ended during the period (short-term churn)

Closing Actives

CA

Subscriptions active at the end of period

Closing Actives (Opening Cohort)

CAO

Subscriptions that started the period and are still active at end of period

  1. In order to audit this and verify it is true, verify that CA = OA + NS - CO - CI

  2. All variables in table above must be exportable

3. Generate retention and churn rate numbers based on the table above

  1. Subscription Retention Rate = (OA - CO)/OA

  2. Total Retention Rate = CA / (OA + NS)

  3. Subscription Churn Rate = CO / OA

  4. In-Period Churn Rate = CI / NS

  5. Total Churn Rate = (CO + CI) / (OA + NS)

    Here is a quick mockup:

    https://v0-subscription-health-report.vercel.app/

Posted by Rana Haleem ·
Status: Available1 comment

Log in to comment and vote

Comments1