Airline Investment Analysis
Overview
Framework
- Business request
- Data quality observations
- Assumptions
- Systems used
- Data security
- Insights and Hypothesis
- Conclusion
- Codes
Business request
Background/Scope:
The business is looking to invest in a new airline and will like to know the best performing airline between Frontier, Hawaiian and Virgin America Airlines based on the trend in flight delays on both arrivals and departures. The business will like to also risks associated with delays using the data from December 2012 as a template.
Data requirement:
The data engineers have provided a csv document containing the key features for fulfilling this requirement
Data quality observations
Some observations after taking a quick look at the csv data;
- Further discussions must be made with the data engineer around the logic behind the computation of arrival and departure delay time
- It is observed there is no unit for delay time
- The data had good data entry and completeness quality
Assumptions
Some assumptions made for the driving insights in this report;
- Delay times:
- Arrival delay = Actual arrival time – Scheduled arrival time
- Departure delay = Actual departure time – Scheduled departure time
- Flights with delay time lesser than or equal to zero are punctual flights
- It is assumed that the unit of delay time is in minutes
- Frontier, Hawaiian and Virgin America Airlines had same risk analysis KPIs for delay time
- High risk: Delay time greater than 30 minutes
- Medium risk: Delay time between 5 and 30 minutes
- Low risk: Delay time less than 5 minutes
- Weekly analysis started from week 49, with week starting on Sunday
- Volatility is the difference between the maximum and minimum volume
- The airlines have the same flight ticket cost and maintenance cost
Systems used
Microsoft Excel was used in viewing and analysing the quality of the raw data
Google data studio was used in driving insights and building visualisations
Data security
- To be assessed by the public
- No PII or other sensitive information captured
Insights and Hypothesis
Data quality insights
It can be observed that the values from the new logic, by computing averages per airline, differs from the original value provided for both arrival and departure delay times.
Volume Trend Analysis
Frontier has the highest volume of fights of 6,002. Have been able to achieve as high as 235 flights in a day, which is the highest ever achieved by any of the three airlines in December. Frontier airline has also seen a the best increase in percentage volume (0.7%) when comparing with the other two airlines, from 8.26% in Week 49 to 8.96% in Week 51. We can also see from the charts that the daily volume is highly volatile, even though dips occurred for the three airlines on Saturdays.
Hawaiian airline achieved 5,867 flights in December, close the number Frontier airline achieved. However, a slow growth rate (increase in percentage volume) of 0.16% during the month, from 8.3% to 8.46% week 52. The volume trend is less volatile compared to Frontier airlines having 30 as the difference between the minimum and maximum volume compared to Fronteir airline who had 79
We can see Virgin America airline is the worst performing airline in terms of volume of 4,496. But has witnessed some steady increase in volume of flights in December of up to 0.69% close to what Frontier airline achieved, which indicates good potential growth from the company in the long run. The difference between the minimum and maximum volume is average compared to Frontier and Hawaiian airline with volatility of 44he difference between the minumum and maximum volume compared to
Customer Experience – Risk Analysis
Frontier airlines have the worst customer experience as they have huge amount of flight delay and punctuality rate as low as 35% and 42% for arrival and departure. They also have huge spikes of too early flights, which could be a negative impact to the company, as customers would expect flights to depart on time, and not before time.
Hawaiian airline is the best performing in terms of punctuality of flights, with arrival punctuality rate of 66% and 76% respectively.
From the 4th dashboard, it is observed that Hawaiian airline has the lowest risk of delayed flights when compared with Virgin America and Frontier airlines on both arrival and departures, making it the safest airline to invest in.
Conclusion
Hawaiian airline Inc is the best performing to invest in as they are the most stable volume throughout December. It also seem like they put customer satisfaction in the heart of their business which is important for steady growth of the business. They might be needing to find new strategies to improve the growth of their flight volume.
Please feel free to explore the dashboards to get more insights or chat me up for a catch-up.
Challenges
- It was difficult to get week-on-week or waterfall chart using google data studio, that would have helped with better visualizing the week on week growth rate of each airline
Codes
- arrival_delay_v2 = arrival_actual – arrival_schedule
- departure_delay_v2 = departure_actual – departure_schedule
- arrival punctuality rate = sum(IF(arrival_delay_v2 <= 0, 1, 0))/sum(case when arrival_actual is not null then 1 else 0 end)
- departure punctuality rate = sum(IF(departure_delay_v2 <= 0, 1, 0))/sum(case when departure_actual is not null then 1 else 0 end)
- arrival_riskFlag = case when arrival_delay_v2 <=5 then ‘Low’ when arrival_delay_v2 <= 30 then ‘Medium’ else ‘High’ end
- departure_riskFlag = case when departure_delay_v2 <=5 then ‘Low’ when departure_delay_v2 <= 30 then ‘Medium’ else ‘High’ end
