Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 3, 2026

A cohort analysis spreadsheet groups your customers by when they started, then shows how many of them are still buying or paying in each later month. You need three things: a flat list of transactions, a column that assigns each customer to a starting month, and a grid with cohorts down the side and months since signup across the top. This guide walks through each step in Excel and Google Sheets, shows how to read the result, and lists the mistakes that quietly break it.

Fraccfo
fraccfo.org
Turn Spreadsheet Data Into Clearer Decisions
Frac CFO works with businesses using spreadsheets, offering financial modeling, forecasting, optimization, and dashboards to improve financial visibility.

Explore spreadsheet finance support

TL;DR

  • Start with one row per transaction: customer ID, date and, if you want revenue retention, an amount.
  • Add two helper columns: cohort month (first purchase) and period number (months since that first purchase).
  • Build a grid with cohorts in rows and periods in columns. Divide every cell by the period-0 count to get retention.
  • Keep customer retention and revenue retention in separate grids, because they can tell different stories.
  • Test the grid against a raw count before you trust it. Most bad cohort tables come from duplicate customers and mismatched dates.

Table of Contents

What a cohort analysis answers that a monthly average cannot

A cohort is a group of customers who share a starting point, most often the month they signed up or made a first purchase. Cohort analysis follows those groups over time instead of blending everyone into one average. That matters because a blended churn or revenue number can hide very different behavior underneath.

A spreadsheet can answer questions like these:

  • Do customers who joined this spring stick around longer than those who joined last winter?
  • Did a new onboarding flow or campaign improve retention, or did we just attract a better crowd of buyers? Cohort comparisons separate those two effects far better than a single monthly figure.
  • Which cohorts produce durable revenue, and which spike and fade?
  • Is growth coming from new customers or from keeping the ones you have?

If you run a small shop, a subscription product, a service business with repeat clients or an e-commerce store, the logic is the same. Only the “starting event” changes: first order, first invoice, first subscription payment.

Decide five things before you open the file

A cohort model is only as clear as its definitions. The DataHub Pro tutorial lists five design decisions: the starting event, the cohort period, the return event, the measure and the normalization against period-0 size. In plain terms:

  1. Starting event. What puts a customer in a cohort? First purchase, signup or first paid invoice.
  2. Cohort period. Monthly works for most small businesses. Weekly suits high-volume stores. Quarterly suits low-volume B2B.
  3. Return event. What counts as “still here”? Any purchase, a subscription payment, a login?
  4. Measure. Customer count (logo retention), revenue, or orders per customer.
  5. Normalization. Divide each cell by the cohort’s size in period 0, so a cohort of 400 and a cohort of 40 can be compared.

Write these five answers in a notes tab. Six months from now, you will not remember whether “active” meant any purchase or a subscription renewal.

The data layout you need

Use a flat transaction log. One row per transaction with these columns is enough:

Column Example Required?
CustomerID C-1042 Yes
OrderDate 2026-03-14 Yes
Revenue 89.00 Only for revenue cohorts
Segment or channel Paid social Optional, for comparisons

Two practical notes:

  • Subscriptions billed annually. If your model is monthly, an annual payment has to be spread into monthly rows. One guide on Excel cohort templates makes this point explicitly.
  • Recurring-revenue businesses. Some modelers prefer a customer-month table instead: one row per customer per month, with customer ID, signup month, observation month, MRR and plan tier. Meritra’s SaaS guide uses that structure.

Export from your payment processor, store or invoicing tool as a CSV, paste it into a tab called Data, and format the date column as real dates, not text. That one step prevents half the problems later.

Build the cohort grid in Excel

Step 1: Assign each customer to a cohort month

In a new column, find the customer’s first purchase date and turn it into a month label. The DataHub Pro guide uses =TEXT(MINIFS(B:B,A:A,A2),"YYYY-MM"), where column A holds customer IDs and column B holds dates. Every row for the same customer now carries the same cohort month.

Step 2: Calculate the period number

Next, count the months between the cohort month and each transaction. The same guide uses =DATEDIF(DATEVALUE(D2&"-01"),B2,"M"), which returns 0 for the first month, 1 for the next, and so on. Period 0 is always the month of acquisition.

Step 3: Count customers per cohort and period

Build a PivotTable with CohortMonth in Rows, PeriodNumber in Columns and CustomerID in Values, counting distinct customers. A customer who buys three times in month 2 should count once. In Excel, distinct counts in a PivotTable require adding the data to the Data Model when you create the table. If that is awkward, add a helper column that flags the first row for each customer and period combination, then sum the flag.

Step 4: Turn counts into retention

Divide each period’s count by the period-0 count for the same cohort. The DataHub Pro method does exactly this. Period 0 becomes 100% for every row, and the rest of the row shows what share came back.

If you prefer formulas to PivotTables, the same grid can be built with COUNTIFS for active customers and SUMIFS for revenue, each divided by the cohort’s month-0 value, as Meritra shows. Formula grids update the moment you paste new data, which is handy for a monthly routine.

Build it in Google Sheets

The approach carries over almost step for step. The Coefficient guide to cohort analysis in Google Sheets calculates each account’s first sale with MINIFS, derives account age in months, then builds a pivot table with First Sale in Rows, Age (months) in Columns and Account ID in Values. Conditional formatting then turns the pivot into a heatmap.

A few Sheets habits help:

  • Keep raw data on its own tab and never type over it. Pivot tables and formulas should read from it.
  • Use whole-column ranges sparingly on big datasets. Sheets slows down quickly with tens of thousands of rows of array formulas.
  • If your pivot table can’t calculate the percentage you want, compute retention in a normal grid beside it. This is a common stumbling block, and a plain formula grid is often easier to audit.

The jargon-free rule: if you can explain every cell’s formula to a colleague in one sentence, your Sheets model is simple enough.

Customer retention and revenue retention are different grids

One grid answers “how many customers stayed.” Another answers “how much revenue stayed.” Meritra recommends keeping them as separate matrices, because expansion or downgrades can make the two diverge. A cohort that lost 30% of its customers can still be growing in revenue if the survivors upgraded.

Choose the measure that matches the decision. Jetti Sheets suggests using customer retention to see whether people remain active, and revenue retention to see how much revenue remains. Add segment fields, such as channel or plan, when you want to compare groups.

There is also a subtle choice in how you define churn. The Point Nine Capital workbook hosted on Andrew Chen’s site separates churn against the original cohort size from monthly churn against the previous month’s survivors. Its example: lose 5 customers from 50 survivors in month 6 and that is 10% monthly churn, but only 5% against an original cohort of 100. Both are valid. Label which one you are showing.

Customer retention and revenue retention are different grids

Create and read the cohort chart

The standard cohort chart is a heatmap table: cohorts in rows, time intervals in columns, and each cell showing the percentage still active. In Excel, select the retention grid, then apply Home > Conditional Formatting > Color Scales. Google Sheets offers the same under Format > Conditional formatting > Color scale. Use one color scale across the whole grid so the shades compare fairly.

For a line chart, plot each cohort as a series with period number on the horizontal axis. Limit it to five or six cohorts, or it turns into spaghetti.

Read the grid in two directions, as Amplitude explains:

  • Across a row: one cohort’s decay. A steep drop between month 0 and month 1 points to an onboarding or first-experience problem. A flat tail suggests a loyal core.
  • Down a column: the same age across different cohorts. If month-3 retention rises from 38% to 46% over successive cohorts, something you changed is working.

Align by customer age (left-justified columns) when you want to compare cohorts fairly. Use calendar alignment when you want to spot a one-time event, such as a price change or a launch, that hit every cohort at once. The VC Corner template supports both views.

What a good cohort analysis template includes

If you would rather start from a template than a blank sheet, look for these parts:

  • A raw data input tab you paste into and never edit elsewhere.
  • A deduplicated customer-month table.
  • Automatic cohort assignment and cohort-age calculation.
  • A cohort grid with separate customer-count and revenue outputs.
  • Optional segment fields.

That list follows the structure Jetti Sheets describes. For early-stage subscription businesses, the long-standing free SaaS workbook by Christoph Janz takes monthly acquired and retained customer counts and, with optional revenue inputs, calculates MRR churn and customer lifetime value. The Andrew Chen page describes its layout: raw calendar-month data, lifetime-aligned retention and churn tables, MRR equivalents, CAC break-even tables by cohort, and a second tab estimating customer lifetime and value.

A template saves setup time, but it will only fit if its inputs match your data. If you sell one-time products, a subscription template that wants MRR will frustrate you.

Use cohorts to check payback on acquisition costs

Once retention is in place, add a cost column per cohort: ad spend and sales cost for the month those customers arrived. Divide by cohort size to get acquisition cost per customer, then accumulate each cohort’s revenue by period until it passes that figure. The month it does is your payback month. A cohort that has not yet paid back tells you how much cash is tied up in growth, which is useful to know before you consider outside capital such as revenue-based funding options.

Common errors that distort a cohort analysis

Most cohort tables look convincing even when they are wrong. Run these checks before you share one:

  1. Dates stored as text. MINIFS and DATEDIF return nonsense or errors. Test with =ISNUMBER(B2).
  2. Counting transactions instead of customers. If a customer buys five times in a month, they should still count as one. Compare your period-0 total with a distinct count of customers from the raw data.
  3. Inconsistent customer IDs. The same person under two emails becomes two cohorts. Clean IDs first.
  4. Incomplete recent periods. The latest cohort and latest calendar month are partial. Leave those cells blank rather than showing a fake drop.
  5. Refunds and test orders in the log. They can create false “first purchases.” Filter them out.
  6. Mixed churn definitions. Original-base and prior-month-base churn are not interchangeable.
  7. Tiny cohorts. A 12-customer cohort moving from 50% to 42% is one person. Show cohort size beside each row.

Two reconciliation checks catch most of the rest. First, the sum of period-0 counts across cohorts should equal your total distinct customers. Second, for any single calendar month, the active customers across all cohorts should match a direct distinct count from the raw data.

Our perspective

Most small businesses we meet do not need new software to understand retention. They need their existing spreadsheet to be structured so the numbers finally tell a story.

We build cohort grids, forecasts and dashboards inside Excel and Google Sheets because you keep full control and understanding of your data, with tools your team already uses. A well-built workbook shows you which customers stay, which channels bring them in, and how that feeds cash flow.

Our approach is to connect the cohort view to the rest of your finances: retention to forecasting, forecasting to cash, cash to decisions. When one sheet explains the whole picture, it is easier to act with confidence.

frac

Skip the build: templates and help from Frac CFO

If you want the retention math done without wiring up formulas yourself, start with our SaaS Metrics Dashboard Excel Template, which is designed for tracking subscription metrics in a spreadsheet. Our guide to a subscription revenue model in Excel covers MRR, churn and cohorts in more depth, and our client profitability analysis guide helps if you are a service business tracking repeat clients.

If your data is messy or you want a custom build, we offer one-time finance projects and ongoing fractional CFO plans. The Launch Kit is $650 one-off, and our Starter plan is $600 per month ($7,200 per year), with hands-on onboarding and email support. Browse our Excel templates or see the one-time projects page to find the right fit.

13-Week Cash Flow Forecast

Prefer to talk it through? Our Starter plan is a simple place to begin.

FAQ

Can I do cohort analysis in Excel without a PivotTable?

Yes. Use COUNTIFS to count active customers for each cohort and period, and divide by the period-0 cell. Formula grids recalculate when you paste new data, and they are easier to audit cell by cell. A PivotTable is faster to set up but needs refreshing.

How many customers do I need for a cohort analysis to be useful?

There is no fixed threshold, but each cohort needs enough customers that one person leaving does not move the percentage noticeably. If your monthly cohorts are small, group by quarter instead. Show the cohort size next to every row.

What is the best cohort period: weekly, monthly or quarterly?

Monthly suits most small businesses and matches how they review their books. Weekly is better for high-volume stores with fast repeat cycles. Quarterly helps when you sign only a few clients a month.

Can I track revenue cohorts, not just customer counts?

Yes. Swap the count for SUMIFS on revenue and divide by the cohort’s period-0 revenue. Keep it as a separate grid from customer retention, since revenue can grow while customer counts fall.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources

Leave a Reply