Hotel Bookings Analysis

Goal:Develop a Database to Analyse & Visualize Hotel booking Data.

By using the above the hotel data which is in excel format we are going to answer the questions by analyzing data using sql and Power BI

The following questions like:

Is our hotel revenue growing by year?
Should we increase our parking size?
What trends we can see in the data?

Dataset Description:

we have historic data of hotels in various fields over period of three years that is from 2018 to 2020 in three different excel sheets and also two other important separate sheets are meal_cost and market_segment ,all these sheets will be added into single table using SQL Joins.

At First We need to build a database by using SQL Server management studio.From there we need to import excel data into SQl server.To start with the dataset ,we will need to start with data cleaning and should join the tables 2018,2019,2020 and market_segment,meal_cost.Data cleaning can be done with excel.After the data cleaning excel file should be imported into SQl server as it is a huge data set we are going to join all the tables by using SQL joins.Below is the sql Query.

After the execution of query the result is as follows

After you finish joining the datasets and making it into one single table, we move on with the visualization part, for which we will use POWER BI as the Visualization tool.

To Start with Power BI, we’ll need to connect it with the SQL Server and Import Data

After importing the data, we’ll make a few additional columns and measures that are needed to complete the analysis.

For This Dataset and according to the tasks given to us, we will have to make Columns and Measure.

- - Revenue (Column)—Revenue = ([stays_in_week_nights]+[stays_in_weekend_nights]*Query1[adr]*(1-Query1[Discount]))
- Total Nights(Measure) -Total Nights = SUM(Query1[stays_in_week_nights])+SUM(Query1[stays_in_weekend_nights])

Car Space(Measure) -Car Space = SUM(Query1[required_car_parking_spaces])

Conclusion

Coming to the Conclusion of the Project, we’ll answer the Questions that have been asked -

The Revenue as clearly can be seen growing from 2018 to 2019 on yearly basis, but then it drops in year 2020, the reason for that is very well known and that is Covid-19 that shut down the world.
The Maximum Car Park size that was required at can’t be determined because of the insufficient data, as you drill down on Hotels you’ll see that the rate of Car Parking doesn’t Fluctuate much.
One of the Biggest Trends that You’ll see is the ADR is more in Resort Hotels in seasons while compared to the city hotel, while Off-season the City Hotels beat Resort hotels but by very thin margin.

Page updated

Google Sites

Report abuse