How to build a cohort analysis

Stage 1: Structuring the database

This is the most important stage, because it is the quality of this database and its organisation that will determine the quality of your cohort model, avoiding overly large files. Bear in mind, however, that there are limits to this approach: for established businesses with substantial customer bases, your cohort analyses can be lengthy and consume a lot of computing power.

To carry out an effective cohort analysis, you will need two complementary databases: an invoicing database, which tracks transaction history, and a customer database, which centralises information about your customers. These two databases are interdependent and enrich each other to build up a complete picture of your business.

Billing basis

You could enrich this database by adding qualitative columns such asRegion, Segment or Acquisition Channel. These additional dimensions would allow you to go even deeper in your analysis, for example by comparing retention rates according to geographical area or customer profile. However, this guide concentrates on the essentials for the sake of readability and efficiency: we have kept only the columns that are essential for calculating cohort metrics.

Customer base

The customer database complements the billing database by centralising stable and qualitative information about each customer. Unlike the billing database, which records each transaction, this database contains a single line for each customer, identified by the Customer ID. It contains permanent or slightly variable attributes: name, business segment, acquisition channel, key dates (acquisition, possible churn), and the last known MRR.

Each line represents a single customer with unchanging or rarely changed characteristics. TheMRR shown corresponds to the last known amount, providing a quick overview of the current value of the customer portfolio. For an overdue customer, the MRR for the last month of activity is displayed.

The relationship between the two databases is via the customer identifier: the billing database records each transaction with its customer identifier, while the customer database stores the customer's permanent attributes. This architecture allows you to navigate easily between the transactional view (billing) and the customer view (profile and characteristics).

Of course, these characteristics are just examples. Depending on your business (product, pricing, geographical scope, KPI, etc), they will differ.

Step 2: Creation of a monthly MRR tracker for each customer

Once you have structured your two databases, the next step is to build a table that summarises the MRR evolution of each customer, month by month. This consolidated table is the cornerstone of your cohort analysis: it will enable you to calculate all the essential metrics (MRR churn, expansion, Downsell, new MRR, ARPA) that we will detail in the following sections.

The structure of this table is based on the information you have already collected: the customer database determines the rows in the table (one customer = one row), as well as the analysis period for each customer (from the date of acquisition to the date of churn, or to the present day if the customer is still active). Labase Invoicing feeds the monthly values: for each month, it tells you the MRR invoiced by this customer.

The result is a table where each column represents a calendar month (from your minimum month to your maximum month of analysis), and each cell contains the customer's MRR for that month. Empty or zero cells indicate either that the customer has not yet been acquired, or that it has already expired. This visual representation makes it possible to identify customer trajectories at a glance: progressive upsells, stability, downsells or churn.

Step 3: Consolidate lines by acquisition period

It is at this stage that the cohorts begin to emerge, because the criterion for the uniqueness of the rows in the table is no longer the customer, but the date of acquisition. Depending on the maturity of the business, you may consolidate by month, quarter or year of acquisition.

It is possible to proceed using two prisms: the MRR prism and the customer prism. These two approaches complement each other, so we're going to use both.

MRR cohorts

Customer cohorts

Thanks to these tables, it is now possible to draw initial conclusions about the behaviour of the cohorts, but the full analysis does not stop there.

Step 4: Transpose the matrix to introduce the evolutionary vision

Without this step, the analysis is limited to a behavioural evolution of the cohorts over the calendar months, and not over their lifetime in your database, which is of greater interest to us.

The logic is simple: determine the number of months elapsed between the month of acquisition and the month of activity observed in the column of the table. As with the previous step, this can be done from either the customer or MRR perspective.

MRR cohorts

Note: The percentages displayed after M0 represent the NRR (Net Revenue Retention) in quasi-direct reading, i.e. NRR - 100%, so a value of +5% corresponds to a NRR of 105%. In other words, '↑' suggests a NRR > 100%, while '↓' suggests a contraction and therefore a NRR < 100%.

Customer cohorts