Recruiting by commute Time

If you recruit from a known-population of candidates like licensed professionals, you use some type of geographical criteria to limit the number of contacts. Most often this is done by using a radius around the work location. (Assuming the work must be done on-site.) There are multiple problems with this approach:

  • Some software packages do a poor job of geocoding. Some don't use the full address of the licensee, others calculate the distance based on just the zip code. Both give inaccurate results.
  • Even in cases where the address was correctly geocoded, the distance used is a "great circle" distance or "air miles". If your location is near a natural barrier like a large body of water, the air miles don't give an accurate representation of commute time.

A better approach is to use estimated commute time. After all, that's what your recruiting target is going to be using as a factor to decide whether to accept your offer.

Today I'm going to demonstrate using Google Sheets to calculate driving time between a work location and around 60 licensed candidates. We'll compare the commute times to the "air miles" radius search that is normally used.

Getting Started

The first thing I do is create a new sheet and put the facility address in a cell. This will be the destination for all the commutes. Next I create a table of all my professionals. I just use addresses because that's all I need. Yours would include name, phone number and email. Now I need a formula that will tell me the commute time between the facility address and the licensee address.

I'm using Google Sheets because of the powerful scripting capabilities available. I can create my own custom function that can access the Google Maps API and ask how long it will take to drive between two addresses.  I want something like "=TravelTime(OriginAddress,DestinationAddress)"

There are plenty of resources out there to show you how to write App Script so I'm not going to go into detail here. (Contact me if you need help or want my code.)

I used the API with a key. This is not free, well not completely free. The first 2500 lookups per day are free. After that there is a nominal fee. The fee is extremely low. There is another reason to use the paid version as well: the completely free version is not very reliable. I tried using it for this posting and had numerous, persistent problems that many others were complaining about as well.


I plotted the miles from the facility versus the commute time in minutes above. As you can see, the relationship isn't completely linear. If you did an 8 mile radius search around the target facility, you'd be excluding people with the same or shorter commutes than people who lived closer. (I've highlighted three.)

Obviously, doing this in Google Sheets would only be appropriate for small operations. You would definitely want to bake this into your application or create an external app to do this for you. Fortunately, the Google Maps API works from several different languages.

If you want to find out more, or just want my Apps Script code for this posting, contact me!