How to Segment a Customer List by Lifecycle Stage

Why this matters

A customer list with no structure gets used exactly one way: somebody searches it when the phone rings. Everything else you might do with it, from planning next quarter's workload to noticing that your best customers are drifting, requires knowing where each name sits.

This is the mechanical version of that job. Not which customers are worth the most, which is a value question answered by grading, but where each customer is in time relative to their own history. The two are different axes and mixing them is the most common way a segmentation project produces a spreadsheet nobody opens twice.

The whole thing runs on four columns and an afternoon. No software required, though software will do the arithmetic for you once the definitions are settled.

Step 1: Get four columns out of your records

For every customer, you need exactly this:

Two definitional traps to settle before you export anything.

Completed means completed. Quotes, cancellations, and no-shows are not jobs. Counting them inflates job count and pulls last-job-date forward, which makes departed customers look active. If your records make this hard to filter, fix that first; everything downstream inherits the error.

One row per property, not per person. A married couple entered twice, or an owner and a property manager on the same address, will show as two half-strength customers and neither will ever look established. Deduplicate on address before you compute anything.

Step 2: Compute months since the last job

One subtraction, as of a fixed date. Use the first of the month you are running this, and write that date at the top of the sheet.

Using a fixed as-of date rather than "today" is what makes the sheet comparable to last month's. If one run is as of the 3rd and the next is as of the 27th, every customer looks 24 days more dormant and you will read a data artifact as a trend.

Step 3: Compute each customer's own interval

For customers with three or more completed jobs, list the gaps in months between consecutive jobs and take the median.

Median, not average, and the reason is specific. An emergency call in the middle of a planned rhythm creates one very short gap. Averaged in, it drags the interval down and the customer starts looking overdue while behaving normally. The median ignores the outlier, which is exactly what you want when you are trying to describe a rhythm rather than a history.

Customers with two jobs have one gap, which is not a rhythm. Customers with one job have none. Both fall to a backstop, covered in Step 5.

Step 4: Apply the rules in order, top to bottom

Order is the point of this step. The rules must be evaluated as a sequence, first match wins, or a customer with one job and four years of silence will qualify for two buckets and land in whichever one the sorting happened to reach first.

1. Lost if a departure reason is recorded. Reason first, always, because a recorded fact beats a computed guess.
2. Prospect if completed job count is zero.
3. First-time if job count is 1 and months since last job is under the backstop.
4. Repeat if job count is 2 and months since last job is under the backstop.
5. Established if job count is 3 or more and months since last job is at or under 1.5 times their personal interval.
6. Slipping if job count is 3 or more and months since last job is between 1.5 and 2.0 times their personal interval.
7. Dormant for everything that falls through.

Write the rules in that order at the top of the sheet. Six months from now somebody else will run this, and a rule set that has to be re-derived will be re-derived differently.

Step 5: Handle the records that do not compute

Three populations need explicit handling or they quietly break the sort.

One and two-job customers. They fall to the shop-wide backstop, commonly 24 months, because they have no personal rhythm to measure against. Do not fabricate an interval from a single gap.

First-time customers past the backstop. Technically they qualify as dormant, and it is worth splitting them out into their own bucket anyway. A first-timer who never returned is a follow-up failure, not a lapsed relationship, and the two need completely different treatment. Mixing them means your dormant list gets dominated by people who were never actually customers in any meaningful sense.

Records with no usable date history. Old imports, hand-entered names, migrated data. Put them in a repair bucket rather than assigning a stage. A record you cannot compute is not dormant, it is unknown, and calling it dormant hides how much of your list is unusable.

Step 6: Cross stage with value only at the end, and only two bands

The temptation after building this is to cross the seven stages against four value grades, producing 28 cells. Nobody works a 28-cell matrix.

Cross it against two bands: the top third of customers by trailing 24-month job count, and everybody else. That gives you 14 cells and only four of them change what you actually do:

That is the entire payoff of crossing the axes. Everything else takes the same action the stage alone would have produced, which is a good sign that you stopped at the right level of detail.

Step 7: Refresh on a fixed day, monthly

Same day every month, same as-of date convention, same rule order, by the same person if possible. Twenty to thirty minutes once the first build is done.

The reason for monthly rather than quarterly is that Slipping is the only stage with a time-sensitive action attached, and a quarterly refresh means a customer can sit in Slipping for eleven weeks before anybody sees them. By then they are dormant and the intervention that would have worked is no longer available.

Worked example: sorting 480 records in an afternoon

A shop exports 480 customer records and works through the steps.

After Step 4, by job count:

• 22 have a recorded departure reason, so they are Lost before any arithmetic runs.
• 61 have zero completed jobs. Prospects, mostly old quotes.
• 138 have exactly one completed job.
• 89 have exactly two.
• 170 have three or more, so they get a personal interval.

Those five add to 480.

Splitting the 170 with a computable interval, using their own medians:

• 61 are inside 1.0 times their interval.
• 44 are between 1.0 and 1.5 times, so still Established but due.
• 31 are between 1.5 and 2.0 times. Slipping.
• 34 are past 2.0 times. Dormant.

Those four add to 170, and the first two combine into 105 Established.

Splitting the 89 two-job customers against the 24-month backstop: 52 are inside it and stay Repeat, 37 are past it and go to Dormant.

Splitting the 138 one-job customers against the same backstop: 59 are inside it and are still live in the follow-up sequence, 79 are past it and never returned.

Final distribution: 61 Prospect, 59 First-time active, 79 First-time never returned, 52 Repeat, 105 Established, 31 Slipping, 71 Dormant, 22 Lost. Those add to 480.

Now read it, and read the biggest number first.

The largest single actionable bucket is not the dormant list. It is 79 customers who used the shop exactly once and never came back, which is about 16 percent of the 480-record list. Against the 138 records that only ever had one job, 79 of them never returned, which is 57 percent of all first-time records this shop has ever created. More than half of everyone who ever tried this shop used it once.

That single number reframes where the next quarter's effort belongs. The shop was about to spend its attention on the 71 dormant records. Those 71 are worth working, and they are also the harder, colder half of the problem. The 79 is both larger and the symptom of a leak that is still running today, which means fixing it improves next year without any campaign at all.

One honest caveat on that 57 percent before anyone panics with it: a list built over many years carries first-time records from before the shop had any follow-up process, and some of those addresses have changed hands entirely. Recompute the same ratio using only first visits from the last 24 months. If it comes back near 57 percent, the leak is current. If it comes back much lower, the old records are describing a shop that no longer exists and the current number is the one to act on.

Second read: the live customer base is 59 plus 52 plus 105 plus 31, which is 247 of the 480 records, or about 51 percent of the list. Roughly half of what the shop calls its customer list is prospects, one-time losses, dormant names, and lost accounts. That is normal and not a crisis, but it means every per-customer figure computed against 480 is understated by about half, and any plan built on "we have 480 customers" is planning for a business that does not exist.

Third read: the Slipping bucket is 31 names. At six to eight calls a day that is a week of somebody's spare time, and it is the highest-yield week in the whole exercise, because those 31 are customers with three or more jobs who have not yet gone anywhere. Compare that to the 71 dormant names, which need a prepared reason each and a full reactivation cycle to work properly. The small bucket is the urgent one and the large bucket is the scheduled one, and shops routinely get that backwards because the large bucket looks more important.

How to verify you got this right

Every record has exactly one stage and the counts add to the total. If they do not add up, the rules are overlapping, which means the top-to-bottom order is not being applied. This is the most common build error and it hides easily.

Sample ten records and re-derive their stage by hand. Not spot-checking the formula, actually reading the job dates. Errors here come from bad source data far more often than from bad arithmetic, and only reading the history catches a customer whose three jobs are actually one job entered three times.

The repair bucket is visible and shrinking. If unusable records are being silently assigned a stage instead of being set aside, your list quality problem is invisible and permanent.

Next month's run differs from this month's. Records should move. If the distribution is identical, somebody copied the sheet rather than rebuilding it, which is the ordinary fate of any report that takes more than half an hour and has no named owner.

The action cells are actually being worked. The sort is not the deliverable. If the Slipping list has been sitting for two months with no calls logged against it, you have built a report, not a segmentation, and the next honest step is to shrink the model until the actions fit the hours you have.

References

• U.S. Small Business Administration (SBA), customer relationship management guidance for small business
• Trade-standard practice for service-history records and maintenance recall scheduling
• See related: The Customer Lifecycle Stages a Service Shop Actually Has, The Dormant Customer Definition Worth Setting, Grading Your Customers A, B, C, and D, Customer Segmentation Strategy