Past Analytics Excellence Awards
Author: Alberto Guisande (@Aguisande), Services Director
Awards Category: Most Unexpected Insight - Proving teachers wrong - Apples & Oranges can be compared! (thanks to Alteryx)
Describe the problem you needed to solve
Our customer is a Public Transportation company, in charge of buses going around the city of Panama. They transport more than 500K passengers a day (1/6 of the total population of the country). Almost 400 routes, with 1,400 buses going around the city all days, working 24/7, reporting position every a few seconds. The company is supporting its operation with a variety of tools, but at the time to put all data together, they realized there was no "point of contact" in the data. They have to compare apples & oranges! Really? Why does the saying exist? Because you can't! So we started trying to do the impossible!
BTW, the business questions are pretty simple (once you got the data!): What route was every bus in, when every transaction occurred? What is the demand of every route? and for every stop?
Describe the working solution
Working with Alteryx, we were able to analyze data coming from three different sources, where the only common information was some LATITUDE & LONGITUDE (taken with different equipment, so the accuracy was, at least, questionable) at some random points in time. The data was received in several files:
- Routes: Contains the ID & the name of every route. Stop Points: Containing every bus stop, its LAT & LONG, and the stop name
- Pattern Detail: Containing every route, its stops and the sequence of those stops in a route
- Some remarks: A lot of stops are used by different routes, and there are some stops, where the bus pass through, that are not part of the specific route the bus is at
So far, the easy part! We managed very easily to get all this info together. Now the tricky part: There mainly two operational datasets: AVL (Every position of every bus, every n seconds, where n is an arbitrary number between 0 and what the piece of hardware wanted to use). BTW, a huge amount of data every day.
Transactions: transactions registered in time, in a bus. As you may infer, there are no data in common that allow us to match records beside an arbitrary range of latitude and longitude in some random time ranges. Because of how everything is reported, the bus may be passing in front a stop that is part of another route, or stopping far from the designated stop.
Describe the benefits you have achieved
With this solution, the company can start analyzing activity per route, demand per bus, route, stop, etc. Without Alteryx, this customer information still be looking like apples and oranges! We were able to make it sense and allow them to use it to get insights.
Colorful note(and some ego elevator) : 5 other vendors took the challenge. No other one could reach a glimpse of solution (of course, "no Alteryx, no gain").
