Build an AI customer-churn predictor

By the time a Northwind account actually cancels, the warning signs have usually been on the books for weeks — usage tailing off, a string of support tickets, a late invoice. The signals are all sitting in a spreadsheet; nobody has the time to read across them account by account and join the dots.

This script does the joining. It reads an Accounts sheet where each row carries the behavioural signals for one account, hands the whole row to Claude as JSON, and asks for a churn-risk score from 0 to 100 with a one-sentence reason. Any account scoring 70 or above is collected into a single digest email to the customer-success team, so they can reach out before the account goes quiet for good.

What you’ll need

• An Accounts Google Sheet with a header row and one account per row. The columns are up to you — name, loginsLast30d, openTickets, daysSinceLastLogin, invoiceStatus and the like — every column becomes a signal Claude can read.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• A team inbox to receive the digest — the script sends to [email protected].

The script

// The sheet of accounts and their behavioural signals.
const ACCOUNTS_SHEET_ID = '1abcAccountsId';

// Accounts at or above this score go into the alert email.
const RISK_THRESHOLD = 70;

// Where the at-risk digest is sent.
const CS_TEAM_EMAIL = '[email protected]';

/**
 * Scores every account for churn risk and emails the customer-success
 * team a digest of the accounts that cross the risk threshold.
 */
function predictChurn() {
  // 1. Read every account row into an object keyed by header.
  const accounts = readSheet(ACCOUNTS_SHEET_ID);
  if (!accounts.length) {
    Logger.log('No accounts to score — nothing to do.');
    return;
  }

  // 2. Score each account and keep the ones above the threshold.
  const atRisk = [];
  for (const a of accounts) {
    // Send the whole row as JSON so every column counts as a signal.
    const prompt =
      'Given this Northwind account state (JSON), score churn risk 0-100 ' +
      'and explain in one sentence. Return JSON ' +
      '{"risk": number, "why": string}.\n\n' + JSON.stringify(a);
    const out = JSON.parse(callClaude(prompt));
    if (out.risk >= RISK_THRESHOLD) {
      atRisk.push({ account: a.name, ...out });
    }
  }

  // 3. If nothing crossed the line, stop without sending an empty email.
  if (!atRisk.length) {
    Logger.log('No accounts at or above the risk threshold.');
    return;
  }

  // 4. Build one digest line per at-risk account and email the team.
  const body = atRisk
    .map((a) => a.account + ' (' + a.risk + '): ' + a.why)
    .join('\n');
  GmailApp.sendEmail(
    CS_TEAM_EMAIL,
    atRisk.length + ' at-risk accounts',
    body
  );
  Logger.log('Emailed ' + atRisk.length + ' at-risk accounts.');
}

/**
 * Reads a sheet's first tab into an array of objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model: 'claude-haiku-4-5-20251001',
      max_tokens: 150,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. predictChurn reads the Accounts sheet into objects keyed by the header row, so every column becomes a named field. It stops early if the sheet is empty.
2. For each account it builds a prompt containing the whole row as JSON. Sending the full row means any column you add — a new usage metric, a renewal date — is automatically treated as a signal without touching the code.
3. It pins the reply to a strict JSON schema, {"risk": number, "why": string}, then parses it. The risk is a 0-100 score and why is a one-line reason.
4. Accounts scoring at or above RISK_THRESHOLD (70) are collected into the atRisk list along with their score and reason.
5. If nothing crossed the threshold it stops — no empty email lands in the team inbox.
6. Otherwise it builds one line per account and sends a single digest email to CS_TEAM_EMAIL, so the team gets one message instead of one per account.

Example run

Say the Accounts sheet holds rows like these:

name	loginsLast30d	openTickets	daysSinceLastLogin	invoiceStatus
Harbour Books	2	3	21	overdue
Glade Interiors	44	0	1	paid
Verge Media	6	1	14	paid

The customer-success team receives one email:

Subject: 2 at-risk accounts

Harbour Books (88): Logins have collapsed, three tickets are open and the invoice is overdue — multiple strong churn signals at once. Verge Media (72): Usage has dropped sharply and it has been two weeks since the last login despite no billing issues.

Glade Interiors scored well below 70 and never appears.

Trigger it

Churn risk shifts week to week, so run this on a schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose predictChurn, a Time-driven source, and a Week timer — early Monday gives the team a fresh list to work through.

Watch out for

• The score is only as good as the signals. Claude reasons over the columns you give it — if the sheet has stale or thin data, the risk score will be thin too. Keep the signal columns fresh before each run.
• One account per call means one API call each. For a few hundred accounts a weekly run is fine; for thousands, batch accounts into one prompt and ask for a JSON array of scores.
• JSON.parse will throw if Claude wraps the reply in a code fence. Haiku is usually clean with a strict schema, but if you see parse errors, strip any fence before parsing rather than reaching for regex.
• The threshold is a judgement call. 70 is a starting point — watch which scored accounts actually churn over a few cycles and adjust RISK_THRESHOLD so the digest stays useful rather than noisy.
• Risk scores are not a tally. They are an estimate from the signals, not a guarantee — treat the digest as a prompt for a human conversation, not a verdict.