Streamline Operational Activities with Booking Data

Overview

This project entailed the analysis of reservation data for a city and resort hotel located in Portugal. The primary objective was to identify operational opportunities to enhance efficiency and increase revenue. We examined key hotel operational metrics, including Average Daily Rate (ADR), cancellations, reservations, bookings, and Average Length of Stay.

Approach

The goal was to provide a one-page report targeted at high-level executives, focusing on understanding who our guests are, what they want, and pinpointing the areas where the hotels could improve.

Data Cleaning and Transformation

Major alterations to the dataset included:

·Reconstructing the date, originally split across three columns.

·Adding a "total guests" column, which helped eliminate skewed data (records where there was a check-in or cancellation but zero guests).

·Enhancing some columns that contained binary values (1 and 0) to improve readability.

·Importing an external table from the web containing full country names and ISO codes.

·Creating another column for guest clusters.

Analysis

Initially, I was uncertain about the distinction between the terms “reservation” and “booking” in the context of a hotel. I consulted ChatGPT for clarity:

"Reservation" refers to an arrangement made by a customer to have a service, such as a room, held for their use at a future time.

"Booking" refers to the actual process of customers securing that service for their use, which typically involves payment or an agreement to pay. It is the formalization of the reservation.

With this understanding, I focused on Bookings to develop the customer profile.

Insights

Cancellation rates were significantly high at over 30% for most years for both facilities. A quick Google search revealed industry benchmarks range from 10%-40%. I established a target at the average of this range, as performance measurement requires benchmarks.

Another notable point was the low repeat guest rate. Falling significantly short of the industry benchmark of 60%, the low repeat guest rate could be attributed to various factors, potentially linked to the guest experience. As a follow-up, it would be beneficial to analyze customer reviews of these facilities to better understand guest experiences.

ADR remained quite consistent when viewed by day of the week. It's typical for hotels to practice dynamic pricing, adjusting room rates based on demand or differentiating between weekdays and weekends.

There was also a huge loss in revenue due to cancellations and “No shows”