Building a Custom Subscription Analytics and Cohort Reporting Tool for SaaS Teams

Aggregate MRR tells you where you are. Cohort analysis tells you why. The difference between a SaaS company that understands its revenue dynamics and one that doesn't is usually visible in the quality of cohort data they have access to — and how quickly they can get it without involving a data analyst.

Off-the-shelf tools like Chartmogul and Baremetrics offer cohort views, but they're constrained by what data Stripe exposes. A custom subscription analytics tool built on your full data model can answer questions those tools can't: Are customers acquired through a specific channel retaining better than others? Do annual contract customers who go through onboarding with a CSM show different expansion patterns than those who self-onboard? Which plan tier has the worst net revenue retention — and why?

What cohort reporting reveals that aggregate metrics hide

A cohort is a group of customers who share a common starting condition — typically their sign-up month. By tracking each cohort's revenue over time, you can see whether your product is improving (newer cohorts retain better), whether a specific acquisition channel produces lower-quality customers (a cohort acquired via a specific promotion churns at 3x the rate of organic cohorts), and where expansion revenue concentrates (cohorts in their second year).

Aggregate churn rate hides all of this. A 3% monthly churn rate looks the same whether all cohorts are churning at 3% or whether one cohort is churning at 8% (dragging down a product that's actually working well for everyone else). Cohort analysis isolates these signals.

The subscription events to capture

The raw material for cohort analysis is a complete log of subscription state changes: activation (new customer on a paid plan), upgrade (increase in MRR from an existing customer), downgrade (decrease in MRR), churn (cancellation), reactivation (returning after cancellation), and payment failure with recovery status.

Each event needs: customer ID, event type, timestamp, plan before (if applicable), plan after, MRR delta, and the customer's cohort identifier (typically signup month). If you're building on Stripe, this data is available via webhook events — but it needs to be stored in your own database to make cohort queries tractable. Querying Stripe's API in real time for cohort analysis at 1,000+ customers is too slow.

Building a cohort analysis engine on your billing data

The cohort analysis engine is a set of materialized views or precomputed tables that transform the event log into cohort-month-level aggregates. The schema looks like: cohort (month), period (months since cohort start), cohort size (number of customers), active customers (still paying), retained MRR, and expansion MRR.

Precomputing these tables nightly means your analytics queries are fast regardless of how many events are in the log. The expensive computation happens in the batch job, not in the UI query.

The main engineering challenge is handling mid-month events correctly: an upgrade on the 15th of the month should affect MRR in that month's metrics, but the customer should still belong to their original cohort. Getting event attribution right is where most DIY cohort tools have bugs.

Visualizing retention curves, expansion revenue, and LTV by segment

The three visualizations that drive the most operational decisions:

Retention curves by cohort: A heatmap where rows are cohort months and columns are periods since signup. Cell values are the percentage of original MRR retained. A healthy SaaS product shows curves that flatten above 70–80% after 6 months; a product with a product-market fit problem shows curves that keep declining past 12 months.

Net revenue retention by plan tier: Which plan tier retains and expands best? This tells your pricing and CS teams where to focus.

LTV by acquisition channel: If you have channel data from your CRM, joining it to cohort retention shows which acquisition channels produce the most valuable customers — often different from which channels produce the most customers.

Sharing reports across teams without a BI tool

A custom subscription analytics tool should have a sharing layer: the ability to create saved views that specific teams can access without building their own queries. Finance wants the monthly MRR movement report. Product wants the feature adoption cohort. CS wants the at-risk cohort view.

Building role-appropriate saved views into the tool — so each team gets the slice of the data that's relevant to them, without needing a data analyst as an intermediary — is what converts an analytics tool from engineering infrastructure into a business asset.