How to: Send personalised SMS messages using Excel

In a research recounted in The Small BIG, by Robert Cialdini, Steve Martin and Noah Goldstein, it is shown that this can cut no-shows by 43%. Simply by adding someone’s first name to the message will boost the response to the message. This is also supported by our own experiences of reducing no-show in the health care sector. 

How to send SMS directly from your Excel file

The CM platform allows you to upload a Microsoft Excel file with phone numbers and send an SMS message to those numbers. To do this you can click the “Send from Excel”-button on your “Send Messages”-page. 

You can also add the text of the actual message to the Excel file and thus send a different message to each recipient. In this way you can personalise the information. To do this we select the column with the text of the message in step 2 of the import process. You can find some more background information on sending to Excel files in our Help center.

Today we take personalisation to a next level by also adding names, gender and even time from your CRM into the message body to create a 100% personalised message for each recipient. 

Preparing your messaging list in excel to send a 100% personalized SMS

This part is about a few nifty Excel tricks that help you merging personal information into your messages. Say you want to confirm appointments for your personal finance business. Your customers expect a personal invitation as well as the date and time of their appointment with you. 

We start with this basic information in our Excel file. Probably this is an export from your CRM tool.

And our goal is to send the following message to the recipients in this file.

“Dear Mr. John Doe, your appointment is scheduled on November 1, 2015 at 11:00. Best regards, Cas Personal Finance”

The first thing we do is adding an additional column in which we will compose our message body. It is this column we will upload in the CM platform.

Using the concatenation function in Excel

The most important Excel function we will be using here is concatenation. Basically this function allows us to paste together different text strings into one, making personalization possible.

So the formula:

=CONCATENATE("Hello"," John")

will resolve into the text “Hello John”. Make sure you tell Excel that these are text strings by adding quotes (“ “) around the text string. Also, do not forget to add spaces as Excel will not add them for you.

The cool thing here is that you can also combine the input of two cells. So if I insert “hello” in A1 and “John” in A2 I can combine them by using the following formula:

=CONCATENATE(A1,A2)

And this will give us “hellojohn”. Apparently we forgot to add spaces :).. adding that will make it look a lot better:

=CONCATENATE(A1," ",A2)

This formula tells Excel to take the input of cell A1, add a space (quotes to indicate a text string, and the space is the only text in this string i.e. “ “), and add the contents of cell A2 to that to give us “hello john”.

Merging data for professionals – 100% personalisation

Let us see if we can take this to the next level. Adding this all together we can make the basic layout of our text message.

=CONCATENATE("Dear ",C2," ",B2,", your appointment is scheduled on ",D2," at ",E2,". Best regards, Cas Personal Finance")

That might look like a complex formula, but if you look at each of the different parts it is nothing different from what we’ve doing before: combining contents of different cells and adding static text strings (and spaces !!) to that.

Getting the time right

Now we only need to solve a few layout problems, the date and time are pasted in as numerical values. Excel has their own notation system for date-formats and time-formats.

Dates in Excel are represented in a number that counts the days since January 1, 1900. So November 3, 2015 is 42311 days after the start of the 20th century. 

Times in Excel work the same. If “1” is an entire day (ie. 24 hours), “0,5” is 12 hours and each hour adds 0.041666667 (1 / 24) to that number. Makes sense right?

So we need to tell Excel that it should use a human readable version of the date and time. To do this we can use the formula TEXT:

=TEXT(D2,"mmmm d, yyyy")

Here we tell Excel to get the date value from D2 and format it in the way we want. “mmmm” means write the full month (ie. “November” instead of “11”). “d” means write the day and “yyyy” means write the full year (ie. “2015” insteads of “15”).

And we can do the same with the time:

=TEXT(E2,"hh:mm")

And if we add this to our existing formula we get

=CONCATENATE("Dear ",C2," ",B2,", your appointment is scheduled on ",TEXT(D2,"mmmm d, yyyy")," at ",TEXT(E2,"hh:mm"),". Best regards, Cas Personal Finance")

A proper salute: mr. or mrs. in Excel

Almost there! Last thing we need to ensure is that we translate the “m” or “f” into “mr.” or “mrs.”. There are many ways to do this. Here we are going to solve it with the IF formula. The IF works as follows:

=IF("a logical test","value if true","value if false")

So we can say; if the value in column C is “f”, you should use “mrs.”, if it is false use “mr.”.

=IF(C2="f","mrs.","mr.")

And in this way we can complete our message, with appropriate salutation, good looking dates and fully personalized.

=CONCATENATE("Dear ",IF(C2="f","mrs.","mr.")," ",B2,", your appointment is scheduled on ",TEXT(D2,"mmmm d, yyyy")," at ",TEXT(E2,"hh:mm"),". Best regards, Cas Personal Finance")

Make sure you apply this formula to all the fields using the fill handle.

Some messaging tools support a “merge tags” functionality that allows you to build the SMS message in the web interface. They require you to insert the column names of the Excel file in the message with specials tags such as |*gender*| or |*name*|.

We receive a lot of user feedback on this functionality. It is hard to use, easy to make mistakes and you need to upload the text in exactly the correct format (ie. for gender and dates). This requires frequently re-uploading the data file and sending example messages to your own phone.

Ready to send your customers 100% personalized sms messages?

By creating the actual message in the Excel file itself it is much easier to build your message and you can directly see the outcomes of your data merge. Although the formula might look daunting, if you break it up in parts it is actually surprisingly comprehensible. Your customers will appreciate it!