Cohort Analysis for Service Businesses: Which Clients Are Worth Winning?

You’ve been making acquisition decisions based on volume: which channels produce the most leads, which produce the most clients. Volume is the wrong optimization target. What matters is which channels produce the clients who stay the longest, expand the most, and generate the highest total revenue over 12 months.

A referral client who signs in March and stays for 18 months is worth five times a cold-outreach client who signs in April and churns after one project. But if you’re only looking at monthly acquisition numbers, both look the same, one new client, until the 18-month difference becomes apparent. By then you’ve been optimizing for the wrong channel for over a year.

Cohort analysis shows you this. It’s not complex analytics software. It’s a spreadsheet that groups your clients by start month and tracks their revenue over time. Thirty minutes to build. Permanent strategic clarity.

The Spreadsheet Setup (Step by Step)

You need a spreadsheet with two tables.

Table 1: Raw Revenue by Client and Month

Rows: each client Columns: calendar months (Jan 2025, Feb 2025, Mar 2025… through present) Cells: revenue from that client in that month (0 if not active)

Add one additional column at the left: “Cohort Month”, the month the client signed their first contract.

Table 2: Cohort Revenue (Normalized)

Group clients by cohort month. For each cohort, create a row. Columns are now Month-Since-Start: M0, M1, M2, M3… through M12.

M0 = the first month the client signed M1 = one month after signing …and so on

Each cell is the total revenue from that cohort in that relative month.

This normalization is the key step. It lets you compare a cohort that started in January with one that started in September, they’re both measured in months-since-start, so they’re directly comparable.

What You’re Looking For: Four Patterns

Pattern 1: The Early Churn Cohort

A cohort where revenue drops sharply by M2 or M3, 40%+ of starting revenue gone within the first 90 days. This typically indicates an expectation-setting failure during sales. Clients who signed expecting X, received Y, and left when they saw the gap.

If one cohort shows early churn and others don’t, look at what was different about that cohort: acquisition source, contract structure, who you were targeting, what you were promising. The acquisition source is usually the culprit, one particular channel attracts buyers with misaligned expectations.

Pattern 2: The Expansion Inflection

Look at M3–M6 across all cohorts. In most service businesses, this is where the first expansion happens: a client who started on a contained project sees results, trusts you, and expands scope. Revenue per cohort rises rather than declining.

If you don’t see any M3–M6 uptick across multiple cohorts, you’re not having expansion conversations at the right time. The pattern shows you exactly when to initiate them.

Pattern 3: The High-LTV Cohort

Which cohort month produces clients with the highest cumulative revenue by M12? Cross-reference that month with your notes on how those clients arrived. Was there a specific referral source active that month? Did you do an event or publish something that drove inbound? Did you change your pricing or targeting?

The high-LTV cohort is your target profile. Everything you know about where those clients came from, what they valued, and how they engaged is a template for acquisition strategy.

Pattern 4: The Stable but Flat Cohort

A cohort that shows consistent monthly revenue but no growth and no expansion. These clients are reliable but not compounding. They represent a segment worth analyzing for expansion opportunity, they clearly value what you do, but haven’t been given a compelling reason to add scope.

Aggregate revenue numbers show you how much. Cohort analysis shows you who and when. The two most important business decisions, who to pursue and when to push for expansion, are answered by cohort data in ways that total revenue numbers simply can’t provide.

A Real Example: What Most Solos Discover

Here’s a composite of what freelancers typically find in their first cohort analysis. The specifics vary, but the pattern is remarkably consistent.

Cohort from Month A (let’s say May), 4 clients acquired through LinkedIn cold outreach, average M0 revenue $2,200. By M3, two have churned. By M6, only one remains, now at $1,800/month. 12-month cohort total: roughly $28,000 across all four clients.

Cohort from Month B (let’s say August), 3 clients acquired through referrals, average M0 revenue $2,800. All three still active at M6, two have expanded. By M12, cohort total: $68,000 across three clients.

Cold outreach produced one more client. Referrals produced $40,000 more in revenue. But in the month-to-month acquisition stats, the result looked like “4 clients” vs. “3 clients.” The cohort analysis is the first time the 2.4× LTV difference is visible.

The freelancer who sees this data spends less time on cold outreach and more time deliberately cultivating referral relationships. The one who never runs this analysis keeps splitting effort equally between channels that produce wildly different outcomes.

The 30-Minute Build

1. Open a spreadsheet. Title it “Cohort Analysis.”
2. Pull every client you’ve had in the past 18 months. Add them as rows.
3. Add “Cohort Month” as column A.
4. Add calendar months as column headers starting with your oldest client’s start month.
5. Fill in revenue data for each client per month (check your invoices, this takes most of the 30 minutes).
6. In a second sheet, group by cohort month using SUMIF formulas.
7. Create the normalized table (M0 through M12) using OFFSET or manual referencing.

Total time: 25–40 minutes depending on how many clients and how good your invoice records are. If your invoicing software has a client revenue export, use that to speed up step 5.

When to Run It Again

Run a full cohort analysis once per quarter. The quarterly update adds a new cohort (last quarter’s new clients) and extends the trailing months for all existing cohorts.

After four quarters, you have a year of cohort data. After eight quarters, you have two years, enough to see which acquisition sources consistently produce long-term clients and which produce churn risk.

The compounding value of this data is significant. A freelancer with two years of cohort data makes acquisition decisions with genuine evidence. Everyone else is guessing.

The best time to start cohort tracking was the day you signed your first client. The second best time is today. Even six months of historical data, organized by cohort, will reveal patterns that completely change how you think about which clients to pursue and how to develop the ones you have.

Connecting Cohort Analysis to Active Decisions

Cohort analysis is not a retrospective exercise. It informs active decisions:

Acquisition: Double down on the channels that produce your highest-LTV cohorts. Reduce investment in channels that produce high-volume, low-LTV clients.

Expansion timing: If M4–M6 is consistently when your best clients expand, schedule explicit expansion conversations at month 4 for every client. Don’t wait for them to raise it.

Onboarding: If early cohort churn (M1–M3) correlates with a specific acquisition source or contract type, change the onboarding process for those clients. Set expectations more explicitly. Check in more aggressively in the first 60 days.

Pricing: If your highest-LTV cohorts came in at higher price points, raise your floor. Your best clients clearly valued the work enough to pay more and stay longer.

Ready to send stronger proposals?

Build, send, and track proposals in one place so follow-up is easier.

Start your free trial →