How to View ALL Jobs in Google Maps to create better routing in TCS

Export completed jobs, clean the spreadsheet, calculate job ticket hours, add the day of week, and import the data into Google My Maps to visualize routes and group homes by cleaning day for better scheduling efficiency.

 

Step 1. Export completed jobs report

• Go to Reports → Jobs Report

• Set date range (example: Feb 1–28)

• Filter to Completed Jobs

• Select columns:

  • Customer, Address, Service Type, Subscription Name, Square Footage (optional), Date Completed, Estimated Time, Price

  • (anything you would need to be able to CLEARLY identify the house)

• Click Search → Download spreadsheet

---

Step 2. Upload and clean the spreadsheet

• Upload the file to Google Sheets

• Freeze the top row

• Sort by Customer Name

• Remove duplicate visits (keep only one per customer)

• Delete deep cleans if you only want recurring customers

• Keep these columns:

  • Customer Name

  • Address

  • Service Type

  • Subscription

  • Size

  • Date Completed

  • Estimated Time

  • Price

---

Step 3. Calculate Job Ticket Hours (JTH)

• Create a new column called JTH

• Convert minutes to hours with a formula
  Example:

  =90/60

• Format the column as Number

• Hide the original estimated time column if desired

JTH represents the actual work hours used for route planning.

---

Step 4. Add the day of the week

Create a Day column using the formula:

=TEXT(F4,"ddd")

This converts the job date into the weekday (Mon, Tue, Wed, etc.).

• Drag the formula down for all rows

• This will later allow grouping by cleaning day

---

Step 5. Import the spreadsheet into Google My Maps

• Go to Google My Maps

• Click Create New Map

• Click Import

• Upload the spreadsheet

• Select columns to include (Customer, Address, Service Type, etc.)

• Use Address as the location for pin drops

• Group pins by Day of Week

Each house will now appear as a color-coded pin.

---

Notes

• Different colors represent different cleaning days

• Look for clusters of homes to build efficient routes

• Example route building:

  • Combine nearby Wednesday homes

  • Adjust customer days if needed to tighten routes

  • Aim for ~7–8 job ticket hours per technician per day

• This visual method helps identify:

  • Overbooked days

  • Underbooked days

  • Geographic route improvements