Common formulas: cohorts
Formulas that roll a customer base forward with churn, for one group or for each acquisition channel.
A cohort formula keeps a count of customers from one period to the next. Each period keeps the customers that did not churn and adds the new customers. Revenue then follows the customer count.
The formulas on this page come from the Simple Cohort Model, Advanced Cohort, and B2B SaaS Revenue Model templates. To start from a template, go to Scenario planning → New model, type the template name, and press Enter.
Roll the customer base forward
The Simple Cohort Model uses one customer base.
| Variable | Formula |
|---|---|
| Customers | Customers[previous] * (1 - Monthly churn rate) + New customers |
| Revenue | Customers * Revenue per customer |
In the first period, Customers[previous] is blank. A blank counts as zero in arithmetic, so the first period has only the new customers.
Start from an opening count
If you already have customers, start from an input. The B2B SaaS Revenue Model uses this formula:
if Customers[previous] = blank then Starting customers else Customers[previous] * (1 - Monthly churn rate) + New customers per monthThe blank test works when Blanks in data is Treat as blank. That is the setting in the templates.
One cohort for each channel
The Advanced Cohort template breaks the customer base down by the Channel dimension. The items are Organic, Paid, Referral, and Sales. New customers, Monthly churn rate, Revenue per customer, Customers, and Revenue all have the dimension.
| Variable | Formula |
|---|---|
| Customers | if Customers[previous] = blank then New customers else Customers[previous] * (1 - Monthly churn rate) + New customers |
| Revenue | Customers * Revenue per customer |
| Blended revenue per customer | Revenue / Customers |
Eigenn runs the Customers formula one time for each channel. Each channel uses its own churn rate. The first period with the example inputs looks like this:
| Channel | New customers | Monthly churn rate | Revenue per customer | Revenue |
|---|---|---|---|---|
| Organic | 120 | 3% | 40 | 4,800 |
| Paid | 200 | 6% | 55 | 11,000 |
| Referral | 60 | 2% | 45 | 2,700 |
| Sales | 40 | 4% | 200 | 8,000 |
| Total row | 420 | 3.75% | 85 | 26,500 |
Monthly churn rate and Revenue per customer use the Average dimension aggregation. Their total rows show a plain average of the channels. Blended revenue per customer has no dimension, so it divides total revenue by total customers: 26,500 / 420 = 63.10. Use the blended row, not the average, for a company-wide figure.
Patterns in Formula templates
In the formula bar, select Formula templates and search for these names:
| Template | Example after you fill it |
|---|---|
| Retained customers | Customers[previous] * (1 - Churn rate) |
| Cohort revenue | Customers * ARPU |
| Lifetime value by cohort | ARPU * Gross margin / Churn rate |