Subscription SaaS Recurring Revenue Reporting

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with customers (id, signup date, country), plans (name, price in cents, interval monthly or annual), subscriptions (customer, plan, start date, end date nullable), subscription_changes (subscription, old plan, new plan, change date) and invoices (subscription, amount, period start and end, paid date). Add keys, foreign keys and check constraints, such as an end date after the start date. Done when: the schema loads in PostgreSQL without errors.

2. 02

   Generate several years of data

   ~2h

   Write a PL/pgSQL function (or generate_series queries) that creates about 3 years of history for 2,000 customers: signups that grow over time, about 3 to 5% monthly cancellations, some upgrades and downgrades, a mix of monthly and annual plans, and invoices for every billing period. Done when: the data loads, and simple counts per month look realistic.

3. 03

   Calculate monthly recurring revenue

   ~2.5h

   Build a month calendar with generate_series, then compute MRR for each month as the sum of the monthly value of subscriptions active on the last day of the month, dividing annual plans by 12. Use the plan in effect on that day, taking plan changes into account. Done when: the MRR for a few months matches a manual check on a handful of customers.

4. 04

   Break MRR down by movement

   ~2.5h

   For each customer and month, compare MRR with the previous month using LAG, and classify the change as new (from 0), expansion (increase), contraction (decrease), churned (to 0) or reactivation. Sum each movement per month. The previous month's MRR plus all movements must equal the current month's MRR. Done when: the movements add up exactly for every month.

5. 05

   Calculate churn rates

   ~1.5h

   Compute monthly customer churn (customers who cancelled divided by customers active at the start of the month) and revenue churn (churned plus contraction MRR divided by the MRR at the start of the month), and show the net revenue retention. Round the percentages to 2 decimals. Done when: the churn rates match the 3 to 5% built into the generated data.

6. 06

   Build a cohort retention table

   ~2h

   Group customers by signup month, and for each cohort and each month since signup (0, 1, 2 and so on) calculate the percentage still paying. Present it as a triangle-shaped table, one row per cohort and one column per month number, using the crosstab function from the tablefunc extension or FILTER clauses. Done when: the retention table shows 100% in month 0 and declines realistically.

7. 07

   Create reporting views and test

   ~2h

   Create views monthly_metrics (MRR, movements, churn and customer counts per month) and cohort_retention, with comments explaining each column (COMMENT ON). Write tests.sql that loads a tiny, hand-made dataset in a transaction where you know the right answers (an upgrade, a mid-month cancellation, an annual plan) and checks the views. Done when: tests.sql passes, and the views are ready to connect to a BI tool.

Resources

• DocsPostgreSQL: generate_series and set-returning functions
• DocsPostgreSQL: window functions
• DocsPostgreSQL: tablefunc (crosstab)
• ArticleCohort analysis (Wikipedia)
• DocspgAdmin documentation