What's the best way to get the most out of any SMS campaign? Making sure that your message actually reaches your customers. And it all starts with your contact list.
Clean contact lists have correct customer records so each message gets delivered to the right person. It also prevents customers receiving the same message from you multiple times in the same campaign.
And ultimately, that keeps your engagement rates high and reduces cost. No more paying for text messages sent to unregistered numbers or duplicate contacts. Plus, it’s much easier to measure your campaign data at the end of the day.
💡 Looking for ways to grow your contact list? Check out our 22 ways to grow your opt-in contact list.
Ready to improve your campaign ROI? Let's get cleaned up 🧼
Discover how to fix common list cleaning solutions (and improve campaign ROI)
List cleaning isn’t hard but it can become tedious or time consuming. Luckily, there are some insider tips and tricks that will make it much quicker. We’ve included answers to common list cleaning questions from our customers in this blog (along with our favourite tips).
The first thing to know is: list cleaning can be done before and after uploading your contact list.
- Make sure your contact list source is in good shape before uploading. Most often, this happens in an Excel Spreadsheet or a Google Sheet.
- Clean your list after uploading and sending campaigns in our Dashboard.
We’ve included some of the common spreadsheet formatting issues we see, as well as ways to ensure your list is in good shape after sending campaigns.
Hopefully this will help you send your next campaign with total contact-list confidence.
Note: We’re using Google Sheets in the examples. If you use Excel, names of functions might be slightly different.
1. Fill in missing column names
We see this one a bit. Often when uploading a contact list for the first time, it can be easy to forget to label each column. If your columns don't have a name (sometimes we call this a header), you may find issues with importing your list.
How to fix it
Open your spreadsheet and add a row above your first row of customer data. Enter the names of each column data.
Tip: using the template header names will make uploading your list easier. These are: phone, first_name, last_name, custom1-4 (for custom fields), fax_number, organization_name, email, address_line_1, address_line_2, address_city, address_state, address_postal_code and address_country.
2. Check phone numbers are the right length
It's easy for people to accidentally miss a digit or tap an extra key when entering phone numbers, so it's common for us to see contacts in lists where phone numbers are not the right length.
While we try to help with some of the heavy lifting – our SMS gateway won’t send messages to numbers that get flagged for incorrect formatting. However, manually checking your list is always a good idea, as you then have the opportunity to correct the data before sending. Some number formats are tricky for example, so it's best to double check just to be sure.
How to fix it
Skim over your list and look out for any issues with phone number prefixes or formats. Update these ASAP.
List too long? Add a column next to your phone number column and use =LEN(cell) to count the digits in that cell, then drag the formula down. Then find cells with incorrect length using the Find tool or by applying filters.
3. Delete any unicode characters
Phone numbers should only contain digits. Odd looking characters in phone numbers, often unicode characters, should be removed.
How to fix it
Check your spreadsheet for odd characters and delete any you find. Then use the find and replace function to easily locate and delete any others.
FYI: common unicode characters we see include: í " â € ™ ` ?
4. Keep the 0 in front of phone numbers
Lost the leading 0 in your numbers? This is a common problem in contact lists. There are two simple formatting fixes.
How to fix it
Option 1: custom number formatting
Use custom number formatting in your spreadsheet to set a zero digit rule. Go to Format, Number and choose Custom number formats.
If your phone numbers should be 10 digits long, add your customer number format as 0000000000 (ie. 10 zeros).
Option 2: add your country code to your spreadsheet
Do this by creating two new columns, add the appropriate country code and then join the country code with the phone number.
Use & to join the contents of cells.
5. No more exponential formatting
Do you see any E+ in any of your numbers? Well, these are good for scientists but not so good for contact lists. This is another common formatting issue that can be fixed in a few clicks.
How to fix it
Once again, custom number formatting to the rescue 💪
Open your spreadsheet and highlight the column. Click Format, Number, Custom number format and choose the number format with no commas or decimal points.
6. Delete unwanted characters from international numbers
If you have global customers, you’re likely to have a mixture of numbers from different countries. Depending on the country, you may find hyphens, brackets, spaces or other symbols in your phone number data. You’ll need to remove any extra characters or symbols.
How to fix it
Find and replace is your friend. Enter the character you want to find and leave the replace field empty to remove it. It may help to sort your spreadsheet by the phone number to easily see numbers by country code.
Tip: it’s best to include the country code when uploading lists with phone numbers from multiple countries. The other option is to add a country column with the appropriate 2-letter country code and make sure to include a + sign in front of phone numbers. More info about international number formatting here.
7. Polish up names with capitals
Have you ever received a text or email where your first name is all in lowercase? It feels very unprofessional. Make sure all of your customer names are properly capitalised in your spreadsheet. It’s speedy to do and it’ll make your personalisation more polished.
How to fix it
Highlight the whole row that lists numbers. Then add a formula. Use PROPER to quickly spit out names in title case format. Every name will be changed to a capital, it’s that simple.
8. Separate first name and surname by column
If you’re personalising texts to customers, you want to call them by their first name only. It can seem very cold to address customers by their full name. Plus, surnames take up precious characters.
Make sure your name data is separated. First names in one column and last names in another. If first and last names are recorded together in your contact list spreadsheet, here’s a few ways to separate them.
How to fix it
Split text to columns
Do this by highlighting the name column in your spreadsheet. Then use the split text to column function, using space as the separator.
Make sure the columns to the right are empty, as the names will be spread over these adjacent columns.
Note: this works well for customers with a single first name and surname eg. John Smith, or where you only need their first name.
Use the left and right function
Find first and last names using LEFT and RIGHT combined with SEARCH.
To find the first name in any given cell, use: =LEFT(cell, SEARCH(" ",cell,1)).
To find a single last name, you can use: =RIGHT(cell, SEARCH(" ",cell,1)).
However, if surnames have multiple parts, there are a few more advanced ways to use the LEFT and RIGHT formula. Check out this guide from Microsoft.
9. Remove duplicate contacts
Nobody likes to double up. Remove duplicates in your contact list is a sure-fire way to boost ROI in your next SMS campaign. You customers don’t want to receive two of the same SMS and you don’t want to pay twice.
How to fix it
There’s a nifty feature in the Dashboard that will find and delete duplicates for you Simply go to your contact list, click Clean up and choose Remove Duplicates. Then select which field to define duplicates by.
That’s it!
You can also remove duplicates before uploading your list. Google Sheets and Excel have ‘remove duplicate’ functions where you can define duplicates based on multiple cells matching.
10. Remove opt outs
When people opt out of SMS, removing them from your contact list is non-negotiable. There are a few ways you can handle opt-outs. Automating this process is best.
How to fix it
The easiest way is using our default opt-out feature. When contacts reply STOP or use the unsubscribe link, their details will be added to an opt-out list for future SMS campaigns.
This is done through inbound message rules - if an inbound message contains 'stop', the contact is moved to an opt-out list which you can apply to all your campaigns. This rule is applied to all accounts as the default.
Note: You’ll need to have your contacts stored with us. You’ll also need to be using a shared number or a ClickSend dedicated number. If you use your business name (alpha tag) or mobile number, you’ll need a different process (our customer service team can help you here).
Check out our detailed Help Guide on managing opt-outs.
11. Remove failed contacts
Any time an SMS fails to deliver, it’s a good idea to find out why. Then you can choose to keep or remove the contact from your list.
Some reasons why messages fail include contacts providing a landline, recipient is abroad, network issues or messages being flagged as spam and more.
How to fix it
First you need to find failed messages. Do that by viewing your history in the Dashboard. Click SMS and then History. Messages are automatically sorted by send time so click Status to group by status.
If there are one or two failed messages, it’s probably easier to copy the phone number over manually. If there are more, click Export in the top right to download your message history.
The file will be in CSV format, so you can open it in your preferred spreadsheet tool. Then filter to the Failed messages and, if invalid, add these numbers to your opt-out list.
Here are more detailed instructions: How to remove failed contacts.
12. Remove stale contacts
It’s normal for contact lists to get stale. People might not need your products or services anymore. Or maybe they’ve changed details but haven’t opted out.
If customers are not engaging with your campaigns, they might be no longer interested. So, why waste money messaging them? You can improve your campaign ROI by removing disengaged contacts.
How to fix it
Check if a contact is disengaged by checking how long it’s been since:
- Their last purchase.
- They clicked on a link in your SMS (you can see this in your SMS history).
- They opened an email from you.
- They called/emailed/texted your business.
Before you remove contacts, it might be worth trying to re engage them with a campaign.
Re-engagement campaigns
We recommend that contacts should have opted in within the past two years. If they haven’t, and they're not engaging with you, it might be worthwhile running a re-engagement campaign for contacts who are ghosting you.
How to set up a re-engagement campaign
Re-engagement campaigns are common in email marketing. Or businesses will use SMS to revive stale email contacts. But you can set up re-engagement campaigns for your SMS contact list and send via SMS – with that nifty 98% open rate, you know the message will be read.
It could be as simple as:
You can add an incentive like a discount, gift or exclusive invitation to encourage subscribers to re-engage. You can also give contacts a warning before removing them from your contact list. Just don’t forget to include an opt-out option in your message.
Tips for collecting contact data
Good input, good output. Save on list-cleaning time by setting up a standard set of data collection conventions wherever customers can drop their details.
For example:
- First name and surname collected as separate fields.
- Address fields separated into line 1, line 2, suburb, state, postcode and country (if customers are across several).
- Data source will only submit if phone numbers have the correct number of digits, begin with the correct area code and don’t contain symbols (like spaces, dashes or brackets).
Using double-opt in will also help to ensure that the phone number collected is valid and active.
Want to spring into SMS with a clean list from day one?
We can help you get set-up for success from day one so there’s less cleaning and more sending. Plus, we only ever use direct routes and has 100% uptime for total reliability.
Talk to us via our live chat, or get started with a free trial.
