Enhancing Hotel Cancellation Management through Data Analysis and Pricing Strategies

Photo by Valeriia Bugaiova on Unsplash

Hotels worldwide are grappling with a significant challenge — the issue of cancellations. Despite the magnitude of this problem, the hotel industry lacks a standardized set of rules to mitigate the financial losses incurred due to booking cancellations. In a bid to address this concern, many hotels resort to charging customers a percentage of the booking price if cancellations occur within the final one or two days. Leveraging a carefully selected dataset from Kaggle, this article delves into the creation of a dynamic cancellation fee structure based on the timing of cancellations. For more details, the entire code is available on my Github profile here.

The initial steps involve data preprocessing to ensure accurate and meaningful analysis. The process entails eliminating erroneous data entries, such as those corresponding to February 29th, 2018. Moreover, the data’s day, month, and year components are merged into a unified date column, along with the calculation of the respective quarter. A strategic decision is made to exclude Room Type 2 and Room Type 3, focusing the analysis on the most prevalent room types. Additionally, to streamline the investigation, bookings that were not canceled are removed from consideration.

Exploratory Data Analysis (EDA):

Informed by the data, exploratory charts are plotted to gain deeper insights into the dataset. EDA highlights include visualizations depicting canceled versus non-canceled bookings by quarter, average prices for both bookings and cancellations, and patterns of bulk bookings and cancellations.

We can observe that there are recurring patterns of bulk bookings and cancellations

Average price of a canceled room was generally higher compared to a non canceled room

As expected, Quarter 1 & 4 saw a relatively lower number of cancellations, mainly because of the pre planning of vacations during the holiday seasons.

Creating Room-Specific DataFrames:

A pivotal aspect of the analysis entails the creation of dataframes for each quarter and room type combination. Statistical measures, including the lower quartile, upper quartile, and mean of lead time, are calculated for every room type and quarter pairing. The number of cancellations for each combination is also documented. These data points facilitate the formulation of ranges for lead time and contribute to the determination of average prices based on cancellation count and average price.

Formulating Cancellation Policies:

A key development involves categorizing bookings into two groups based on lead time: those canceled before 90 days and those canceled within 90 days. This segregation is aimed at refining the cancellation charges strategy. The bookings that were cancelled before 90 days have not been charged. Furthermore, critical predictors are assigned specific weights, encompassing the count of cancellations, maximum lead time, and total price paid by customers for cancellations.

Calculation of Cancellation Charges:

The core of the approach lies in the formulation of a comprehensive formula to determine cancellation charges. This formula is articulated as follows:

Amount to be charged = (Price + Count + Lead Time) / Sum

Where:

- Price = Weighted_price * Total price

- Count = Weighted_count * Total Count

- Lead Time = Weighted_lead_time * (1 / Lead_time_max)

- Sum = Weighted_count + Weighted_lead_time + Weighted_price

Additionally, the actual cost for each canceled booking is computed by dividing the amount by the number of cancellations. The percentage of the recouped amount is gauged by comparing the charged amount to the mean price for that room, quarter and lead time combination.

Sample Output of all the calculations and the final revenue for each combination of Room Type, Quarter and Lead Time.

Results and Future Scope:

The application of this data-driven strategy demonstrated its potential to assist hotels in recovering around 22.21% of potential losses attributed to booking cancellations. This outcome underscores the feasibility of adopting a more structured approach to cancellation charges.

In terms of future prospects, it’s important to recognize that while the approach presented here holds promise, it’s not a one-size-fits-all solution. Expanding the dataset to encompass more diverse booking scenarios and including variables like cancellation dates and revenue per room and per booking could enhance the accuracy of insights.

As part of a broader strategy, implementing a trial period could provide valuable feedback on the effectiveness of the proposed changes. This expansion should consider the need to understand any decline in customer sentiments and booking patterns before implementing any changes. Such an approach would enable hotels to fine-tune the solution based on real-world responses, ensuring that the balance between recouping losses and maintaining customer satisfaction for bookings is achieved.

In conclusion, while the initial results are promising, it’s clear that ongoing refinement and practical testing are essential to shaping a cancellation policy that strikes the right balance between financial recovery and customer experience. The insights garnered from this study underscore the value of data-driven approaches in navigating the complexities posed by booking cancellations, offering a blueprint for enhanced cancellation policy management in the ever-evolving landscape of the hospitality sector.

Disclaimer: This project is an extension of a group project done as part of the MSBA curriculum at McCombs School of Business

Feel free to leave any questions or comments!