Contact list cleaning header image

12 ways to a squeaky clean contact list for SMS, email and more

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

Screenshot showing custom number formatting in Google Sheets

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.

Screenshot showing two adjacent cells being joined in Google Sheets

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

Formula to change cells to title case screenshot

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

Screenshot showing use of the LEFT function in google sheets to find first name

To find a single last name, you can use: =RIGHT(cell, SEARCH(” “,cell,1)).

Screenshot using RIGHT function in Google Sheets to find surname

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!

Screenshot showing remove duplicates function in ClickSend dashboard

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.

Screenshot showing remove duplicates feature in Google Sheets

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.Screenshot showing default opt-out rule applied on all accounts

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

Hi (first. name), we haven’t heard from you in awhile. Do you still want to receive messages from us? Reply YES to continue receiving promotions or NO to be taken off the VIP list. Cheers, [Business name]./span>
Is it us (first. name)? We miss you. If you’d still like offers from us, reply YES to confirm. Otherwise we will assume that you are no longer interested and not message again. You can always come back, just reply START. [Business name]./span>

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.

Sharing is caring!