Creating Health Scores

Karen Bruer

I am building a customer health score (RYG) which includes various measures of health, and want to create an Overall Health Score that takes into consideration all the other health scores, weights them, and then populates the Overall Health Score field with the right colour.

The various health scores include things like: How well the customer likes Product A, B, C, D and E? How well the Sales organization are doing in terms of expansion opportunities, renewals, etc.? Where the customer is in terms of payments? NPS score?

Does anyone have any thoughts on how to do this? What formula could I use? What weighting system could I use to make sure that each of the various health scores (above) are contributing the right amount of weight to the Overall Health Score?

Scott Peters

Hi @Karen Bruer - A popular method is develop a matrix for each of your scores, like this:

or like this:

You might have a 3x3 matrix like this for one health score, but then have a 4x4 or 5x6 grid for some other score. The text in the grey boxes would be your specific form or drop down options in your fields. For example, "How easy was it to place your order?" might have 5 levels: "Easy, Somewhat Easy, Slightly Difficult, Challenging, and Impossible".

All of these matrices would be stored in a master sheet, hidden from your users.

Then, in your sheet, as they select from a drop down (or fill out a form), the text they pick would have a hidden column called "Weight" or "Score", using Index(Match or Index(Match(Match formula to look up the numeric value from your hidden sheet.

We use this to weigh out our software enhancements, using factors like Customer impact vs. Cost to implement.

I hope this gives you some ideas, let me know if you would like to discuss more.