Tips on Exporting: The Dreaded Dropped Zero on Zips

Sending direct mail to lists of property owners at their home address often means that your marketing outreach is going to get delivered all over the nation, not just the state where the properties you are targeting are located.

This is especially true if you are targeting “Non-owner occupied” properties and are trying to reach the “Absentee Owners” at their home address.

For example, John and Jane Smith own a rental property in California, but reside in Connecticut. To reach them by direct mail, your outreach will travel to Connecticut.

Exports with Zip Codes

There are 3,669 zip codes in the US that start with a zero, mostly located on the east coast.

When you export a List to a .CSV file that contains a zip code that begins with a zero, it is written in the .CSV file as "01234" because zip codes are five digits: 12345. Zip+4 is also more common, "12345-1234." The first five digits indicate the destination post office or delivery area, and the ZIP+4 Code represents a specific delivery route.

Importing .CSV files that contain zip codes with leading zeros can lead to the "Dreaded Dropped Zero on Zips problem."

Some programs, Microsoft Excel most notably, treats the "01234" as an integer, not a string, and it drops the zero leaving you with a 4-digit zip code "1234". Not good!

Postal services, especially the USPS, will not deliver to malformed zip codes. They must be five digits, even if the first 1, 2, or 3 zeros get dropped.

Managing & Validating your Exports

When sending a direct mail campaign, it's always best to inspect your list and sort by zip code smallest to largest and validate that all zip codes are five digits.

If you happen to find 3 or 4 digit zip codes, you will need to change the column format to a "00000 zip code" and add back the leading zeros before printing your labels or uploading that to a direct mail service.

Most direct mail services will reject addresses with malformed zip codes. So if your list of 1,000 addresses contains 100 malformed "dropped zero" zips, only 900 will be uploaded. This true with our print partners.

We intend to build a native postal address validation service into PropertyRadar in the future, however, until that is available and you do a lot of direct mail from exports, please consider using a 3rd party postal address validation service or minimally inspect your zip codes from exports.

This “Dreaded Dropped Zero on Zips” is a problem well known to marketers who frequently send direct mail, and dates back to 1979 and the first consumer spreadsheet program that viewed imported zips as integers, not strings.

The best way to preserve leading zeros in Excel can be found here:

https://support.office.com/en-us/article/Keep-leading-zeros-1bf7b935-36e1-4985-842f-5dfa51f85fe7?ui=en-US&rs=en-US&ad=US&fromAR=1

Have more questions? Submit a request

Comments

Powered by Zendesk