Cohort Analysis for Marketing: Why Your Newest Month Looks Worst

Cohort analysis groups people by when they started, such as every lead who arrived in March, and then compares each group at the same age: 30, 60, and 90 days later. In marketing, it shows what each month of spend actually produced, instead of judging a month only by the sales that happened inside it.

This guide is for owners and managers whose customers take days or weeks to buy: insurance agencies, coaching and info product companies, clinics and med spas, and real estate investors. Most cohort analysis guides are written for app and SaaS teams measuring user retention. You will get a worked lead-to-sale cohort table, a way to forecast months that are still maturing, a spreadsheet build you can do in Excel or Google Sheets, and the mistakes that lead owners to cut budget from their best month.

Key takeaways

• Cohort analysis groups leads or customers by a shared start date and compares the groups at the same age, such as day 30, 60, and 90.
• Your newest month always looks worst in a calendar report because its leads have not had time to buy yet. A cohort report compares it fairly.
• Pick the start date that matches the question: lead created date for marketing, first call date for sales, first purchase date for retention and lifetime value.
• You can forecast a young cohort from how older cohorts matured, and you should report the forecast as a range.
• Ad platforms only credit sales inside their conversion window (Google Ads defaults to 30 days), so late sales need your CRM as the source of truth.

What is cohort analysis?

Cohort analysis is a way to measure groups that share a starting point over time. A cohort is the group (all leads created in June, or all customers whose first purchase was in Q1), and the analysis follows each group from its own start date, not the calendar.

The difference from a normal monthly report is the clock. A calendar report asks "how many sales closed in October?" and mixes buyers who inquired in July, August, and October. A cohort report asks "of the leads who arrived in October, how many bought within 30 days?" and puts that next to the same number for every earlier month.

Cohort analysis vs segmentation: a segment is any group with a shared trait (leads from Facebook, customers over 50). A cohort is a group with a shared start time. You often combine them: the June cohort split by source shows whether June's Google leads bought faster than June's Facebook leads.

Why does your newest month always look worst?

Because its leads have not finished buying. If your typical customer buys two to eight weeks after inquiring, the leads from the last few weeks have had almost no time to convert. Any report that divides sales by leads for each month will show the newest month at the bottom, every time, even when it is your best month.

Here is a monthly report an owner might pull on October 31 (illustrative numbers, not a benchmark). The business spends $12,000 a month on ads.

Read naively, results are collapsing: conversion fell from 8.5% to 1.4%, and October's cost per sale is $4,000 against roughly $700 in May. Plenty of owners cut a channel or replace an agency on a report like this.

Now the same leads as a cohort table, counting sales at each age:

At equal age, September has the best day-30 conversion of any month. Nothing collapsed. The blank cells are the time those leads have not lived yet, not zeros.

How do you read a cohort analysis table?

A cohort table has three directions, and each answers a different question.

1. Read across a row to see how one cohort matures. May went from 8 sales at day 30 to 17 by day 90, so more than half of May's buyers took longer than a month.
2. Read down a column to compare cohorts at the same age. This is the column that tells you whether marketing or sales is improving. Day 30 rose from 4.0% to 4.5% between May and September.
3. Read the diagonal edge to see what is still pending. Everything below the staircase is the future. Never average it in as zero.

Two rules keep you honest. Compare a young cohort only to older cohorts at the same age (September's day 30 against May's day 30, never against May's day 90). And use cumulative counts for lead-to-sale cohorts, meaning each cell includes every sale up to that age, because a lead buys once and you want to see the total climb.

Which start date should define the cohort?

The start date decides what the analysis can answer. Mixing dates is the most common reason two people get different numbers from the same CRM.

Choose the milestones from your sales cycle. Thirty, 60, and 90 days fits most consult-based businesses. A real estate investor whose sellers take months to decide may need 30, 90, and 180. An insurance agency selling around renewal dates may track some cohorts to the next renewal. A coaching business that sells through a webinar or launch can use the registration date as the start.

Types of cohort analysis

Most guides list three types. For a business that sells on a call, they map like this:

• Acquisition cohorts group people by when they arrived (lead month). This is the one to start with, and the one in the tables above.
• Behavioral cohorts group people by something they did: leads who booked within a day, leads who took the quiz, patients who bought a package. They answer "do people who do X buy more?" Treat the answer as a clue, not proof, since the people who act fast may simply be more motivated.
• Customer cohorts group buyers by first purchase month and track repeat revenue. This is customer cohort analysis, the retention view, and it is how you measure lifetime value without guessing.

How to forecast a cohort that is still maturing

You can estimate where a young cohort will land by using how older cohorts matured. This is the step that keeps a team from overreacting to a month that is only half done.

Worked example using the table above:

1. Find the maturity ratio from complete cohorts. Day 90 sales divided by day 30 sales: May 17 / 8 = 2.13, June 18 / 9 = 2.00, July 16 / 8 = 2.00.
2. Apply it to the young cohort. September has 10 sales at day 30. 10 x 2.00 to 10 x 2.13 = about 20 to 21 sales by day 90.
3. Do the same for every pending milestone. August's day 60 to day 90 ratio was 1.20 to 1.23 in earlier months, so 14 x 1.20 to 1.23 = about 17 sales by day 90.
4. Turn it into cost per sale. September: $12,000 / 20 to 21 sales = about $570 to $600 per sale at day 90, against $706 for May ($12,000 / 17). The month that looked worst on October 31 is on track to be the cheapest.
5. Report a range, not a single number, and replace the forecast with actuals as the cohort reaches each milestone.

The forecast is only as good as the history behind it. If you changed your offer, pricing, or sales process recently, older cohorts may not predict newer ones. Note the change on the report and weight recent cohorts more.

How to build a cohort analysis in Excel or Google Sheets

You do not need special software. We use whatever tool solves the problem, and that often includes Microsoft Excel. What you need is clean data from your CRM.

1. Export one row per person with: lead ID, lead created date, original source, first consult date, first sale date, and sale value. Pull revenue from your CRM, practice software, or accounting system, not from the ad platform.
2. Remove duplicates. If someone inquired twice, keep the first created date and the original source, or the same buyer lands in two cohorts.
3. Add a cohort column: =TEXT(B2,"yyyy-mm") where B is the created date.
4. Add days to sale: =IF(E2="","",E2-B2) where E is the sale date.
5. Add milestone flags: =IF(AND(F2<>"",F2<=30),1,0) for day 30, then the same with 60 and 90.
6. Build a pivot table with cohort month as rows, a count of leads, and the sum of each flag. Add sum of sale value to get revenue per cohort.
7. Blank out immature cells. A cohort has fully reached day 90 only when its last lead is 90 days old, meaning the report date is at least 90 days after the last day of that month. Leave those cells empty and show the forecast beside them.
8. Add spend by month next to each cohort to get cost per sale and revenue per dollar at each age.

Once this works, add a source column to the pivot to compare channels at equal age. That is where cohort analysis pays for itself: a source with cheap leads that rarely buy by day 90 shows up clearly.

Can you do cohort analysis in Google Analytics?

Yes, for website behavior. GA4's cohort exploration groups users by first visit or another event, at daily, weekly, or monthly granularity, and shows up to 60 cohorts. It is useful for questions like whether visitors from a campaign come back.

It does not know which leads became paying customers unless you send that data in, and most sales for a consult-based business happen on the phone or in person. The same gap affects ad platforms. Google Ads counts a conversion only inside its conversion window, which defaults to 30 days and can be set from 1 to 90 days. A customer who buys on day 100 is invisible to the platform. For lead-to-sale cohorts, make your CRM and revenue data the source of truth and treat platform numbers as inputs.

Cohort analysis mistakes that cost money

• Judging a month by calendar sales. The table above is the whole argument. Cutting the newest month's channel is often cutting your best one.
• Mixing start dates. One report uses lead date, another uses opportunity date, and the team argues about whose numbers are right.
• Losing the original source. If a repeat inquiry overwrites the source, your cohorts credit the last touch and channels look better or worse than they are.
• Reading tiny cohorts as trends. A cohort of 20 leads that produces 1 sale instead of 2 has not halved its conversion. Combine months into quarters or pool sources until the groups are big enough to move slowly.
• Treating blanks as zeros. Averages that include unfinished cells understate every recent month.
• Never acting on it. The report exists to change budget, offers, follow-up, and staffing. If nothing changes after the monthly review, simplify the report until something does.

What cohort reporting looks like in practice

Our monthly client reports track cohorts at 30, 60, and 90 days with forecasts for the months still maturing, and they run about 97% accurate to the available data, with a team of data analysts to explain what changed. For Innovation Health, a multi-location clinic, that means cohort maturation from lead to consult and lead to patient at each milestone, value per patient, and revenue by lead source, with the eClinicalWorks EMR connected so marketing is measured in dollars. The clinic sustained 40% lead-to-consult for a full year and grew monthly new patients by more than 150% in 8 months.

Cohorts also show where to fix the funnel. If day-30 conversion is weak but later milestones catch up, leads are interested but slow to be worked, which points to speed to lead and the first week of follow-up. If day 90 stays flat, the gap is usually in lead nurturing after the first call. And leads from old cohorts who never bought are a list worth working, not deleting: see how to revive dead leads without burning the list. If you want the reporting built for you, that is what our marketing analytics and reporting work does.

Frequently asked questions

What is cohort analysis in simple terms?

Cohort analysis puts people into groups by when they started, such as the month they became a lead, and compares the groups at the same age. It answers "did this month's leads do better or worse than earlier months' leads by day 30?" instead of mixing everyone together.

What is an example of cohort analysis?

A business takes all 220 leads from September and counts how many bought within 30 days (10, or 4.5%), then compares that to the day-30 rate for May through August. Because every month is measured at the same age, the comparison is fair even though September's leads are still buying.

What is cohort analysis used for in marketing?

It shows which months, campaigns, and sources produce buyers and revenue, not just leads. It stops you from cutting a channel because its newest leads have not bought yet, and it lets you forecast what recent spend will return. With customer cohorts, it also measures repeat purchases and lifetime value.

How many leads do you need for a cohort analysis?

Enough that one extra sale does not swing the percentage wildly. If a monthly cohort produces only a handful of sales, group months into quarters or combine sources until the numbers move slowly. Small businesses can still use cohorts; they just read them over longer periods.

What tools can you use for cohort analysis?

Excel or Google Sheets with a CRM export is enough for most service businesses. GA4 has a cohort exploration for website behavior, and product analytics tools such as Mixpanel are built for app usage. For lead-to-sale cohorts, the data has to come from your CRM and revenue system, whichever tool draws the table.

How often should you review a cohort report?

Monthly suits most businesses: add the new cohort, update every pending milestone, and replace forecasts with actuals as they arrive. Review it with whoever controls budget and the sales team, since the decisions it drives are spend, follow-up, and staffing.

If your newest month always looks like your worst and you want to know what your spend really produced, we can build your cohort reporting with you. Book a call.