Often small and medium-sized businesses use email to keep in contact with their customer. Email such as seasonal greetings, festival greetings are great; it is even more fabulous when you can personalize the email to each of your customers, such as a birthday greetings email which provides special offers to your customers for a particular month. This will greatly improve customer engagement.
Scenario: Your company has the DOB and Email of customers and wants to send a birthday email greeting to the customers to show your appreciation to them.
Step 1: Prepare your Google Sheet
Prepare your google sheet as below, make sure you had formatted the column DOB to Date format using:
Format → Number → Date
This will tell google sheet that this field is a date field, you can choose the format you prefer such as
dd/MM/yyyyMM/dd/yyyy2. Setup Google App Script
Go to Tools → Script Editor to open up and the Authorized Google Apps Script, you will notice a file named
Code.gsPaste the following code inside:
function sendBirthdayEmails() {
// Get the sheet where the data is, in sheet 'CustomerDb'
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CustomerDb")
//Setting up today date, the date when the function activated
var now = Utilities.formatDate(new Date(), "GMT+8", "dd-MM");
// Get all value of the sheet
var data = sheet.getDataRange().getValues();
// Loop through each row of the sheet
data.forEach(function (row) {
// Check if the date (month and day) are matched to today date, send email when matched
if (Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === now) {
Logger.log("Send Email")
MailApp.sendEmail({
to: row[3],
subject: "Happy Birthday " + row[1],
htmlBody: "Dear " + row[1] + "," + "<br>" + "<br>" +
"Happy Birthday to you! " + "<br>" + "<br>" +
"<img width='500' src='https://images.pexels.com/photos/3905849/pexels-photo-3905849.jpeg?auto=compress&cs=tinysrgb&dpr=2&h=750&w=1260'>" +
"<br>" +
"Sincerely from," + "<br>" + "DS Company"
});
}
else {
Logger.log("Birthday Not Match")
}
});
}The code will run through each row of the database, and the row[0], row[1], row[2], row[3] in the code is corresponding to the column A, B, C, D in the datasheet.
Whenever the code found matches between today date and the DOB (exclude the year):
Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === nowIt will fire a function “sendEmail”.
Test the code by saving and click the Run button on top. You will get an email as below:
As the date of writing this article is 30/5/2021, the function will only send to customer with a DOB of 30/5, which is “Manfye Goh 0", customize your email according to your organization's need.
3. Scheduling Daily Checking
The idea of this function is to schedule the
sendBirthdayEmails()In this case, I would like the function to be triggered every day 10 am — 11 am as it is a perfect period of time to open a Birthday Greeting Email. Lastly, save the trigger, and you have will never miss any birthday greet email to your customer anymore!
This simple setup only took 5 minutes and it will improve your relationship with your customers, try it out!
Final words:
This article demonstrates how to schedule send birthday emails according to the data in the cells, with slight modification and understanding of the code, you can modify it to automate schedule email such as invoice reminder, seasonal greeting, or even schedule a specific email at a specific time.
Do share with me your creativity if you do so.
Lastly, Thank you for your time in reading my article!
Credits and References:
- https://developers.google.com/google-ads/scripts/docs/features/dates
- Featured Image credits: cottonbro from Pexels