Case studyOperations Analyst
Supervising a city with data — C&M Consorcio / TransMilenio (2018–2020)
Verifying with evidence that Bogotá's mass transit system was operated as agreed, when a discount or a penalty could hang on each figure.
- Excel · VBA
- SQL · SQLite
- Performance dashboards
By the numbers
~150
routes supervised
10
concession companies
5
sources cross-referenced to rebuild the operation
−40%
processing time, at the very least
Context
I entered mass transit through the supervision of TransMilenio: about 150 routes grouped into 10 concession companies. No single source described the operation; it had to be rebuilt by cross-referencing fare collection, fleet and GPS, scheduling, incidents and user complaints, each with its own structure and its own quality.
The challenge
Producing indicators that could withstand the discussion between the authority and ten concessionaires with different interests: traceable from the GPS or fare record to the figure in the report.
How I did it
Traceability as a condition of legitimacy
When an indicator can support a discount or a penalty, its lineage must be as solid as its calculation. Every figure had to be reconstructable down to the operational event that produced it; with ten concessionaires across the table, that was the only argument that held.
Performance dashboards and reports
Two weekly reports, a monthly consolidated one and those requested on demand: scheduled versus completed services, routes and coverage, headways between buses and compliance. I learned to separate outcome indicators from behavior indicators: the headway between buses warned earlier than the monthly report.
The written rule first, then the macro
I automated with Excel, VBA and SQL the extraction, validation and consolidation of the concessionaires' weekly files. Input validation —mandatory fields, possible ranges, duplicates— was the part that changed most and the one that prevented the most errors.
The system's statistical memory
I organized the accumulated weekly files in SQLite: the same route and the same headway, month after month. It was my first complete data pipeline, and the asset that remained when I left in May 2020.
Two questions that are now research
The data showed two problems I could not solve from supervision: driver–vehicle–route assignment and bus bunching. Today they are two of the research pieces I publish in the showcase, with their method and their result.
Impact
- Weekly and monthly performance reports for about 150 routes, traceable from the figure to the record.
- At least 40% less processing time with automation in Excel, VBA and SQL.
- A statistical memory of the operation that stopped starting from scratch every week.
- Two questions from the operation turned, years later, into published research.
Observing a system is much more than producing indicators: it requires rebuilding its relationships and preserving evidence of every transformation.