The Problem:
A business that relied on an online booking system for its logistics operations was facing significant challenges when it came to preparing and managing daily pickup and dropoff schedules. While the system handled bookings, it did not cater well to the mobile nature of the job. Both drivers and on-site staff struggled to access the information they needed because they couldn’t spend time in front of a screen or desk, particularly when out in the field.
The lead manager spent at least an hour each day preparing paper sheets for drivers, manually splitting up runs based on location and ensuring the correct dropoffs and pickups were aligned. An additional sheet was required for the on-site staff, so they knew what to prepare for each pickup and delivery. This manual process led to a host of problems:
- Frequent errors resulted in missed pickups, delayed dropoffs, and miscommunication.
- Customer satisfaction was low, around 60%, due to the inefficiency and unreliability of the service.
- The process was heavily reliant on the lead manager’s personal knowledge, making it difficult for other staff to take over during holidays or absences.
- Drivers also had to manually track which clients required keys, codes, or alarm instructions, and these details were often forgotten, causing further delays and frustration.
Overall, the company was losing an estimated £3,000 annually in staffing costs, with the added burden of managing customer complaints, errors, and operational stress.
The Solution:
The first step in solving the problem was to fully understand the internal processes the business used to manage logistics. This required going beyond just technical solutions and actually riding along with drivers to see firsthand how routes were planned, runs were executed, and where the bottlenecks occurred. This allowed us to map out the logic the management used to split and balance the workload between drivers manually.
The challenge lay in keeping the solution simple and functional for a team that was not tech-savvy and often worked in rural areas with no signal. A fully digital system was impractical for the end users. Instead, we aimed to retain a paper-based output while automating the backend processes to remove human error and reduce manual workload.
Here’s how the automation worked:
- We used the existing booking system’s API to push every booking into a simple Google Sheet.
- The Google Sheet was enhanced with formulas, data manipulation, and Google Code that applied the business’s internal logic to split and assign runs to the correct drivers.
- Each morning, the management could generate the entire day’s logistics sheets with the click of a button. The system automatically produced individual “tickets” for each run, along with a summary sheet for the on-site team, and a master overview for management.
- These sheets were automatically sent to the appropriate printers, and a digital version of the overview was posted to a Slack channel for easy visibility by all relevant staff.
There were several obstacles to overcome during the project:
- We had to modify the booking system to introduce a new field that classified clients by area, based on the management’s local knowledge. This allowed for more accurate routing and reduced mistakes in assigning jobs to the wrong driver.
- Additional hardware, such as extra printers, had to be set up to streamline the output process.
- It took several iterations to get the balance of information just right on the sheets, as there was a tendency to include too much data when only essential details were needed.
The Outcome:
The results were transformative. Though the project took three months to deploy due to ongoing refinements and adjustments fully, the impact was felt almost immediately.
- The time required for daily sheet preparation dropped from one hour to just 5 minutes per day, saving the company significant staffing costs.
- Client satisfaction skyrocketed from 60% to 98% within six months, thanks to a more reliable and error-free pickup/dropoff service.
- Staff morale improved dramatically as they now had clear, concise, and colour-coded sheets that were easy to follow, reducing stress and the need for in-depth knowledge of routes and clients.
- The management team was particularly pleased with how simple the system was to use and maintain. Since the solution was built using accessible tools like Google Sheets, it was easy for them to adjust or fix if necessary without needing a developer to step in.
Ultimately, the company experienced fewer errors, faster service, and a more streamlined logistics operation, all while providing a better experience for both staff and customers.
