How Do I Build a Customer Health Score Template in a Spreadsheet?

A customer health score is a weighted sum of a few signals, each scored on the same scale. The template question is which signals, what weights, and what rule turns raw data into a score. Our answer, after reading 3,628 public reviews and the r/CustomerSuccess threads on the subject, is five categories, weighted toward the signals that move before the customer has decided anything.

The reviews are blunt about what happens without a written template. Of the 2,094 sentences where reviewers describe the problem they bought a platform to solve, 37 mention a spreadsheet or Excel, and the story is the same each time: the sheet was the health score until nobody maintained it. Another 731 reviews describe the job as "one place" or "getting out of spreadsheet hell". The sheet did not fail because it was a sheet. It failed because the rules lived in people's heads.

The weights are a starting split, not a law. Product usage gets the most because it is the only category that changes before a renewal conversation exists. Sentiment gets the least because surveys are answered by a small, unrepresentative slice of the base. If you want to test other splits before committing, the customer success scorecard builder runs the same five-category model and shows how many points each category contributes.

Budget three hours for the first version, most of it spent pulling exports. Do it in one sitting so the rules are written while the data is in front of you.

1. 1

   Export the account list from billing

   One row per paying account: name, ARR, renewal date, start date, plan, licensed seats. Billing is the source of truth because it cannot contain a customer who is not paying. Add a column for the named CSM.

2. 2

   Add five signal columns and write the rule above each

   Paste the rule from the table into the header cell as a note. The rule is the product; the number is a by-product. If a rule needs judgement to apply, tighten it until it does not.

3. 3

   Fill each column from its export

   Product usage from your analytics tool or a monthly active-users export. Last contact from CRM activity. Open tickets and escalations from the helpdesk. Invoice status from billing. Last survey response from the survey tool. Use VLOOKUP or XLOOKUP on the account name or ID. Do not type numbers by hand; a typed number is an opinion.

4. 4

   Compute the score with one formula

   In the score column: =SUMPRODUCT(weights, signals)/100, where weights is a fixed row holding 35, 25, 15, 15, 10 and signals are the five cells for that account. The result is 0 to 100.

5. 5

   Band it with conditional formatting

   Four rules on the score column: 75 and above green, 60 to 74 amber, 40 to 59 orange, under 40 red. Write the action for each band in a legend at the top of the sheet: healthy means run the expansion conversation, watch means fix the weakest signal before renewal season, at risk means open a rescue play this week, critical means escalate today.

6. 6

   Add a previous-score column and a delta

   Before each refresh, copy the current score into a last-month column. Delta is current minus previous. Sort by delta ascending. A healthy account that dropped 20 points is a better use of Tuesday than a critical account that has been critical for a year.

7. 7

   Add the new-customer exception

   For any account under 90 days old, replace the usage rule with three onboarding milestones (admin configured, first integration live, first weekly active user outside the champion). Score 100 if on plan, 50 if one milestone is late, 0 if two are. Without this, every new account scores red and your CSMs stop trusting the sheet in month one.

8. 8

   Put the refresh in the calendar

   First working Monday of the month, 90 minutes, one owner. Score refreshes that depend on "when someone has time" stop within a quarter, and a health score nobody refreshes is worse than none, because people keep reading it.

Here is the template applied to five accounts from a fictional 120-account book, using the rules above. The names are invented; the patterns are the ones we see most often.

A login-only score would have called Bluefin healthy and made Meridian the only red. Both calls are wrong, and that is the whole argument for five signals instead of one.

The most common failure of a spreadsheet score is not the maths. It is a CSM nudging a cell to make a review go faster. The r/CustomerSuccess thread that asked for health score examples describes it exactly.

Four rules keep the sheet honest, and each is cheap:

• Three values only. A signal is 0, 50 or 100. "Feels like a 70" is not available, so there is nothing to nudge.
• No override column. If a CSM believes the score is wrong, they change the input data or propose a rule change at the monthly refresh, with an example account. The rule improves for everyone or it does not change.
• Every input is a lookup. The moment a column is typed by hand, it becomes the column people argue about.
• Log rule changes in a tab with the date. A score whose rules moved twice this quarter cannot be trended, and the trend is the part that predicts.

The one score that does need a human is sentiment, and only because survey coverage is thin. Allow the CSM to score sentiment 0 from a written complaint, but require the complaint to be linked in the cell note.

The person who wrote the most-read thread on this subject had already built the sheet. He was asking why it was not helping.

Most health scores we have seen are a weighted average of whatever data was easy to export, refreshed when someone remembered. The template above is better than that because the rules are written down, but it has four limits you should know before you present it to leadership.

• It is monthly. A usage drop in week one shows up at the next refresh, up to five weeks later. The account in the quote above churned in that gap.
• It sees seats, not depth. "Active" in most exports means logged in. A customer who logs in to export their data before leaving looks active.
• It has no history until you give it some. The delta column is the most predictive thing in the sheet and it is empty for the first month and thin for three.
• It is one score for one product. If you sell two products or bill by parent and child accounts, the sheet needs a row per relationship, and most teams give up here.

None of these are reasons not to build it. They are the reasons the sheet is a 90-day tool, and the list of things to check when it says green and the customer churns anyway. That failure has nine known causes, and we walk through each in why your health score is wrong.

The sheet stops working at a predictable point, and it is a capacity point rather than a data point. Here is the check we would run each quarter.

Zero or one of these true: keep the sheet, tighten the rules, and spend the money elsewhere. Two or more: the score is no longer the bottleneck, the refresh is, and the honest comparison is the cost of a platform against the hours the refresh consumes plus the churn that slipped through the gap. We set that comparison out, with the admin-time figures from the review corpus, in spreadsheet to customer success platform.

GainTrace runs the same five-signal model across every account and re-scores as the data changes, with the inputs pulled from billing, CRM, product usage and support rather than exported by hand. Product signals replaces the usage column with depth and trend rather than logins, and churn prediction ranks the amber accounts so a CSM knows which of the 40 to call first. The rules stay visible: every score breaks down signal by signal, the way the sheet does.