top of page
  • Writer's pictureZach

Scrub an Email List with the Query Function

Use this Query function to clean a list of emails by subdomain. A good alternative for those of us too stingy to pay for “bounce rate reduction” software


Get that email list squeaky clean!

If you have done any email marketing you’ve dealt with the headache that is invalid email addresses. You try to send a blast and either you reach a send limit or find out that your emails are going direct to spam. You do a little research and discover that your “bounce rate” is the likely culprit.


There are some pretty decent software solutions for this problem. Standalone tools like BriteVerify and FreshAddress work well. And most email marketing tools (MailChimp, Marketo, Hubspot) and transactional email services (Sendgrid, Postmark) have integrations built in. The good news: these tools use effective, sophisticated methods. The bad news: they are expensive.


For all ye casual and resource-constrained marketers, here’s an alternative:


QUERY ( returnRange , " SELECT returnRange_columnLetter WHERE returnRange_columnLetter <> '' " & JOIN ( " " , ARRAYFORMULA ( " AND NOT returnRange_columnLetter CONTAINS " & ARRAYFORMULA ( "'" & FILTER ( exclusionList , exclusionList <> "" ) & "'" ) ) ) )

With this formula, you create a list of email domains to ignore (.e.,g “@test.com), and then the formula filters your list to exclude emails with those domains.


Quick pause… I’m sorry for using QUERY. I really am. It’s confusing. And error checking is a nightmare. I try to avoid it whenever possible. Unfortunately, this time, I couldn’t. (Send suggestions!)


Anyway, here’s the formula in action:



In this mini-example I’ve created my own small list of email domains to ignore, specific to my context. You can also source lists of crap domains from GitHub and elsewhere (this, for example)


Leave likes and comments below. Thanks for reading!

39 views0 comments

Recent Posts

See All

Comments


bottom of page