A complex analysis in Excel of a huge dataset with booking information for a resort in Algarve, Portugal, with more than 800,000 values.
- Original Dataset include booking information for a city hotel and a resort hotel, both in Portugal.
- Dataset includes information such as when the booking was made, length of stay, number of nights booked in week-day and in the week-ends, the number of adults, children, and/or babies, the source of the booking (direct client, corporate, travel agencies) and many other things.
- Original dataset has 119.390 rows, of which 79.330 for the City Hotel and 40.060 for the Resort Hotel. In order my laptop can handle the analysis I had to reduce the size of the data.
- The total of 119.390 rows are referred to a period of 3 years: 21.996 rows for year 2015, 56.707 rows for 2016 and 50.687 for 2017.
- I take only the year in the middle, that is year 2016, for two reasons: 1) it is the year with the greater number of data and 2) it is the year in between, so I can be sure that there are no weeks/months lost.
- Than I split the data for the two hotels, the City Hotel file with 38.140 rows and Resort Hotel file with 18.567 rows.
- I analyze this last Resort Hotel file, with more than 44 columns and more than 800.000 values
- This is a several hour job ending with 9 spreadsheets, 23 pivot tables, 1 working sheet and 1 service table.
- And this is only the first part of what a full and complete analysis could consist.
- I am trying to give the first answers about: where clients come from, when clients book, number of bookings and how many booking are cancelled, source of bookings, differences for room/nights sold accordingly the source of booking, the month of stay, if clients are private ones or corporate, if they are singles, couples or families…
- In order Excel for the Web (that require a max of 25MB file) to accept the link, I splitted my 35MB file in 2 different workbook files.
For cleaned and modified data of Resort datasheet (no Pivot and no visualization): Resort Hotel Booking RAW Sheet For data analysis, pivot tables and spreadsheet visualization: Resort Data Analysis and Visualization Hotel Booking