Why I needed a better routing system

For the first couple years of Patriot Chimney’s existence, we basically ran two trucks. Both trucks doing a little bit of everything. Inspections, sweeps, installations, masonry — you name it.

Very early on, my vision was for our technicians to specialize. We weren’t sure exactly how the best way to do this so we’ve tabled it for a long time.

In the summer of this year (2020), our receptionist had a baby and I had to step in, scale back our marketing (my realm), and answer all of the calls.

I also saw this as an opportunity to see how we’ve been routing.

At that time, our receptionist would just write a city in the calendar and then schedule jobs in that city on the calendar.

I should also note that I had also just got done reading E-Myth Revisited by Michael Gerber. He talked about setting up an organization chart for how you want your company to exist.

Taking over the phones was a great opportunity to finally set up specializations by implementing our new org chart and optimize our routing system.

Two birds with one stone!

My goal was to solve a couple of problems:

1. Set up the org chart – build out the specializations (2 types of jobs – repairs & inspections)
2. Quantify the routing area – build a system to determine where we are the most. Schedule jobs based on that information. 
3. Schedule jobs with proximity or other jobs in mind – keep jobs in specific areas. This way our techs aren’t driving all over the place.
4. Increase our efficiency and revenue – If we can limit windshield time, we can fit more jobs in each day.

You’ll probably have a ton of other reasons, but you can’t go wrong with increasing your own day-to-day efficiencies. 

Pull Reports from Your CRM

I use Jobber as my scheduling software and CRM.

The first thing I did was pull a report for all of our visits for a 24 month period. You need to make sure you are pulling information for ZIP Codes. 

If your jobs are already segmented and your techs are already specialized, you should make sure you also pull information on the line items.

Upload the File to Google Sheets

Jobber sends the report as a CSV file. You can actually uploade the file into Google Drive and open it with Google Sheets.

The CSV files are fine as they are pertaining to aesthetics. But I like to format the file in Google Sheets so that it’s a bit nicer to the eyes. Nothing too fancy, unless you’re presenting it to your boss. 

Organize Your Data

Once the file is in Google Sheets and everything looks nice, I updated the spreadsheet to include four new columns to work with. So you’ll have the following columns: 

• List of Zip Codes
• Closest City
• Count
• Average per Month
• Scheduled Days per Month

List of ZIP Codes

When you first have your CSV file, the information will still be raw data. You’ll have a complete list of all the ZIP codes you’ve serviced. 

When you create the new column of ZIP codes, the goal is to have a list with no duplicates. 

In cell E2, add this formula: 

=UNIQUE(B2:B)

This will pull all of the unique ZIP codes from the original list of ZIPs in column B. 

Closest Cities

Brace yourself. This is the worst part. It’s very tedious, especially if you have a large service area with lots of ZIP Codes. 

This is a tedious step, but it’s 100% needed. And it’s not something that I’d recommend outsourcing to your virtual assistant if you have one. 

I used ZipMap to manually search each ZIP code in my spreadsheet to look at the closes city to the ZIP code. 

Some ZIPs are in a small town right outside of the bigger cities, so they should be bunched in with the city.