Score client health from your email log

Every Monday morning this workflow reads the emails you have logged from clients, has Claude summarise each new one and score how satisfied the client sounds from its tone and what it asks for, and writes the result into a client health tab: one row per client with their latest score, a status, the trend against their previous email, the summary and the reasons behind the score. Read it top to bottom and the accounts that need a call this week are the ones marked At risk.

Scores run from 1 (angry or about to leave) to 10 (delighted); a routine email with no complaint in it is a 7. Status follows the score - Healthy at 7 or above, Watch from 4 to 6, At risk at 3 or below - and Trend says whether the score rose, fell or held against the client's previous email. The model is told to judge only the email in front of it, to assume nothing about the relationship that the email does not say, and not to soften: a polite but frustrated email scores low. The reasons are written next to every score so you can disagree with one.

Each email is scored once. The workflow remembers the emails it has scored (the last 300, in a stored value named client_health_scored) and never looks above the oldest of them again, so re-running it does not rescore a client from old mail. On its first run it scores the most recent 60 emails in the log, so the dashboard starts from where your clients are now rather than where they were a year ago. If more emails arrive between runs than 'maxEmailsPerRun' allows, the newest are scored and the run's summary says how many older ones it left; raise the setting if you log more than that a week.

Every write is checked before it happens. A row with no Client or no Message is reported and skipped, and tried again once it is filled in. A reply from Claude with no readable score is reported and the client's row is left exactly as it was, to be tried again next week; one email that fails is logged while the rest are still scored. The health tab is only ever written in the nine columns the workflow owns, and it refuses to run against a tab whose header row it does not recognise rather than write into somebody else's columns. If the tab does not exist it is created.

To set it up: paste the spreadsheet link into 'spreadsheetUrl', name the tab your emails are logged in (its header row needs Client and Message columns, spelled like that; Date and Subject columns are used when they are there) and the tab the dashboard should live in - an empty tab, or one that does not exist yet. Connections needed: Google Sheets and Anthropic.