PROJECT PORTFOLIO ON AIRLINE ANALYSIS USING POWER BI
INTRODUCTION
The dataset is about a fictitious Air boarding company that operates in NIGERIA. The company has been having unstable growth in terms of customer satisfaction and is looking for ways to steadily grow its sales and is hoping to expand by trying to meet their customer satisfaction.
The air boarding data that will help to determine how to improve in their customers satisfaction has been provided.
IMPLEMENTATION OUTLINE
To analyze this dataset, I followed the PMAVM process (Prepare, Model, Analyze, Visualize, Manage). Each of the steps in the process is discussed below.
The tool I used for the entire analysis process is power bi.
1. DATA PREPARATION
I deployed the OMG-C method in the data preparation step. Objectives, Measures, Get and Clean the data.
Objective:
- Calculate the total customer
- %Net Promoter Score (NPS)
- Total dissatisfied customer
- Total satisfied customer
- Analyze the customer satisfaction rate with baggage handling, on board service, age, class, type of travel
- Which of the satisfaction status will you recommend to the company to improve on between on-board service and baggage handling?
Measures
Based on the datasets provided, I identified 1 Objects and the Variables within the object.
Summary of the datas
Object: Air boarding
Variables: Customer id, gender, age, type of travel, flight distance, inflight Wi-Fi service, food and drink, seat comfort, departure and arrival times, online booking process, gate location, and other airport services.The dataset has 129,880 rows
Get and Clean Data
In air boarding sheet I removed the first column which no header name, flight distance, inflight Wi-Fi service, seat comfort, departure and arrival times, online booking process, gate location, and other airport services column that is not needed from the objectives.
Steps I Took in The Data Cleaning Process.
a) In cleaning, I first transform the data to power query POWER BI, renamed the table.
b) Next, I removed the column mentioned above that are not need in the analysis from the given objectives by selecting them together, then right click and select Remove.
c) I used conditional column to get the satisfaction rating of the following columns Baggage handling, Check-in service, On-board service, Ease of Online booking ,age.
d) I split the overall satisfaction column by delimiter “custom” to have satisfied and dissatisfied in a separate column.
e) I renamed some of column header tittle and change the data type to the suitable one.
f) I closed and apply.
RENAME THE TABLE.
I changed the table worksheet, Air boarding, with table name, NGAMZ AIRWAY
Since this is customers satisfaction dashboard, the first thing I did was to create the necessary totals and measures.
a. Totals and Measures(KPI)
To create Measures for Total customers for the company.
Total Customer = COUNT (‘NGAMZ AIRWAY’[Customer Id])
or
DISTINCTCOUNT (‘NGAMZ AIRWAY’[Customer Id])
To create a measure for total satisfied customer
Total satisfied = SUM (‘NGAMZ AIRWAY’[satisfied])
To Create Measures for Total Dissatisfied Customer
Total dissatisfied = SUM (‘NGAMZ AIRWAY’[dissatisfied])
To create measures for dissatisfaction rate
Dissatisfaction Rate = [Total dissatisfied]/ [Total Customer]
To create measures for satisfaction rate
Satisfaction rate = [Total satisfied]/ [Total Customer]
To create measures for net promoter score
Net Promoter Score = [Satisfaction rate]- [Dissatisfaction Rate] *100
A positive NPS indicates that more customers are likely to recommend the company’s service to others, while a negative NPS indicates that more customers are likely to discourage others from using the service. A higher NPS generally indicates a higher level of customer satisfaction and loyalty.
ANALYSIS AND VISUALS
Analysis and visuals DASHBOARD.
- The first thing i did was to check if the customers Dissatisfaction with the satisfaction rating with respect to baggage handling, on-board services, food and drink, age, class and type of travel will make the customers promote the company by recommending the companies services to other people.
Dashboard
I then created the sample dashboard shown below.
Insight & Recommendations
a) The customer satisfaction rating the affect the company most is that of ECO CLASS, and the Net Promoter Score rate is the worst at (-63%) followed by that of ECO PLUS at (-51%). The manager should organize a training for the company’s staffs to educate them on how to improve better on their customer because from the negative NPS gotten, none of the customer from either of these two classes will recommend anybody.
b) Age limit 21–40 also is affecting the company also with NPS of (-21%) which the customer is likely not going to recommend their company to anyone.
c) The chat shows a high rate of dissatisfaction from customer which simply means the management and staff of NGAMZ AIRWAY shop either retrench or resolve their staffs or organize a customer satisfaction program for the growth of the company.