W  I  L  L      B  E  A  U  C  H  E  M  I  N

 

"A problem 
well stated 
is a problem 
half solved."

~ Charles F. Kettering, American inventor


If you had to find three good candidates for a job vacancy, why would you consider finding 30,000?    

Consider this instead:  What is the most efficient way to find good candidates for any position in the entire country?  When I asked myself that question when contracted by Wells Fargo, my thoughts went to how candidates were currently searched:  by searching the web.  What if we had a database of every potential candidate in the country and could search them as we liked?  The result of that line of thinking led me to create the database of 30,000 people --- the idea that would have seemed ridiculous had I limited myself to the immediate question of finding three good candidates. 

On this page I describe this and other case studies involving creative problem-solving.  I am a fan of Roger von Oech and often use his "Creative Whack Pack" to spur creative thinking.

Wells Fargo:  Finding 30,000 prospects was easier than finding a few

SITUATION

Wells Fargo Home Mortgage (WFHM) contracted my services to help them identify prospective candidates for mortgage loan officer positions.  The criteria were usually very specific and involved foreign language fluency, loan or building specialization, and sales awards as well as geographic proximity to the office for which WFHM was recruiting.

Although I was contracted for my research skills, I quickly realized that it would be easier to simply create a searchable database of almost every mortgage loan officer in the country.  With permission to redefine my contracted role, I began to web scrape the mortgage loan officer web sites.  NOTE:  Since this time, U.S. law and "Terms of Use" agreements concerning web site information have gotten stricter, but not prohibitive --- so such a project may be more difficult today.

ANALYSIS

  • Get information on every U.S. mortgage loan officer.
  •  
  • Clean, parse, and delimit the data.
  •  
  • Enable highly-refined searches to be run:  geography, skills, languages, etc.
  •  

SOLUTION

Using web scraping software, I automated searches of mortgage loan officer web sites, running searches by ZIP code using a table of all U.S. ZIP codes.  Out of professional courtesy (but now a requirement of U.S. law), I minimized the demand on the web site's server by running searches only during overnight hours and spacing each search by one minute.  During the day, I cleaned up the data with VBA and often by hand, populating a growing table of prospective candidates.

For the interface I created CANINE:  Candidate Identification & News Database (the news part was a feature that employed my competitive intelligence skills).   This was a distributed MS Access-only client/server application.

 

  With the user interface:

Recruiters could specify a radius (miles) around the ZIP code of the office for which they were hiring --- and pull every competitor mortgage loan officer who worked in that radius.
Recruiters could refine their search by specifying one or more foreign language skills, areas of specialization, and whether or not they wanted to see only those with sales awards in the past five years.
For any prospective candidate, the recruiter could click a button and see if the candidate was or had been already considered for employment.  This feature tied into an HR database.

With CANINE, recruiters could spend more time recruiting and almost no time researching.  For me, I was able to move into creating the competitive intelligence part of the tool, which fed the recruiters talking points about the prospective candidate's current company.  

After my assignment was completed, I was contracted twice a year to refresh the data in CANINE.  At one point, the database recorded over 30,000 mortgage loan officers from around the United States.

 

Click the image below for a slide show on CANINE:

GTT:  Using US Census web site to validate addresses

SITUATION

After a trade show, the client would have 100+ names and addresses of potential government customers.  First handwritten and then typed into a spreadsheet, the results needed to be verified before being uploaded to the CRM database.  For-fee services could be used, as could the US Postal Services lookup API --- but the first would be too expensive for small counts and the second is limited by terms- of-use, user account, and number of searches.  

ANALYSIS

  • Need an efficient, free, "good enough" method of address verification.
  •  
  • The few unverifiable addresses could be individually searched using the US Postal Service website's lookup or by web searches.
  •  

My solution would therefore need to address three areas:

  • Accuracy
    Automation/Ease of use
    Be free or low-cost


SOLUTION

The US Census Bureau website has a no-cost feature that lets you look-up an address to obtain information on its Census geography:  census block, census tract, county and more.  In doing so, it verifies that the address actually exists.  I therefore automated the search of the trade show addresses using this feature.  The Census data was reliable because the Census tracts individual homes and offices.  Although the Census is performed only every 10 years, there are regular updates of address information as the Bureau makes estimates and prepares for the next decennial Census.  Post office boxes, however, are not tracked and will therefore always be unverifiable with this method.

 

 

Since the time of my automated solution, the US Census Bureau has created a batch lookup feature.  You can now search 1,000 addresses at a time with no restrictions concerning frequency or terms of use.  The principle and results of that new method are the same as my original solution:

Names are not used in the search, so anonymity is preserved.   
"No match" is returned if unverifiable.  Matches are noted as beings Match/Exact, Match/Non-Exact (usually due to abbreviations), and "Tie." Ties occur if you pass an address for an apartment building but omit the apartment number.  It is still a match, but identifies a problem.  
You can pass street addresses with only a ZIP code and get the city and state returned.  If you have city and state without a ZIP code, ZIP code will be returned.
As an added bonus, you also get the county of the address as well as its census block and census tract (useful for applying demographics to an address).

As mentioned, the new Census Bureau bulk search feature has now made my automated solution obsolete.  In a recent test using 1,000 actual residential addresses, the feature was able to verify all but 202 --- 39 of which were post office boxes or blank addresses (which are always unidentifiable).  Omitting these obvious exceptions, the Census Bureau bulk lookup had an 83% match rate at no cost.  If this were applied to very large customer databases, there could be a significant cost savings in using for-fee address correction services.

To learn how the Bureau's new feature works, click the image below.

Website designed and created by Will Beauchemin.  Graphics 2013 Iconshock by Unusual Minds and 2002 Riverdeep Interactive Learning Ltd and its licensors.  Map graphics 1988-2012 Microsoft Corporation and/or its suppliers.  Except as noted, website 2015 Will Beauchemin.