Customer success teams need a single health number for portfolio reviews. Weighted components beat gut feel alone.
customerHealthScore blends usage (50%), normalized NPS (40%), and a ticket penalty term (10%).
NPS from -100..100 maps to 0..100; each open ticket subtracts 5 points from a 100 baseline, capped at 40 penalty.
Tune weights with CS leadership — store them in Config if they change often.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Customers | A id | Customer key |
| Customers | B usageIndex | 0–100 usage |
| Customers | C nps | -100..100 |
| Customers | D openTickets | Count |
| Customers | E healthOut | 0–100 score |
What this script does
Weighted health score with ticket penalty and NPS normalization.
Prerequisites
Usage and NPS synced; ticket counts current.
- NPS scale agreed
- Weights documented
- Run nightly
Walkthrough
usage 80, nps 50, tickets 0 → high health near 80+.
Edge cases
Missing NPS as 0 pulls score down — use blank handling if needed.
- Clamp 0–100
- Ticket penalty capped
How to test
Many tickets should not drop the ticket term below 60 before weighting.
Hardening for production
Segment scores by plan; exclude trial customers.
Variations
Add payment delinquency flag as hard fail.
Full code: customerHealthScore()
Refresh usage/NPS/tickets, then run customerHealthScore() before QBRs.
/**
* Customer health score from usage, NPS, and support tickets.
*/
const CUST = "Customers";
// A id, B usageIndex(0-100), C nps(-100..100), D openTickets, E healthOut
function customerHealthScore() {
const sheet = SpreadsheetApp.getActive().getSheetByName(CUST);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const usage = Number(values[i][1]) || 0;
const nps = Number(values[i][2]) || 0;
const tickets = Number(values[i][3]) || 0;
const npsNorm = (nps + 100) / 2; // 0–100
const ticketPenalty = Math.min(tickets * 5, 40);
let health = usage * 0.5 + npsNorm * 0.4 + (100 - ticketPenalty) * 0.1;
health = Math.max(0, Math.min(100, Math.round(health)));
values[i][4] = health;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 14: Maps NPS to 0–100.
- Line 15: Capped penalty from open tickets.
- Line 16: Usage weight.
- Line 5: Column E score.
- Line 17: Integer score.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: health score
- 1Input columns fresh
- 2NPS scale is -100..100
- 3Weights approved by CS
- 4Trials filtered if needed
- 5Dashboard reads healthOut
- 6Spot-check 5 customers
Frequently asked questions
Scale to -100..100 or change npsNorm accordingly.
Stops one noisy account from zeroing health entirely via tickets alone.
Trigger after ticket sync or hourly.
Map score bands to A/B/C in a neighboring column.
Treat blank as null and skip or impute — do not silently use 0 without a policy.
Average product indexes or weight by ARR.
Also write component columns for usageTerm/npsTerm/ticketTerm.