Risk flags turn health scores into actionable queues for CSMs.
churnRiskFlag sets High when health < 40 or inactivity > 45 days; Medium for softer thresholds; else Low.
Accounts with MRR ≥ 2000 escalate Medium to High so large logos get attention sooner.
Run after customerHealthScore so health is current.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Customers | A id | Customer |
| Customers | B health | 0–100 |
| Customers | C daysSinceActivity | Inactivity |
| Customers | D mrr | Monthly revenue |
| Customers | E churnRisk | Low/Medium/High |
What this script does
Rule-based churn risk labels with MRR escalation.
Prerequisites
Health and activity freshness; MRR populated.
- Thresholds agreed
- Run after health score
- Filter High for CSM queue
Walkthrough
health 35 → High; health 55 days 10 → Medium.
Edge cases
MRR blank treated as 0 — no escalation.
- String labels exact
- Order of ifs matters
How to test
MRR 2500 with Medium inputs becomes High.
Hardening for production
Write riskReason text; create tasks in Asana/Jira via API.
Variations
Numeric risk 1–5 instead of labels.
Full code: churnRiskFlag()
Update health and activity, then run churnRiskFlag() for CSM dashboards.
/**
* Flag churn risk when health is low or days since activity is high.
*/
const CUST = "Customers";
// A id, B health, C daysSinceActivity, D mrr, E churnRisk
function churnRiskFlag() {
const sheet = SpreadsheetApp.getActive().getSheetByName(CUST);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const health = Number(values[i][1]) || 0;
const days = Number(values[i][2]) || 0;
const mrr = Number(values[i][3]) || 0;
let risk = "Low";
if (health < 40 || days > 45) risk = "High";
else if (health < 60 || days > 21) risk = "Medium";
if (mrr >= 2000 && risk === "Medium") risk = "High"; // elevate big logos
values[i][4] = risk;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 15: Hard health threshold for High.
- Line 15: Inactivity High trigger.
- Line 16: Softer dual thresholds.
- Line 17: Escalates Medium→High for large accounts.
- Line 5: Column E label.
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: churn flags
- 1Health column current
- 2daysSinceActivity definition documented
- 3MRR currency consistent
- 4Thresholds approved by CS
- 5High-risk view/filter ready
- 6Test escalation with a sample enterprise row
Frequently asked questions
Same Medium risk on a huge account deserves faster response.
This is rules-based — export features to BigQuery for models later.
Add daysToRenewal as another High trigger.
Filter new High flags vs previous snapshot and MailApp.
Keep English codes for filters; translate in the UI layer.
Tune days thresholds per product; exclude seasonal inactive segments.
Append daily snapshots to ChurnRiskHistory.