Copa Airlines Takes Data to Cruising Altitude

Overview of Use Case

In this Use Case you will learn how Copa Airlines, Panama’s national carrier, used Alteryx to enable key business departments such as Pricing, Revenue Management, and Central Reservation Control to thrive. Their teams have automated and optimized report analyses, vastly minimized fare filing errors, and predicted flights in danger of selling out, to name just a few operational benefits. Copa has seen sky-high productivity growth as they have drastically reduced manual labor and improved the agility of their decision-making process.

Describe the business challenge or problem you needed to solve

Data Insights

Copa Airlines flies to 80 cities in 32 countries in the Americas. In 2014, Copa’s analysts had to manually download hundreds of files and append them to Excel. Alteryx completely transformed their data culture. One of the first challenges that Nuria Saavedra, Copa’s Intelligence Analyst, faced at the company was to create reports faster without compromising on accuracy. “In our department we have to sell the right product to the right customer at the right time for the right price,” Nuria explained.

Part of Nuria’s job is to analyze customer behavior and project future bookings demand for critical decision making. “Our revenue management analyst needs the data by 8am to manage the availability of each flight each day. The team needs to analyze 115 million rows of data every morning,” she said.

Pricing Automation

Copa’s Pricing and Revenue Management team is responsible for strategizing and filing fares for about 5,000 different markets across the globe. This used to be a lengthy process because each analyst had to apply different rules and strategies to a number of Excel files for each market they were in charge of. On top of that, they had to ensure that there were no fare filing errors.

“Depending on the market, sometimes we have to cut our prices to compete, but sometimes we can charge a premium. We wanted to be very agile and effective and have our own strategy,” Isacar Racine, Copa’s Senior Intelligence Analyst, said. He wanted an application that could analyze competitors’ fare data and apply the pricing team’s strategies to set Copa’s fares faster and more reliably, avoiding costly errors.

Predict Sold Out Flights

Copa noticed that some flights were selling out too quickly. They were missing an opportunity to sell seats for last minute passengers, who are willing to pay more. They wanted to develop something fast that would identify which flights are in danger of selling out. “We have a team that use R and Python and they could have developed this project using those languages, but we needed something quick. We have worked with Alteryx previously and we knew it could handle millions of millions of rows,” Isacar said.

Describe your working solution

Data Insights

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.

Pricing Automation

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.

Isacar’s team developed a macro that would send the outcome from the previously developed algorithm with a table of flights that were in danger of selling out to each respective analyst. Then, the analysts would make the necessary adjustments to prevent the flights from selling out. That way, they could capture the revenue from last minute passengers.

Describe the benefits you have achieved

Alteryx not only helped Copa Airlines capture lost revenue, it also revolutionized the way the company handled its data. “The benefits of Alteryx were so huge that it became viral in Copa. After we started using it, other teams did too,” Nuria said. Isacar enjoyed the flexibility of Alteryx. ”I didn’t have any background in pricing when I started developing this application. Don’t be afraid of making mistakes,” he added. For the analysts, using Alteryx Predictive package was easy. “It was familiar to our analysts because it was similar to models made in R or Python,” said Isacar.

“The volume of data wasn’t an issue for Alteryx. We deploy this data on Alteryx Server, and it takes 1h30 to process more than 300 million rows of future flights. So now when our analysts arrive at work, they already have an email identifying which flights are going to sell out, and they can act very quickly,” he concluded.