Automated Shipping Allocation Engine (E-commerce & Logistics)
Mohamed Zahran
This project was implemented in a real-world business context and delivered measurable results for an Ecommerce company.
The solution is flexible and scalable, easily adaptable to any e-commerce platform or industry.
Its methodology for order distribution and performance optimization can be customized to fit different business needs and data sources.
The Challenge
E-commerce operations struggled with manual allocation of thousands of daily orders across multiple shipping companies (3PLs).
This process was:
⏱ Time-consuming – operations team spent hours matching orders manually.
⚠️ Error-prone – mismatches in cities, weights, and carriers led to delays.
💸 Costly – suboptimal allocations increased shipping costs and reduced delivery performance.
The client needed an automated, data-driven solution to optimize shipping allocation, reduce delivery times, and improve customer satisfaction.
The Solution
I developed a Power Query-based automation engine in Excel that dynamically distributes e-commerce orders across multiple shipping companies using advanced business rules, KPIs, and live data from APIs.
The system:
🔗 Integrates multiple data sources (APIs, Google Sheets, SharePoint Excel files).
🗺 Normalizes city/area data across platforms with a Cities Lookup Table.
📊 Calculates KPIs (delivery rates, weights, zones, performance).
🤖 Applies dynamic business logic to assign each order to the optimal 3PL partner.
📦 Generates ready-to-dispatch tables per shipping company, formatted for their systems.
How It Works – Step by Step
1. Data Integration
Connects to multiple APIs, Google Sheets, and Excel files in SharePoint.
Consolidates order data into one unified table.
2. Data Cleaning & Standardization
Uses Cities Lookup Table to normalize city/area names.
Removes duplicates and validates key fields.
3. KPI Calculation
Groups orders by city, area, weight, and shipping partner.
Calculates delivery success rates, costs, and performance scores.
4. Dynamic Allocation
Excludes low-performing 3PLs automatically.
Uses nested joins and rules to match orders with the best partner (city, weight, historical performance).
5. Output Generation
Creates dispatch tables for each shipping company.
Customizes columns to match each partner’s required template.
Technical Highlights
⚙️ Excel Power Query (M Language): ETL for multi-source integration.
🌐 API Integration: Fetches live order and delivery data.
🗺 Smart Lookup Table: Resolves city/area naming inconsistencies.
📊 Automated KPI Calculation: Tracks delivery success rate and partner performance.
📈 Scalable Workflow: Handles thousands of orders daily with transparent, auditable steps.
Results & Value Delivered
⏱ Reduced manual work by 80% in order allocation.
✅ Improved delivery performance by assigning top-performing carriers per zone and weight category.
🤝 Enhanced client satisfaction with faster, more reliable deliveries.
Ready to optimize your shipping? Let’s connect to tailor this solution for your business!
Like this project
Posted Sep 27, 2025
Excel + Sharepoint + API Integration system automates order assignment to shippers using rules on cost, capacity & coverage—reducing manual work and Errors.
