Helpful tips for cleaning your email list

We work with a lot of customers that are moving between marketing tools and one of the big things that usually comes up is cleaning up the email list a bit and filtering out the contacts that aren't engaging anymore. 

Spent a bunch of time today in Excel and Google Sheets working through cleaning up a list and found a couple of things that would be helpful to share. 

I'm no Excel wizard, so if you are - this probably will be incredibly boring, but for the rest of you folks it may actually be helpful. 

 

#1 - DeDupe your List

You have a whole bunch of different lists, combine them together, and have no idea who is a duplicate. Ideally you want to filter those out to avoid creating a new mess, in your new tool. 

With Google Sheets it's incredibly easy with the built in Unique function. (The unique function is not available in Excel from what I can tell - but I imagine there is something similar.)

=Unique(A1:A)   - update this to include whatever the range you're looking to dedupe and it will do the rest of the work for you, spitting out only the unique email addresses. 

Google Sheets Unique Function

 

#2 - Remove people that are in another list

Working with another customer and we got them to a great spot with a cleaned up list of contacts they wanted to engage with for marketing, but wanted to be sure that they filtered out existing customers from that.

This is pretty simple if you create a spreadsheet in Google Sheets or Excel (this does work the same in both) and use a vlookup.

Here is the exact function that you need to use: 

=IF(ISERROR(VLOOKUP(A2,$C$2:$C$12, 1, FALSE)),FALSE,TRUE )

Update A2 to be the cell you're checking and the $C$2:$C$12 to include the range you're checking from. Just so you know the $ is there to keep that range despite moving the function on the page. 

Then just copy/paste it alongside all of the other emails you want to check, and filter for the ones that are false - which indicates they aren't in the other list. 

This will walk you through the whole process.

Google Sheets filtering specific leads

There you have it - all of your customers filtered out and your list is ready to go to get uploaded into your system. 

Hopefully this helps save you some time and Googling finding the answers when it comes time to clean up your list. (I selfishly wanted to post this so I could come here the next time I need to do it...) 

Don't hesitate to reach out sometime if you need help with your list cleanup.