Arnold's Online

Overview

Arnold's Fruit Market, a growing local business, sought to streamline their order management and delivery planning processes. The aim was to automate the categorization of customer orders into separate Google Sheets based on location and optimize delivery routes using OptimoRoute. This case study details the implementation of a solution leveraging Python, Selenium, Make, and Heroku.

Challenge

The primary challenge was efficiently categorizing customer orders into distinct Google Sheets tabs according to their locations and automating the planning of delivery routes. Given the volume of orders and the need for precise categorization, a robust automation solution was essential.

Solution

The solution was implemented in several stages:

• Order Categorization: Python scripts, using Selenium for web scraping, were developed to extract and categorize orders based on location. These orders were then distributed into specific sheets within a Google Sheets document.
• Integration with OptimoRoute: Using Make, the categorized orders were sent to OptimoRoute for route planning. The automation script was designed to handle various scenarios, including skipping specific order types like click-and-collect.
• Scheduling and Execution: The automation was configured to run every weekday at 6:02 AM. The Python script fetches the necessary data, processes it, and updates Google Sheets with new orders, ensuring they are correctly categorized.
• Deployment: The Python script was deployed to Heroku, ensuring reliable execution and scalability. Heroku's scheduling feature was utilized to run the automation at specified times, eliminating manual intervention.

Implementation Details

• Google Sheets Integration: The automation script connects to Google Sheets, categorizes orders based on location, and distributes them into specific sheets. The script reads JSON data from a central "JSON Data" sheet to map postcodes to their respective locations.
• Order Categorization: The script scrapes order data from the Arnold's Online back office using Selenium. Orders are categorized by extracting relevant details such as contact information, delivery address, and delivery date. The script ensures that canceled orders and click-and-collect orders are skipped.
• OptimoRoute Integration: Using Make, the categorized order data is sent from Google Sheets to OptimoRoute's APIs. Make handles the API requests to create orders and plan routes in OptimoRoute based on the categorized data.
• Scheduling and Execution: The automation is scheduled to run daily at 6:02 AM using Heroku's scheduler add-on. This ensures the process is automated and runs without manual intervention, saving significant administrative time.

Results

• Efficiency: The automation significantly reduced the time spent on manually categorizing orders and planning routes.
• Accuracy: Orders were accurately categorized into the correct sheets, ensuring precise delivery planning.
• Optimization: The integration with OptimoRoute allowed for optimized delivery routes, improving delivery efficiency and reducing operational costs.
• Scalability: The solution's deployment on Heroku ensured it could handle increasing order volumes and scheduling requirements seamlessly.

Conclusion

The integration of Python, Selenium, Make, and Heroku provided Arnold's Fruit Market with a powerful automation solution that streamlined their order management and delivery planning processes. This automation not only saved time but also improved operational efficiency, allowing the business to focus on growth and customer satisfaction. The use of Make to handle the API requests to OptimoRoute further enhanced the efficiency and reliability of their delivery operations, ensuring that routes were planned and optimised automatically.