TL;DR
A customer health score turns scattered product usage, support history, and billing signals into a single number your CS team can act on every morning. You can ship a working v1 model in an afternoon using a spreadsheet, then graduate to a dedicated platform when your account list grows past 150. You need product analytics data, CRM data, and either Google Sheets or a SQL-connected BI tool to get started.
What You Need Before You Start
- Product usage data from your analytics platform. Mixpanel, Amplitude, or Segment all work. If you use a custom database, a SQL export is fine. You need at least 90 days of history.
- CRM data: account tier, contract value, renewal date, and open support tickets. Salesforce or HubSpot both export this easily as a CSV.
- A list of accounts that churned in the past 12 months. You will use this to validate your model. You need at least 20 churned accounts for the weights to mean anything.
- NPS or CSAT data if you run surveys. Optional, but valuable.
- Read access to your billing system for payment history and contraction signals.
- Google Sheets (free) for the v1 model. If you already have Looker or Power BI wired to your data warehouse, use that instead.
- Optional: Gainsight, ChurnZero, or Totango for the automated version you will build toward in Step 9.
Step 1: Define What Healthy Looks Like for Your Product
Before you open a spreadsheet, write down two or three things a healthy customer does consistently. Be specific. “Uses the product” is not specific enough. “Creates at least one report per week and has connected a data source” is specific.
Pull up your churned accounts list and your ten happiest renewing accounts. Compare their behavior side by side. You are looking for two or three behaviors where healthy accounts differ most clearly from churned ones. Those become your core usage signals.
For a project management SaaS, healthy signals might be: active users as a percentage of purchased seats, number of features used beyond the core workflow, and number of integrations enabled. For a data tool, it might be query volume per user, dashboard views per week, and whether an admin has invited more than one teammate.
Write these down before moving on. You should now see a short list of three to five behaviors that are measurably different between healthy and churned accounts. If you cannot find a difference, your data coverage is the real problem.
Step 2: Map Every Signal to a Real Data Source
For each signal you identified, write down exactly where the data lives, how frequently it updates, and how you will pull it. Vague data plans create broken models.
Build a simple mapping table:
| Signal | Source | Refresh frequency | How to pull |
|---|---|---|---|
| Weekly active users | Mixpanel | Daily | API / CSV export |
| Open support tickets | Zendesk | Real-time | CSV export |
| NPS score | Delighted | Monthly | CSV export |
| Days to renewal | Salesforce | Real-time | CRM report |
| Feature adoption % | Product DB | Weekly | SQL query |
Gaps will surface here. Maybe your NPS data only covers 40% of accounts. Maybe support tickets live in two different tools. Fix gaps before you start scoring. A score built on patchy data will send your team chasing the wrong accounts.
You should now see a complete table where every signal has a source, a refresh cadence, and a pull method. Any row missing one of those three fields is a gap to close before you move forward.
Step 3: Choose Your Scoring Dimensions
Group your signals into three to five named dimensions. Dimensions are the categories that roll up into the final score. Keeping them distinct makes it easy to spot which part of the customer relationship is breaking.
Common dimensions for B2B SaaS:
- Product engagement: login frequency, active users per seat, feature breadth
- Business outcomes: whether the customer is achieving the goal they bought for
- Relationship health: NPS, executive sponsor contact frequency, open escalations
- Support health: ticket volume, average ticket age, CSAT on recent resolutions
- Financial signals: on-time payments, contract growth or contraction, days to renewal
You do not need all five. Three solid dimensions beat five half-measured ones. Pick the dimensions you can fully populate with real data today.
You should now see a short list of dimensions, each containing two or three specific signals, with every signal mapping back to a confirmed data source from Step 2.
Step 4: Assign Weights to Each Dimension
Not every dimension deserves equal weight. Weighting is where your model stops being generic and starts reflecting your actual business. Assign each dimension a percentage that sums to 100, then validate against your churned accounts list.
A reasonable starting point for mid-market SaaS:
| Dimension | Weight |
|---|---|
| Product engagement | 40% |
| Business outcomes | 25% |
| Support health | 20% |
| Relationship health | 15% |
Run your churned accounts through this weighting. If the model scores at least 60% of them below 50 at least 60 days before their churn date, your weights are working. If it does not, adjust the weighting toward whichever dimension showed the sharpest drop before actual churn events.
You should now see a weighted dimension table and a rough validation pass against 12 months of historical churn. A solid v1 model catches at least 60% of churn risk at least 30 days in advance.
Step 5: Define Score Thresholds for Each Signal
Inside each dimension, score every individual signal on a 0-10 scale with explicit thresholds. Vague thresholds create inconsistent results when different people update the model.
Example thresholds for Product Engagement signals:
Active Users as % of seats (last 30 days):
>= 80% → 10
60-79% → 7
40-59% → 5
20-39% → 3
< 20% → 0
Feature Adoption (distinct features used in last 30 days):
>= 5 → 10
3-4 → 7
2 → 4
1 → 1
0 → 0
In Google Sheets, translate these into a nested IFS formula:
=IFS(B2>=0.8, 10, B2>=0.6, 7, B2>=0.4, 5, B2>=0.2, 3, TRUE, 0)
Where B2 holds the active user percentage as a decimal. Copy this pattern for every signal column. Keep the threshold definitions in a separate tab so your team can reference them without digging through formulas.
You should now see a numeric score from 0 to 10 in each signal column for every account row in your spreadsheet.
Step 6: Roll Up to a Composite Score
Combine signal scores into dimension scores using a simple average, then combine dimension scores into the final health score using your weighted percentages.
Dimension score (example for Product Engagement with two signals in columns C and D):
=AVERAGE(C2, D2)
Final health score (weighted composite, dimensions in columns E through H):
=(E2*0.40) + (F2*0.25) + (G2*0.20) + (H2*0.15)
This produces a score between 0 and 10. Multiply by 10 if your team prefers a 0-100 scale. Either convention works, just keep it consistent.
Spot-check five accounts you know well. Does the composite score match your intuition? If three out of five feel wrong, go back to Step 5 and tighten your signal thresholds. The goal is a model your most experienced CSM would nod at.
You should now see a final health score column populated for every account.
Step 7: Segment Accounts Into Health Bands
A raw score of 6.4 is not actionable. “At-risk” is. Map your numeric scores to named bands so every CS rep knows what to do without interpreting numbers.
A standard three-band system:
| Band | Score range | Default action |
|---|---|---|
| Healthy (Green) | 7.0 – 10 | Run expansion motion, invite to reference program |
| Neutral (Yellow) | 4.0 – 6.9 | Schedule a check-in call, identify blockers |
| At-Risk (Red) | 0 – 3.9 | Escalate immediately, activate save playbook |
Add a Band column to your sheet:
=IF(I2>=7, "Green", IF(I2>=4, "Yellow", "Red"))
Where I2 is your final health score. Apply conditional formatting: green fill for Green, yellow fill for Yellow, red fill for Red. Red accounts jump out before your team even reads the account names.
You should now see every account labeled and color-coded. Share this view with your CS team before you move on to automation.
Step 8: Build a Dashboard for Daily Visibility
A spreadsheet works well for 50 accounts. Beyond that, you need a live view that does not require anyone to open and scroll through a file.
Connect your scoring sheet to Looker Studio (free, formerly Google Data Studio). In Looker Studio: click Add data > Google Sheets > select your scoring workbook and map the health score and band columns. Build three charts:
- A donut chart showing the Green/Yellow/Red distribution across your full book
- A table sorted by lowest health score, with renewal date and account owner visible
- A 90-day trend line per account showing score movement over time
The trend line is where you catch accounts in slow decline before they hit Red. A customer who drops from 7.2 to 6.8 to 6.1 over three months tells a different story than a customer who scores 6.1 consistently.
The Looker Studio setup takes under 30 minutes. Refresh your sheet daily (manually or via script from Step 9) and the dashboard stays current automatically.
You should now see a dashboard that your CS team can open each morning to see which accounts need attention that day.
Step 9: Automate Data Ingestion and Alert Triggers
Manually refreshing the spreadsheet weekly is sustainable for month one. It is not sustainable after that. Automate the data push so the model stays fresh without anyone touching it.
A lightweight Python approach using the Google Sheets API:
import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Customer Health Scores").sheet1
# Push fresh active user percentage from your product database
sheet.update("B2", [[new_active_user_pct]])
Run this as a daily cron job at 6 AM so your team arrives to fresh data. Pair it with a Slack alert that fires when any account drops into the Red band:
if health_score < 4.0:
requests.post(SLACK_WEBHOOK_URL, json={
"text": f"ALERT: {account_name} dropped to Red ({health_score:.1f}) — renewal in {days_to_renewal} days"
})
If you prefer no-code, Zapier can connect your analytics source to the spreadsheet with a daily trigger and send a Slack or email alert on score thresholds.
You should now see health scores updating automatically each morning and your CS team receiving proactive alerts without having to monitor a dashboard manually.
Common Mistakes To Avoid
- Weighting NPS too heavily. NPS is a lagging indicator. By the time NPS drops, the customer is already partially checked out. Keep NPS at 10-15% of the total weight and let usage signals lead.
- Using logins as a proxy for engagement. A user can log in and stare at a blank screen. Measure actions that correlate with value delivered, not just session starts.
- Building a score you cannot explain to a customer. If your CSM cannot tell an account why their score is 4.2 and what to change, you have a black box. Every score should trace back to specific behaviors the customer can act on.
- Skipping historical validation. Building weights on intuition alone without checking them against actual churned accounts means you might be measuring the wrong things entirely. Always run the churned accounts test from Step 4.
- Setting thresholds once and walking away. Your product changes, your customer base evolves, and what “healthy” looks like shifts over time. Run a calibration review every quarter against the most recent cohort of churned accounts.
- Scoring new accounts the same as mature ones. Onboarding customers have noisy usage patterns. Exclude accounts under 60 days old from your standard model or build a separate onboarding health track.
When To Level Up
The spreadsheet model starts creaking around 150-200 accounts, or when your CS team is spending more time updating the model than actually talking to customers.
That is the inflection point where a dedicated CS platform pays for itself. Gainsight integrates with Salesforce, your product database, and your support tool. It refreshes scores automatically, runs playbooks on trigger conditions, and tracks CS activity per account without anyone touching a spreadsheet. ChurnZero is a lighter and cheaper option popular with teams managing 200-500 accounts. Totango fits well when you have a high volume of SMB accounts that need automated digital touches rather than human-led interventions.
The clearest sign you need to make the move: your team is spending Friday afternoons on data hygiene instead of customer calls, or your model is consistently too slow to surface declining accounts before the renewal conversation is already awkward.
For the tools that take this work to the next level, browse the data analysis tools category and the customer success metrics guide for platform comparisons. The churn prediction models guide covers how to build on top of your health score with predictive analytics once your data foundation is solid.
Frequently Asked Questions
How many signals should I include in my health score?
Start with six to ten signals across three dimensions. More signals add complexity without necessarily improving predictive accuracy. Once you have validated your v1 model against historical churn data, add or remove signals based on which ones actually correlated with outcomes.
Should I include contract value or account size in the health score?
Financial signals like days to renewal and payment status belong in the score, but keep their combined weight at 10-15%. A large account with declining usage is still at risk. Over-weighting contract value causes teams to neglect high-revenue accounts that are quietly churning because the number looks big and safe on paper.
How often should the score refresh?
Daily for at-risk and high-value accounts. Weekly for the rest. The refresh cadence matters less than having alerts that fire on meaningful score drops. Your team should act on changes automatically, not by remembering to check a dashboard.
Can I use one health score model across all customer segments?
Generally no. Enterprise accounts and SMB accounts behave differently, have different success metrics, and interact with your team through different channels. Build separate models if your segments differ significantly in seat count, use case, or how deeply they integrate your product. The product usage analytics guide covers how to slice behavioral data by segment before building your model.
What does a healthy score distribution look like?
A typical healthy SaaS book runs 60-70% Green, 20-30% Yellow, and under 15% Red. If your Red band exceeds 20% consistently, the signal is usually in your onboarding or your product, not in how your CS team is working the accounts.
Bottom Line
Building a customer health score is about replacing gut feel with a consistent signal your whole team agrees on. You start by defining what healthy behavior actually looks like for your customers, pick three to five measurable dimensions, set explicit thresholds for each signal, weight the dimensions against real churn history, and build a spreadsheet your CS team can act on before end of week. Automate the data refresh as soon as the manual process becomes a time sink. Recalibrate the model quarterly so the weights stay honest as your product and customer base evolve. The model does not need to be perfect on day one. It needs to be better than intuition, and it needs to give every CSM a clear reason to call a specific account today. When you hit the ceiling of what a spreadsheet can do, the data analysis tools category has detailed comparisons of the platforms built specifically for this work.