Introduction

Hotel booking cancellations create challenges for forecasting occupancy, managing room availability, and planning staffing. A hotel may receive many reservations but still face uncertainty about how many guests will actually arrive. Understanding cancellation patterns can help hotel managers identify booking groups that require closer attention when planning operations. This project, “Hotel Booking Cancellations: Exploring Lead Time and Market Segments Across Two Hotels,” explores how cancellations vary between a City Hotel and a Resort Hotel, with particular attention to booking lead time and market segments.

This study uses the hotels dataset, which contains 119,390 booking records with scheduled arrival dates between July 2015 and August 2017. Each record represents a hotel booking. The analysis focuses on four main variables: hotel, cancellation status, lead time, and market segment. Lead time measures the number of days between the booking date and the scheduled arrival date, while market segment identifies the booking’s market category, such as Direct, Corporate, Groups, or Online Travel Agents.

The project addresses three research questions:

  1. How do cancellation rates differ between City Hotel and Resort Hotel?
  2. How does booking lead time relate to cancellation rates within each hotel?
  3. How do cancellation rates vary across market segments within each hotel?

For the midterm, we focus on data preparation and a proposed exploratory data analysis approach. We will examine data quality, prepare the relevant variables, and create lead-time groups to support comparisons. Our analysis will use descriptive statistics, summary tables, and visualizations to compare cancellation patterns. Cancellation rates will be calculated as the number of canceled bookings divided by the total number of bookings in each group. Using rates allows us to compare groups with different booking volumes, while reporting group sizes helps us interpret results from smaller segments.

The intended audience includes hotel managers and revenue management teams. The analysis may help them identify groups with higher observed cancellation rates and inform further evaluation of demand forecasts, customer communication, and booking policies. These findings will describe associations rather than establish the causes of cancellations. Because the dataset represents only two hotels during a specific historical period, the results should be interpreted within that context.

Packages Required

The following packages are used to prepare the hotel booking data and present the results in this report.

# Display all analysis code in the HTML report.
knitr::opts_chunk$set(
  echo = TRUE,
  fig.width = 8,
  fig.height = 5)

# Load all packages used in this report.
library(tidyverse)
library(knitr)
library(scales)

Data Preparation

Data Source and Description

This project uses the hotel booking dataset distributed through the TidyTuesday repository for February 11, 2020. The local copy used in this report is named hotels(2).csv.

TidyTuesday identifies the original source as António, de Almeida, and Nunes (2019), whose article, Hotel booking demand datasets, provides hotel booking data for research and education in revenue management, machine learning, and data mining.

The original publication describes two datasets with 31 variables each: 40,060 bookings for a Resort Hotel and 79,330 bookings for a City Hotel. TidyTuesday standardized the column names, added a hotel identifier, and combined the datasets. The supplied CSV therefore contains 119,390 booking records and 32 variables.

Each row represents a booking, including canceled and non-canceled reservations. Scheduled arrival dates range from July 1, 2015, through August 31, 2017. This is the scheduled arrival period; February 11, 2020 is the TidyTuesday release date.

The TidyTuesday data dictionary provides definitions for the variables. Our analysis focuses on the following four source variables.

Variable Description Connection to the research questions
hotel Identifies City Hotel or Resort Hotel Defines the hotels being compared
is_canceled Indicates cancellation: 1 = canceled, 0 = not canceled Provides the outcome for calculating cancellation rates
lead_time Days between entering a booking into the property management system and the scheduled arrival date Measures how far in advance a booking was recorded
market_segment Market category associated with the booking Supports comparisons across segments within each hotel

In market-segment labels, TA means Travel Agents and TO means Tour Operators.

The source file contains missing or NULL entries in some variables. These entries require inspection because their meaning can differ across columns. For example, a NULL intermediary identifier may reflect the absence of an associated agent or company rather than an unknown booking characteristic.

Importing the Data

We import the local CSV and preserve the full dataset as hotels_raw. Keeping the imported data unchanged allows us to compare the analytical dataset with the source after preparation.

Blank entries, "NA", and "NULL" are read as R missing values for consistent inspection. We also check for parsing errors before continuing.

# Import the supplied file.
hotels_raw <- read_csv(
  "hotels.csv",
  na = c("", "NA", "NULL"),
  show_col_types = FALSE
)

# Stop if any values could not be parsed correctly.
stopifnot(nrow(problems(hotels_raw)) == 0)

# Display the dimensions of the imported data.
tibble(
  bookings = nrow(hotels_raw),
  variables = ncol(hotels_raw)
) %>%
  kable(caption = "Dimensions of the source dataset")
Dimensions of the source dataset
bookings variables
119390 32

The imported file contains 119,390 bookings and 32 variables. The following steps assess missing values, repeated records, and the validity of the selected analytical variables.

Checking Missing Values

We first inspect missing values across all source columns. We then check the four variables required for the research questions separately. This distinguishes missing information that affects the planned analysis from missing information in unrelated variables.

# Count missing entries in every source column.
missing_summary <- hotels_raw %>%
  summarise(
    across(everything(), ~ sum(is.na(.x)))
  ) %>%
  pivot_longer(
    cols = everything(),
    names_to = "variable",
    values_to = "missing_count"
  ) %>%
  filter(missing_count > 0)

missing_summary %>%
  kable(caption = "Missing or NULL entries in the source data")
Missing or NULL entries in the source data
variable missing_count
children 4
country 488
agent 16340
company 112593 
# Check missing values in the four analytical variables.
key_missing_summary <- hotels_raw %>%
  select(hotel, is_canceled, lead_time, market_segment) %>%
  summarise(
    across(everything(), ~ sum(is.na(.x)))
  ) %>%
  pivot_longer(
    cols = everything(),
    names_to = "variable",
    values_to = "missing_count"
  )

key_missing_summary %>%
  kable(caption = "Missing values in the selected variables")
Missing values in the selected variables
variable missing_count
hotel 0
is_canceled 0
lead_time 0
market_segment 0

Missing or NULL entries occur in children, country, agent, and company. These variables are not used in the three research questions, so their missing entries are not a reason to remove booking records.

The four analytical variables contain no R missing values. However, market_segment includes two entries labeled "Undefined". We will relabel these entries as "Unknown" while retaining the associated bookings.

No missing-value imputation is performed. This avoids introducing assumptions about information that is not required for the planned comparisons.

Checking Repeated Records

We check for rows with identical values across all original columns. This check is performed before selecting the analytical variables because separate bookings may share the same values for hotel, cancellation status, lead time, and market segment.

# Count rows identical to an earlier row across all source columns.
repeated_rows <- sum(duplicated(hotels_raw))

tibble(
  repeated_full_rows = repeated_rows
) %>%
  kable(caption = "Repeated full-row values in the source data")
Repeated full-row values in the source data
repeated_full_rows
31994

There are 31,994 rows identical to an earlier row across all original columns. The supplied dataset does not contain a unique booking identifier, so identical recorded characteristics do not establish whether these are accidental copies or separate bookings.

We retain these rows in the main dataset to avoid deleting potentially valid bookings without sufficient evidence. This decision preserves the supplied booking population, but uncertainty about repeated records remains a limitation. A later sensitivity analysis could assess whether excluding repeated full rows substantially changes the findings.

Selecting and Validating Analytical Variables

We retain the four source variables needed for the research questions. The complete imported dataset remains available in hotels_raw.

Before transforming the selected data, we verify that hotel names match the expected categories, cancellation status contains only 0 and 1, and lead time consists of finite, nonnegative values.

# Select the variables needed for the proposed analysis.
hotels <- hotels_raw %>%
  select(
    hotel,
    is_canceled,
    lead_time,
    market_segment
  )

# Check for missing or invalid values.
stopifnot(
  !anyNA(hotels),
  all(hotels$hotel %in% c("City Hotel", "Resort Hotel")),
  all(hotels$is_canceled %in% c(0, 1)),
  all(is.finite(hotels$lead_time)),
  all(hotels$lead_time >= 0)
)

Cancellation status remains numeric. Since it is coded as 0 and 1, its mean equals the proportion of bookings that were canceled. This will allow cancellation rates to be calculated consistently for each group.

Preparing Categories and Lead-Time Groups

Hotel and market segment are converted to factors because they represent categories. The Undefined market segment is renamed Unknown without removing its records.

We also create five ordered lead-time groups: 0–7 days, 8–30 days, 31–90 days, 91–180 days, and 181 or more days. These groups will support readable comparisons between bookings recorded shortly before arrival and those recorded further in advance.

The boundaries are exploratory choices, not established cancellation-risk thresholds. The original numeric lead time is retained so that alternative groupings or continuous analyses remain possible.

# Prepare categorical variables and create lead-time groups.
hotels <- hotels %>%
  mutate(
    hotel = factor(
      hotel,
      levels = c("City Hotel", "Resort Hotel")
    ),
    market_segment = factor(
      if_else(
        market_segment == "Undefined",
        "Unknown",
        market_segment
      )
    ),
    lead_time_group = cut(
      lead_time,
      breaks = c(-Inf, 7, 30, 90, 180, Inf),
      labels = c(
        "0–7 days",
        "8–30 days",
        "31–90 days",
        "91–180 days",
        "181+ days"
      ),
      ordered_result = TRUE
    )
  )

# Verify that preparation preserved all booking records.
stopifnot(
  nrow(hotels) == nrow(hotels_raw),
  !anyNA(hotels)
)

Long lead times are retained because a large value alone does not demonstrate a recording error. Removing them without justification could distort the planned analysis of advance booking.

The preparation decisions are summarized below.

Decision Reason Research question supported
Select four relevant source variables Keep the analytical dataset focused All three questions
Validate cancellation status Ensure rates are based on valid 0/1 values All three questions
Convert hotel and segment to factors Represent categorical comparisons explicitly Questions 1 and 3
Relabel Undefined as Unknown Identify unspecified market segments without deleting bookings Question 3
Create ordered lead-time groups Support comparisons across advance-booking intervals Question 2
Retain repeated full-row values Avoid deleting potentially distinct bookings without an identifier All three questions

Preview of the Prepared Dataset

The prepared dataset contains 119,390 booking records and 5 variables. It includes four selected source variables and the new lead-time grouping variable.

Only the first six rows are shown below to illustrate the structure without printing the full dataset.

hotels %>%
  slice_head(n = 6) %>%
  kable(caption = "Preview of the prepared analytical dataset")
Preview of the prepared analytical dataset
hotel is_canceled lead_time market_segment lead_time_group
Resort Hotel 0 342 Direct 181+ days
Resort Hotel 0 737 Direct 181+ days
Resort Hotel 0 7 Direct 0–7 days
Resort Hotel 0 13 Corporate 8–30 days
Resort Hotel 0 14 Online TA 8–30 days
Resort Hotel 0 14 Online TA 8–30 days

Summary of the Analytical Variables

The table below summarizes each analytical variable. These summaries document the prepared data; comparisons addressing the three research questions are planned for the next stage.

variable_summary <- tibble(
  variable = c(
    "hotel",
    "is_canceled",
    "lead_time",
    "market_segment",
    "lead_time_group"
  ),
  type = c(
    "Categorical",
    "Binary numeric",
    "Numeric",
    "Categorical",
    "Ordered categorical"
  ),
  summary = c(
    paste0(
      "City Hotel: ",
      scales::comma(sum(hotels$hotel == "City Hotel")),
      "; Resort Hotel: ",
      scales::comma(sum(hotels$hotel == "Resort Hotel"))
    ),
    paste0(
      "Canceled: ",
      scales::comma(sum(hotels$is_canceled == 1)),
      "; not canceled: ",
      scales::comma(sum(hotels$is_canceled == 0))
    ),
    paste0(
      "Mean: ", round(mean(hotels$lead_time), 1),
      "; median: ", median(hotels$lead_time),
      "; range: ", min(hotels$lead_time),
      "–", max(hotels$lead_time), " days"
    ),
    paste0(
      n_distinct(hotels$market_segment),
      " categories including Unknown; Unknown bookings: ",
      sum(hotels$market_segment == "Unknown")
    ),
    paste(levels(hotels$lead_time_group), collapse = "; ")
  )
)

variable_summary %>%
  kable(caption = "Summary of the analytical variables")
Summary of the analytical variables
variable type summary
hotel Categorical City Hotel: 79,330; Resort Hotel: 40,060
is_canceled Binary numeric Canceled: 44,224; not canceled: 75,166
lead_time Numeric Mean: 104; median: 69; range: 0–737 days
market_segment Categorical 8 categories including Unknown; Unknown bookings: 2
lead_time_group Ordered categorical 0–7 days; 8–30 days; 31–90 days; 91–180 days; 181+ days

City Hotel has more booking records than Resort Hotel. Therefore, the planned comparisons will use cancellation rates rather than cancellation counts alone.

Lead time ranges from 0 to 737 days, with a mean of 104 days and a median of 69 days. The grouped variable will help present this wide range in a readable form.

Market segment includes seven identified categories and an Unknown category. All booking records are retained, and no missing values have been imputed. The resulting dataset is prepared for the proposed exploratory analysis. ## Visual Overview of the Prepared Data

The following plots describe the composition of the prepared dataset. They provide context for the proposed analysis without yet comparing cancellation rates.

Booking Counts by Hotel

# Count booking records for each hotel.
hotel_counts <- hotels %>%
  count(hotel, name = "bookings")

ggplot(hotel_counts, aes(x = hotel, y = bookings, fill = hotel)) +
  geom_col(width = 0.6, show.legend = FALSE) +
  geom_text(
    aes(label = scales::comma(bookings)),
    vjust = -0.5
  ) +
  scale_fill_manual(
    values = c(
      "City Hotel" = "#0072B2",
      "Resort Hotel" = "#D55E00"
    )
  ) +
  scale_y_continuous(
    labels = scales::label_comma(),
    expand = expansion(mult = c(0, 0.12))
  ) +
  labs(
    title = "Number of Bookings by Hotel",
    x = NULL,
    y = "Number of bookings"
  ) +
  theme_minimal(base_size = 12)

City Hotel contributes more booking records than Resort Hotel. This difference in booking volume supports using cancellation rates, rather than cancellation counts alone, in the proposed comparisons.

Distribution of Booking Lead Time

# Display the full range of lead times in the prepared dataset.
ggplot(hotels, aes(x = lead_time)) +
  geom_histogram(
    binwidth = 30,
    boundary = 0,
    fill = "#0072B2",
    color = "white"
  ) +
  scale_y_continuous(labels = scales::label_comma()) +
  labs(
    title = "Distribution of Booking Lead Time",
    subtitle = "Histogram intervals represent 30 days",
    x = "Lead time (days)",
    y = "Number of bookings"
  ) +
  theme_minimal(base_size = 12)

Lead time covers a wide range, with a mean of 104 days and a median of 69 days. This histogram helps assess the distribution before comparing cancellation rates across lead-time groups.

The 30-day histogram intervals are used only to display the distribution. The five lead-time categories created earlier will be used for the planned grouped comparisons. # Proposed Exploratory Data Analysis

Planned Analytical Approach

We plan to explore cancellation rates between the two hotels and across lead-time groups and market segments within each hotel. These grouped comparisons may reveal patterns that are not apparent from the overall dataset.

For each group, we will calculate total bookings, canceled bookings, and the cancellation rate:

\[ \text{Cancellation rate} = \frac{\text{Canceled bookings}}{\text{Total bookings}} \]

Cancellation rates will be displayed as percentages. The denominator will include all canceled and non-canceled bookings in the corresponding group.

We will report group sizes alongside rates because percentages based on a few bookings require more cautious interpretation than percentages based on many bookings. No external data joins are currently planned because the variables required for the three questions are available in the prepared dataset.

Question 1: How Do Cancellation Rates Differ Between City Hotel and Resort Hotel?

We will group bookings by hotel and calculate total bookings, canceled bookings, and cancellation rates. A summary table will display these measures, and a bar chart will compare the two rates using the same percentage scale.

Using rates will account for the different booking volumes. We will describe the difference between hotels in percentage points. This comparison will provide a starting point for examining lead time and market segments, rather than establish why any difference exists.

Question 2: How Does Booking Lead Time Relate to Cancellation Rates Within Each Hotel?

We will group bookings by hotel and the five lead-time categories created during data preparation. For each combination, we will calculate the number of bookings and the cancellation rate.

A summary table will report the counts and rates. A line chart with a separate line for each hotel will show how cancellation rates vary across the ordered categories. We will examine whether the patterns are similar between the hotels and whether rates increase, decrease, or follow a more complex pattern.

The categories have unequal widths, so the connecting lines will indicate their order rather than daily changes in cancellation risk. We may compare alternative group boundaries to assess whether the observed pattern depends on the selected intervals.

Question 3: How Do Cancellation Rates Vary Across Market Segments Within Each Hotel?

We will calculate booking counts and cancellation rates for each combination of hotel and market segment. A summary table and horizontal bar charts with separate panels for the hotels will support comparisons within and across hotels.

The panels will use the same percentage scale. We will consider the number of bookings in each segment when interpreting differences.

The Unknown category will remain in the table but will be treated separately from identified market segments. A segment with no bookings in a hotel will be treated as unavailable rather than assigned a cancellation rate of zero.

If useful, we will also examine lead time within selected market segments to explore whether differences in advance-booking behavior provide additional context.

Planned Tables and Visualizations

Research question Planned table Planned visualization
Cancellation rates between hotels Total bookings, canceled bookings, and rates by hotel Bar chart comparing the two hotels
Lead time and cancellations within hotels Booking counts and rates by hotel and lead-time group Line chart across ordered lead-time categories
Market segments and cancellations within hotels Booking counts and rates by hotel and market segment Horizontal bar charts with separate hotel panels

Skills to Develop

To complete the proposed analysis, we need to strengthen our understanding of how to choose lead-time group boundaries and evaluate whether different boundaries change the interpretation.

We also need to learn more about interpreting cancellation rates for small groups and constructing confidence intervals for proportions. These skills would help us communicate uncertainty rather than rely only on differences between percentages.

If we include a statistical model in the final project, we will need to develop our ability to fit and interpret logistic regression.

Plans for Machine Learning or Statistical Modeling

No machine learning model is included in this midterm submission. Descriptive statistics, grouped summaries, and visualizations are the main methods proposed for answering the research questions.

For the final project, we may consider logistic regression because cancellation status is a binary outcome. A possible model would examine cancellation status in relation to hotel, lead time, and market segment simultaneously.

This extension has not been implemented. The decision to include it will depend on the final project requirements and whether it adds useful information beyond the descriptive comparisons. Any model would be interpreted as describing associations rather than proving causation.

Limitations and Next Steps

The dataset covers two hotels during a historical period, so findings may not generalize to other hotels or current booking behavior. The analysis is observational and cannot establish the causes of cancellations.

Repeated full-row values are retained because the dataset lacks a unique booking identifier. Small market segments and the choice of lead-time boundaries will also require careful interpretation.

At the midterm stage, the data have been imported, checked, prepared, and summarized. The next step is to produce the proposed tables and visualizations, evaluate the resulting patterns, and determine whether further analysis would be useful.

References

TidyTuesday. (2020). Hotels: February 11, 2020. Dataset documentation and preparation code.