Personalised marketing emails from customer data

Once a day, your sheet of customers is read - what each one bought, how much they spend, how recently they ordered and any feedback they left - and the AI reads the whole customer before writing a word. It judges whether the feedback is positive, neutral or negative, then decides whether this customer should be offered your coupon, following the coupon policy you write in plain language: a frustrated customer or a lapsed regular gets the code, a happy customer who ordered last week gets a thank-you and a reason to come back.

Each subject line and body is then written fresh for that customer, in your tone and towards the goal you set, and sent from your own Gmail account. Your sheet is never changed; a separate 'Emails sent' tab records one line per email with the address, product, sentiment, the coupon decision and why, 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. Each run sends a capped number of emails.

Your sheet needs Name, Email and Product columns; Order total, Previous orders, Last order date, Feedback and Segment columns are used when present.

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 emailGoal to bring them back for their next order.Set emailGoal to bring them back for their next order.

Set couponCode to blank.Set couponCode to blank.

Set couponDetails to blank.Set couponDetails to blank.

Set couponPolicy to Offer the coupon when the feedback is negative or mixed, or when a customer who has ordered more than once has not ordered for over 60 days. Do not offer it to a happy customer who ordered recently - they will come back anyway..Set couponPolicy to Offer the coupon when the feedback is negative or mixed, or when a customer who has ordered more than once has not ordered for over 60 days. Do not offer it to a happy customer who ordered recently - they will come back anyway..

Set onlySegment to blank.Set onlySegment 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 outsideSegmentRows to 0.Set outsideSegmentRows to 0.

Set skippedRows to 0.Set skippedRows to 0.

Set sendFailures to 0.Set sendFailures to 0.

Make onlySegment lowercase and save it as segmentFilter.Make onlySegment lowercase and save it as segmentFilter.

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 and Product columns followed by at least one customer.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 and Product columns followed by at least one customer.

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 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.

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 and Product columns, spelled exactly like that, and one of them is missing.Show in the output: Skipped rowLabel: the sheet needs Name, Email and Product 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

Set orderTotal to blank.Set orderTotal to blank.

If a step below fails, save the error as orderTotalErrorIf a step below fails, save the error as orderTotalError

Try

Trim row["Order total"] and save it as orderTotal.Trim row["Order total"] and save it as orderTotal.

On error

Change orderTotal to blank.Change orderTotal to blank.

Set previousOrders to blank.Set previousOrders to blank.

If a step below fails, save the error as previousOrdersErrorIf a step below fails, save the error as previousOrdersError

Try

Trim row["Previous orders"] and save it as previousOrders.Trim row["Previous orders"] and save it as previousOrders.

On error

Change previousOrders to blank.Change previousOrders to blank.

Set lastOrderDate to blank.Set lastOrderDate to blank.

If a step below fails, save the error as lastOrderDateErrorIf a step below fails, save the error as lastOrderDateError

Try

Trim row["Last order date"] and save it as lastOrderDate.Trim row["Last order date"] and save it as lastOrderDate.

On error

Change lastOrderDate to blank.Change lastOrderDate to blank.

Set feedback to blank.Set feedback to blank.

If a step below fails, save the error as feedbackErrorIf a step below fails, save the error as feedbackError

Try

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

On error

Change feedback to blank.Change feedback to blank.

If segmentFilter isn't emptyIf segmentFilter isn't empty

Yes

Set rowSegment to blank.Set rowSegment to blank.

Set segmentIsReadable to true.Set segmentIsReadable to true.

If a step below fails, save the error as segmentErrorIf a step below fails, save the error as segmentError

Try

Make row.Segment lowercase and save it as rowSegment.Make row.Segment lowercase and save it as rowSegment.

On error

Change segmentIsReadable to false.Change segmentIsReadable to false.

If segmentIsReadable is noIf segmentIsReadable is no

Yes

Show in the output: Skipped rowLabel: 'onlySegment' is set but the sheet has no Segment column. Add one, or clear 'onlySegment' to email every row.Show in the output: Skipped rowLabel: 'onlySegment' is set but the sheet has no Segment column. Add one, or clear 'onlySegment' to email every row.

Add 1 to skippedRows.Add 1 to skippedRows.

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

No

No steps

If rowSegment is not segmentFilterIf rowSegment is not segmentFilter

Yes

Add 1 to outsideSegmentRows.Add 1 to outsideSegmentRows.

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

No

No steps

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

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

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

If orderTotal is emptyIf orderTotal is empty

Yes

Change orderTotal to not recorded.Change orderTotal to not recorded.

No

No steps

If previousOrders is emptyIf previousOrders is empty

Yes

Change previousOrders to not recorded.Change previousOrders to not recorded.

No

No steps

If lastOrderDate is emptyIf lastOrderDate is empty

Yes

Change lastOrderDate to not recorded.Change lastOrderDate to not recorded.

No

No steps

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

If feedbackForPrompt is emptyIf feedbackForPrompt is empty

Yes

Change feedbackForPrompt to none left.Change feedbackForPrompt to none left.

No

No steps

Set customerContext to Business: businessNameSet customerContext to Business: businessName Customer's name: customerNameForPrompt What they bought: productForPrompt Order total: orderTotal Previous orders with us: previousOrders Last order date: lastOrderDate Today's date: today's date Feedback they left: feedbackForPrompt Tone to write in: emailTone Sign off as: senderName.

Set assessmentPrompt to You are helping businessName personalise a marketing email to one customer. Read their purchase details and the feedback they left, then decide two things: the overall sentiment of their feedback, and whether this email should include a coupon.Set assessmentPrompt to You are helping businessName personalise a marketing email to one customer. Read their purchase details and the feedback they left, then decide two things: the overall sentiment of their feedback, and whether this email should include a coupon. customerContext Coupon policy: couponPolicy Reply with exactly three lines and nothing else: Sentiment: positive, neutral or negative (neutral when no feedback was left or it is too little to tell) Coupon: yes or no Reason: one plain sentence, under 30 words, saying why this customer should or should not get the coupon.

ClaudeAsk Claude: assessmentPrompt. Save it as assessmentAnswer.Ask Claude: assessmentPrompt. Save it as assessmentAnswer. Maximum response length: 200

Set sentiment to blank.Set sentiment to blank.

Set couponDecision to blank.Set couponDecision to blank.

Set couponReason to blank.Set couponReason to blank.

If a step below fails, save the error as assessmentErrorIf a step below fails, save the error as assessmentError

Try

Set assessmentText to assessmentAnswer.response_text.Set assessmentText to assessmentAnswer.response_text.

Make the first match in assessmentText lowercase and save it as sentiment.Make the first match in assessmentText lowercase and save it as sentiment.

Make the first match in assessmentText lowercase and save it as couponDecision.Make the first match in assessmentText lowercase and save it as couponDecision.

Trim the first match in assessmentText truncated to 300 characters and save it as couponReason.Trim the first match in assessmentText truncated to 300 characters and save it as couponReason.

On error

Change sentiment to blank.Change sentiment to blank.

Change couponDecision to blank.Change couponDecision to blank.

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

If couponDecision is not yes and couponDecision is not noIf couponDecision is not yes and couponDecision is not no

Yes

Show in the output: Skipped rowLabel (customerEmail): no clear yes or no came back on the coupon. It will be tried again on the next run.Show in the output: Skipped rowLabel (customerEmail): no clear yes or no came back on the coupon. 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 couponReason is emptyIf couponReason is empty

Yes

Change couponReason to no reason given.Change couponReason to no reason given.

No

No steps

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 couponDecision is yesIf couponDecision is yes

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 marketing email from businessName to one customer.Set couponSubjectPrompt to Write the subject line for a short marketing email from businessName to one customer. customerContext The email includes a coupon for their next order. Why it is being offered: couponReason If their feedback was negative, the email apologises and makes it right; otherwise it thanks them and gives them a reason to come back. 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 marketing email (under 170 words) from businessName to one customer.Set couponBodyPrompt to Write the body of a short marketing email (under 170 words) from businessName to one customer. customerContext Goal of the email: emailGoal. Why this customer is getting a coupon: couponReason If their feedback was negative, apologise sincerely and specifically for what they described, without being defensive or making excuses, and thank them for telling you. Otherwise thank them for their custom and respond in a sentence to anything they said. Then offer them this coupon for their next order, 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. Use only the facts given above - do not invent details about the product, the order or the customer. 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 plainSubjectPrompt to Write the subject line for a short marketing email from businessName to one customer.Set plainSubjectPrompt to Write the subject line for a short marketing email from businessName to one customer. customerContext The email thanks them for their custom, answers anything they said in their feedback, and works towards this goal: emailGoal. No discount or coupon is offered. 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: plainSubjectPrompt. Save it as plainSubjectAnswer.Ask Claude: plainSubjectPrompt. Save it as plainSubjectAnswer. Maximum response length: 60

If a step below fails, save the error as plainSubjectErrorIf a step below fails, save the error as plainSubjectError

Try

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

On error

Change subject to blank.Change subject to blank.

Set plainBodyPrompt to Write the body of a short marketing email (under 150 words) from businessName to one customer.Set plainBodyPrompt to Write the body of a short marketing email (under 150 words) from businessName to one customer. customerContext Goal of the email: emailGoal. Thank them for their purchase and respond in a sentence or two to anything they said in their feedback. If the feedback was negative, apologise for what they described and say what you will do better, without offering compensation. Then work towards the goal above. 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. Use only the facts given above - do not invent details about the product, the order or the customer. Do not offer a discount, a coupon or a refund. Sign off as senderName. Reply with the email body only.

ClaudeAsk Claude: plainBodyPrompt. Save it as plainBodyAnswer.Ask Claude: plainBodyPrompt. Save it as plainBodyAnswer. Maximum response length: 700

If a step below fails, save the error as plainBodyErrorIf a step below fails, save the error as plainBodyError

Try

Trim plainBodyAnswer.response_text and save it as body.Trim plainBodyAnswer.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.message.Show in the output: Could not send to customerEmail (rowLabel): sendError.message.

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, outsideSegmentRows as text row(s) were outside the chosen segment, 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, outsideSegmentRows as text row(s) were outside the chosen segment, skippedRows as text row(s) were skipped and sendFailures as text send(s) failed. Raise 'maxEmailsPerRun' to send more in one run.