By Jason Kiefer
Mailing Systems & Postal Affairs/Lane Press
The key to getting your publications expeditiously into the hands of your readers is (drumroll please) … DATA. Specifically, clean data.
When your mailing address data is accurate and properly formatted, your books move through the postal system swiftly and at the least cost to you.
On the other hand, if your data is outdated, riddled with errors, or simply just isn’t formatted correctly, problems ensue. Your readers may receive their magazines late, or in some cases, not at all; you can incur unnecessary postage charges; and worst, the very same errors may dog you from one issue to the next.
Here in the Lane Press mailing and distribution center, we find that some rules of thumb for data hygiene are familiar to our customers—and that others can seem like inside baseball. So, it’s not uncommon for us to see any of the following errors plague a mailing list. Here, we’ve provided an explanation of these issues and tips on how to fix or avoid them.
-
Zip codes are incomplete.
Excel is often the culprit of this error because it will drop the lead zero in any zip code beginning with zero. Our customers will sometimes try to avoid this by replacing lead zeros with the letter o, but this doesn’t do the trick. Mailing software looks for numeric values only for this field.
Why is this bad? A mail piece with an incomplete zip code can get delayed for verification or outright rejected.
How to fix it: Format the column in your Excel file dedicated to zip codes with the Excel “Special & Zip Code” style. Here’s how:
1. Select your zip code column
2. Right-click and select “Format”
3. Select “Special,” then “Zip Code”
4. Save your file as an Excel or Text Tab Delimited file in order to preserve the lead zeros. Saving as a Comma Separated file will drop them again.
-
Data appears in the wrong place.
There is no set rule dictating the order of columns in your mail list file (one reason for the inside baseball analogy). However, there is one golden rule with regard to data layout: Keep data types consistent within each column. That is, your “Address Line 1” column can’t at any point in your document contain “City” data.
You might say, “Well, duh.” But, here’s the thing: When publishers merge data from various file sources, or someone manually enters information, data can easily end up in the wrong column in even just a portion of your file.
Why is this bad? Mailing software looks for certain data elements in order to validate addresses and then achieve the optimal automation rates for your entire mailing. If the data points can’t be located, some of your addresses may be rejected or delayed, and you may not receive your full automation discount.
How to fix it: Be especially cautious when merging data from multiple sources. Spot check your file to make sure data is consistent throughout each column.
-
Names are formatted backwards.
It is a common error for publishers to format names like so: last_name, first_name.
Why is this bad? Duplication-elimination programs and address-correction software like the Postal Service’s Address Change Service (ACS) and National Change of Address (NCOA) require names to be formatted in first_name last_name order and without commas in order to effectively identify address matches.How to fix it: You can use one single field or multiple fields to house all the potential elements of your addressee name; however, the key is to consistently maintain the following order: prefix | first_name | middle_name | last_name | suffix | professional title.
-
Address_Line_2 information is missing.
One of the most common errors with street addressing is omitting the apartment, unit, or suite number.
Why is this bad? I often tell customers: Imagine your addressee lives in a 300-unit apartment complex in New York City. If your address doesn’t contain a unit number, the carrier will mark that piece as undeliverable.
How to fix it? Spot check your Address_Line2 column to make sure that data pieces such as apartment, unit, or suite number are represented in that column.
-
Abbreviations are incorrect.
Addresses often contain incorrect abbreviations, typically for either street suffix names (i.e.: Anex or Square) or foreign countries.
Why is this bad? Again, services like duplication elimination and address correction look for patterns and key pieces of information. If an address contains an invalid abbreviation, the service may fail to catch important address matches (resulting in delayed or undeliverable mail as well as higher postage costs). With international mail, incorrect country abbreviations require the foreign mail distributor to assign country codes, a process that takes time and incurs a fee.
How to fix it? Here are two foolproof ways to avoid this problem: To ensure you format street names correctly, refer to this list of street suffix abbreviations. With foreign mail, simply spell out the country (in a dedicated country column) rather than trying to abbreviate it.
-
Special characters turn into illegal characters.
It is not uncommon for a special character, say a grave accent, to turn into a not-so-special character, say an upside-down question mark. This typically happens when the data has been saved from a Macintosh.
Why is this bad? At the very least, your reader’s name will appear with the incorrect accent mark. But at the worst, illegal characters can wreak havoc, including acting like escape characters and shutting down processes being performed on your list.
How to fix it: We advise our customers who manage their mailing database on a Mac to avoid using special characters.
-
Your mail file is corrupt.
You know that feeling: When you open a file and in a moment of sheer horror, realize that all your bits of precious data have been rearranged and you want to panic? We see this kind of file corruption happen most often when customers save their mailing file as a DataBase File (DBF).
Why is this bad? Any number of problems can occur when a data file is corrupted, including data appearing in the wrong places, names getting truncated, and single records breaking into multiple ones.
How to fix it: Here’s your rule of thumb: Do not export your mail files as DBF files. The best file formats are Tab Delimited or Comma Delimited. The next-best option is Ascii Fixed Length (larger file size than the others but still, little chance of corruption).
Establishing and maintaining good data hygiene practices with your mail list data is key to your distribution plan. We are always available to help you identify and fix problems in your list—and to help you carry those fixes over to your future lists.
. . . . .
Join us on Twitter and LinkedIn to share your thoughts & opinions.