Subscription Churn Model

Project roadmap

1. 01

   Define the events and outputs

   ~1.5h

   Write README.md: the raw table subscription_events (event id, user id, event type signup, activate, pause, resume or cancel, occurred at), the rules (which statuses count as active), and the outputs: monthly active users, monthly churn rate, and a retention table by signup cohort. Write schema.sql with the raw table and a clean events table. Done when: the schema loads, and README.md defines every metric precisely.

2. 02

   Generate realistic events

   ~1.5h

   Write a PL/pgSQL function that creates about 2 years of events for 5,000 users: signups over time, activations, pauses, cancellations and some returning users. Deliberately add messy data: duplicate events, events with the same time, and cancellations for users who never activated. Done when: the raw table is filled, and a quick count shows the messy cases are present.

3. 03

   Clean and validate the events

   ~2h

   Write a clean-up step that removes exact duplicates, orders each user's events by time then id, and drops events that make no sense in sequence (such as pausing an inactive subscription), recording every dropped event with its reason in a rejected_events table. Load the rest into the clean table. Done when: every raw event ends up either in the clean table or in rejected_events with a reason.

4. 04

   Turn events into active periods

   ~2.5h

   Using LEAD over each user's clean events, turn them into periods (user, status, start, end), where a period ends when the next event starts. Create a view active_periods with only the active ones. Done when: a user who activated, paused, resumed and cancelled has exactly two active periods with the right dates.

5. 05

   Calculate monthly active users and churn

   ~2h

   With a generate_series calendar of months, count users active at any time during each month, and users who were active at the start of the month but cancelled during it. Calculate the churn rate as cancellations divided by active users at the start of the month, rounded to 2 decimals. Wrap it in a view monthly_metrics. Done when: the metrics are calculated for every month, including months with no cancellations.

6. 06

   Build cohort retention curves

   ~2h

   Group users by the month they first activated. For each cohort and each month number since then (0, 1, 2 and so on), calculate the percentage still active in that month. Output it both as a long table (cohort, month number, percentage) for charts, and as a crosstab with one column per month. Done when: month 0 is 100% for every cohort, and the curves decline realistically.

7. 07

   Test with psql

   ~1.5h

   Write test.sql, run with psql, that loads a small hand-made set of events with known answers in a transaction (a pause and resume, a returning user, a duplicate, an invalid cancellation), checks the views against the expected values and raises an error on any mismatch, then rolls back. Done when: psql -f test.sql finishes without errors.

Resources

• DocsPostgreSQL: window functions
• DocsPostgreSQL: generate_series
• DocsPostgreSQL: PL/pgSQL
• Docspsql documentation
• ArticleCohort analysis (Wikipedia)