To tackle the problem, Nuria analyzed bookings year-to-year. She used the Flown data, which is what passengers actually flew versus what was originally booked, the Booked data, which is what they booked, and the Demand data, which is an estimate of how much demand Copa should expect in every flight.
The Flown and Booked data come from the same data source and they are at the same aggregation level, but the Demand data comes from a totally different source. “We used a lot of Join tools for Booked and Flown data and then integrated them with the Demand data. Since it was my first report, I used simple tools such as Filter, Formula, and Aggregate,” Nuria explained.
“It’s a constant optimization. I realized later that I didn’t need a lot of Join tools to aggregate the data, I could use just one Multi Formula tool. When I made that change, the performance improved. You can constantly learn and improve it. Keep it simple, there’s always an easier way to break it down,” she concluded.
Isacar and his team split Copa’s Pricing Project into modules, and with Alteryx Designer, they solved their pricing problems using macros. They developed five modules: Transform, Homologate, Strategy Engine, Validate, and Report. Next, they asked the Pricing team to test each module, they adjusted and then re-deployed. “This way we could spot errors easily, and it was clear how we were doing on the project,” he said.
In the Transform module, they would make sure their fares were clean and had the right data set and data type. Every airline has different codes for their fares, so in the Homologate module the team would translate other airlines’ fares into Copa’s language and ensure that they matched. After that, in the Strategy Engine module they would apply strategies that the pricing team had on every market to the fares that the application was processing. In the Validation module they would check whether the rules they had established for each market were followed. Finally, in the Reporting module, the application would send the results by e-mail to the Pricing team and to the coordinator of each market.
“One analyst could work on the Strategy Engine and the other one in the Validate module. Whenever someone was working on the Transform side they knew the output, so the person working on the Homologate module could work in parallel,” Isacar added.
“The Strategy engine is the key part of the project. We made sure this macro was very clear in case someone has to modify it. The Test tool was also important because if we were going to have a wrong fare in a market we wanted it to break, so it would throw an error if something was going wrong,” he explained. Now, their pricing analyst just has to run the workflow in the Server and it gives the output based on the strategy that they set previously.
Predict Sold Out Flights
In Copa’s booking system, every flight becomes available 330 days before departure. In order to predict future demand, they needed to know the entire booking curve of the flight. “Our revenue management system has different variables for all our flights such as bid price, remaining seats, and booking velocity. Using Alteryx’s predictive package, we applied the models to the data to find which variables had the most predictive power. We tested different models, and it was as easy as drag and drop rather than having to write custom code,” Isacar added.