4.1 Problem

A hotel chain wants to understand which customer segments and booking characteristics are associated with higher cancellation rates. The objective is to reduce revenue loss by identifying cancellation patterns and improving reservation management.

4.2 Data Analysis

In this case, identifying key metrics, such as the cancellation rate, previous cancellations, booking changes, the average lead time, and the average repeat stays are key to analyzing booking characteristics associated with cancellations.

This dataset was provided by the Kaggle website. It’s composed of 32 columns and 108,734 rows after data was cleaned and shaped through MySQL.

Queries used during this project are shown below:

-- Using hotel_booking_project dataset for all the queries in this project:
USE hotel_booking_project;

-- Creating the main table 'booking_data':
CREATE TABLE booking_data(
  hotel VARCHAR(50),
  is_canceled INT,
  lead_time INT,
  arrival_date_year INT,
  arrival_date_month VARCHAR(50),
  arrival_date_week_number INT,
  arrival_date_day_of_month INT,
  stays_in_weekend_nights INT,
  stays_in_week_nights INT,
  adults INT,
  children INT,
  babies INT,
  meal VARCHAR(50),
  country VARCHAR(50),
  market_segment VARCHAR(50),
  distribution_channel VARCHAR(50),
  is_repeated_guest INT,
  previous_cancellations INT,
  previous_bookings_not_canceled INT,
  reserved_room_type VARCHAR(50),
  assigned_room_type VARCHAR(50),
  booking_changes INT,
  deposit_type VARCHAR(50),
  agent VARCHAR(50),
  company VARCHAR(50),
  days_in_waiting_list INT,
  customer_type VARCHAR(50),
  adr DECIMAL(10,2),
  required_car_parking_spaces INT,
  total_of_special_requests INT,
  reservation_status VARCHAR(50),
  reservation_status_date DATE
);

-- Exploratory Data Analysis:
SET SQL_SAFE_UPDATES = 0;

DELETE
FROM booking_data
WHERE stays_in_weekend_nights = 0
  AND stays_in_week_nights = 0;

DELETE
FROM booking_data
WHERE agent IS NULL
  AND company IS NULL;

SELECT
  SUM(hotel IS NULL) AS hotel,
  SUM(is_canceled IS NULL) AS is_canceled,
  SUM(lead_time IS NULL) AS lead_time,
  SUM(arrival_date_year IS NULL) AS arrival_date_year,
  SUM(arrival_date_month IS NULL) AS arrival_date_month,
  SUM(arrival_date_week_number IS NULL) AS arrival_date_week_number,
  SUM(arrival_date_day_of_month IS NULL) AS arrival_date_day_of_month,
  SUM(stays_in_weekend_nights IS NULL) AS stays_in_weekend_nights,
  SUM(stays_in_week_nights IS NULL) AS stays_in_week_nights,
  SUM(adults IS NULL) AS adults,
  SUM(children IS NULL) AS children,
  SUM(babies IS NULL) AS babies,
  SUM(meal IS NULL) AS meal,
  SUM(country IS NULL) AS country,
  SUM(market_segment IS NULL) AS market_segment,
  SUM(distribution_channel IS NULL) AS distribution_channel,
  SUM(is_repeated_guest IS NULL) AS is_repeated_guest,
  SUM(previous_cancellations IS NULL) AS previous_cancellations,
  SUM(previous_bookings_not_canceled IS NULL) AS previous_bookings_not_canceled,
  SUM(reserved_room_type IS NULL) AS reserved_room_type,
  SUM(assigned_room_type IS NULL) AS assigned_room_type,
  SUM(booking_changes IS NULL) AS booking_changes,
  SUM(deposit_type IS NULL) AS deposit_type,
  SUM(agent IS NULL) AS agent,
  SUM(company IS NULL) AS company,
  SUM(days_in_waiting_list IS NULL) AS days_in_waiting_list,
  SUM(customer_type IS NULL) AS customer_type,
  SUM(adr IS NULL) AS adr,
  SUM(required_car_parking_spaces IS NULL) AS required_car_parking_spaces,
  SUM(total_of_special_requests IS NULL) AS total_of_special_requests,
  SUM(reservation_status IS NULL) AS reservation_status,
  SUM(reservation_status_date IS NULL) AS reservation_status_date
FROM booking_data;

DELETE
FROM booking_data
WHERE country IS NULL;

-- Adding columns like total nights and revenue:
ALTER TABLE booking_data
ADD COLUMN total_nights INT,
ADD COLUMN revenue DECIMAL(10,2);

UPDATE booking_data
SET total_nights = stays_in_weekend_nights + stays_in_week_nights,
    revenue = adr * (stays_in_weekend_nights + stays_in_week_nights);

After the data cleaning process, the updated data is presented through visualizations.

image.png

4.3 Summary (Observations → Findings → Insights → Recommendations)

Observation Findings Insights Recommendations
The cancellation rate represents 38%. 62% of bookings remain uncancelled. Cancellation policies are flexible, resulting in almost 40% of the hotel’s capacity remaining unused after cancellations. Changing the reservation management to a less flexible policy should reduce the cancellation rate.
The average number of previous cancellations is 8.28%. 8% of returning customers have cancelled bookings previously. As they know the flexible policies of this hotel, customers repeatedly book stays. Changing to a less flexible policy could prevent losses generated by these customers.
The average number of booking changes is 21%. One in five customers make changes after booking their accommodation. Twenty percent of customers make booking changes, which is a risky customer behavior that leads to increasing the potential revenue loss. The company could reduce this risk by introducing modification and cancellation fees.
The average lead time is 108.56 days. On average, customers have 108 days to make changes in their bookings. Customers have almost 4 months to make changes to their reservations. This is a long period for the company to generate potential revenue loss. It would be interesting to reduce the lead time to make changes.
The average number of repeat stays is 2.65%. About 2% of customers are repeat guests. Only 2% of customers return to the hotel, while 98% of them are new. If the flexible policy aims to build customer loyalty, the strategy is not working as forecasted. Potential losses from adopting a less flexible strategy may be lower than the current revenue losses caused by cancellations.
The potential revenue loss is $12.7 million. After customers book in this hotel, cancellations represent $12.7 million. It includes no-deposit and refundable bookings. $12.7 million represents a substantial portion of the total potential revenue, which is $39.8 million. It would be recommended to switch to a less flexible strategy. Removing no-deposit reservations.
The biggest portion of the potential revenue loss comes from travel agencies. Customers booking through agencies usually cancel their bookings afterwards. Travel agencies offer their services through no-deposit and refundable bookings. This represents almost $11 million. Communicate to travel agencies that no-deposit reservations will no longer be available and set up a fee if the reservations are cancelled.
No-deposit bookings constitute $12.2 million of the potential revenue loss. No-deposit bookings represent almost all the potential revenue loss. No-deposit bookings lead to a significant revenue loss of $12.2 million.) Removing no-deposit reservations to avoid financial losses.
Portugal is the main guest country generating revenue loss for the company. It represents $4.5 million. The potential revenue loss is also generated by different guest countries like Spain, the UK, France, etc. The flexible policy is leading to losses, regardless of the guest country. Setting up this less flexible strategy regardless of the guest country.
Resort hotels and city hotels generate potential revenue loss. Both hotels experience inefficient reservation management. The flexible policy available in both hotels, as data shows, is contributing to losses. Setting up this less flexible strategy regardless of the hotel.
Potential revenue loss is produced all year. The biggest loss period corresponds to the summer season. The potential revenue loss during the summer season represents around $6 million. The company is experiencing potential revenue losses monthly. Setting up this less flexible strategy throughout the year.

4.4 Conclusion

In conclusion, to answer the question “A hotel chain wants to understand which customer segments and booking characteristics are associated with higher cancellation rates. The objective is to reduce revenue loss by identifying cancellation patterns and improving reservation management”. Based on my data analysis, the company could remove no-deposit reservations to avoid over $12 million per year. Most of this loss is generated by the travel agency distribution channel. Potential revenue losses are generated all year, in different hotels and from different guest countries. The core problem is the flexibility of its reservation policies. Switching to a less flexible one could lead to losing a portion of potential customers, but ensuring cash inflows. The company could set up refundable reservations with cancellation fees.