Automated Sales Territory & Route Optimization System
Budget: $3,000 – $5,000 CAD
Project Title: Automated Sales Territory & Route Optimization System (Airtable + Make + OpenRouteservice + Softr/Glide)
Project Description:
We are looking for an experienced automation specialist to build a lightweight, no‑code system that processes open government construction permit data (CSV format, 100–200 records per month) and generates optimized daily visit routes for a small sales team (2–3 reps). The system will import CSV files that are externally downloaded and provided (e.g., placed in a cloud folder or manually uploaded). It will then filter recent permits (rolling 3–4 month window), apply target profile rules (project type, value, contractor type, exclusions), deduplicate entries, geocode addresses, group them into geographic regions using a fixed radius, allow a manager to manually assign regions to reps, and let each rep choose a daily starting point to re‑optimize their route. The optimized stop order must be displayed as a simple numbered list in a mobile dashboard (built with Softr or Glide) where reps can mark visits and add notes. The system should run on a daily schedule (2 AM) with an optional manual trigger, and be designed to integrate seamlessly with a separate external form system (schema alignment provided).
Key Features:
CSV import (manual upload or from Google Drive/Dropbox) – no web scraping required
Date window filtering (rolling 3–4 months)
Target profile filtering (configurable rules)
Deduplication (address + permit ID, exclude visited leads)
Geocoding via OpenRouteservice (free, no API key)
Radius‑based clustering (fixed distance, e.g., 5 miles)
Manual region assignment to reps (via Airtable)
Rep‑selected daily starting point (via mobile app)
Route optimization using OpenRouteservice (return optimized stop order)
Mobile dashboard (Softr or Glide) showing numbered stop list, visit toggle, notes field
Manager overview page
Scheduled daily run (2 AM) and manual trigger
Error logging and email notifications
Integration‑ready schema for external form system
Technology Stack:
Airtable (database and base architecture)
Make (formerly Integromat) for automation workflows
OpenRouteservice (geocoding and routing)
Softr or Glide (mobile app frontend)
No custom code required – all no‑code/low‑code tools
Deliverables:
Fully functional Airtable base with tables: Raw Imports, Active Leads, Archive, Regions, Routes, Reps.
Make scenarios for:
CSV import and processing (filtering, deduplication, archiving)
Geocoding batch processing
Clustering logic (radius‑based region assignment)
Route optimization (triggered by rep's starting point)
Scheduled daily run (2 AM) and manual trigger
OpenRouteservice integration (geocoding and routing) with error handling.
Softr/Glide mobile app with:
Rep login and region view
Numbered optimized stop list
Visit toggle and notes field
Starting point selection input
Manager dashboard overview
Testing with sample data (up to 200 records).
Documentation: system walkthrough, automation diagram, API notes, admin guide.
Timeline: 6–8 weeks
Budget: $2,500 – $3,500 USD (depending on experience and proposed approach)
(Adjusted to reflect the removal of web scraping and external CSV handling – now focused purely on data processing and automation.)
Payment Schedule:
50% upfront
25% on delivery of core automation (Airtable + Make workflows)
25% on final delivery and handover
Requirements for Freelancer:
Proven experience with Airtable (base design, formulas, linked records)
Strong expertise in Make (Integromat) – building complex scenarios with error handling
Familiarity with geocoding and routing APIs (OpenRouteservice preferred)
Experience building mobile apps with Softr or Glide
Ability to work with CSV data and implement deduplication logic
Excellent communication skills and ability to meet deadlines
How to Apply:
Please submit a proposal that includes:
Your relevant experience with similar projects (links to past work)
A brief outline of how you would approach this project
Your estimated timeline and fixed price within the budget range
Any questions you have about the requirements
We look forward to working with a skilled freelancer who can deliver a clean, maintainable, and user‑friendly system!
Project Description:
We are looking for an experienced automation specialist to build a lightweight, no‑code system that processes open government construction permit data (CSV format, 100–200 records per month) and generates optimized daily visit routes for a small sales team (2–3 reps). The system will import CSV files that are externally downloaded and provided (e.g., placed in a cloud folder or manually uploaded). It will then filter recent permits (rolling 3–4 month window), apply target profile rules (project type, value, contractor type, exclusions), deduplicate entries, geocode addresses, group them into geographic regions using a fixed radius, allow a manager to manually assign regions to reps, and let each rep choose a daily starting point to re‑optimize their route. The optimized stop order must be displayed as a simple numbered list in a mobile dashboard (built with Softr or Glide) where reps can mark visits and add notes. The system should run on a daily schedule (2 AM) with an optional manual trigger, and be designed to integrate seamlessly with a separate external form system (schema alignment provided).
Key Features:
CSV import (manual upload or from Google Drive/Dropbox) – no web scraping required
Date window filtering (rolling 3–4 months)
Target profile filtering (configurable rules)
Deduplication (address + permit ID, exclude visited leads)
Geocoding via OpenRouteservice (free, no API key)
Radius‑based clustering (fixed distance, e.g., 5 miles)
Manual region assignment to reps (via Airtable)
Rep‑selected daily starting point (via mobile app)
Route optimization using OpenRouteservice (return optimized stop order)
Mobile dashboard (Softr or Glide) showing numbered stop list, visit toggle, notes field
Manager overview page
Scheduled daily run (2 AM) and manual trigger
Error logging and email notifications
Integration‑ready schema for external form system
Technology Stack:
Airtable (database and base architecture)
Make (formerly Integromat) for automation workflows
OpenRouteservice (geocoding and routing)
Softr or Glide (mobile app frontend)
No custom code required – all no‑code/low‑code tools
Deliverables:
Fully functional Airtable base with tables: Raw Imports, Active Leads, Archive, Regions, Routes, Reps.
Make scenarios for:
CSV import and processing (filtering, deduplication, archiving)
Geocoding batch processing
Clustering logic (radius‑based region assignment)
Route optimization (triggered by rep's starting point)
Scheduled daily run (2 AM) and manual trigger
OpenRouteservice integration (geocoding and routing) with error handling.
Softr/Glide mobile app with:
Rep login and region view
Numbered optimized stop list
Visit toggle and notes field
Starting point selection input
Manager dashboard overview
Testing with sample data (up to 200 records).
Documentation: system walkthrough, automation diagram, API notes, admin guide.
Timeline: 6–8 weeks
Budget: $2,500 – $3,500 USD (depending on experience and proposed approach)
(Adjusted to reflect the removal of web scraping and external CSV handling – now focused purely on data processing and automation.)
Payment Schedule:
50% upfront
25% on delivery of core automation (Airtable + Make workflows)
25% on final delivery and handover
Requirements for Freelancer:
Proven experience with Airtable (base design, formulas, linked records)
Strong expertise in Make (Integromat) – building complex scenarios with error handling
Familiarity with geocoding and routing APIs (OpenRouteservice preferred)
Experience building mobile apps with Softr or Glide
Ability to work with CSV data and implement deduplication logic
Excellent communication skills and ability to meet deadlines
How to Apply:
Please submit a proposal that includes:
Your relevant experience with similar projects (links to past work)
A brief outline of how you would approach this project
Your estimated timeline and fixed price within the budget range
Any questions you have about the requirements
We look forward to working with a skilled freelancer who can deliver a clean, maintainable, and user‑friendly system!