This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Announcing Alteryx + Snowflake | Alteryx and Snowflake make analytics and data science fundamentally easier. With the new integrated starter kit, you can push down data prep transformations and more into Snowflake for faster data quality and analytics output. Learn More
Collaborators: Dae Lee - Sr. Ops Research Developer, Brian O'Keefe - Business Consultant Crew Analysis
Overview of Use Case
Managing crew costs is a major challenge for airlines. In February 2018, Reuters reported that labor costs surpassed fuel as global airlines’ biggest single expense in 2016, at 22 percent of costs. That is expected to jump this year to 30.9 percent versus 20.5 percent for fuel. Airlines must attempt to minimize crew costs to survive in this highly competitive industry. One way to decrease crew costs is to provide forecasts to the schedulers to help make optimal last-minute assignment decisions for pilot and flight attendant reserve crews.
Southwest Airlines now serves 100 destinations with more than 4,000 flights per day during peak travel, and a total crew labor force of over 24,000 pilots and flight attendants. As our airline, crew, and operation grow, we have experienced increasing amounts of unplanned absences, which result in uncovered lines of flying (i.e. “open time”). Additionally, our schedulers are managing more open flying than they ever have before with limited reserve crews (i.e. on-call pilots and flight attendants) at each base. Making optimal decisions for over 20 categories for pilots (10 bases with 2 pilot positions each) and 10 categories for flight attendants (10 bases) was not possible with the existing tools/information. Therefore, the objective of this project was to provide forecasts to the schedulers in order to decrease extra crew costs associated with unassigned reserves, premium assignments, and unplanned absences or open time.
Describe the business challenge or problem you needed to solve
Crew scheduling must make decisions for the following day without knowledge of the availability of reserves or amount of open flying to be seen. For pilots, they decide to assign open flights to a reserve (limited resource) or to bidders who get paid a premium (i.e. overtime pay). For flight attendants, schedulers can release reserves prior to the day of work in order to minimize extra reserve costs from overstaffing. In either case, failure to anticipate daily changes in reserve crew availability and open flights can cost Southwest Airlines hundreds of thousands in extra crew costs, one of our largest expenses.
The Southwest Airlines Advanced Analytics & Optimization Solutions Team built an Alteryx workflow to provide forecasts to crew scheduling to assist with the reserve crew assignment process. Before Alteryx, crew scheduling relied on individual scheduler’s experience and intuition to make daily reserve crew assignment decisions. Today, crew scheduling receives daily forecasts for pilot & flight attendant reserves availability as well as the magnitude of flights to cover the following day. These forecasts are generated by an Alteryx Workflow scheduled to execute early each morning.
Describe your working solution
We utilized Alteryx to provide crew reserve forecasts alongside open flight forecasts for each of Southwest Airlines 10 bases and 2 pilot seats so that crew scheduling can anticipate the reserve vs. open flight balance before making assignment decisions. Schedulers for pilots & flight attendants receive these forecasts each morning and reference them when making decisions for the following day.
We broke up the solution into two separate Alteryx workflows with respective Tableau Server dashboards for reporting, one for pilot schedulers and one for flight attendant schedulers, in order to address the two different work groups. Data utilized for forecasting were from historical Southwest Airlines daily crew data as well as daily operational data that significantly improved forecasting errors. The workflows execute from Southwest Airline’s Alteryx Gallery early each morning where they pull fresh data, build a time-series forecasting model, and then generate forecasts for each category. Both workflows utilize a Publish-to-Tableau tool for external reporting via Tableau Server where the schedulers can navigate to view forecasting results. Additionally, we utilize Alteryx’s Reporting tool suite to notify team members if there were issues running the tool that morning.
For the forecasts provided to pilot schedulers, datasets were gathered for each of Southwest Airlines 10 bases and 2 pilot seats combinations for a total of 20 separate datasets. Forecasting is performed on each of the datasets every morning to provide 20 forecasts for open flights to cover for each category to the pilot schedulers. For example on April 20th, 2018, the model forecasted 12 open flights will need to be covered for Atlanta captains and 13 for first officers whereas it forecasted 24 open flights to cover for Dallas captains and 23 for first officers. These 20 forecasts can be summed to provide the system-level open flights to cover forecast.
For the forecasts provided to flight attendant schedulers, the datasets were only gathered for each of the 10 bases, for a total of 10 datasets used to generate 20 forecasts: one forecast for reserve crew availability and one for open flights to cover from each dataset. Again, these forecasts may be summed to provide the system-level reserve crew availability or open flights to cover for Flight Attendants.
Since the data is at the daily level, time series forecasting models such as time series linear regression, exponential smoothing, and the autoregressive integrated moving average (ARIMA) models were considered to forecast pilot & flight Attendant availability and open flights to cover. Ultimately, we chose to stick with one model that consistently outperformed the other models for this problem. Development of the models has been finalized and crew scheduling has been using daily forecasts generated from Alteryx since August 2017.
Describe the benefits you have achieved
Initially, schedulers had to rely on their experience and intuition to guess how many Reserves and open flights to cover for the next day. Increasing unplanned absences combined with a growing operation, crew size, and cost basis created a problem too large for schedulers to consistently make the most optimal decisions with respect to their expectations for the next day’s operation.
Due to these reasons, we decided to use CRAN R and Alteryx for data gathering, data processing, forecasting model development and testing, as well as for the daily forecasting runs. Alteryx’s seamless integration with the R language via the R-Tool provided by Alteryx allowed us to quickly and easily gather data, prepare it, then build and evaluate models to find the final and best forecasting method for the problem. Many different data sources from Teradata and Oracle, to CSV files, were easily incorporated in our workflow using Alteryx’s basic data input and preparation tools. Alteryx makes the process of combining these datasets containing multiple tables and data types feel intuitive. Additionally, crew scheduling wanted to receive the forecasts early each morning prior to the work day. Southwest Airlines Alteryx Gallery combined with workflow scheduling allowed us to schedule the workflows to run automatically from a remote location without the help of additional developers. Finally, Alteryx’s Publish-To-Tableau tool made the process of exporting the final forecasts to Southwest Airlines Tableau Server particularly easy and automatic.
In general, the benefits of the Reserve and open flights to cover forecasts include (but are not limited to) the following:
The dashboards provide live overview of reserves vs. open flights to cover for scheduling leaders to review each day
The tool for pilot schedulers updates known quantities each hour throughout the day (current reserves, current open flights, etc.)
The dashboard provides historical forecast values vs. the actuals to provide daily error measurements for the forecasts directly to the schedulers
Crew schedulers are optimizing crew reserve assignments
Crew schedulers are optimizing premium decisions; reducing overtime costs
Crew scheduling can quickly & visually identify bases with a significant reserve vs. open flight imbalance
Link to any existing resources related to your use case. Case study, testimonial, blog article, media, etc.