Context / Domain / Market of Consultancy

​

We are a consulting group that helps businesses to solve problems by analyzing data and making reports to them. Recently, we are helping a travel agency that has suffered a lot during the pandemic of Covid-19 and is trying to regain the market share and generate more profits covering the loss and debt it had during the spread of the virus. Since they have already built a strong relationship with multiple airlines and have a really good reputation among their existing clients, we are aiming to help them to choose appropriate airlines that can offer better services and travel experiences for customers on the go. Additionally, due to their huge loss during the pandemic, the agency is not able to keep all of its existing partners and need to choose airline companies to accompany with and try to create some new travel routes to accommodate consumers’ satisfaction, the excitement of traveling after the pandemic, which is extremely important for the agency keeping the reputation in the whole market. From our perspective, both valuing customers' travel experience and generating revenues are the top important factors increasing the agency’s business value. Because of the limitation of our dataset, we assume that better customer satisfaction may help us get more customers, as then we can generate more profits.

​

​

Focus of the Consultancy

​

In this case, for our business, we attach more importance to the revenue that they can generate by booking airline tickets for customers while caring about our clients’ good experiences. To make sure their customers could have good experiences choosing innovative travel routes, we focus on several aspects which their customers mainly care about, which are on-time arrival by airlines, complaints, and mishandled baggage. We will be conducting deeper research using data from the US department of transportation in the next sections. Furthermore, finding out the on-time scale of the percentage of arrivals and departures by airports is crucial for them to set new travel routes between the origin and destination. By doing so, we can help our clients improve their performance on the market, attracting more customers and increase its own competitiveness as well on the internal.

 

​

Data Quality Assurance

​

Our data sources are derived from the U.S. Department of Transportation official website that is clear and reliable. https://www.transportation.gov/airconsumer/air-travel-consumer-report-archive The data included the Air Travel Consumer Report from 1998 to 2021 and the data are presented monthly. To ensure data consistency, we have all used Air Travel Consumer Reports for the recent 3 years (June 2018 to July 2021). After getting the data, we extracted different data according to different indicators and then we cleaned and integrated them in the Tableau Prep. We removed some of the data that are not useful for our visualization and renamed the columns that are not correctly interpreted by Tableau Prep. Although our raw data is well-organized on the website, we still encounter some problems in the process of data cleaning. For example, when we took the average over three years, we needed to integrate monthly data from each airline into three years. In this process, we found that some of the carriers did not appear in the reports of certain months. Therefore, when we do table joining, there are mismatches in the carriers’ column. What we did is that we left joined the tables and set the missing value as null and then took the average of the rest values. However, if the sample size is smaller than 30, we are not going to use the data(carrier) because it will cause inaccurate results and the results are not comparable with other airlines. Plus, there is another problem with our airport data. Some of the cities have more than one airport but the Tableau can only show airports in the city-state. Therefore, we took the average percentage of on-time arrival of different airports in this city as the city's airport punctuality rate. After we have done the cleaning, we import the cleaned data into Tableau for data visualization.