FreightLens
A freight analytics pipeline turning 8,000 messy simulated FTL shipment records into carrier scorecards and lane economics — cleaning, warehouse, and dashboard included.
End-to-end data product: raw CSV → cleaned → SQLite warehouse → live KPI dashboard
Three decisions every freight ops team keeps making.
Which carrier to use on a lane. Whether costs are drifting. Where delays concentrate. FreightLens builds the data foundation and the analytical views to answer all three — an ETL pipeline that ingests messy shipment data, cleans and models it into a SQLite warehouse, and serves a live analytics dashboard on top.
The dataset is simulated, and that's the point: 8,000 shipments across 8 real Indian FTL lanes with realistic parameters — per-km rates by lane, vehicle capacity by truck type, carrier-specific reliability profiles, and fuel-linked monthly cost drift. The generator intentionally injects real-world data quality problems, because the cleaning logic is the demonstration.
These numbers come from the deployed pipeline — right now.
Injected data problems, and how the pipeline handles each.
Five analyses in the documented SQL layer.
- Carrier ranking per lane — CTE + RANK() OVER (PARTITION BY ...)
- Month-over-month cost trend — LAG() window
- Delay cost exposure — CTE + conditional aggregation
- Cumulative spend burn-down — running SUM() OVER
- Lane efficiency quartiles — NTILE(4)
What the data actually says.
A ~15-point on-time gap separates best and worst carriers
Reallocating volume on shared lanes is the fastest reliability win available.
Cost per km drifts upward steadily through the year
Fuel-linked — a contract-negotiation signal rather than a seasonal effect.
Price and reliability are uncorrelated across carriers
Carrier choice should be lane-specific, not network-wide.