Case studyPost-Operational Analysis
Data that moves a city — TransMilenio / C&M (2021–2022)
I returned to the same system with a different question: no longer supervising compliance, but explaining how the SITP operated and helping decide how to adjust it.
- BI
- ETL
- scikit-learn
By the numbers
+70%
in analysis precision and speed
25+
key users adopted the BI
+25%
in the indicators addressed with the SITP
10
months in use for the demand model
Context
Post-operational analysis of the TransMilenio system relied on heterogeneous sources and manual reports: the city's operation produced data faster than it could be analyzed.
The challenge
Unify the sources, speed up the analysis and bring the data to the tables where the transit system's operation is decided.
How I did it
Five sources, one operation
I built the ETL that unified fare collection, fleet and GPS, scheduling, incidents and user complaints on common keys: route, service, vehicle, time slot and day. Validations lived inside the pipeline, written as rules: a service with no schedule or a bus with GPS and no dispatch stayed visible, not silently corrected.
BI around operational questions
I drove Power BI adoption with more than 25 key users: the people who scheduled and decided the service. Each dashboard answered a concrete question, such as which routes and time slots saw demand exceed the scheduled supply, or which concessionaires concentrated the incidents.
Evidence, mechanism and consequence
I brought analytics to working sessions with the SITP's concessionaire leadership. Each recommendation connected evidence, mechanism and consequence, and the same pipeline kept measuring after the action: that is why the +25% could be stated.
Forecasting demand to schedule
I developed a demand model with scikit-learn by route and time slot, updated monthly and validated with RMSE respecting temporal order. It ran for ten months inside the report used to decide how many buses to assign and at what times.
Automating with controls
Scripts cut the time spent on repetitive tasks by 40%, with input validation and visible exceptions: a weekly file arriving with a missing column is neither loaded silently nor allowed to crash the process. That time went to the model and to the sessions.
Impact
- +35% efficiency of analytical processes from BI adoption.
- +70% in analysis precision and speed with the unified ETL.
- +25% in indicators after the SITP strategy sessions.
- +20% system performance with demand forecasting.
A reliable decision requires an equally reliable data path.