# How to Build a Client Health Score in Google Sheets > A client health score is a single percentage that shows how satisfied a client is, based on several KPIs compared with benchmarks you would usually expect. To build one, choose your KPIs, give each a weight that reflects what your clients care about, divide each result by its benchmark, multiply by the weight, and add them up. Then map the total to ranges such as at risk or critical so you know what to do next. Source: https://fluxomate.com/blog/predict-churn-using-health-score.html Published by Fluxomate (https://fluxomate.com/), 2024-08-28. Section: Full breakdowns. Video: https://www.youtube.com/watch?v=i2McuBiHnxw ## Key takeaways - Health score is one percentage made from several KPIs, each compared with a benchmark. - Weight the KPIs by what your clients care about, and make sure the weights add up to 100%. - Define score ranges with a next step for each, or the number is not actionable. - A Google Sheet with three tabs is enough to start: weights and benchmarks, client data, and a summary. - Out of date data is as useless as no data, so automate updates, with reminders as a first step and data imports as the goal. ## What is a client health score? A health score is a key performance indicator that measures how satisfied a client is. It does this by measuring several KPIs across different areas and comparing them with your usual results. The output is a percentage per client. You define it in three parts: the KPIs you track, the weight of each KPI, and the ranges that tell you how to read the final score. The tool matters less than the structure. We use Google Sheets here because everyone has it, but the same method works in Airtable, SmartSuite or an internal tool builder such as Noloco, which we prefer for a proper long term system. ## Which KPIs should go into a health score? Choose indicators that fit your service. The example is a content agency with ten KPIs in three groups. A more advanced version for a bigger agency might track around twelve indicators across content performance, conversion, client satisfaction and engagement, but a simple version is easier to copy and start with. - Content KPIs, for short form and long form: engagement rate, new followers and impressions. - Conversion KPIs: leads, community growth and money conversations. - Financial KPIs: revenue growth. ## How do you weight each KPI? Indicators are not equal. Revenue matters more to a client than the number of comments on a video. So instead of giving ten indicators 10% each, give different weights, such as 3%, 20% or 7%, to show each one's importance to the overall score. 1. Ask what your clients actually care about. You can interview them and ask which metrics, if they improved, would make them want to stay. 2. Assign a percentage to each group. In the example, content KPIs are 40%, conversion KPIs 40% and financial KPIs 20%. 3. Split each group's percentage across its individual KPIs. The KPIs in the content group, for example, must add up to 40%. 4. Keep the weights easy to edit. If clients care more about leads than engagement, you can change the weight in one cell and the score updates. ## How do you interpret a health score? Set ranges and write what each means and the next step. The example uses these ranges. Without them the number sits there and nobody acts on it. When a client drops below 40%, you should already know the steps to bring them back up. - Over 100%: outstanding. - 80% to 100%: excellent. - 60% to 79%: good. - 40% to 59%: at risk. - Below 40%: critical. ## How to build the Google Sheets tracker The tracker has three tabs. The first holds KPI weights and benchmarks. The second holds client data, one row per client per period. The third is a monthly summary dashboard. 1. On the weights tab, list each KPI with its weight and its benchmark, which is the result you expect. Add a sum of the weights and check it equals 100%. 2. On the client tab, add columns for client name, email, client ID, date, health score and range. Next to each KPI add a KPI performance column. Freeze the header rows and identifying columns so they stay visible as the sheet grows. 3. For KPI performance, divide the result by its benchmark. Use dollar signs on the benchmark reference so it stays fixed when you drag the formula down. 4. For the health score, multiply each KPI performance by its weight, add them together and divide by 100. Lock the weight references with dollar signs too. 5. For the range, use an IF formula that returns outstanding, excellent, good, at risk or critical depending on the score. 6. On the summary tab, add dropdowns for client and date, using data validation drawn from the client tab. If both are empty, show the average across everything. If a client or date is chosen, pull that data. Add the range label and optional conditional formatting that turns a result red when it is below its benchmark and green when above. ## How do you automate health score tracking? Data you do not keep up to date is as useless as no data, and possibly worse. Two kinds of automation help. The first is a reminder automation. For example, every 30 days a Slack message prompts an employee to update the sheet. It is not the best solution, since people have to keep doing it and the data can go out of date, but it is better than nothing. The second is a reporting automation, and it is the one we set up for partners. It pulls data from the relevant platforms, such as Meta, YouTube or Instagram analytics, into the sheet automatically. It is more complicated, because there are several indicators and platforms, and usually means a few separate automations. If you are starting out, build the sheet, begin with reminders, then gradually build the imports. The video above walks through the full build. ## Questions people ask ### What is a client health score? It is a KPI that measures how satisfied a client is. It combines several metrics, each compared with a benchmark and weighted by importance, into one percentage that you can read against defined ranges. ### How do you calculate a health score? Divide each KPI result by its benchmark to get KPI performance. Multiply each by its weight, add them together and divide by 100. The weights should add up to 100%. ### How should I choose the weights? Base them on what your clients care about. Assign a percentage to each group of KPIs, then split it across the indicators in that group. You can also ask clients which metrics would make them want to stay. ### How do I keep the health score data up to date? Start with a reminder automation that prompts someone to update the sheet on a schedule. The better option is a reporting automation that pulls data from platforms such as Meta, YouTube or Instagram analytics into the sheet. Tools used: Google Sheets, Slack, Noloco, Airtable, SmartSuite Free templates for this guide: https://fluxomate.com/resources.html?r=predict-churn-using-health-score