Scores how satisfied each client sounds in the emails you log every week and keeps a client health dashboard tab up to date.
Uses
Claude
Google Sheets
Score client health from your email log
spreadsheetUrl to blank.Set spreadsheetUrl to blank.emailsTabName to Emails.Set emailsTabName to Emails.healthTabName to Client health.Set healthTabName to Client health.maxEmailsPerRun to 60.Set maxEmailsPerRun to 60.maxMessageChars to 6000.Set maxMessageChars to 6000.maxRememberedEmails to 300.Set maxRememberedEmails to 300.scoredStore.Get the stored value 'client_health_scored'. Save it as scoredStore.remembered to an empty list.Set remembered to an empty list.scoredStore.found is yesIf scoredStore.found is yesYes
memoryErrorIf a step below fails, save the error as memoryErrorTry
scoredStore.value from position 0 on and save it as remembered.Take scoredStore.value from position 0 on and save it as remembered.On error
No
No steps
emailsRead to nothing.Set emailsRead to nothing.emailsReadErrorIf a step below fails, save the error as emailsReadErrorTry
spreadsheetUrl. Save it as emailsRead.Read the rows of spreadsheetUrl. Save it as emailsRead.On error
emailsReadError.message. Check the tab name and that the Google Sheets connection can open the spreadsheet.Stop and report a problem: Could not read the '@emailsTabName' tab: emailsReadError.message. Check the tab name and that the Google Sheets connection can open the spreadsheet.emailsRead is emptyIf emailsRead is emptyYes
No
No steps
emailHeaders to emailsRead.headers.Set emailHeaders to emailsRead.headers.emailRows to emailsRead.rows.Set emailRows to emailsRead.rows.emailRows is emptyIf emailRows is emptyYes
No
No steps
emailHeaders doesn't contain Client or emailHeaders doesn't contain…If emailHeaders doesn't contain Client or emailHeaders doesn't contain…Yes
emailHeaders joined by , .Stop and report a problem: The '@emailsTabName' tab needs Client and Message columns in its header row, spelled exactly like that (Date and Subject columns are optional). Its header row currently reads: emailHeaders joined by , .No
No steps
logOrderDays to 0.Set logOrderDays to 0.emailRows is greater than 1If the length of emailRows is greater than 1Yes
logDateErrorIf a step below fails, save the error as logDateErrorTry
emailRows[0].Date to emailRows[-1].Date and save it as logOrderDays.Count the days from emailRows[0].Date to emailRows[-1].Date and save it as logOrderDays.On error
logOrderDays to 0.Change logOrderDays to 0.No
No steps
logOrderDays is less than 0If logOrderDays is less than 0Yes
emailRows[0].Date and its last emailRows[-1].Date. This workflow takes new emails from the bottom of the log, so a newest-first log would hide new mail from it. Sort the tab by Date, oldest first, and add new emails as rows at the bottom, then run it again.Stop and report a problem: The '@emailsTabName' tab looks newest-first: its first email is dated emailRows[0].Date and its last emailRows[-1].Date. This workflow takes new emails from the bottom of the log, so a newest-first log would hide new mail from it. Sort the tab by Date, oldest first, and add new emails as rows at the bottom, then run it again.No
No steps
healthRead to nothing.Set healthRead to nothing.healthReadErrorIf a step below fails, save the error as healthReadErrorTry
spreadsheetUrl. Save it as healthRead.Read the rows of spreadsheetUrl. Save it as healthRead.On error
createTabErrorIf a step below fails, save the error as createTabErrorTry
healthTabName in spreadsheetUrl.Create the tab healthTabName in spreadsheetUrl.healthRead to {headers:[],rows:[]}.Change healthRead to {headers:[],rows:[]}.On error
createTabError.message. Add an empty tab with that name to the spreadsheet, then run it again.Stop and report a problem: Could not read or create the '@healthTabName' tab: createTabError.message. Add an empty tab with that name to the spreadsheet, then run it again.healthHeaders to healthRead.headers.Set healthHeaders to healthRead.headers.expectedHeaders to [Client, Health score, Status, Trend, Summary, Why, Last email, Emails scored, Updated at].Set expectedHeaders to [Client, Health score, Status, Trend, Summary, Why, Last email, Emails scored, Updated at].expectedHeaderLine to Client|Health score|Status|Trend|Summary|Why|Last email|Emails scored|Updated at.Set expectedHeaderLine to Client|Health score|Status|Trend|Summary|Why|Last email|Emails scored|Updated at.healthHeaders with | and save it as actualHeaderLine.Join the items from index 0 up to 9 of healthHeaders with | and save it as actualHeaderLine.mustWriteHeaders to false.Set mustWriteHeaders to false.healthHeaders is emptyIf healthHeaders is emptyYes
mustWriteHeaders to true.Change mustWriteHeaders to true.Else if @actualHeaderLine is not @expectedHeaderLine
expectedHeaderLine| starts with actualHeaderLine| (ignoring case)If expectedHeaderLine| starts with actualHeaderLine| (ignoring case)Yes
mustWriteHeaders to true.Change mustWriteHeaders to true.No
actualHeaderLine), so it will not write into it. Its first nine columns need to be, in this order: expectedHeaders joined by , . Point 'healthTabName' at an empty tab and the workflow writes them for you.Stop and report a problem: The '@healthTabName' tab has a header row this workflow does not recognise (actualHeaderLine), so it will not write into it. Its first nine columns need to be, in this order: expectedHeaders joined by , . Point 'healthTabName' at an empty tab and the workflow writes them for you.Otherwise
No steps
mustWriteHeaders is yesIf mustWriteHeaders is yesYes
healthTabName in spreadsheetUrl.Overwrite row 1 of healthTabName in spreadsheetUrl.No
No steps
sheetKeys to an empty list.Set sheetKeys to an empty list.logged in emailRows:For each logged in emailRows:loggedKey to blank.Set loggedKey to blank.loggedClient to blank.Set loggedClient to blank.loggedMessage to blank.Set loggedMessage to blank.loggedDate to blank.Set loggedDate to blank.logged.Client is yesIf logged.Client is yesYes
logged.Client and save it as loggedClient.Trim logged.Client and save it as loggedClient.No
No steps
logged.Message is yesIf logged.Message is yesYes
logged.Message and save it as loggedMessage.Trim logged.Message and save it as loggedMessage.No
No steps
logged.Date is yesIf logged.Date is yesYes
logged.Date and save it as loggedDate.Trim logged.Date and save it as loggedDate.No
No steps
loggedClient isn't empty and loggedMessage isn't emptyIf loggedClient isn't empty and loggedMessage isn't emptyYes
loggedKey to loggedClient in lowercase truncated to 40 characters|loggedDate truncated to 24 characters|loggedMessage in lowercase truncated to 40 characters.Change loggedKey to loggedClient in lowercase truncated to 40 characters|loggedDate truncated to 24 characters|loggedMessage in lowercase truncated to 40 characters.No
No steps
loggedKey to the end of sheetKeys and save it as sheetKeys.Add loggedKey to the end of sheetKeys and save it as sheetKeys.startPos to 0.Set startPos to 0.startingFresh to the length of remembered is 0.Set startingFresh to the length of remembered is 0.runNotes to an empty list.Set runNotes to an empty list.startingFresh is noIf startingFresh is noYes
anchorPos to -1.Set anchorPos to -1.rememberedKey in remembered:For each rememberedKey in remembered:rememberedKey in sheetKeys and save it as foundAt. Find the position of rememberedKey in sheetKeys and save it as foundAt.foundAt is greater than or equal to 0 If foundAt is greater than or equal to 0Yes
anchorPos to foundAt.Change anchorPos to foundAt.No
No steps
anchorPos is less than 0If anchorPos is less than 0Yes
remembered emails this workflow remembers scoring are in the '@emailsTabName' tab any more, so it treated the tab as a new log. to the end of runNotes and save it as runNotes.Add None of the the length of remembered emails this workflow remembers scoring are in the '@emailsTabName' tab any more, so it treated the tab as a new log. to the end of runNotes and save it as runNotes.remembered to an empty list.Change remembered to an empty list.startingFresh to true.Change startingFresh to true.No
startPos to anchorPos.Change startPos to anchorPos.No
No steps
startingFresh is yesIf startingFresh is yesYes
startPos to the length of emailRows minus maxEmailsPerRun.Change startPos to the length of emailRows minus maxEmailsPerRun.startPos is less than 0If startPos is less than 0Yes
startPos to 0.Change startPos to 0.No
No steps
startPos is greater than 0If startPos is greater than 0Yes
maxEmailsPerRun emails in the tab; the startPos older rows above them will not be scored. to the end of runNotes and save it as runNotes.Add First run: started from the most recent maxEmailsPerRun emails in the tab; the startPos older rows above them will not be scored. to the end of runNotes and save it as runNotes.No
No steps
No
No steps
newEmails to 0.Set newEmails to 0.scanPos to 0.Set scanPos to 0.scanned in emailRows:For each scanned in emailRows:scanPos is greater than or equal to startPosIf scanPos is greater than or equal to startPosYes
scanKey to sheetKeys[@scanPos].Set scanKey to sheetKeys[@scanPos].scanKey isn't empty and remembered doesn't contain scanKeyIf scanKey isn't empty and remembered doesn't contain scanKeyYes
newEmails.Add 1 to newEmails.No
No steps
No
No steps
scanPos.Add 1 to scanPos.surplus to newEmails minus maxEmailsPerRun.Set surplus to newEmails minus maxEmailsPerRun.surplus is less than 0If surplus is less than 0Yes
surplus to 0.Change surplus to 0.No
No steps
surplus is greater than 0If surplus is greater than 0Yes
newEmails emails were new but 'maxEmailsPerRun' is maxEmailsPerRun, so only the newest maxEmailsPerRun were scored. The surplus older ones will not be scored; raise 'maxEmailsPerRun' if you log more than that between runs. to the end of runNotes and save it as runNotes.Add newEmails emails were new but 'maxEmailsPerRun' is maxEmailsPerRun, so only the newest maxEmailsPerRun were scored. The surplus older ones will not be scored; raise 'maxEmailsPerRun' if you log more than that between runs. to the end of runNotes and save it as runNotes.No
No steps
scoredThisRun to 0.Set scoredThisRun to 0.clientsScored to an empty list.Set clientsScored to an empty list.skippedSurplus to 0.Set skippedSurplus to 0.loopPos to 0.Set loopPos to 0.email in emailRows:For each email in emailRows:emailPos to loopPos.Set emailPos to loopPos.loopPos.Add 1 to loopPos.emailPos is less than startPosIf emailPos is less than startPosYes
No
No steps
emailKey to sheetKeys[@emailPos].Set emailKey to sheetKeys[@emailPos].emailKey is emptyIf emailKey is emptyYes
email.row_number in '@emailsTabName': it has no Client or no Message, and both are needed to score it.Show in the output: Skipped row email.row_number in '@emailsTabName': it has no Client or no Message, and both are needed to score it.No
No steps
remembered contains emailKeyIf remembered contains emailKeyYes
No
No steps
skippedSurplus is less than surplusIf skippedSurplus is less than surplusYes
skippedSurplus.Add 1 to skippedSurplus.No
No steps
email.Client and save it as client.Trim email.Client and save it as client.client lowercase and save it as clientKey.Make client lowercase and save it as clientKey.email.Message and save it as message.Trim email.Message and save it as message.emailDate to blank.Set emailDate to blank.emailSubject to blank.Set emailSubject to blank.email.Date is yesIf email.Date is yesYes
email.Date and save it as emailDate.Trim email.Date and save it as emailDate.No
No steps
email.Subject is yesIf email.Subject is yesYes
email.Subject and save it as emailSubject.Trim email.Subject and save it as emailSubject.No
No steps
messageForModel to message.Set messageForModel to message.message is greater than maxMessageCharsIf the length of message is greater than maxMessageCharsYes
messageForModel to message truncated to maxMessageChars charactersChange messageForModel to message truncated to maxMessageChars characters
[Message cut off here.].No
No steps
rowLabel to the email from client (row email.row_number).Set rowLabel to the email from client (row email.row_number).emailErrorIf a step below fails, save the error as emailErrorTry
spreadsheetUrl. Save it as healthNow.Read the rows of spreadsheetUrl. Save it as healthNow.existingRow to nothing.Set existingRow to nothing.healthRow in healthNow.rows:For each healthRow in healthNow.rows:healthRow.Client is yes If healthRow.Client is yesYes
healthRow.Client in lowercase is clientKeyIf healthRow.Client in lowercase is clientKeyYes
existingRow to healthRow.Change existingRow to healthRow.No
No steps
No
No steps
client
Date: emailDate
Subject: emailSubject
Email:
messageForModel'. Save it as summaryAnswer.
Maximum response length: 300summary to blank.Set summary to blank.summaryAnswer.response_text is yesIf summaryAnswer.response_text is yesYes
summaryAnswer.response_text and save it as summary.Trim summaryAnswer.response_text and save it as summary.No
No steps
summary is emptyIf summary is emptyYes
rowLabel; it will be tried again on the next run.Show in the output: No summary came back for rowLabel; it will be tried again on the next run.No
No steps
client
Subject: emailSubject
Summary: summary
Email:
messageForModel'. Save it as scoreAnswer.
Maximum response length: 200verdict to blank.Set verdict to blank.scoreAnswer.response_text is yesIf scoreAnswer.response_text is yesYes
scoreAnswer.response_text and save it as verdict.Trim scoreAnswer.response_text and save it as verdict.No
No steps
scoreLine to blank.Set scoreLine to blank.why to blank.Set why to blank.verdictLine in verdict split by a new line:For each verdictLine in verdict split by a new line:verdictLine and save it as lineText. Trim verdictLine and save it as lineText.lineText starts with SCORE: (ignoring case) and scoreLine is empty If lineText starts with SCORE: (ignoring case) and scoreLine is emptyYes
scoreLine to lineText.Change scoreLine to lineText.Else if @lineText starts with "WHY:" (ignoring case)
lineText with WHY: replaced by (ignoring case) and save it as why.Trim lineText with WHY: replaced by (ignoring case) and save it as why.Otherwise
No steps
scoreLine and save it as scoreRaw.Extract the first number from scoreLine and save it as scoreRaw.scoreRaw is blankIf scoreRaw is blankYes
rowLabel: the reply began '@verdict truncated to 120 characters'. The client's row is unchanged; it will be tried again on the next run.Show in the output: Could not read a score for rowLabel: the reply began '@verdict truncated to 120 characters'. The client's row is unchanged; it will be tried again on the next run.No
No steps
scoreRaw is less than 1 or scoreRaw is greater than 10If scoreRaw is less than 1 or scoreRaw is greater than 10Yes
rowLabel came back as scoreRaw, which is not between 1 and 10. The client's row is unchanged; it will be tried again on the next run.Show in the output: The score for rowLabel came back as scoreRaw, which is not between 1 and 10. The client's row is unchanged; it will be tried again on the next run.No
No steps
scoreRaw to the nearest integer and save it as score.Round scoreRaw to the nearest integer and save it as score.why is emptyIf why is emptyYes
why to verdict.Change why to verdict.No
No steps
status to Watch.Set status to Watch.score is greater than or equal to 7If score is greater than or equal to 7Yes
status to Healthy.Change status to Healthy.No
No steps
score is less than or equal to 3If score is less than or equal to 3Yes
status to At risk.Change status to At risk.No
No steps
previousScore to blank.Set previousScore to blank.scoredBefore to 0.Set scoredBefore to 0.clientLabel to client.Set clientLabel to client.existingRow isn't emptyIf existingRow isn't emptyYes
existingRow["Health score"] and save it as previousScore.Extract the first number from existingRow["Health score"] and save it as previousScore.existingRow["Emails scored"] and save it as countBefore.Extract the first number from existingRow["Emails scored"] and save it as countBefore.countBefore isn't blankIf countBefore isn't blankYes
scoredBefore to countBefore.Change scoredBefore to countBefore.No
No steps
existingRow.Client is yesIf existingRow.Client is yesYes
existingRow.Client and save it as clientLabel.Trim existingRow.Client and save it as clientLabel.No
No steps
No
No steps
trend to New.Set trend to New.previousScore isn't blankIf previousScore isn't blankYes
trend to Steady.Change trend to Steady.score is greater than previousScoreIf score is greater than previousScoreYes
trend to Improving.Change trend to Improving.No
No steps
score is less than previousScoreIf score is less than previousScoreYes
trend to Declining.Change trend to Declining.No
No steps
No
No steps
lastEmail to emailDate.Set lastEmail to emailDate.lastEmail is emptyIf lastEmail is emptyYes
lastEmail.Get today's date and save it as lastEmail.No
No steps
rowValues to [clientLabel, score, status, trend, summary, why, lastEmail, scoredBefore plus 1, the current date and time].Set rowValues to [clientLabel, score, status, trend, summary, why, lastEmail, scoredBefore plus 1, the current date and time].existingRow isn't emptyIf existingRow isn't emptyYes
existingRow of healthTabName in spreadsheetUrl.Overwrite row existingRow of healthTabName in spreadsheetUrl.No
healthTabName in spreadsheetUrl.Add a row to healthTabName in spreadsheetUrl.emailKey to the end of remembered and save it as remembered.Add emailKey to the end of remembered and save it as remembered.remembered is greater than maxRememberedEmailsIf the length of remembered is greater than maxRememberedEmailsYes
remembered from position the length of remembered minus maxRememberedEmails on and save it as remembered.Take remembered from position the length of remembered minus maxRememberedEmails on and save it as remembered.No
No steps
remembered.Set the stored value 'client_health_scored' to remembered.scoredThisRun.Add 1 to scoredThisRun.clientsScored doesn't contain clientKeyIf clientsScored doesn't contain clientKeyYes
clientKey to the end of clientsScored and save it as clientsScored.Add clientKey to the end of clientsScored and save it as clientsScored.No
No steps
On error
rowLabel: emailError.message. It will be tried again on the next run.Show in the output: Could not score rowLabel: emailError.message. It will be tried again on the next run.atRiskClients to an empty list.Set atRiskClients to an empty list.dashboardNote to blank.Set dashboardNote to blank.summaryReadErrorIf a step below fails, save the error as summaryReadErrorTry
spreadsheetUrl. Save it as dashboardNow.Read the rows of spreadsheetUrl. Save it as dashboardNow.dashboardRow in dashboardNow.rows:For each dashboardRow in dashboardNow.rows:dashboardRow.Status is At risk and dashboardRow.Client is yes If dashboardRow.Status is At risk and dashboardRow.Client is yesYes
dashboardRow.Client to the end of atRiskClients and save it as atRiskClients.Add dashboardRow.Client to the end of atRiskClients and save it as atRiskClients.No
No steps
atRiskClients isn't emptyIf atRiskClients isn't emptyYes
dashboardNote to At risk: atRiskClients joined by , .Change dashboardNote to At risk: atRiskClients joined by , .No
dashboardNote to No client is marked At risk..Change dashboardNote to No client is marked At risk..On error
dashboardNote to The dashboard could not be read back for this summary: summaryReadError.message.Change dashboardNote to The dashboard could not be read back for this summary: summaryReadError.message.notesText to blank.Set notesText to blank.runNotes isn't emptyIf runNotes isn't emptyYes
notesText to runNotes joined by a space.Change notesText to runNotes joined by a space.No
No steps
attempted to newEmails minus surplus.Set attempted to newEmails minus surplus.scoredThisRun is 0If scoredThisRun is 0Yes
newEmails is 0If newEmails is 0Yes
No
attempted new email(s) in '@emailsTabName' and none succeeded; see the messages above. The dashboard in '@healthTabName' is unchanged.@dashboardNote@notesText.Stop and report a problem: Tried to score attempted new email(s) in '@emailsTabName' and none succeeded; see the messages above. The dashboard in '@healthTabName' is unchanged.@dashboardNote@notesText.No
No steps
scoredThisRun email(s) for the length of clientsScored client(s) in '@healthTabName'.@dashboardNote@notesText.Finish the run: Scored scoredThisRun email(s) for the length of clientsScored client(s) in '@healthTabName'.@dashboardNote@notesText.