Thank happy customers and win back unhappy ones with a coupon

Once a day, your sheet of purchases and feedback is read, AI works out whether each customer's feedback is positive, neutral or negative, and each one gets a personalised email from your own Gmail account in the tone you choose. Negative feedback earns an apology and a coupon code; everyone else gets a thank you that answers what they said.

Your sheet is never changed; a separate 'Emails sent' tab records one line per email: address, product, how the feedback was read, whether a coupon went out and the subject. That tab is checked before every send, so nobody is emailed twice about the same purchase, and if it cannot be written after a send the run stops at once. Rows with no feedback yet wait until it arrives, rows with a bad address are reported and skipped, and no coupon is ever offered on the thank you path. Each run sends a capped number of emails.

Your sheet needs 'Name', 'Email', 'Product' and 'Feedback' columns, plus a coupon code and what it is worth.

Run every day.Run every day.

Set sheetUrl to blank.Set sheetUrl to blank.

Set sheetTabName to blank.Set sheetTabName to blank.

Set businessName to blank.Set businessName to blank.

Set senderName to blank.Set senderName to blank.

Set emailTone to warm and friendly.Set emailTone to warm and friendly.

Set couponCode to blank.Set couponCode to blank.

Set couponDetails to blank.Set couponDetails to blank.

Set optOutLine to blank.Set optOutLine to blank.

Set sentLogTabName to Emails sent.Set sentLogTabName to Emails sent.

Set maxEmailsPerRun to 1.Set maxEmailsPerRun to 1.

Set sentLogIsNew to true.Set sentLogIsNew to true.

If a step below fails, save the error as createLogTabErrorIf a step below fails, save the error as createLogTabError

Try

Google SheetsCreate the tab sentLogTabName in sheetUrl.Create the tab sentLogTabName in sheetUrl.

On error

Change sentLogIsNew to false.Change sentLogIsNew to false.

If sentLogIsNew is yesIf sentLogIsNew is yes

Yes

Google SheetsAdd a row to sentLogTabName in sheetUrl.Add a row to sentLogTabName in sheetUrl.

No

No steps

Set alreadyEmailed to an empty list.Set alreadyEmailed to an empty list.

Set sentLogKeys to an empty list.Set sentLogKeys to an empty list.

If a step below fails, save the error as sentLogReadErrorIf a step below fails, save the error as sentLogReadError

Try

Google SheetsRead column 'A' of sentLogTabName in sheetUrl. Save it as sentLog.Read column 'A' of sentLogTabName in sheetUrl. Save it as sentLog.

Change sentLogKeys to sentLog.values.Change sentLogKeys to sentLog.values.

On error

Stop and report a problem: Could not read the '@sentLogTabName' tab, so there is no way to tell which customers have already been emailed. Nothing was sent. Check the spreadsheet, then run it again.Stop and report a problem: Could not read the '@sentLogTabName' tab, so there is no way to tell which customers have already been emailed. Nothing was sent. Check the spreadsheet, then run it again.

For each loggedKey in sentLogKeys:For each loggedKey in sentLogKeys:

If a step below fails, save the error as loggedKeyErrorIf a step below fails, save the error as loggedKeyError

Try

Add loggedKey in lowercase to the end of alreadyEmailed and save it as alreadyEmailed.Add loggedKey in lowercase to the end of alreadyEmailed and save it as alreadyEmailed.

On error

Show in the output: Ignored a cell in the '@sentLogTabName' tab that is not text.Show in the output: Ignored a cell in the '@sentLogTabName' tab that is not text.

Set sentThisRun to 0.Set sentThisRun to 0.

Set couponsSent to 0.Set couponsSent to 0.

Set alreadyEmailedRows to 0.Set alreadyEmailedRows to 0.

Set waitingForFeedbackRows to 0.Set waitingForFeedbackRows to 0.

Set skippedRows to 0.Set skippedRows to 0.

Set sendFailures to 0.Set sendFailures to 0.

Google SheetsRead the rows of sheetUrl. Save it as customerSheet.Read the rows of sheetUrl. Save it as customerSheet.

If customerSheet.count is 0If customerSheet.count is 0

Yes

Show in the output: No rows were found in the '@sheetTabName' tab. Check the tab name, and that the sheet has a header row with Name, Email, Product and Feedback columns followed by at least one purchase.Show in the output: No rows were found in the '@sheetTabName' tab. Check the tab name, and that the sheet has a header row with Name, Email, Product and Feedback columns followed by at least one purchase.

No

No steps

For each row in customerSheet.rows:For each row in customerSheet.rows:

If sentThisRun is greater than or equal to maxEmailsPerRunIf sentThisRun is greater than or equal to maxEmailsPerRun

Yes

Stop repeating.Stop repeating.

No

No steps

Set rowLabel to row row.row_number as text.Set rowLabel to row row.row_number as text.

Set customerName to blank.Set customerName to blank.

Set customerEmail to blank.Set customerEmail to blank.

Set product to blank.Set product to blank.

Set feedback to blank.Set feedback to blank.

Set rowIsReadable to true.Set rowIsReadable to true.

If a step below fails, save the error as rowErrorIf a step below fails, save the error as rowError

Try

Trim row.Name and save it as customerName.Trim row.Name and save it as customerName.

Trim row.Email and save it as customerEmail.Trim row.Email and save it as customerEmail.

Trim row.Product and save it as product.Trim row.Product and save it as product.

Trim row.Feedback and save it as feedback.Trim row.Feedback and save it as feedback.

On error

Change rowIsReadable to false.Change rowIsReadable to false.

If rowIsReadable is noIf rowIsReadable is no

Yes

Show in the output: Skipped rowLabel: the sheet needs Name, Email, Product and Feedback columns, spelled exactly like that, and one of them is missing.Show in the output: Skipped rowLabel: the sheet needs Name, Email, Product and Feedback columns, spelled exactly like that, and one of them is missing.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If not customerEmail is a valid email addressIf not customerEmail is a valid email address

Yes

Show in the output: Skipped rowLabel: the Email cell is empty or does not hold an email address.Show in the output: Skipped rowLabel: the Email cell is empty or does not hold an email address.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If feedback is emptyIf feedback is empty

Yes

Add 1 to waitingForFeedbackRows.Add 1 to waitingForFeedbackRows.

Skip to the next item.Skip to the next item.

No

No steps

Set sendKey to customerEmail in lowercase | product in lowercase.Set sendKey to customerEmail in lowercase | product in lowercase.

If alreadyEmailed contains sendKeyIf alreadyEmailed contains sendKey

Yes

Add 1 to alreadyEmailedRows.Add 1 to alreadyEmailedRows.

Skip to the next item.Skip to the next item.

No

No steps

Shorten feedback to 1500 characters and save it as feedbackForPrompt.Shorten feedback to 1500 characters and save it as feedbackForPrompt.

Set sentimentPrompt to You are reading feedback a customer left after buying a product. Decide the overall sentiment of the feedback.Set sentimentPrompt to You are reading feedback a customer left after buying a product. Decide the overall sentiment of the feedback. positive - they are pleased with the product or the experience neutral - mixed, matter-of-fact, or too little to tell either way negative - they are disappointed or frustrated, or something went wrong Reply with EXACTLY one of these words and nothing else: positive, neutral, negative. Product: product Feedback: feedbackForPrompt.

ClaudeAsk Claude: sentimentPrompt. Save it as sentimentAnswer.Ask Claude: sentimentPrompt. Save it as sentimentAnswer. Maximum response length: 16

Set sentiment to unclear.Set sentiment to unclear.

If a step below fails, save the error as sentimentErrorIf a step below fails, save the error as sentimentError

Try

Make sentimentAnswer.response_text with . replaced by lowercase and save it as sentiment.Make sentimentAnswer.response_text with . replaced by lowercase and save it as sentiment.

On error

Change sentiment to unclear.Change sentiment to unclear.

If sentiment is not positive and sentiment is not neutral and sentiment is…If sentiment is not positive and sentiment is not neutral and sentiment is…

Yes

Show in the output: Skipped rowLabel (customerEmail): could not tell whether the feedback is positive, neutral or negative. It will be tried again on the next run.Show in the output: Skipped rowLabel (customerEmail): could not tell whether the feedback is positive, neutral or negative. It will be tried again on the next run.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

Set customerNameForPrompt to customerName.Set customerNameForPrompt to customerName.

If customerNameForPrompt is emptyIf customerNameForPrompt is empty

Yes

Change customerNameForPrompt to not known.Change customerNameForPrompt to not known.

No

No steps

Set productForPrompt to product.Set productForPrompt to product.

If productForPrompt is emptyIf productForPrompt is empty

Yes

Change productForPrompt to not recorded - refer to it as their recent purchase.Change productForPrompt to not recorded - refer to it as their recent purchase.

No

No steps

Set customerContext to Business: businessNameSet customerContext to Business: businessName Customer's name: customerNameForPrompt What they bought: productForPrompt What they said in their feedback: feedbackForPrompt Tone to write in: emailTone Sign off as: senderName.

Set subject to blank.Set subject to blank.

Set body to blank.Set body to blank.

Set couponFlag to no.Set couponFlag to no.

If sentiment is negativeIf sentiment is negative

Yes

Set couponLine to Coupon code couponCode - couponDetails.Set couponLine to Coupon code couponCode - couponDetails.

Set couponSubjectPrompt to Write the subject line for a short email from businessName to a customer whose feedback about their purchase was negative.Set couponSubjectPrompt to Write the subject line for a short email from businessName to a customer whose feedback about their purchase was negative. customerContext The email apologises for what went wrong, shows that their feedback was read, and offers a coupon to make it right. Do not put the coupon code in the subject line. Rules: under 60 characters, no quotation marks, no emoji, no placeholders such as [Name]. Write in the tone given above. Reply with the subject line only.

ClaudeAsk Claude: couponSubjectPrompt. Save it as couponSubjectAnswer.Ask Claude: couponSubjectPrompt. Save it as couponSubjectAnswer. Maximum response length: 60

If a step below fails, save the error as couponSubjectErrorIf a step below fails, save the error as couponSubjectError

Try

Trim couponSubjectAnswer.response_text and save it as subject.Trim couponSubjectAnswer.response_text and save it as subject.

On error

Change subject to blank.Change subject to blank.

Set couponBodyPrompt to Write the body of a short email (under 170 words) from businessName to a customer whose feedback about their purchase was negative.Set couponBodyPrompt to Write the body of a short email (under 170 words) from businessName to a customer whose feedback about their purchase was negative. customerContext Apologise sincerely and specifically for what they described, without being defensive or making excuses, and thank them for telling you. Then offer them this coupon as an apology, quoting the code exactly as written: couponLine. Rules: plain text only - no subject line, no markdown, no bullet points and no placeholders such as [Name]. Write in the tone given above. If the customer's name is not known, open with a plain greeting rather than inventing one. Do not invent facts about the product or the order. Do not promise a refund, a replacement or anything beyond the coupon. Sign off as senderName. Reply with the email body only.

ClaudeAsk Claude: couponBodyPrompt. Save it as couponBodyAnswer.Ask Claude: couponBodyPrompt. Save it as couponBodyAnswer. Maximum response length: 700

If a step below fails, save the error as couponBodyErrorIf a step below fails, save the error as couponBodyError

Try

Trim couponBodyAnswer.response_text and save it as body.Trim couponBodyAnswer.response_text and save it as body.

On error

Change body to blank.Change body to blank.

If body isn't empty and body doesn't contain couponCode (ignoring case)If body isn't empty and body doesn't contain couponCode (ignoring case)

Yes

Change body to bodyChange body to body couponLine.

No

No steps

Change couponFlag to yes.Change couponFlag to yes.

No

Set thanksSubjectPrompt to Write the subject line for a short follow-up email from businessName to a customer, thanking them for a recent purchase.Set thanksSubjectPrompt to Write the subject line for a short follow-up email from businessName to a customer, thanking them for a recent purchase. customerContext The email thanks them, shows that their feedback was read, and invites them to shop with businessName again. Rules: under 60 characters, no quotation marks, no emoji, no placeholders such as [Name]. Write in the tone given above. Reply with the subject line only.

ClaudeAsk Claude: thanksSubjectPrompt. Save it as thanksSubjectAnswer.Ask Claude: thanksSubjectPrompt. Save it as thanksSubjectAnswer. Maximum response length: 60

If a step below fails, save the error as thanksSubjectErrorIf a step below fails, save the error as thanksSubjectError

Try

Trim thanksSubjectAnswer.response_text and save it as subject.Trim thanksSubjectAnswer.response_text and save it as subject.

On error

Change subject to blank.Change subject to blank.

Set thanksBodyPrompt to Write the body of a short follow-up email (under 150 words) from businessName to a customer, thanking them for a recent purchase.Set thanksBodyPrompt to Write the body of a short follow-up email (under 150 words) from businessName to a customer, thanking them for a recent purchase. customerContext Thank them for their purchase, respond in a sentence or two to what they said in their feedback, and warmly invite them to shop with businessName again. Rules: plain text only - no subject line, no markdown, no bullet points and no placeholders such as [Name]. Write in the tone given above. If the customer's name is not known, open with a plain greeting rather than inventing one. Do not invent facts about the product or the order. Do not offer a discount, a coupon or a refund. Sign off as senderName. Reply with the email body only.

ClaudeAsk Claude: thanksBodyPrompt. Save it as thanksBodyAnswer.Ask Claude: thanksBodyPrompt. Save it as thanksBodyAnswer. Maximum response length: 700

If a step below fails, save the error as thanksBodyErrorIf a step below fails, save the error as thanksBodyError

Try

Trim thanksBodyAnswer.response_text and save it as body.Trim thanksBodyAnswer.response_text and save it as body.

On error

Change body to blank.Change body to blank.

Trim subject with replaced by " and save it as subject.Trim subject with replaced by " and save it as subject.

Trim body with ** replaced by and save it as body.Trim body with ** replaced by and save it as body.

If subject is empty or body is emptyIf subject is empty or body is empty

Yes

Show in the output: Skipped rowLabel (customerEmail): no usable subject line or email body came back. It will be tried again on the next run.Show in the output: Skipped rowLabel (customerEmail): no usable subject line or email body came back. It will be tried again on the next run.

Add 1 to skippedRows.Add 1 to skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If optOutLine isn't blankIf optOutLine isn't blank

Yes

Change body to bodyChange body to body optOutLine.

No

No steps

Set emailSent to true.Set emailSent to true.

If a step below fails, save the error as sendErrorIf a step below fails, save the error as sendError

Try

GmailSend an email to customerEmail.Send an email to customerEmail. Subject: subjectbody

On error

Change emailSent to false.Change emailSent to false.

Show in the output: Could not send to customerEmail (rowLabel): sendError as text.Show in the output: Could not send to customerEmail (rowLabel): sendError as text.

If emailSent is noIf emailSent is no

Yes

Add 1 to sendFailures.Add 1 to sendFailures.

Skip to the next item.Skip to the next item.

No

No steps

If a step below fails, save the error as logWriteErrorIf a step below fails, save the error as logWriteError

Try

Google SheetsAdd a row to sentLogTabName in sheetUrl.Add a row to sentLogTabName in sheetUrl.

On error

Stop and report a problem: Sent the email to customerEmail but could not add it to the '@sentLogTabName' tab. The run stopped here so that nobody is emailed twice on the next run - fix the spreadsheet, then run it again.Stop and report a problem: Sent the email to customerEmail but could not add it to the '@sentLogTabName' tab. The run stopped here so that nobody is emailed twice on the next run - fix the spreadsheet, then run it again.

Add sendKey to the end of alreadyEmailed and save it as alreadyEmailed.Add sendKey to the end of alreadyEmailed and save it as alreadyEmailed.

Add 1 to sentThisRun.Add 1 to sentThisRun.

If couponFlag is yesIf couponFlag is yes

Yes

Add 1 to couponsSent.Add 1 to couponsSent.

No

No steps

Show in the output: Sent sentThisRun as text email(s) this run, couponsSent as text with a coupon. alreadyEmailedRows as text row(s) had already been emailed, waitingForFeedbackRows as text row(s) have no feedback yet, skippedRows as text row(s) were skipped and sendFailures as text send(s) failed. Raise 'maxEmailsPerRun' to send more in one run.Show in the output: Sent sentThisRun as text email(s) this run, couponsSent as text with a coupon. alreadyEmailedRows as text row(s) had already been emailed, waitingForFeedbackRows as text row(s) have no feedback yet, skippedRows as text row(s) were skipped and sendFailures as text send(s) failed. Raise 'maxEmailsPerRun' to send more in one run.