Hotel Booking Analysis
- Aroyewun Airat
- Dec 13, 2022
- 3 min read
Updated: Sep 30, 2024
About the Dataset
This dataset contains hotel booking information from the year 2010-2019.
The dataset contains the following columns:
1. Booking ID
2. Date of booking
3. Year
4. Customer ID
5. Gender
6. Age
7. Origin Country
8. State
9. Location
10. Destination Country
11. Destination City
12. No of People
13. Check-in date
14. No of days
15. Check-out Date
16. Rooms
17. Hotel Name
18. Hotel Rating
19. Payment Method
20. Bank Name
21. Booking Price [SGD]
22. Discount
23. GST
24. Profit Margin
Data Profiling
I examined the data structure, Interrogated column properties and statistics, and Identified data anomalies with the query editor using the column quality, column distribution, and column profile features in the views tab.
Data Importing
I imported the CSV file into power query using the "Get data" option on the Power BI desktop.
Data Cleaning
After examining the dataset, I
Changed Data type, making sure all columns have the appropriate datatype.
Removed Duplicate to reduce redundancy.
Inserted a Month name column.
Inserted a multiplication column to get the discount price for each row, Discount(%) multiplied by the Booking price. Named it “Discount Amount” and changed the data type to a decimal number.
Inserted a subtraction column to get the Net Booking Fee, Booking Price minus Discount Amount. Named it “Net Booking Fee”.
Inserted a multiplication column to get the amount of Tax Paid, Booking Price multiplied by the GST column. Named it “Tax Paid”.
Inserted a subtraction column to get the booking fee after TAX fee has been paid, Net Booking Fee minus Tax Fee. Named it “Booking Fee after Tax”.
Inserted a Multiplication column to get the Profit, Booking Fee after Tax multiplied by Profit Margin. Named it “Profit”.
PBIDS File
To makes the data source easy to share with the team, after cleaning the data I created pre-wired data source connection settings using the data source settings option. Now other team members can enter their credentials to get the data.
DAX-Calculated Table
I created a Calendar Table using the CALENDARAUTO function.
Data Modeling
Created a 1:* relationship between the Booking Fact Table and the Calendar table.
DAX-Measures
I created the following measure,
Total Customers, using the DISTINCTCOUNT function
Last Year customers, Using the CALCULATE and DATEADD function
Last Quarter customers, Using the CALCULATE and DATEADD function
Last Month customers, Using the CALCULATE and DATEADD function
Total Booking Fee, using the SUM function
Last Year’s Booking Fee, Using the CALCULATE and DATEADD function
Last Quarter Booking Fee, Using the CALCULATE and DATEADD function
Last Month’s Booking fee, Using the CALCULATE and DATEADD function
Total Profit, Using the SUM function
Last Year’s Profit, Using the CALCULATE and DATEADD function
Last Quarter Profit, Using the CALCULATE and DATEADD function
Last Month’s Profit, Using the CALCULATE and DATEADD function
Total Discount, using the SUM function
Total Tax Paid, Using the SUM function
Avg Hotel rating.
Groups
Added a group that gathers each value in the Age column into “bins”. The size of each bin is 5.
Visualization
For the visualization,
I used a sunflower Twilight for the report color theme.
I created a custom canvas type making the height 2,000px and Width 1,500px
I created 3 report pages; All 3-report pages contain the same visual However First page displays yearly trend analysis, 2nd page displays quarterly trend analysis, and the 3rd page displays monthly trend analysis.
A tree map visual which serves as the slicer
5 KPI cards showing current year value versus previous year value e.g. Total Profit vs Previous year profit.
A bar chart showing the top 5 Destination countries by Profit.
A bar chart showing the total profit by Payment Mode.
A text box containing key insight or information from the analysis.
A column and line chart showing the total booking fee and total profit by year.
A column chart showing the Total customer and Total Profit by Origin country.
A matrix visual showing top 10 hotels in the rows by Total Profit, Total Booking Fee, and Avg hotel ratings in the values.
Power BI Service
I created a workspace in power bi service and named it “Hotel Booking Analysis”
Published the report into hotel booking analysis workspace.
Added other team members and granted them access.
After reviewing of analysis, I endorsed the report and created an app granting the entire organization access.
App link{Click link to view report}
Comments