Simple Zip-Based Prospect Ranking Using Social Explorer and VLOOKUP
If you got past that title, then welcome, fellow nerd.
Today I faced a common conundrum of many a fundraiser: a looming deadline and a long list of potential donors that needed to be prioritized.
Background: About a year ago, the organization for which I work purchased a database screening (sending constituent records to a vendor for data modeling and/or having asset data appended). We had 975 additional screens remaining, and a fast-impending expiration date, after which we would lose the screens.
The data I had was sparse -- just the names and addresses of a pool of 3,715 potential donor prospects -- and I had mere hours to select the quarter of these 3,715 individuals I should screen. Because this type of screening is best used to identify major gift prospects, I wanted to get the best bang for my buck by narrowing my list to those who were likeliest to have sufficient wealth to make a gift of $25,000 or more to our organization.
By "pre-screening" the data, I figured I would wind up with a greater percentage of major gift prospects among my 975 names than I would were I to randomly select 975 individuals from the list of 3,715. (If you want to skip the how and get to the what happened, scroll to the end to see the results.)
What to do?
With only address data on hand, the path was clear: geodemography.
I decided to rank the prospects using census data: median income in each 5-digit zip code.
First, I had to get my hands on the census data. Then, I had to correlate the data to my prospect list. This is where VLOOKUP enters the picture, but first things first: the census data.
The U.S. Census site is an absolute nightmare to navigate, and extracting data from it is challenging and frustrating. (What is it with government websites? They are consistently awful.) Thank goodness for Social Explorer, which lets you create your own maps and reports based on U.S. Census data.
Social Explorer is very easy to use -- in the Reports tab, simply select your geographic area (in this case 5-digit zip code) and then select the demographic data you want to extract (Median Household Income in 1999). I decided that 1999 data was good enough for this project. When selecting your output, I recommend using the .csv (comma-separated values) or .tab (tab-delimited) option available in the "Download Data" tab. (The Excel option made a really weird output.)
So, I'm sure you're dying to know: when do we get to use Excel's VLOOKUP formula?
Right now!
The next thing I did was open up my list of 3,715 prospects. Then I created a second sheet (creatively named Sheet2) in the same workbook, and imported my .csv file from Social Explorer into Sheet2.
Now I had the prospects' zip codes and the median income by zip code list in the same Excel workbook. (They don't actually have to be; I just find it easier to work with that way.) I needed to correlate each prospect's zip code with the correct median income value so that I could rank them.
In situations like this, Excel's VLOOKUP (vertical look up) formula is ideal. I created a new column in my original prospect list called "Zip Median Income" and I entered the following formula in that column:
=VLOOKUP (F2,Sheet2!K:L,2,false)
This formula tells Excel:
- Look at the value in cell F2. In this case, Column F contains the prospects' zip codes.
- Now look up the value in F2 on a list in Sheet2 in Column K (the first column named in the range given in the formula). In this example, Column K contains all of the zip codes in the U.S.
- Look at the second column in the range, Column L (median income), and return the value that is in the same row as the zip code match.
- The last bit of instruction ("false") tells Excel "Don't accept somewhat close values -- I want you to look up and match the exact value that's in cell F2."
So, if the first prospect on my list lives in Esopus, NY, the value in cell F2 is 12429. Zip code 12429 is listed in Sheet2 in Column K. The corresponding median income value in Column L is $200,001 (since Esopus is one of the ten zip codes in the country that had a median income greater than $200,000 in 1999). The look up formula will return the value of $200,001.
Side note: What if your address data has zip+4 codes? Use the "Text to Columns" function on Excel's Data menu. Set the hyphen as your delimiter to split your zip+4 into two columns, one a 5-digit zip and the other the +4.
I used my new prospect list with its median income data to rank my prospects (or more accurately, to rank their zip codes) from highest median income to lowest median income. Additionally, I cross-referenced my prospect list with Forbes' Expensive Zip Code list, also using the VLOOKUP function, which helped me find a few more potential high-net worth individuals. (There are a few wealthy geographies with high home values but relatively low incomes, likely because the income data is skewed by a large retiree presence.)
So... the results?
Out of the 975 individuals I screened, the results indicated that over one-third appear to possess sufficient wealth to give a major gift. An additional third appear to have the capacity to make a significant gift of $10,000 or so. This was a fabulous result, since out of any given random prospect population, I would only expect five to ten percent at most to have the wealth required to make a major donation.