Customer Segmentation Analyser

What this tool does

CM8-281 ranks customers by what they are worth rather than by what they spend. One row per customer — revenue, gross margin, orders, days to pay, an estimate of what it costs to serve them, a judgement on the relationship — and the tool works out the contribution each leaves behind, sorts the book into ABC classes, measures your dependence on the largest accounts, and produces an action list. It answers the question most businesses answer wrongly: where should the effort go?

Everything runs inside this single file — no account, no upload, no network request — so a document naming your customers, their margins and their payment behaviour never leaves this computer.

Why revenue ranking misleads

A revenue league table answers "who buys the most from us". It gets treated as if it answered "who is worth the most to us", and those are different questions with routinely different answers.

The first distortion is margin. A customer at a fifth of your revenue on a 35% margin out-earns a customer at a third of your revenue on 14%. Large customers know they are large and price accordingly — the annual negotiation, the volume rebate, the price-hold clause. Revenue rank and gross profit rank therefore drift apart as the top of the book grows. The second distortion is what it costs to serve the customer at all.

The cost to serve — the figure almost nobody tracks

Standard accounts stop at gross margin and bury the rest in overhead, spread as though every customer consumed it equally. They do not. One places a standard order on a standard lead time and pays on time. Another changes drawings after release, needs a visit whenever something slips, wants special packaging, generates rework, holds stock on your shelf and takes three reminders to pay. Both carry the same overhead recovery in the accounts. Only one is expensive.

The service cost field makes that difference explicit. Build it from the same activity list for every customer — account-management time, expediting and premium freight, special packaging, labelling and documentation, rework and warranty, technical support, dedicated stock, credit control. It will be an estimate; estimate it anyway. A consistent estimate ranks your customers correctly even if every figure is 20% out, while no figure guarantees the ranking is wrong. What comes out is usually uncomfortable: the dominant account everyone protects often earns two or three per cent, and somewhere mid-book sits a respectable revenue line whose contribution is below zero.

ABC classification, and what to do with each class

Rank customers by revenue, largest first, and accumulate. Those inside the first cutoff — 70% by default — are class A; those between the two cutoffs are class B; the rest are class C. A typical book puts a handful in A and a long tail in C. The classes are not grades; they are instructions about attention:

• A — protect and deepen. Named ownership, planned contact, more than one relationship inside the account. Deepening is defensive as well as commercial: an account with four relationships is far harder for a competitor to take than one with a single contact.
• B — grow selectively. Where growth actually comes from. Some B customers are A customers nobody has asked for more; others are permanently mid-sized and perfectly good as they are. The growth-potential column is your judgement about which is which.
• C — serve efficiently, and do not lavish attention. The temptation with the tail is to ignore it or to over-serve it, and over-serving is the more expensive mistake. Standard terms, standard lead times, minimum bespoke work — with one exception, the small high-margin high-growth account, which is why contribution margin sits beside class in the ranking.

The largest customer is always class A, even where that account alone exceeds the cutoff; any other treatment produces a book with no A class.

The formulas

Net contribution is not net profit: it excludes general overhead that would exist either way. Use it to rank customers, not to report what the business earned.

Concentration risk, stated plainly

One customer above roughly a quarter of your revenue is a strategic risk, regardless of how good the relationship feels. That is not a comment on the customer; it is arithmetic about your fixed costs if they insource, get acquired or put the work out to tender. The concentration curve draws it to scale: past half the book within its first two or three points, one decision taken in a meeting you are not in can remove a year of profit.

The relationship chart is the early warning beside it. Revenue behind a fragile, single-contact relationship depends on one person staying, staying well disposed and staying in the same job. Large and fragile together is the combination worth losing sleep over — and the cheapest fix here, because widening a relationship costs meetings, not money.

Loss-making customers and the three honest options

When a customer shows a negative net contribution there are exactly three responses. Everything else is a way of not choosing.

• Reprice. Take the contribution figure into the conversation. Customers who understand that expediting and short-notice changes cost money will often accept paying for them, and an increase tied to specific activities lands better than a general uplift.
• Re-scope the service. Often better than repricing and easier to agree. Standard packaging, standard lead times, batched deliveries, a minimum order value. The customer keeps the price and gives up the cost driver.
• Let them go. A legitimate decision, and almost always postponed too long. Serving an account at a loss quietly consumes capacity that could serve a profitable one. If repricing has been refused and re-scoping has failed, declining to renew is the honest end of the conversation — with notice, a handover and no burnt bridges.

Before acting, test the figure: the service cost estimate behind it is the least certain number in the row.

Dormant and lapsed accounts

A customer who has not ordered for six months is flagged as dormant revenue at risk; one who has stopped altogether is lapsed. Between them they are the cheapest revenue available to most businesses — people who already know what you do, with the cost of acquisition already sunk. Most dormancy has a specific cause: a contact moved, a restructure sent volume in-house, one delivery went badly and nobody followed up. Very little is a decision never to buy from you again. Keeping these accounts in the register with their old revenue attached is the only way they reach an agenda.

Payment behaviour is part of customer value

Two customers with identical revenue and margin are not equally valuable if one pays in 28 days and the other in 68. The slow payer borrows from you interest-free for forty extra days on every invoice and consumes credit-control time doing it. Neither cost appears in gross margin.

Record days from invoice to cash, not the terms you granted — the two are frequently unrelated. The tool averages across the book weighted by revenue, so a large slow payer moves the figure more than a small one. Customers paying more than fifteen days past terms reach the action list, where the answer is rarely more chasing: it is usually a shorter term, payment on account, or an escalation to somebody senior enough to care.

Running the review

Once a year suits most businesses, shortly after the year end while the figures are fresh. It is not a monthly exercise: relationships move slowly, and frequent reclassification produces churn, not insight.

1. Pull revenue, margin, orders and average days to pay for every customer over the same twelve months.
2. Estimate service cost for all of them from one agreed activity list, with the people who do the work — sales, planning, quality, credit control. They know which accounts are painful.
3. Read the tiles, then the concentration curve and the contribution chart.
4. Work the action list. Each line gets a name and a date, or it did not happen.
5. Save the file, and next year compare: did the loss-makers get repriced, did the fragile relationships get widened, did concentration fall?

FAQ

Should the service cost include the salespeople? Include time spent on that specific customer — visits, calls, quotations, complaints. Exclude general prospecting and the cost of running the department: that is overhead, and spreading it back defeats the measure.

What if I cannot estimate service cost at all? The tool still ranks by revenue, margin, order value and payment — but the interesting finding will be missing. Even a crude three-band estimate, light, normal or heavy, costed at a standard rate, is enough to change the ranking.

Why is a class C customer showing the best contribution margin? Class is set by revenue, contribution by economics, and small specialised accounts are often the most profitable per unit of revenue. That is a finding, not an error: it usually means a segment worth pursuing.

Can I use monthly rather than annual figures? Yes, provided every column uses the same period. An annual revenue against a monthly service cost is confidently and completely wrong.

Saving your work

Customers, settings and the report header are written to this browser's local storage as you type. That storage belongs to one browser on one computer: another browser, a private window or a clean-up tool that clears site data will not have it.

Treat Export .json as the real save — one file containing everything, restored anywhere by Import .json, and the file you keep so next year's review can be compared with this one. Export CSV gives you the register for spreadsheet work. Reset asks twice, then erases everything stored here, with no undo. A file naming your customers, their margins and their payment behaviour is among the most sensitive your business holds.

Accuracy & disclaimer

The arithmetic is simple and the tool does it faithfully. The uncertainty is in the inputs, overwhelmingly in one: the service cost is an estimate unless you time-track it, and it decides whether a customer appears as your best or your worst. Be consistent rather than precise — the same activity list, rates and period for every customer — and read the output as a ranking to investigate, not a set of true costs.

This is a management analysis aid, not accounting, tax, credit or legal advice, and nothing in it is a recommendation to raise a price, change terms or end a relationship — those are commercial judgements that belong to you, informed by facts this tool cannot see.