Give your marketing and IT people a HUGE tip: Run everything you have in any and all of your databases, lists, MRC’s, MCIF, etc. through Excel and find all kinds of errors you can easily fix.
If you have more than 1,048,576 records, split them into pieces so they fit in Excel 2007/ 2010. Of course, we also suggest making a back up and saving your work frequently in case the data is accidentally corrupted.
1. Start by using the Remove Duplicates function. Start with full records, then concatenate various fields, such as first and last name, (with and without MI), first name and street address, last name and street address, and so on to avoid removing records that are similar but not dups.
If even more analysis is desired, consider splitting fields such as street address to separately analyze “directionals”, etc. Others take an additional step and remove vowels and rerunning all the de-dup applications.
2. Sort every column and look at the top and bottom:
- A. Are there blanks in any fields that matter?
B. Do all street addresses start with numbers? Is “Address 2” all appropriately populated with nothing but apartment, suite, or unit numbers? A lot of junk ends up here.
C. Do all the PO boxes look right? Check “PO”, as well as “P.O.” and “P. O.”.
D. On business lists with mail and physical addresses, are the PO boxes (always a mail address) or the physical addresses in your territory (or are both)? There are always records in different towns, or even different states. Some mail isn’t appropriate outside of the desired physical location. As with the PO boxes, decide which address you want to mail if they should be mailed.
E. Are the zips and states legitimate? Do you have any Hawaii, Alaska, Puerto Rico, Virgin Islands, Guam, or Papua New Guinea’s records you may not want to mail? They’re in some nationwide lists as they’re part of America. Are there any Canadians with letters in their zip codes?
F. Does every record have a Zip+-4? If not the USPS isn’t recognizing that address so there’s no postal discounts and a much higher likelihood of being undeliverable.
G. Does any field start with Care Of, C/O, c/o, or % or any other characters such as parenthesis or asterisks? They’re likely all wrong! Change “and” to ampersands-it looks better.
H. Do the names all start with names or are some just initials and do any have just two initials (first and middle)? How do you treat these when writing to them? Dear A or A A? How are your mailers handling middle initials and those with 2 initials?
I. Sorting EVERY column also identifies anomalies in demographics…all lists, in house and purchased, have anomalies (junk).
J. Drag the elevator up and down and just scan each field after hitting “auto-fit columns”. Look for long or short fields as there’s often something wrong with them.
3. Run the “Trim” function on every field.
- A. It can make a big difference in how addresses are printed and for matching/eliminating dups in addresses and allows proper analysis of applications.
B. Extra spaces can be a huge problem for some systems as while they appear to be empty fields, they do have a value…the space(s) which aren’t expected and ruin calculations.
C. After you’re finished with Trim, run Find and Replace on the entire list searching for two spaces, replacing it with MISSED. Then do a search for the word MISSED to see if you missed any space-related corrections.
4. Search or find and replace erroneous characters, really any characters (that weren’t spotted in the sorts above). There should be very little punctuation if any in lists. Punctuation can and should be added at the addressing/printing stage (to make them look nice).
5. Look at business names for “auto-capitalize”, where ATT became Att and IBM became Ibm. It happens a great deal more often than many think. One option is to look through the entire list for these errors or to simply change everything to capitals.
6. If you paid for or gathered demographics you should be checking them before using the data (and of course using them to evaluate your results on every campaign).
- A. Use the “Filter” function to make sure they’re all legitimate values, and how well they’re populated.
B. Next, sort those fields and count the number of each variable. Is the spread and are the quantities of each as expected?
C. Look for every possible anomaly such as 18-year old millionaires or 5-person firms with millions in sales. At the same time, verify how much you’re spending to market to very small home-based businesses or very old or young people or the poorer people.
7. LOOK AT THE RECORDS, especially the names.
Even if you have thousands and thousands you can go through them fairly quickly or you could enlist individual employees (or smart teenagers) to look at a certain number. It really doesn’t take long unless you find a lot of things to repair.
- A. First sort it on last name and look for duplicates. They’ll often have a slightly different first name or an initial or middle initial or misspelling or something to make them appear not to be duplicates to the list seller, but many are.
B. Do the people’s names look right? Nothing could be more offensive than an imperfect name.
C. Check how you address Mr., Ms., Dr., and those with no salutation.
D. Do business contact names have multiple or duplicated titles? We’ve seen lists with 4 “Presidents” at the same company and address. Who knows which one is correct? Most mailers mail all of them as received and never see they mail such junk.
E. Are the business contact titles reasonable? Many are so abbreviated they’re ridiculous, and in our opinion, offensive to the recipient. Unfortunately, many are assigned without the compiler knowing it…they assign “Principal” to many contacts believing they’re an owner of some sort, yet that person’s title is NEVER Principal unless they head a school. Another example is assigning IT/Data Processing, or Sales/Marketing, or some other generic title to everybody on an IT or marketing list. This is all too common. And they ridiculously assign “Owner” to many records, as they did with the mayor of New York City. His title in D&B’s database is owner. Even worse, when buying multiple titles we see 5 “presidents” at a single bank.
F. Address abbreviations are also ugly and surprisingly inaccurate on business lists. You’ll see if you look at them. Upper/lower case is also an issue here.
G. Last, and worst, are business name abbreviations. There is no consistency and they are often ridiculous and nearly unrecognizable. You can’t fit “The Seventh Day Adventists Church of This Town” into 25-characters without some significant abbreviations. Harvard Corporation (the oldest in America) is abbreviated Harvrd Cllege Prsdent Fellows over 50-times in D&B’s file. Not only are most duplicates they’re nearly impossible to match for append or suppress with such a poor abbreviation. Imagine when it’s a less-well recognized firm…it can be absolutely offensive to the mail recipient. In many cases it is a daunting task to fix all of them since there are so many…but we do it a few thousand at a time…
8. If your list isn’t too big, consider running spell check on at least some fields. It can be a painfully slow process on business names so you might skip that, but street addresses also have quite a bit of inconsistency and obvious misspellings.
All of these steps should be taken before running your file through CASS and DPV Standardization and NCOA to increase the chances of matching within those processes.
Once you’ve run your data through all of the appropriate postal processes, send it to the leading compilers. Ask them how many match various datasets (all records, or just those with the best addresses, as well as those within “your” parameters), pretending you’ll want to append some of their data or to buy the unique records they have in your parameters after “suppressing” your in-house lists. And, you might buy something.
It should cost nothing to run a match or match to suppress. This is further discussed at
HowToBuyAList.com.
And for goodness sake, have your people check with us before they buy anything from anybody. If they take a minute to BCC us on their list correspondence, we’ll let them know the possible pitfalls of their selects (and how to correct them) at no charge and without obligation.
Having sold thousands of lists to the nation’s largest 100 banks, insurance companies, and market research firms, we (with their help) have learned to cull only the most accurate and appropriate lists and data for nearly any campaign. We know of no other list sources able to provide higher quality lists than we provide.