DashLogistics — Juan de la Fuente Larrocca

Context

Logistics companies operating across multiple US states face a fundamental problem: costs are driven by factors that change daily—fuel prices, weather conditions, regional demand, and route-specific constraints. Most logistics analytics tools provide descriptive dashboards that show what happened but not what will happen.

The business problem: a mid-market logistics provider needs to predict route-level costs with enough accuracy to price contracts profitably, optimize fleet allocation, and identify inefficiencies before they impact margins.

Challenges

Data heterogeneity: Combining population data, live fuel prices (AAA API), weather (OpenWeatherMap), and internal operational data across 50 states
State-level variability: Each state has different regulations, fuel taxes, and infrastructure quality that affect costs
Prediction accuracy: Cost predictions must be within 5% of actuals to be useful for contract pricing
Multi-source ETL: Data arrives in different formats and schedules—fuel prices update daily, weather hourly, operational data in batch
Scalability: The pipeline must process 50 states without manual intervention for each region

Solution

Architecture

Multi-Source Pipeline

AAA Fuel API ──┐ OpenWeatherMap ┤→ Ingestion → Quality Gate → Feature Matrix → XGBoost → Streamlit Population ┤ ↓ Operational ──┘ PostgreSQL (Historical Store)

Pipeline

Multi-source ingestion: Independent data collectors pull from AAA fuel prices (daily), OpenWeatherMap (hourly), US Census population data (static, quarterly updates), and internal operational databases (continuous).
Data quality layer: Each source passes through validation checks—missing values, outliers, schema conformance. Failed records are quarantined for review.
Feature engineering: The validated data is joined into a unified feature matrix indexed by route-state-week. Features include fuel cost per mile, weather severity index, population density, distance, and historical cost per mile.
ML prediction: An XGBoost regression model predicts cost per mile for each route-state-week combination. Hyperparameters tuned via Optuna.
Dashboard: Streamlit frontend with 15+ KPIs, chloropleth maps, regional efficiency comparisons, and what-if scenario analysis.

Tech Stack

Python with Pandas for ETL and feature engineering
XGBoost for cost prediction (selected for performance on tabular data with mixed feature types)
Streamlit with Plotly for interactive visualization
PostgreSQL for historical data storage
Docker with CI/CD pipeline

Implementation

The key architectural decision was the separation of ingestion from prediction. Ingestion runs on its own schedule (fuel prices daily, weather hourly, etc.) and writes to staging tables. A separate pipeline triggers feature computation and model inference when all required source data is available.

This decoupling prevents a single source failure from blocking the entire pipeline. If the weather API is down, the system still processes fuel and operational data, and the model runs with partial features (with reduced confidence).

The data quality layer was essential. Initial attempts to build a unified feature matrix failed repeatedly because NULLs from one source would silently propagate through joins. The validation layer now explicitly checks for completeness before allowing a feature matrix to proceed to the model.

Trade-offs

XGBoost was chosen over a simpler linear model despite higher implementation complexity. Initial tests with linear regression achieved R² around 0.82 but failed to capture non-linear interactions between features (e.g., the compounding effect of high fuel prices during winter storms). The accuracy gain from XGBoost justified the additional complexity, but it also introduced a maintenance burden: the model requires periodic retraining to adapt to shifting cost patterns.

The system processes data in batch (daily granularity) rather than streaming. Real-time cost prediction could enable same-day route adjustments, but the operational decisions this system supports (contract pricing, fleet allocation) operate on weekly cycles. Batch processing is appropriate for the current use case.

Reliability

The data quality gate is the most important reliability mechanism. Every source must pass schema validation and completeness checks before its data enters the feature matrix. If a source fails validation twice consecutively, an alert is sent and the system uses the last known values with a confidence penalty.

Model predictions include a confidence score based on feature completeness. When fewer sources than expected are available, the confidence score decreases proportionally, and the dashboard displays predictions with a visual warning.

Results

R² = 0.987 on cost per mile prediction against held-out test data. The model was trained on 80% of historical route data and evaluated on the remaining 20%.
15+ operational KPIs tracked across 50 states with automated weekly reporting
End-to-end ETL pipeline ingests from 5+ data sources (AAA fuel, OpenWeatherMap, US Census, operational databases) into a production Streamlit dashboard
Multi-regional architecture: designed for US states but generalizes to any geographic region with available data sources

Lessons Learned

Data quality gates prevent debugging nightmares. Every source has a test that must pass before its data enters the feature matrix. This caught schema changes from external APIs multiple times.
Model accuracy is easier than data availability. The XGBoost model converged quickly. Getting complete, aligned data from 5 sources was the real engineering challenge.
Visualization drives adoption. The operations team started trusting the system when they could see state-level cost maps that matched their ground experience.

Future Work

Real-time cost prediction using streaming data for same-day route adjustments
Automated contract pricing tool that uses predicted costs to suggest profitable rates
Integration with routing software for dynamic re-routing based on predicted costs