Cohort analysis is the single most important pattern in any SaaS finance toolkit. The analysis answers the question that every investor asks: are existing customers worth more over time, or less? The five steps below are the standard pattern we ship on every SaaS engagement, from pre-Series-A to listed companies.
The five steps
01
Pull customer orders with date and revenue
Export the orders table for the last 24 months. Each row is an order with customer ID, order date, and revenue. The data lands in a Table (Ctrl+T). The senior uses Power Query to ingest the export and merge with the customer master to get the cohort month (first order month).
02
Tag each customer with cohort month
Add a column to the customer master: Cohort = DATE(YEAR(MIN(orders[Date])), MONTH(MIN(orders[Date])), 1). The cohort is the first day of the first month the customer ordered. The senior uses the first day of the month for clean grouping.
03
Calculate months since cohort
Months since = DATEDIF(cohort, order_date, "m"). The DATEDIF function returns the number of complete months between two dates. The senior wraps DATEDIF in a column on the orders table and merges with the cohort.
04
Build the cohort matrix
Pivot Table: rows = cohort month, columns = months since, values = count of unique customers (for retention) or sum of revenue (for revenue cohort). The matrix is the output. The senior uses two matrices - one for retention, one for revenue - side by side.
05
Calculate retention and NRR
Retention = cohort N customers in month M / cohort N customers in month 0. NRR = cohort N revenue in month M / cohort N revenue in month 0. The senior uses conditional formatting on the matrix to highlight the curves. A heatmap-style colouring makes the pattern visible at a glance.
The five metrics
The cohort matrix is the input; the metrics below are the output that the board and the investors see. Each metric is a single ratio on a single cell of the cohort matrix.
Logo retention
Cohort customers in month M / Cohort customers in month 0
Above 90% at month 12 for SMB SaaS, above 80% for mid-market, above 70% for enterprise.
Net revenue retention (NRR)
Cohort revenue in month M / Cohort revenue in month 0
Above 100% means existing customers are net positive. The senior targets 110%+ for any business with expansion motion.
Gross revenue retention (GRR)
Cohort revenue in month M (excluding expansion) / Cohort revenue in month 0
Above 90% for SMB, above 80% for mid-market. Below 70% is a churn problem that needs immediate attention.
Customer lifetime value (LTV)
ARPU / Monthly churn rate
LTV/CAC above 3 is the SaaS benchmark. Below 1 is a broken business model.
Months to recover CAC
CAC / (ARPU × Gross margin)
Below 12 months is the SaaS benchmark. Below 18 is acceptable. Above 24 is a problem.
How to read the cohort matrix
The cohort matrix is read top to bottom and left to right. The top-left cell is the cohort - the customers who signed up in the most recent month. The top row is month 0 - every customer is active. As the eye moves right, customers churn and the numbers drop. The senior looks for two patterns: a flat curve (good retention) and an upward curve (expansion revenue is winning). A diagonal drop in the matrix is a churn problem. A flat bottom row in the oldest cohorts is the goal - a stable base of long-tenured customers.
What the operational impact looks like
The cohort analysis is the input to every other decision in the SaaS business - pricing, customer success headcount, expansion playbook, churn investigation. The senior reruns the analysis on the first business day of every month, updates the board pack, and walks the executive team through the changes. The meeting takes 30 minutes and the output drives the next quarter's plan.
