The Dreaded Prospect List – Tips & Hacks

By William Lessley

If you’re anything like me, you see a lot of data on a day-to-day basis. Hopefully, the data you control (membership, meetings, donors, etc.) is orderly, contained, easily reviewed and manipulated. But what about the data you don’t control? Prospect lists, whether they are for membership recruitment, sponsorship opportunities, or even your very own custom-made drip campaign, can be a pure nightmare to deal with. Trust me, I’ve been dealing with membership recruitment and retention for the last 5+ years – I’ve seen it all. (And, if I haven’t, I don’t want to!)

I won’t give advice on where to purchase prospect lists because things are handled differently depending on where you are geographically located and what you’re looking for, but here are some good things to ask the person or company you’re considering purchasing a list from:

1)      Ask for references! If they’re not willing to give you contact information for previous customers that should be a red flag.

2)      Ask if the list is “one time use” or “continuous use”. As a rule, lists are only supposed to be purchased as “one time use”. Beware if you’re told you can use the list as often as you like.

3)      Ask them how often they update their list and how long ago the list you’re about to buy was updated.

4)      Find out if they use NCOA (National Change of Address) or CASS Certification (Coding Accuracy Support System). Both of these processes evaluate the accuracy of the data you’re receiving. Even if they say they do, contact your mail house vendor to see if they can run your purchased list through their NCOA or CASS system to verify.

There are a few places you can try for prospect lists that do not require payment. If you are a national group, try greasing the wheels with your state level components. If you’re a state level group and there’s a national level, try the reverse (or even your biggest competitor). Many times you can swap member lists, prospect lists, or even swap an e-blast to your members for a mailing list of their members. You can also try state boards if the profession you work with is licensed or has to register at the state level. BONUS: Sometimes you can download these lists right from their website without even having to pick up the phone.

In my experience, most likely software you’ll be using to sort and manipulate your new list is Microsoft’s Excel. The additional tips and hacks I’ll be discussing here relate to this software but if you use different software, some of this information may still apply.

I don’t like formulas. I wasn’t what you would consider an “A student” when it came to math of most kinds. So, the first thing I’m going to suggest to you is a software add-on called ASAP Utilities (  If this add-on isn’t to your liking, there are plenty of other ones out there. Some only do a few functions while others offer even more than ASAP Utilities. Remember to be safe when downloading unknown software – you never know what virus your computer can catch. Use protection!

You want to remove any active members (attendees, sponsors, etc.) from this prospect list before you use it. A lot of the lists I’ve run into over the years have the contact’s name all in one column while my database has separate fields for each part of the contact’s name. Excel has a built in function to separate the data in one column into multiple columns (version 2010 and later). It’s on the “Data” tab of the ribbon and it’s called “Text to Columns”. Before you use it, go ahead and insert a couple of blank columns between the one you’re going to separate and the rest of your information – this will save you from accidentally over-writing data you want to keep.

Highlight the column you want to separate and click the button “Text to Columns”. A wizard will open to walk you through the process. Most of the time, you’ll be sticking with the “delimited” option and telling the wizard what separates the data in that column. If we’re talking about a contact name, most likely there’s a space between the first name and last name. Whatever the delimiter feature is, just tell the wizard and watch it do the rest.

The “Text to Columns” is a great option when you’re trying to determine who to remove from a list based solely on e-mail addresses. For instance, one of my associations structures membership so that anyone from the company is considered a member. We don’t want to solicit employees of a member company but the list may not tell us the company name and it may have the contact’s home address which wouldn’t match our membership database either. But, if you’re lucky, they used their company e-mail address. You just need to compare domain names (the part after the “@” symbol) to see if any prospects share a domain name with an existing member. Use the “Text to Columns” option and tell the wizard the delimiter is “@” and it will separate the e-mail address. Then sort your domain column alphabetically and look for duplicates.


[on step 2 of the wizard, enter “@” in the “other:” option as pictured above]

I’ll be offering additional tips and tricks regarding membership and marketing in the future. Please leave a comment if there’s something in particular you’d like to add about Excel or prospect list tricks.


3 thoughts on “The Dreaded Prospect List – Tips & Hacks

  1. After looking into a few of the articles on your website,
    I honestly like your way of blogging. I saved as a favorite it to my bookmark site list
    and will be checking back in the near future. Please visit my website
    as well and let me know your opinion.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s