Customer health scoring: a spreadsheet version

A customer who has quietly halved their orders is easy to miss and costly to lose. A health score in a spreadsheet catches the drop with facts you already hold. It has four signals taken from your records, a rule that turns them into green, amber or red, and a named person who must act on each red. You can build it in an afternoon from your invoice register. It will not predict the future. It gives someone a reason to ring before the customer is gone.

Which four signals?

Choose signals that come from records, move slowly enough to mean something, and need no opinion to read. For a supplier with repeat orders:

• Rhythm: days since the last order, set against that customer's own usual gap.
• Size: the last three orders against the three before.
• Payment: days taken on the last two invoices, against agreed terms.
• Contact: the date of your last real conversation, not the last message you sent.

Leave satisfaction out for now. A survey is a project, and these four are already in your files. If you keep a complaints log with an owner, add open complaints as a fifth.

How is the sheet laid out?

One row per customer. Columns, in order: name; usual gap in days (the average of the last four gaps); days since last order (today minus the date). Then the total of the last three orders and the total of the three before. Then average days to pay, the date of the last real contact, and an owner. Beside them go four score columns, each 0, 1 or 2, and a total.

Write the scoring rule in plain words at the top, so anyone can read it.

Total out of 8. Green is 7 or 8, amber 4 to 6, red 3 or below. A nested IF produces each score and conditional formatting colours the total. The thresholds are your judgement, and you will change them after a few rounds, once you see which customers the sheet flagged and what became of them.

One override: any customer scoring 0 on rhythm is amber at best. A customer who has stopped ordering but pays on time is not healthy.

Who acts on a red?

This column makes the sheet matter. Beside every red write a name and a next step, and go through the reds on a fixed day you choose and keep. Green needs nothing; do not message customers for the sake of it. Amber means the account owner reads the row and decides whether a call is worth making. Red means the owner phones, notes what the customer said, and reports back on the fixed day.

If a red customer is yours alone, put your own name down. Reds that belong to nobody can stay red until the customer leaves. Write beside each call what you learned, because those notes are what let you improve the thresholds later.

The school-stationery supplier that rang the wrong people

Here is an invented case. A supplier in Bareilly sells registers, chalk and art material to some eighty schools, with orders that rise before each term. In its first week the sheet turned half the schools red. They had not ordered for two months. The owner started ringing and met confusion: of course they had not ordered, it was the summer vacation.

The rhythm rule compared each school to a flat "usual gap", and school buying comes in bursts before the April session and again in the monsoon term. The owner changed the rule for these accounts. Rhythm now compares days since the last order with the gap in the same season last year. Sixteen reds fell to four. Three of those were schools that had bought in the June rush of the previous year and not this one; the fourth was a school that had merged with another.

A second lesson came from a different row. One school was green on every count except contact, which had gone quiet for five months. The clerk who always rang had not rung. That clerk had left, and the replacement had been told to get three quotes. The supplier got to quote because the owner called first. The sheet gained a column, "buyer changed?", and a yes makes the account amber whatever the score.

When is a spreadsheet the wrong tool?

If customers order once, or once in several years, rhythm means nothing. Use project milestones or warranty dates. If you have hundreds of accounts, hand-feeding will fail, and the same rules belong in your CRM or billing system. If nobody will own the reds, do not build it: a sheet full of red that you ignore trains you to ignore the next one.

For the thinking behind choosing signals, see what customer health scoring is. This post is the sheet.

Build it for your twenty largest

Open your invoice register, take the twenty customers with the biggest yearly value and fill the columns for those only. Colour them and ring the reds. Add the rest once you trust the thresholds. Do not use the colour to set prices, credit terms or who is served first; it is a prompt to look at the account, not a verdict, and customers go amber for reasons that have nothing to do with you. If you would rather have help choosing the signals for your own trade, tell us what you need; keeping accounts healthy is one thread of sales.