How to build a customer health score
Which inputs to use, how to weight them, the formula for a spreadsheet, and what to do when a score drops.
What a customer health score is for
A customer health score is one number, usually 0 to 100, that sums up how likely an account is to renew and grow. Its job is to tell a customer success manager where to spend time this week. If a score cannot change what someone does on Monday morning, it is decoration.
The signals that predict churn usually exist already: usage drops, the champion stops replying, tickets pile up, the last real conversation was months ago. The trouble is that they live in different places. A health score adds them up so nobody has to.
Step 1: pick four to six inputs you trust
Choose signals you already collect reliably for every account. Common choices:
- Product usage: logins, active users or key actions, compared with what the customer bought.
- Engagement recency: days since the last meaningful contact, such as a call, meeting or QBR.
- Support tickets: open or recent tickets, where more tickets lower the score.
- Sentiment: the CSM's read of the relationship, entered on a fixed scale.
- NPS or another survey score, if you run one.
- License deployment: seats in use compared with seats paid for.
Leave out anything you would have to guess for half your accounts. A missing input quietly drags scores towards the middle and makes the whole score less trustworthy.
Step 2: put every input on a 0–100 scale
Inputs arrive in different units, so convert each one before combining them. Some simple conversions:
- Recency: 100 if the last contact was within 7 days, falling in a straight line to 0 at 60 days.
- Tickets: 100 minus 10 points per ticket, with a floor of 0.
- NPS: rescale −100…+100 to 0…100 with (NPS + 100) ÷ 2.
- Deployment: deployed seats ÷ purchased seats × 100, capped at 100.
Step 3: weight the inputs
Give each input a weight so the weights add up to 100%. Start from a sensible default, for example usage 30%, sentiment 20%, support 15%, recency 20% and NPS 15%. Then check it against history: look at accounts that churned last year and ask whether the score would have flagged them. If it would not, move weight towards the input that did change before they left.
Agree the weights with your CS leader. A score whose weights leadership signed off is far easier to use in a forecast or a QBR.
Step 4: the formula
The health score is the weighted average of your 0–100 inputs:
- Health score = (usage × 30%) + (sentiment × 20%) + (support × 15%) + (recency × 20%) + (NPS × 15%)
In Excel or Google Sheets, put the five inputs for each account in columns B to F and the weights in row 1 of the same columns, then use:
=SUMPRODUCT(B2:F2, $B$1:$F$1) / SUM($B$1:$F$1)
Dividing by the sum of the weights means you can enter weights as relative importance (3, 2, 1.5…) and the result still lands on 0–100.
A worked example
An account has usage 80, sentiment 60, two open tickets (support 80), last contact 20 days ago (recency about 76) and NPS +20 (60).
- (80 × 0.30) + (60 × 0.20) + (80 × 0.15) + (76 × 0.20) + (60 × 0.15)
- = 24 + 12 + 12 + 15.2 + 9 = 72.2
That account is just inside Green. If the CSM misses the next check-in and recency falls to 40, the score drops to about 65, which is Yellow, and that drop should trigger a response.
Step 5: set bands and decide what each one triggers
Split scores into three bands, for example Green at 70 and above, Yellow from 40 to 69 and Red below 40. The most important part of the whole exercise is this: decide in advance what happens when an account moves into a worse band. For example, a check-in call within 3 days for Yellow and an escalation within 1 day for Red. A band change that nobody hears about is the same as having no score.
Mistakes that make health scores useless
- Stale inputs. A sentiment score from six months ago is not a sentiment score. Show when each input was last updated and flag anything older than a month.
- A black box. If a CSM cannot see why an account is Yellow, they will ignore the colour. Show the inputs beside the score.
- Too many inputs. Fifteen signals at 5% each move nothing. Fewer inputs with real weight are easier to explain and to act on.
- No alert on a drop. The value is in the change, not the number. Somebody must be told when a band drops.
- Never revisiting the weights. Check every few quarters which inputs actually moved before churn, and adjust.
Outgrowing the spreadsheet
A spreadsheet is a fine place to design your score. It gets hard to keep running: inputs go stale, nobody sees a drop until the renewal call, and the sheet drifts away from the CRM. OneVio's customer health score uses the same model. It has five weighted inputs with weights you set in Settings, and recency is worked out from the activity log. Scores fall into Green, Yellow and Red bands. When an account drops a band, it shows up in the notification bell and creates playbook tasks for its CSM. Inputs are flagged once they are 30 days old.
A health score works best inside a customer success CRM that also tracks renewals and billing on the same account.
Related
See OneVio with your own renewals.
A 30-minute walkthrough with your accounts in mind. Tell us a little about your team and we'll reply within one working day.
- A 30-minute walkthrough with a CS lead
- Your own separate workspace if you go ahead
Health score questions
What should a customer health score include?
Start with four to six signals you already collect reliably: product usage, how recently you were in touch, support ticket load, your read of the relationship (sentiment) and NPS are common choices. A few inputs you trust beat many you have to guess.
How do you calculate a customer health score?
Convert each input to the same 0 to 100 scale, multiply each by its weight, and add them up, with weights that total 100%. Then split the result into bands, for example Green at 70 and above, Yellow from 40 to 69 and Red below 40.
How often should a health score be updated?
Inputs that come from systems, such as activity dates, can update every day. Inputs a person enters, such as sentiment, should be refreshed at least monthly. A score built on inputs that are months old should be flagged as stale rather than trusted.
Can I build a health score in Excel or Google Sheets?
Yes. Put one account per row, one input per column on a 0 to 100 scale, and use SUMPRODUCT against a row of weights. The hard part is not the formula but keeping the inputs current and making sure someone acts when a score drops.