Implementation of Business Intelligence Solution for United Airlines Business Insights and Data Analytics for United Airlines Industry

—US Airline is recognized as the world's largest airline, with a massive number of daily departures completed and a combined fleet of over 2700 aircraft. US Airlines have carried major 18 airlines, categorized as mainline, regional, and freight airlines. United Airline is one of the major airlines in the US after American Airlines and Delta Airlines in the world. Today, companies received as much feedback from their customers. Customers can share their opinion and emotion through social media platforms, such as Twitter. Thus, collecting and understanding customer’s opinion become the key benefits for the aviation industry to get actionable insights while increasing their competitiveness. Such insights are useful in planning and execution to increase the relationship with customers. Thus, this study was conducted to analyze customer’s feedback in different airlines to discover actionable insights that increase the competitiveness of United Airline. The analysis result will be visualized on Tableau dashboards and BI solutions will be provided. By implementing the BI solutions, United Airline can make accurate decisions and define next strategies by identifying those positive and negative references. Thus, United Airline can improve the quality of their service, enhance customers loyalty, and boost business profitability.


INTRODUCTION
The COVID-19 pandemic is having a significant impact on the aviation industry, including a sharp decline in demand for passenger air travel. Demand for airline services has fallen dramatically because of changes in passenger behavior brought on by the COVID-19 crisis and travel restrictions. Additionally, containment efforts are endangering the viability of numerous businesses in the aviation industry as a whole, putting many jobs at risk. Despite some setbacks from the COVID-19 wave, the herd vaccination campaign is now beginning to pay off. Many nations have carried out their plans to resume some semblance of normality by removing numerous health restrictions, easing travel stipulations, and reopening borders. The momentum generated by the reopening had a positive and immediate impact on global demand for air travel.
Big data insights can provide airlines with a significant competitive advantage. The airline industry can learn a lot about their customers during the booking, check-in, boarding, and even during the flight. Airlines, along with loyalty programmes, generate arguably more customer data than any other industry. This data contains vast amounts of valuable intelligence that affects operations, efficiency, and service.

A. Business Intelligence Methodology
Business intelligence is a collection of technologies that are used to help businesses make better use of data to enhance decision-making quality. It is a process of extracting a large amount of data from information and knowledge to provide accurate intelligence on the nature of the market [1]. With business intelligence, the term business analysis is often used interchangeably. Business analysis, however, is a subset of business intelligence because business intelligence addresses strategies and instruments, while business analysis focuses more on techniques. Business intelligence is descriptive, whereas forecasts for business analysis are indicative and used to solve problems or business issues [2].
Business intelligence tools are application software that can be used to gather and process vast amounts of unstructured data. With the BI tools, it can also help the business to prepare data information for analysis, making it easy to build organizational reports and create dashboards to make visualizations of the information. As a result, decisionmaking can be accelerated and improved by staff and managers, operational efficiency improved, new revenue potential clarified, market trends identified, true KPIs reported, and new business opportunities identified [3]. There are many types of business intelligence tools such as Tableau, Power BI, Yellowfin BI, and more [4]. They can perform data analysis by generating reports, summaries, dashboards, maps, graphs, and charts that are informative and easy to understand.

B. Overview of United Airline
US Airline is also recognized as the world's largest airline, with a massive number of daily departures completed and a combined fleet of over 2700 aircraft. There are flights departing and arriving every day from different countries. There are almost 889 million passengers being transported in 2018, according to the research, which can be recognized as the largest air travel market in any single country. US Airlines have carried major 18 airlines, categorized as mainline, regional, and freight airlines. United Airline is one of the major airlines in the US whose headquarters are located at Willis Tower in Chicago. United Airlines is as known as the third largest airline after American Airlines and Delta Airlines in the world. The slogan of United Airlines is "Fly the Friendly Skies" [5]. www.ijacsa.thesai.org The main business goal of United Airline was to provide the highest quality of service for every single passenger wherever they encounter the airline. Moreover, to create repeat customers through satisfying them on their leisure or business travel. To achieve this, United Airlines had to provide top quality service which must deal with the customer in a respectful and helpful way. Furthermore, ensure their service meets customer needs by exploring the new alternatives and goods for enhancing customer loyalty [6].
Since the United States is a prosperous country, there are also many people traveling between countries. Therefore, many customers book or buy air tickets online or at the counter every day. United Airlines must collect the associated information to understand the daily flights, customer purchase records, and so on. Through the data collected, United Airlines can improve the quality of service, improve customer loyalty, meet everchanging customer needs, and boost business profitability.

C. Challenges Faced by United Airline
 Stiff competition: The aviation industry has the characteristics of high investment, high risk, high technology, and low profit. In the United States, there are a lot of airline companies such as United Airlines, Delta Airlines, Southwest Airlines, Virgin Airlines etc.
In the airline industry, demand for air travel and capacity has seen tremendous growth, but profitability is comparatively poor, even though unit costs have decreased by half in the past 40 years. Much of the productivity benefits from reduced prices have been passed on in the form of lower airfares to consumers [7].
 Social media: Over the past few years, social media has changed the Internet completely. Social media creates a bigger and easier forum for consumers to communicate and share knowledge and opinions. The content discussed and posted on social media can come in many shapes and forms, such as text, pictures, audio, and video, which can be used to communicate different types of messages for different scenarios. Airlines also receive airport and flight experience complaints from travelers. In addition to formal grievances, individuals nowadays often express views on social media about their travel experience, especially when unsatisfied. Such feedback regarding the preferences of customers is highly revealing. The knowledge is used by advanced data analytics to identify patterns and best practices in customer service.
 Digital transformation: Customer data, product data and operational data are required for the aviation company to stand out from competitors. United Airlines can collect thousands of operational and transaction data every day from various departments. Thus, how to analyze the data is important to get the information insights and discover hidden knowledge. The data itself has no value what matters what United Airlines is do with the data. Poor data quality such as missing values, noisy data and outliers may become a big challenge for the United Airlines when processing with [8]. Without general knowledge of the various methods of data processing, incorrect information can be presented. It can be very risky for decision makers without the aid of a trained data scientist to analyze the data. Therefore, several prejudices can exist, and their decision making cannot be validated by the evidence.

D. Opportunities of United Airline
 Enhance customer experience: Companies that truly recognize and prioritize their consumer demand benefit from crucial competitive advantages such as higher revenue and market shares and more loyal customers. They can distinguish themselves and improve customer loyalty from their rivals. Introducing new channels to their customer service, such as chatbots on websites, or event-trigger customized deals or communications, the customer experience is enhanced by data analytics. These tools detect trends based on previous experiences and provide clients with optimized solutions to their most common problems, leaving human interaction to solve more complicated problems [9].
 Improve customer loyalty: Airline companies get access to detailed information about each individual customer. They manage to obtain the customers' recent social media interactions with them and even access to customers' previous journey. With the help of BI, airline companies can get a holistic view of each customer. More specific marketing communications can be offered by airlines companies and personalize customer experience. This eventually improves the conversion of campaigns and consumer satisfaction. Hence, retain the customer loyalty [9].
 Reduce marketing expenses: Among the various airline companies, it can be competitive for the customer to choose the right airline for them. It might depend on different factors, such as overall ratings for the airline company, the seat cleanliness of the airplane, the food and beverage that was provided on plane and the value for money. A customer would choose their preferred airline company over another. Some might choose United Airlines because of the tidiness of the seats. With the help of BI solution, the company would be able to analyze the preferences of each customer and get to know how these factors affect the customers and give impact to the company. The marketing expenses could be reduced, and more revenue could be used on other sectors.

II. DATASET ANALYSIS
The dataset is grabbed from Kaggle which is the Twitter airline sentiment. The original source version is slightly reformatted from Crowdflower's Data for Everyone library [10]. Both a CSV and SQLite files are included with it. The dataset basically contains the sentiment of tweets for 4 airline companies, which are Delta Airline, Southwest Airline, United Airline and Virgin Airline. The Table I and Table II identify the description of each attribute for two different datasets.

A. Dataset Cleaning
Microsoft Excel is a software that can be used to store, manipulate, and present the data sets. It provides various types of tools to make sense of the datasets using its characteristics and formulas. Thus, Microsoft Excel will be used to clean the datasets, so that noisy data and duplicate values can be identified and removed.
 Remove duplication: Large datasets appear to have redundant material. Identifying duplicate data and deleting it is the first step in data pre-processing. Duplicate data has given us little benefit. The performance and quality of the dashboard will be reduced. Thus, the duplicate records are removed from the datasets. There are a total of 70711 duplicate records have been removed. Now the datasets remain 61185 unique values.
 Split texts into columns: To perform text analysis using word cloud, it is necessary to split the whole text into each individual column. Thus, select the entire column of the text then split text to column with delimiters. It is required to select the delimiter type to split the text into columns. The selected delimiters include semicolon, comma, space, and full stop.

III. BUSINESS INTELLIGENCE ARCHITECTURE
Business intelligence architecture refers to the architecture used by an organization to define their data source of collection information processing and all the technology that supports their business intelligence. BI architecture consists of various parts and layers and every single of them are important as each of the components have its own purpose [11]. The components include:  Data collection: The very first steps of business intelligence. Nowadays data is becoming more and more important to drive a business to success. Depending on a company's criteria and finances, a company may obtain their data through different data sources, such as CRM, ERP, databases, files, or APIs. These applications and methodologies collect data from internal processes and external sources of organisations.
 Data integration: The following step will be data integration which uses the data collected previously and proceeds on with the Extract, Transform and Load (ETL) process. For extract, the data will be pulled out from external sources. Then transform the data into a demand standard which also ensures the data is clean with no noise data. After that, load it into the destination data warehouse.
 Data warehouse: This is the place where all the data is being stored. The data warehouse outlines data for upstream applications in dimensions and fact tables. Processes performed in a data centre are data cleansing, metadata management, data distribution, resource management, recovery, and backup planning.
 Data analysis: This phase will be focused on data processing after it has been handled, transformed, and cleaned in previous steps by the data warehouse. The ubiquitous need for effective analysis to enable businesses of all sizes to grow and benefit is accomplished through BI application tools. Modern BI tools like Tableau enable users to create queries using drag and drop, building visualization graphs within a few clicks.
 Data distribution: When it comes to sharing information and providing stakeholders with indispensable perspectives to achieve sustainable business growth, data distribution is one of the most critical processes. distributed can be performed in 3 ways which are reporting, dashboard and embedding. Reports can be viewed and shared among recipients on a defined schedule. Alternatively, a dashboard can be shared for the recipient to view but not able to make changes. Without the need to mark the BI tool on external www.ijacsa.thesai.org applications of intranets, your own application may use dashboards as a means of analytics and reporting.
 Actionable insights: Data driven decision will be the final stage for the BI architecture. With the insight taken from the previous stage, higher management like CEO will have the ability to produce true, specific, data-based decisions that will assist them to move forward. This helps a high-level manager to get an understanding of strategic growth and future decisions to build a profitable organisation. Fig. 1 shows the example of BI architecture.

IV. DIMENSIONAL MODEL
A dimensional model is a data structure consisting of a table of facts and tables of measurements. A fact table consists of measures that allow us to quantify quantitative data with ease of dimensional modelling. The dimension tables include descriptions of the fact table that surrounds the business process case. The following section discusses five steps to construct a dimensional model [12].
 Identify business process: Business process must be identified because it needs to define how the data stores in the data warehouse. The way to store the data should be efficient to process the data analysis. The data is scraped on the Internet and compared between the different companies. It could be used to identify the pros and cons of the different airline companies and help the company in terms of their decision making on marketing purposes.
 Identify granularity: This step is to identify the granularity of each This could allow the system to optimize the query processing time. The schema can be implemented on the data warehouse with the help of ETL process which processes dimensional modelling. Fig. 2 shows the star schema developed for United Airline.

V. DASHBOARD VISUALIZATION
There are 4 dashboards created for this study, Airline Recommendation Analysis, Passenger Review Analysis, Customer Sentiment Analysis, and Airline Rating Analysis. Fig. 3 section describes the dashboards in detail.

A. Airline Recommendation Analysis
The airline recommendation analysis is a dashboard which provides insights from different perspectives. There are four major airline companies which are Delta Airlines, Southwest Airlines, United Airlines, and Virgin America. This dashboard is focused on the recommendation of the users whether the users would recommend the particular airline which they have tried before, this could be critical because it might affect the customer's loyalty and company's profits It allows the users to have a better understanding of the overall recommendation of each airline company. Moreover, it provides some drill-down features about the airline companies and the cabin types. Fig. 4 shows the total percentage of users who are recommended and not recommended to the airline companies. So, from the pie chart below it shows that the users who have taken flight before do not recommend their airline companies which they have taken before. This might be related to several reasons, such as food and beverage availability in the airplane, the comfortless of the seat on the plane, and the value for their money. www.ijacsa.thesai.org     5 shows the number of users who recommend and not recommend the airline company. Although Virgin America has the least number of records which has a total of 0.56%, most of the customers who had travelled with it were willing to recommend the airline to others. The company United Airlines has the highest number of customers who had travelled with them before, but most of the customers do not recommend it to others which consists of 58.24% among all the airline companies. From the customer perspective, it indicates that the United Airlines does not do a great job to serve their customers and it might be better to choose the other airlines rather than United Airlines. Although there are many customers who do not recommend United Airlines, there are still many customers who have taken flight with them. The reason behind this might be because of the cost of the air ticket since most people only care about the cost and want to keep the cost to the lowest. Fig. 6 stacked bar chart shows the drill-down features with the airlines and cabin types. The users would have a clear understanding of each cabin type and how they relate to the number of customers. In the economy class of the cabin type, all the four airline companies own the highest number of passengers. The reason for this might be that the cost for the air ticket is the cheapest and most value of money when compared with the other cabin types. Among the economy class passengers, it indicates that most customers are from United Airlines which is true from the previous section, because it sold the most air tickets among his competitors. Fig. 7 shows dashboard for passenger's review analysis that is able to provide useful insight to the airline company. The overall review of cabin class can give insight of which type of cabin class should be improved more to meet passenger's satisfaction. The number of reviews shows the total number of passengers reviewed from the year of 2014 to 2018. Count of cabin will define the cabin type taken for the passengers. Lastly the word cloud of review will display the most frequent word collected from passengers' review. The dashboard will be split and explained in more details in following page.  848 | P a g e www.ijacsa.thesai.org  Apart from that, the Fig. 9 shows the overall reviews of the cabin types in each airline. There are four types of cabins which are business class, economy class, first class and premium economy. In the United Airlines, the economy class is the lowest selling ticket which only consists of 2.597 in average. This could lead to some serious problems because most of the seats in the airplane are economy class and if the more seats are empty, United Airlines would not be able to obtain the highest profitability on each flight. To get the highest profitability in terms of efficiency and effectiveness, the ticket sold on each cabin class should be equally distributed which would result in the plane being filled with customers in each flight. Other than that, the Fig. 10 shows the reviews from the customer are important for the airline company as they can obtain information from it and make improvements. However, there are more than millions of reviews and the time taken for the airline company to read one by one will be a waste of time and effortless. This is the place where word cloud plays an important part as it will display the most relevant word from the reviews. Figure above shows the top 30-word review from the customer. The larger the word the higher the count of the word. The largest word that observed from the diagram above was "flight" which means the reviews are related to flight. Furthermore, the author noticed that "delayed", "hours "and "minutes" appear on the word cloud which mean that there are many reviews related to the flight delay which can up from minutes to hours. Another word "service", "good" and "experience" can be concluded as the passengers' experience with the good service.

C. Customer Sentiment Analysis
In this section, the customer opinions as well as emotions towards the services that the airline companies provided will be discussed in detail. This dashboard provides information from a different viewpoint. Sentiment analysis, also known as opinion mining, helps airline companies to analyze the sentiment of a piece of text and present it in the form of a dashboard. Basically, the study of text sentiment helps us to get an understanding of whether a piece of text is positive, negative, or neutral. While at the time the airline companies gathered the tweets, this gives them a clear sense of the public's opinion about the service they provided and tells the airline companies anything about what exactly people were talking about positively or negatively. Sentiment analysis will tell the airline companies what a piece of text 's sentiment is. But people-generated text typically talks about more than one thing and sometimes has more than one emotion. Someone might write, for instance, that they didn't like the customer services of the United Airlines, but they liked the inflight experiences and feel comfortable on the journey, and a model of document-level sentiment analysis would only look at the whole document and sum up whether the overall feeling was mostly positive or negative.  Customer satisfaction plays an important role within the airline company (Fig. 12). It is not only the leading metric for assessing consumer satisfaction, recognizing dissatisfied customers, minimizing turnover, and increasing revenue, but it is also a crucial point of differentiation that allows you in competitive market environments to gain new customers. Thus, airline companies need to analyze and understand the customer's experience to improve the services and products provided. Fig. 13 shows the percentage of customer's emotion about the flight. Based on the result, we can conclude that most of the passengers were not satisfied with the services provided by the airlines company. More than 60% of the passengers write a negative response to the airline companies, while only 16.86% of passengers feel satisfied with the services provided by the airlines company. While 23% of the passengers did not specify their emotions on the flight experience. Since most of the passengers were not satisfied with the service provided by the airline companies, thus the airline companies need to figure out the reason why the customer's feel bad about the flight. Else, the companies may lose a lot of customers and profit from that. Since the overall sentiment of customers towards the U.S. aviation industry has been analyzed, drill down is also necessary to know the customer's emotion on the specified airlines companies, so that we can compare the customer's reviews of United Airlines and its competitors, which are Delta Airline, Southwest Airline, and the Virgin Airline. The Fig. 14 shows the customer emotions in each airline. There are three types of emotions which are positive, negative, and neutral. In the United Airlines, 44.83% of customers have negative emotions about the services provided, while around 12% of passengers did not express their emotions, and only 8.38% of the passengers feel satisfied with the services provided by the United Airlines. However, if we look at the other airline companies, the gap in customer satisfaction with service is not very large. This result could lead to some serious problems which may result in loss in both customers and profit, since the customers are more likely to pursue better services. Thus, if the United Airlines did not figure out why customers were not satisfied with the services provided, it is possible for the customers to switch to the competitive airlines.
As the Fig. 14 analyzed how many people feel unsatisfied with the service provided by the airline companies, it is important for the airline companies to look at the negative reviews to find out the reason. The Fig. 15 shows the number of negative reasons for each airline. The negative reasons are basically classified in several types, such as customer service issues, flight cancelled, luggage lost and damaged, flight www.ijacsa.thesai.org booking problems, longlines, etc. Among them, around 26% of passengers did not like the customer services, while around 20% of the passengers feel unhappy because of the flight delays. Around 10% of passengers unhappy because their luggage lost and damaged during the transportation, while 7.40% of passengers unhappy because their flight was cancelled. These are the major reasons which tell the airline companies why the passengers did not satisfy the airline service. Remaining 20% of the passengers feel unhappy because the booking system is not efficient, or unhappy because of the longlines which required them to waste a lot of time booking for a flight and waiting for the flight. However, 15% of passengers did not want to tell why they feel unhappy on the service. Thus, it is important for the airline company to know in detail which service needs to be improved to fulfil the customer needs.  The United Airlines should understand why the customer was not satisfied with the services provided as it is the critical element to increase the number of customers. To achieve this, United Airline must prepare analysis with the negative reviews to see how they performed if compared with other airline companies, so that United Airline can know what they can improve to attract the customer. Fig. 16 above shows the dashboard which tells the United Airline negative reasons of the customer. The insights are not accurate for Virgin America because the amount of negative reasons for them are not sufficient to compare with other airlines. So, the insights will be focusing on the three other airlines which are Delta Airlines, Southwest Airlines and United Airlines. Based on the result, we can know that the top three negative reasons are customer service issues, flight delay, and loss of luggage. Among them, most of the customers which are 1926549 feel unhappy because they were not satisfied with the customer service. Thus, the airline company needs to improve their customer service to the customers. For example, providing training to the cabin crew and the ground service crew which can greatly improve the customer experience. Besides, 1,485,225 customers feel unhappy because of the delayed flight. Flight delay may disrupt the customer's schedule, especially for the businessman. There are 761,001 unhappy customers because of their luggage lost during the transportation. It can be critical issues as it may cause customer losses on financial.

D. Airline Rating Analysis
The Fig. 17 shows The Fig. 18 shows the traveler's average rating to the seat comfort between the different cabins. Usually, there are four types of cabins which are business class, economy class, first class, and premium economy. Different types of cabins, the price is also different, and the services provided are also not the same. According to the Fig. 18, the rating given by the passenger of the seat comfort is high and low, compared to the different airlines and different cabins. We can see that the highest average rating of seat comfort is given by the family leisure travelers of the business class of Southwest Airlines and the couple leisure travelers of the premium economy of Virgin American. Southwest Airlines and Virgin America Airlines have significantly different ratings of seat comfort given by passengers. In contrast, the average ratings given by passengers of Delta Airlines and United Airlines are not much different. Both get the 5 of average rating, this means that the travelers feel it is comfortable when they sit on the seat. On the other hand, the lowest average rating that we can view from the figure is given by the travelers, which are the couple leisure travelers of business class of Southwest Airlines and family leisure travelers of the premium economy of Southwest Airlines. The average rating is the 1 score given by travelers. The reason why travelers are given a low rating might be that they feel that the seat is not comfortable. www.ijacsa.thesai.org   Usually, the stewardess on the plane will push the food truck to ask each passenger if they need food and beverage, or some passengers have already ordered the food and beverage they need when buying tickets. Therefore, the Fig. 19 shows how satisfied the passenger is with food and beverage. From the Fig. 19, passengers of different airlines have different levels of satisfaction with food and beverages. The passengers of Delta Airlines gave an average rating of satisfaction with food and beverage between 2.5 and 3.5. So, we can know that they are not very satisfied or dissatisfied with the food and beverages provided by the airline. We can see that the highest rating of food and beverage is given by the family leisure travellers of the business class of Southwest Airlines. Then, solo leisure travellers of the first-class cabin of Virgin America gave the second-highest rating for the food and beverage. The average rating for both is 5.0 and 4.3. This shows that passengers are very satisfied with the food and drinks provided by the airline. On the contrary, the business travellers of the first-class cabin of Virgin America gave the lowest rating for the food and beverage. The second-lowest rating is given by the business travellers of the economy class of United Airlines. The average rating given by both is 1.0 and 1.7. The reason they are given low ratings may be that they feel that food and drink cannot satisfy them.
When passengers take a long-haul flight, they may feel bored on the flight. Therefore, some of the airlines have provided entertainment for their passengers to spend time. There are many entertainments on the plane for the passengers such as in-flight WI-FI, music library, TV shows and movies, games, digital shopping and so on. The Fig. 20 shows the average rating of passengers on the entertainment provided on plane. Overall, we can see that the first-class cabin of the passenger is more satisfied with the entertainments that provide on the plane than the passenger of the economy class. This is normal because the ticket price of the first-class cabin is higher than that of the economy class, so the entertainment provided is different. The highest average rating was given by the solo leisure travellers of the first-class cabin of Virgin America which have 4.5 of average rating. Because Virgin America has been voted as the airline which has the best inflight entertainment for the passenger, that is why it got the highest rating. On the other hand, the lowest average rating from the above figure is business travellers of the economy class of United Airlines which have 1.725 of average rating. Meanwhile, the overall entertainment rating of the economy class and first-class cabin of United Airlines is lower than other economy class and first-class cabins of the airlines. Thus, United Airlines must figure out the problem and improve to provide better entertainment for its passengers.  The Fig. 21 shows how satisfied the passenger was with the service provided by the stewardess in a different cabin. According to the graph, the rating given by the passenger is different. The cabin service provided by Delta Airlines overall is more average. Besides that, the average rating of Southwest Airlines and Virgin Airlines some are high, and some are low. The highest rating that was given by the family leisure travelers of business class of Southwest Airlines and couple leisure travelers of the premium economy class of Virgin America, both get 5 average ratings each. Meanwhile, the lowest rating that was given by the family leisure travelers of premium economy class of Southwest Airlines and couple leisure travelers of the first class of Virgin America, both get 1 average rating each. Thus, the airlines must figure out the problem and provide solutions to prevent loss of customers and achieve a good reputation. To prevent this, the airlines can provide training for their stewardess and other staff, instill the concept of customer first. In addition, give punishment for those who had bad service that was provided to the passenger.

VI. CONCLUSION
In conclusion, this paper demonstrates the importance of business intelligence in the aviation industry. United Airlines needs to uncover hidden knowledge and gain a more competitive advantage. This study has analyzed the customer reviews on Twitter to understand customers' opinions and emotions on the aviation industry. The authors believe that the insights and BI solutions included in this study can help United Airline to improve their business values.