Exploring Hotel Booking Patterns and Cancellations: Insights from a 2017-2018 Dataset

Link to Dashboard

Structure and Scope of the Dataset: The dataset consists of 36,275 unique booking records from a hotel management system. Each record contains multiple features, such as the number of guests, type of room, meal plan, and booking status. It covers booking details from 2017 to 2018 and provides insights into customer behavior, booking patterns, and revenue generation.

The dataset provides insights into key aspects of the hotel’s operations, such as: Customer preferences (e.g., preferred room types and meal plans) Booking frequency (e.g., booking trends by day of the week, month, and holiday periods) Cancellation behavior (e.g., previous cancellations and cancellation rates) Revenue generation (e.g., price fluctuations and segmentation by room types)

Tools and Technologies Used: 🗂️📝 R: for data cleaning, transformation, and preliminary analysis 📊 Power BI: for data visualization and interactive dashboard creation DAX : Employed for calculating various metrics and measures, including bookings, cancellations, weekends and average prices.

Number of records: 36,275 Number of fields: 19 License: from www.kaggle.com

Data Processing and Analysis:

Cleaning and Structuring the Data: The dataset required several cleaning and processing steps to ensure its readiness for analysis. The following tasks were performed: ✨ Standardization of columns, such as removing extra spaces in the Form field. ✨ Filling in missing values where necessary to maintain consistency. ✨ Handling of Leap Year (February 29th): All occurrences of February 29th were adjusted to February 28th to ensure consistent dates within the 2017-2018 range. ✨ Filtering out incomplete or irrelevant records to focus on the most relevant data. ✨ Creating a dimDate table for time-based analysis, which includes various date attributes (e.g., weekday, weekend, month, year).

In this project, I demonstrated my skills in handling missing data and transforming raw information into a usable format. The following code shows how I identified and managed missing values, corrected leap year dates, and created a new date column.

Visualization and Data Exploration: Various visualizations were created in Power BI to explore booking trends, pricing patterns, and seasonal variations. Key visualizations included: Bar charts for Total Bookings by month. Line charts highlighting weekends and holidays. Boxplots showing price fluctuations by room type and meal plan.

Interactive Dashboards: Interactive dashboards were built in Power BI, allowing users to drill down by date, room type, and market segment. These dashboards provided an overview of bookings, cancellations, and trends over time.

Key Findings:

⭐ Total Bookings are most concentrated in the first half of the month, with significant peaks towards the end. However, peak booking days do not coincide with weekends, and this varies from year to year. ⭐ Weekend Bookings: While weekends are important for bookings, the overall distribution shows a strong variation year by year. Different months such as October and November show higher booking volumes during weekends. ⭐ Cancellation Rate: The cancellation rate stands at 32.76%, with longer stays (13-16 nights) and Meal Plan 2 contributing significantly. Room Type 4 with Meal Plan 2 has the highest cancellation rate.

Learning and Value Creation: By focusing on high-demand periods (peak booking days), hotels can optimize pricing and offer promotions to increase revenue. Segmenting bookings based on room types and meal plans can provide deeper insights into customer preferences. A more targeted approach to handling cancellations, especially for longer stays and specific meal plans, could help reduce losses.