Using Free Zip Code Data for Quick and Simple Analysis

As a one‐person researcher working at a smaller college, I became very interested in discovering cost‐effective ways in which I could quickly assess my prospect pool. One of the more effective and simple ways I have found, has been to append zip code data to any list, using MS Access.

If you have not used MS Access much or at all, I urge you to add this powerful tool to your arsenal. The research paradigm has already shifted, from one‐at‐a‐time profile creation, to top‐down assessment. This trend has caught on, because it works. Anything you can employ, in terms of data manipulation, is only going to make you more effective in your position. If you are doing prospect research in an organization that has a prospect pool with prospects that are dispersed among several states, zip code‐related data appends will serve you extremely well.

Using a zip code as a geographic region is not without its problems. Zips were not created with geography in mind: they are mail routes. But because zip codes already exist in our database, and because they will tend to always map together with other zips generally, the zip is a natural variable to rely on for most projects, lending itself very nicely to further analysis.

There are many ways I have found to use zip code data, but there are two that have been the most helpful to my work. The first is in mapping or listing individuals and organizations by metro area. The second is with IRS average adjusted gross income (AGI).

Databases are typically organized so that an ID attached to a record links to the same unique ID in
other tables. What we will be doing uses the same concept, but links the zip code in a list that you have exported from your database, to a saved spreadsheet on your network or PC. In order to link the data, your zip codes must be 5 digit zips, and must be in a text format in order to preserve leading zeroes.

Zip IRS AGI data and Zip metro area data are free. When I come across tables that will be of use to other researchers, I tend to save them in my google documents. I have various tables there, so feel free to look at them all or download all of them. The two tables I will be speaking about here, are in a subfolder, and are called:

Zips_and_IRS_Avg_AGI_2008.xlsx (1MB)

ZipAndMSA.xlsx (629KB)

The link to the subfolder is: http://tinyurl.com/zip‐agi

Metropolitan Statistical Areas (MSA) are higher population density areas. Not every zip code will have an associated MSA to go with it. Linking a Zip Code to a metropolitan area will enable you to group records in your database together into such areas.

The Internal Revenue Service publishes Average Adjusted Gross Income on its website. As of this
writing the most recent data is from 2008. There are limitations to the data. For prospect identification, I have found the use of this data to work very well. Linking two tables in Access is a very simple procedure, and will enable you to create a query using fields from those two tables. In MS Access, you will need to import a table containing information from your database. This table will need to contain basic biographical information to help you identify or qualify these individuals later on. I always like to include a system ID. A five digit zip code, in a text format will be essential.

Next you will need to import the two tables that I have linked to above, via tinyurl. (In Office 2010, External Data > Import > Excel).

Once all of these tables are in Access, you can create a query to append AGI or metro values to the individuals from your database. (In Office 2010, Create > Other > Query Design). Add your database table, then add the Avg AGI table to the query. Create a table join by clicking on the zip code field in your database table, and drag to the zip code field in the Avg AGI table.

Right‐click on the join itself, and choose "Join Properties". You will want to choose the join where all
the records from your table will be displayed, but only the records from the IRS data that match will be included.

Once your table join properties are set up, click on the fields you want to include in your results. Sort
descending by IRS Average AGI to reveal wealth potential. Automatic and instantaneous prioritization for any prospecting activity. I have used this method constantly and it has yielded great results. You can also combine it with other variables to come up with a composite score.

The simple procedure I've outlined in this article is very basic and is meant to serve as only an example of a way we can quickly assess a prospect pool. Starting with wealthy zip codes might be a good idea when doing prospect identification.