Project
2023-03-17
Discussion of data source and problem statement definition
Hotel reservations are called off either due to cancellations or no-shows as a result of some common reasons such as a change of plans, scheduling conflicts and many more. Typically, hotels provide an option to do so at no or low cost which would be beneficial for guests but not ideal for hotels as it affects the planning of number of available rooms - which ends up with potentially lower rates of room utilisation.
The problem statement: Predict if a customer is going to honor the reservation or cancel it.
Reading and pre-processing data
hotel_raw = read.csv("Hotel Reservations.csv")## Booking_ID no_of_adults
## 0 0
## no_of_children no_of_weekend_nights
## 0 0
## no_of_week_nights type_of_meal_plan
## 0 0
## required_car_parking_space room_type_reserved
## 0 0
## lead_time arrival_year
## 0 0
## arrival_month arrival_date
## 0 0
## market_segment_type repeated_guest
## 0 0
## no_of_previous_cancellations no_of_previous_bookings_not_canceled
## 0 0
## avg_price_per_room no_of_special_requests
## 0 0
## booking_status
## 0No NA values are found after checking through ‘is.na’. Next, we found no duplicated in our data set with ‘get_dupes’.
## [1] Booking_ID no_of_adults
## [3] no_of_children no_of_weekend_nights
## [5] no_of_week_nights type_of_meal_plan
## [7] required_car_parking_space room_type_reserved
## [9] lead_time arrival_year
## [11] arrival_month arrival_date
## [13] market_segment_type repeated_guest
## [15] no_of_previous_cancellations no_of_previous_bookings_not_canceled
## [17] avg_price_per_room no_of_special_requests
## [19] booking_status dupe_count
## <0 rows> (or 0-length row.names)## 'data.frame': 36275 obs. of 19 variables:
## $ Booking_ID : chr "INN00001" "INN00002" "INN00003" "INN00004" ...
## $ no_of_adults : int 2 2 1 2 2 2 2 2 3 2 ...
## $ no_of_children : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_weekend_nights : int 1 2 2 0 1 0 1 1 0 0 ...
## $ no_of_week_nights : int 2 3 1 2 1 2 3 3 4 5 ...
## $ type_of_meal_plan : chr "Meal Plan 1" "Not Selected" "Meal Plan 1" "Meal Plan 1" ...
## $ required_car_parking_space : int 0 0 0 0 0 0 0 0 0 0 ...
## $ room_type_reserved : chr "Room_Type 1" "Room_Type 1" "Room_Type 1" "Room_Type 1" ...
## $ lead_time : int 224 5 1 211 48 346 34 83 121 44 ...
## $ arrival_year : int 2017 2018 2018 2018 2018 2018 2017 2018 2018 2018 ...
## $ arrival_month : int 10 11 2 5 4 9 10 12 7 10 ...
## $ arrival_date : int 2 6 28 20 11 13 15 26 6 18 ...
## $ market_segment_type : chr "Offline" "Online" "Online" "Online" ...
## $ repeated_guest : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_cancellations : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_bookings_not_canceled: int 0 0 0 0 0 0 0 0 0 0 ...
## $ avg_price_per_room : num 65 106.7 60 100 94.5 ...
## $ no_of_special_requests : int 0 1 0 0 0 1 1 1 1 3 ...
## $ booking_status : chr "Not_Canceled" "Not_Canceled" "Canceled" "Canceled" ...We then converted the relevant variables as factors.
## 'data.frame': 36275 obs. of 19 variables:
## $ Booking_ID : chr "INN00001" "INN00002" "INN00003" "INN00004" ...
## $ no_of_adults : int 2 2 1 2 2 2 2 2 3 2 ...
## $ no_of_children : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_weekend_nights : int 1 2 2 0 1 0 1 1 0 0 ...
## $ no_of_week_nights : int 2 3 1 2 1 2 3 3 4 5 ...
## $ type_of_meal_plan : Factor w/ 4 levels "Meal Plan 1",..: 1 4 1 1 4 2 1 1 1 1 ...
## $ required_car_parking_space : Factor w/ 2 levels "0","1": 1 1 1 1 1 1 1 1 1 1 ...
## $ room_type_reserved : Factor w/ 7 levels "Room_Type 1",..: 1 1 1 1 1 1 1 4 1 4 ...
## $ lead_time : int 224 5 1 211 48 346 34 83 121 44 ...
## $ arrival_year : int 2017 2018 2018 2018 2018 2018 2017 2018 2018 2018 ...
## $ arrival_month : int 10 11 2 5 4 9 10 12 7 10 ...
## $ arrival_date : int 2 6 28 20 11 13 15 26 6 18 ...
## $ market_segment_type : Factor w/ 5 levels "Aviation","Complementary",..: 4 5 5 5 5 5 5 5 4 5 ...
## $ repeated_guest : Factor w/ 2 levels "0","1": 1 1 1 1 1 1 1 1 1 1 ...
## $ no_of_previous_cancellations : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_bookings_not_canceled: int 0 0 0 0 0 0 0 0 0 0 ...
## $ avg_price_per_room : num 65 106.7 60 100 94.5 ...
## $ no_of_special_requests : int 0 1 0 0 0 1 1 1 1 3 ...
## $ booking_status : Factor w/ 2 levels "Canceled","Not_Canceled": 2 2 1 1 1 1 2 2 2 2 ...We also removed booking ID as it is not relevant for our analysis
## 'data.frame': 36275 obs. of 18 variables:
## $ no_of_adults : int 2 2 1 2 2 2 2 2 3 2 ...
## $ no_of_children : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_weekend_nights : int 1 2 2 0 1 0 1 1 0 0 ...
## $ no_of_week_nights : int 2 3 1 2 1 2 3 3 4 5 ...
## $ type_of_meal_plan : Factor w/ 4 levels "Meal Plan 1",..: 1 4 1 1 4 2 1 1 1 1 ...
## $ required_car_parking_space : Factor w/ 2 levels "0","1": 1 1 1 1 1 1 1 1 1 1 ...
## $ room_type_reserved : Factor w/ 7 levels "Room_Type 1",..: 1 1 1 1 1 1 1 4 1 4 ...
## $ lead_time : int 224 5 1 211 48 346 34 83 121 44 ...
## $ arrival_year : int 2017 2018 2018 2018 2018 2018 2017 2018 2018 2018 ...
## $ arrival_month : int 10 11 2 5 4 9 10 12 7 10 ...
## $ arrival_date : int 2 6 28 20 11 13 15 26 6 18 ...
## $ market_segment_type : Factor w/ 5 levels "Aviation","Complementary",..: 4 5 5 5 5 5 5 5 4 5 ...
## $ repeated_guest : Factor w/ 2 levels "0","1": 1 1 1 1 1 1 1 1 1 1 ...
## $ no_of_previous_cancellations : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_bookings_not_canceled: int 0 0 0 0 0 0 0 0 0 0 ...
## $ avg_price_per_room : num 65 106.7 60 100 94.5 ...
## $ no_of_special_requests : int 0 1 0 0 0 1 1 1 1 3 ...
## $ booking_status : Factor w/ 2 levels "Canceled","Not_Canceled": 2 2 1 1 1 1 2 2 2 2 ...Visualisation
To check for anomalies, we plotted variables (lead_time, arrival_date, avg_price_per_room, no_of_previous_bookings_not_canceled))
- type_of_meal_plan and average price per room
- Lead_time
We observed that lead_time also has outliers, we’ll normalise it by logging the variable
Checking for relationships between variables
To create a correlation table, we recoded certain variables to categorical values namely:
- Meal plan types: “Not Selected”=0,“Meal Plan 1”=1,“Meal Plan 2”=2,
“Meal Plan 3”=3
- Room type reserved: “Room Type 1”=1 to “Room Type 7”=7
- Market segment type: “Online”=1,
“Offline”=0,“Corporate”=2,“Aviation”=3,“Complementary”=4
- Booking status: “Canceled”=1,“Not_Canceled”=0
## no_of_adults no_of_children no_of_weekend_nights
## no_of_adults 1.00 -0.02 0.10
## no_of_children -0.02 1.00 0.03
## no_of_weekend_nights 0.10 0.03 1.00
## no_of_week_nights 0.11 0.02 0.18
## type_of_meal_plan 0.00 0.04 -0.02
## required_car_parking_space 0.01 0.03 -0.03
## no_of_week_nights type_of_meal_plan
## no_of_adults 0.11 0.00
## no_of_children 0.02 0.04
## no_of_weekend_nights 0.18 -0.02
## no_of_week_nights 1.00 0.03
## type_of_meal_plan 0.03 1.00
## required_car_parking_space -0.05 -0.02
## required_car_parking_space room_type_reserved
## no_of_adults 0.01 0.27
## no_of_children 0.03 0.36
## no_of_weekend_nights -0.03 0.06
## no_of_week_nights -0.05 0.09
## type_of_meal_plan -0.02 0.09
## required_car_parking_space 1.00 0.04
## lead_time arrival_year arrival_month arrival_date
## no_of_adults 0.10 0.08 0.02 0.03
## no_of_children -0.05 0.05 0.00 0.03
## no_of_weekend_nights 0.05 0.06 -0.01 0.03
## no_of_week_nights 0.15 0.03 0.04 -0.01
## type_of_meal_plan 0.23 -0.19 0.02 0.02
## required_car_parking_space -0.07 0.02 -0.02 0.00
## market_segment_type repeated_guest
## no_of_adults -0.10 -0.19
## no_of_children 0.07 -0.04
## no_of_weekend_nights -0.02 -0.07
## no_of_week_nights -0.07 -0.10
## type_of_meal_plan -0.22 0.01
## required_car_parking_space 0.12 0.11
## no_of_previous_cancellations
## no_of_adults -0.05
## no_of_children -0.02
## no_of_weekend_nights -0.02
## no_of_week_nights -0.03
## type_of_meal_plan -0.01
## required_car_parking_space 0.03
## no_of_previous_bookings_not_canceled
## no_of_adults -0.12
## no_of_children -0.02
## no_of_weekend_nights -0.03
## no_of_week_nights -0.05
## type_of_meal_plan 0.01
## required_car_parking_space 0.06
## avg_price_per_room no_of_special_requests
## no_of_adults 0.30 0.19
## no_of_children 0.34 0.12
## no_of_weekend_nights 0.00 0.06
## no_of_week_nights 0.02 0.05
## type_of_meal_plan 0.13 -0.09
## required_car_parking_space 0.06 0.09
## booking_status
## no_of_adults 0.09
## no_of_children 0.03
## no_of_weekend_nights 0.06
## no_of_week_nights 0.09
## type_of_meal_plan 0.05
## required_car_parking_space -0.09## Var1 Var2 value
## 1 no_of_adults no_of_adults 1.00
## 2 no_of_children no_of_adults -0.02
## 3 no_of_weekend_nights no_of_adults 0.10
## 4 no_of_week_nights no_of_adults 0.11
## 5 type_of_meal_plan no_of_adults 0.00
## 6 required_car_parking_space no_of_adults 0.01
Upon viewing the correlation heatmap, we have identified several relationships that we want to delve deeper into, namely:
- Lead time vs Booking status
- No. of special requests vs booking status
- No of weekend nights vs booking status
- Avg price per room vs booking status
- No of previous cancellations and non-cancellations vs booking status
- Market segment type vs booking status
- Arrival Year
- Required Car parking space
We’ll look into the first 3 pairwise relationships below:
Lead time vs booking_status: As seen above, the lead time for cancelled rooms is higher than that for rooms that were not cancelled. This means that customers who book their rooms early are more likely to cancel their rooms.
No. of special requests vs booking status: From this pair, it can be seen that if the number of special requests is more than 2, the booking will not be cancelled. It is also observed that in general, people usually make 1 or no special requests, whether or not the booking is cancelled.
No of weekend nights vs booking status
It can be seen that it is more likely for the booking to be cancelled when there is a higher number of weekend nights. This may be due to change in plans seeing that there is a long vacation.
- Avg price per room vs booking status
It can be seen that the average price per room of cancelled rooms is
slightly higher than that of not cancelled.
- No of previous cancellations and non-cancellations vs booking
status
There are almost no previous cancellations nor non-cancellations for customers who cancelled their booking this time. Meanwhile, for those who did not cancel their booking, there are a minor few who had multiple previous cancellations and non-cancellations. This means that a large proportion of the customer base are first time guests. This is supported by the graph on repeated guests, though it can be seen that there are more repeated guests from those who did not cancel.
- Market segment type vs booking status
A large proportion of customers are from the online segment. Also, it can be seen that no one from the complimentary segment cancelled their booking.
- Arrival Year
In general, the years do not affect the customer’s decision to cancel their booking or not.
- Required car parking space
A large proportion of customers who did not cancel their bookings did not require a car parking space,but it’s interesting to note that a significant proportion of customers who canceled did not require a car parking space as well.
Predictive Analytics
Models used are CART, logistic regression, and random forest.
Rationale for not using other models:
- Cluster then Predict (K means): There were no meaningful clusters formed as this dataset contains more categorical variables than numerical variables which is tough to incorporate and perform well. Thus, we decided to not proceed with cluster then predict model (moreover, there is low accuracy results when we ran the model for it)
We first split the data into Train and Test respectively and normalised the data using preProcess function before proceeding with our analysis.
## no_of_adults no_of_children no_of_weekend_nights no_of_week_nights
## Min. :-3.5472 Min. :-0.2633 Min. :-0.9321 Min. :-1.5649
## 1st Qu.: 0.3003 1st Qu.:-0.2633 1st Qu.:-0.9321 1st Qu.:-0.8571
## Median : 0.3003 Median :-0.2633 Median : 0.2164 Median :-0.1493
## Mean : 0.0000 Mean : 0.0000 Mean : 0.0000 Mean : 0.0000
## 3rd Qu.: 0.3003 3rd Qu.:-0.2633 3rd Qu.: 1.3650 3rd Qu.: 0.5585
## Max. : 4.1478 Max. :24.4107 Max. : 7.1076 Max. :10.4679
##
## type_of_meal_plan required_car_parking_space room_type_reserved
## Meal Plan 1 :19518 0:24618 Room_Type 1:19685
## Meal Plan 2 : 2319 1: 775 Room_Type 2: 480
## Meal Plan 3 : 4 Room_Type 3: 6
## Not Selected: 3552 Room_Type 4: 4219
## Room_Type 5: 190
## Room_Type 6: 699
## Room_Type 7: 114
## lead_time arrival_year arrival_month arrival_date
## Min. :-0.9946 Min. :-2.1434 Min. :-2.0942 Min. :-1.66387
## 1st Qu.:-0.7973 1st Qu.: 0.4665 1st Qu.:-0.7890 1st Qu.:-0.86375
## Median :-0.3214 Median : 0.4665 Median : 0.1899 Median : 0.05068
## Mean : 0.0000 Mean : 0.0000 Mean : 0.0000 Mean : 0.00000
## 3rd Qu.: 0.4910 3rd Qu.: 0.4665 3rd Qu.: 0.8425 3rd Qu.: 0.85080
## Max. : 4.1468 Max. : 0.4665 Max. : 1.4950 Max. : 1.76522
##
## market_segment_type repeated_guest no_of_previous_cancellations
## Aviation : 86 0:24756 Min. :-0.06115
## Complementary: 293 1: 637 1st Qu.:-0.06115
## Corporate : 1409 Median :-0.06115
## Offline : 7412 Mean : 0.00000
## Online :16193 3rd Qu.:-0.06115
## Max. :36.05056
##
## no_of_previous_bookings_not_canceled avg_price_per_room no_of_special_requests
## Min. :-0.08837 Min. :-2.9341 Min. :-0.7873
## 1st Qu.:-0.08837 1st Qu.:-0.6481 1st Qu.:-0.7873
## Median :-0.08837 Median :-0.1102 Median :-0.7873
## Mean : 0.00000 Mean : 0.0000 Mean : 0.0000
## 3rd Qu.:-0.08837 3rd Qu.: 0.4801 3rd Qu.: 0.4791
## Max. :35.05977 Max. : 7.6963 Max. : 5.5448
##
## booking_status
## Canceled : 8320
## Not_Canceled:17073
##
##
##
##
## Logistic Regression Model
To begin building the Logistic Regression model, we first run the most basic model with all the variables to identify significant variables.
##
## Call:
## glm(formula = booking_status ~ ., family = "binomial", data = Train)
##
## Deviance Residuals:
## Min 1Q Median 3Q Max
## -3.0671 -0.6067 0.3315 0.6573 2.8576
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) 9.674e+02 1.221e+02 7.922 2.34e-15 ***
## no_of_adults -8.753e-02 3.759e-02 -2.329 0.01988 *
## no_of_children -1.635e-01 5.907e-02 -2.767 0.00565 **
## no_of_weekend_nights -1.408e-01 1.981e-02 -7.105 1.20e-12 ***
## no_of_week_nights -3.507e-02 1.224e-02 -2.865 0.00417 **
## type_of_meal_planMeal Plan 2 -9.719e-02 6.655e-02 -1.460 0.14419
## type_of_meal_planMeal Plan 3 -1.195e+01 1.608e+02 -0.074 0.94076
## type_of_meal_planNot Selected -2.193e-01 5.340e-02 -4.106 4.02e-05 ***
## required_car_parking_space1 1.572e+00 1.413e-01 11.128 < 2e-16 ***
## room_type_reservedRoom_Type 2 4.214e-01 1.343e-01 3.138 0.00170 **
## room_type_reservedRoom_Type 3 2.613e-01 1.366e+00 0.191 0.84828
## room_type_reservedRoom_Type 4 2.738e-01 5.336e-02 5.132 2.86e-07 ***
## room_type_reservedRoom_Type 5 5.604e-01 1.992e-01 2.814 0.00490 **
## room_type_reservedRoom_Type 6 1.032e+00 1.489e-01 6.930 4.21e-12 ***
## room_type_reservedRoom_Type 7 1.323e+00 3.144e-01 4.207 2.59e-05 ***
## lead_time -1.569e-02 2.665e-04 -58.880 < 2e-16 ***
## arrival_year -4.781e-01 6.051e-02 -7.901 2.76e-15 ***
## arrival_month 4.578e-02 6.484e-03 7.061 1.66e-12 ***
## arrival_date -9.968e-04 1.940e-03 -0.514 0.60728
## market_segment_typeComplementary 1.961e+01 2.234e+02 0.088 0.93005
## market_segment_typeCorporate 1.127e+00 2.702e-01 4.170 3.05e-05 ***
## market_segment_typeOffline 1.939e+00 2.579e-01 7.519 5.52e-14 ***
## market_segment_typeOnline 2.110e-01 2.549e-01 0.828 0.40765
## repeated_guest1 2.754e+00 6.576e-01 4.188 2.82e-05 ***
## no_of_previous_cancellations -2.333e-01 8.914e-02 -2.617 0.00886 **
## no_of_previous_bookings_not_canceled 4.461e-02 8.980e-02 0.497 0.61938
## avg_price_per_room -1.849e-02 7.356e-04 -25.135 < 2e-16 ***
## no_of_special_requests 1.490e+00 3.020e-02 49.330 < 2e-16 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## (Dispersion parameter for binomial family taken to be 1)
##
## Null deviance: 32122 on 25392 degrees of freedom
## Residual deviance: 21494 on 25365 degrees of freedom
## AIC: 21550
##
## Number of Fisher Scoring iterations: 16Next, we prepare for cross validation.
fitControl = trainControl(method = "cv", number = 10, classProbs = TRUE, summaryFunction = twoClassSummary)First, we tried using only variables with *** significance, giving an ROC of 0.8631703.
## Generalized Linear Model
##
## 25393 samples
## 11 predictor
## 2 classes: 'Canceled', 'Not_Canceled'
##
## No pre-processing
## Resampling: Cross-Validated (10 fold)
## Summary of sample sizes: 22853, 22854, 22854, 22853, 22854, 22854, ...
## Resampling results:
##
## ROC Sens Spec
## 0.8631703 0.6282452 0.8916417Second, we tried using only variables with *** and ** significance, giving an ROC of 0.8633096.
Lastly, we also tried one model with variables of three, two, and one star significance, as well as another model with all the variables. However, we found that the model with only *** and ** significance produced the highest ROC. Hence, we will use this model to move forward with data transformation.
For data transformation, we tested the linearity assumption for each of the variables. We determined that the following variables required transformation: 1. no_of_children
By making a table of no_of_children against booking_status, we see possible right skewness, hence we perform sqrt(no_of_children).
- lead_time
From the plot, we see that data is not completely linear, hence we tried
sqrt and log transformations. sqrt(lead_time) gave a better ROC
result.
- no_of_previous_cancellations
By making a table of no_of_previous_cancellations against booking_status, we see possible right skewness, hence we perform sqrt(no_of_previous_cancellations).
- avg_price_per_room
From the plot, we see possible right skewness, hence we perform
sqrt(avg_price_per_room).
- no_of_special_requests
By making a table of no_of_special_requests against booking_status, we see possible changing rate of decrease, hence we performed log1p(no_of_special_requests).
Finally, we have the resulting model with an ROC of 0.8653404.
set.seed(1)
train(booking_status ~ sqrt(no_of_children) + no_of_weekend_nights + no_of_week_nights + type_of_meal_plan + required_car_parking_space + room_type_reserved + sqrt(lead_time) + arrival_year + arrival_month + market_segment_type + repeated_guest + sqrt(no_of_previous_cancellations) + sqrt(avg_price_per_room) + log1p(no_of_special_requests), data = Train, method = "glm", family = "binomial", trControl = fitControl, metric = "ROC")Out of Sample Testing: gives an ROC of 0.8650141
CART Model
We will start off the CART model by creating a model with all independent variables apart from BookingID that was removed previously. According to the CART model, the important variables are lead time; number of special requests; average price per room; market segment type; and the number of adults. The AUC of this model is 82.18%.
## [1] 0.8217722From this, we will utilise 10-fold cross validation on our model to obtain the optimal cp value achieve higher prediction accuracy.
library(e1071)
fitControl = trainControl(method = "cv", number = 10)
cpGrid = expand.grid(.cp = (0:100)*0.0000005)
set.seed(123)
cvResults = train(booking_status ~., data = normtrain, method = "rpart", trControl = fitControl, tuneGrid = cpGrid)
cvResultspredictCARTCV1 = predict(hotelTreeCv, data = normtrain)
predCARTCV1 = prediction(predictCARTCV1[,2], normtrain$booking_status)
ROCRaucCARTcv1 = performance(predCARTCV1,"auc")
ROCRaucCARTcv1@y.values[[1]]The in sample AUC is 96.14%
The optimal cp value is 5e-05 or 0.0000005. We will now proceed to train the model using the optimal CP. The accuracy of the new CART model is 92.34%.As seen above, the CART model is very large, and this is due to the small data set which has resulted in overfitting.
Random Forest Model
We will start off the default Random Forest model by creating a model with all independent variables without BookingID that was removed previously. The In-Sample-AUC of this default model is 0.954774.
We will then apply full cartesian grid search to obtain top 10 models that are ranked based on RSME for optimal mtry and nodesize which results in mtry in the range of 4,5 & 6 and nodesize = 1. We will then try to see the optimal ntree using randomForest package which lies between 100 to 3000. As there might be some iterations between optimal ntree, nodesize and mtry, we will build models based on these range and find the best model with optimal ntree nodesize and mtry. During the try and error phase, we will set the seed at 333. The In-Sample-ROC of the optimised model is 0.9559477. The out-of-sample AUC is 0.9552154.
Based on permutation-based and imputation-based variane check, we can
learn the importance of the variables.
According to the code and plot, lead_time is the most important, along
with special requests and average price per room which affects the
booking status.
Diagnostic analytics
Propensity Score Matching will be used to confirm if there are any selection bias with the observational data such as confounding factors in our analysis. In our analysis, we want to determine if lead time is really a causal factor to booking status seeing that it has been used substantially in our analysis.
Proportion of booking: 0 refers to not cancelled and 1, to cancelled.
We can check if there is any significant difference between bookings that are cancelled and bookings that are not cancelled in terms of average price per room, percentage of weekend nights, percentage of children. From the analysis done shows similar findings to that found in visualisation - there is significant difference.
We conducted t-test to confirm whether the differences between the above 3 independent variables are statistically significant. It is observed that all the variables selected are significant with a p-value below 0.05.
From the above tables, we see that there’s quite a bit of difference between the booking statuses. Particularly, bookings that were canceled had booked rooms with higher prices (110.59 vs 99.93) and mostly during weekend nights (88.75% vs. 77.33%), and with children in the booking(12.44% vs. 9.6%)
We will now use the Propensity Score Matching to find appropriate matching between Bookings that were cancelled and not cancelled.
matching = matchit(booking_status~avg_price_per_room+no_of_weekend_nights+no_of_children,data = hotel2,method = "nearest",ratio = 1)
result = summary(matching)
result##
## Call:
## matchit(formula = booking_status ~ avg_price_per_room + no_of_weekend_nights +
## no_of_children, data = hotel2, method = "nearest", ratio = 1)
##
## Summary of Balance for All Data:
## Means Treated Means Control Std. Mean Diff. Var. Ratio
## distance 0.3434 0.3199 0.3345 0.8906
## avg_price_per_room 110.5900 99.9314 0.3304 0.8090
## no_of_weekend_nights 0.8875 0.7733 0.1242 1.1879
## no_of_children 0.1244 0.0960 0.0644 1.3302
## eCDF Mean eCDF Max
## distance 0.0822 0.1754
## avg_price_per_room 0.0799 0.1773
## no_of_weekend_nights 0.0143 0.0544
## no_of_children 0.0048 0.0149
##
## Summary of Balance for Matched Data:
## Means Treated Means Control Std. Mean Diff. Var. Ratio
## distance 0.3434 0.3432 0.0034 1.0198
## avg_price_per_room 110.5900 110.1945 0.0123 0.9583
## no_of_weekend_nights 0.8875 0.8943 -0.0074 1.1369
## no_of_children 0.1244 0.1125 0.0269 1.1929
## eCDF Mean eCDF Max Std. Pair Dist.
## distance 0.0006 0.0231 0.0041
## avg_price_per_room 0.0182 0.0548 0.2655
## no_of_weekend_nights 0.0060 0.0169 0.4835
## no_of_children 0.0019 0.0081 0.3422
##
## Sample Sizes:
## Control Treated
## All 24390 11885
## Matched 11885 11885
## Unmatched 12505 0
## Discarded 0 0According to the results, the matching worked very well for our data set, considering the 3 chosen independent variables. We were able to find similar customers that did not cancel their rooms. Prior to matching, the cancelled rooms had a higher average price per room, on a higher percentage of weekend nights, and consisting of a higher percentage of children. After matching, the average price per room for cancelled and non-cancelled rooms are similar, with slightly higher number of weekend nights for non-cancelled rooms and a slightly higher number of children for cancelled rooms.
For better visualisation, we plotted the distribution of propensity scores to further check the matching performance. The matching was successful with similar histograms for the matched data points and the jitter plot also points at the close match between the treatment and matched control units.
plot(matching,type = "hist")
plot(matching,type = "jitter")
## [1] "To identify the units, use first mouse button; to stop, use second."Analysing matched data
We have 11,885 pairings through this PSM model. There are the same number of cancelled and non-cancelled rooms in our new data frame.
bookingmatched = match.data(matching)
str(bookingmatched)## Classes 'matchdata' and 'data.frame': 23770 obs. of 21 variables:
## $ no_of_adults : int 2 2 1 2 2 2 3 2 1 2 ...
## $ no_of_children : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_weekend_nights : int 1 2 2 0 1 0 0 0 1 2 ...
## $ no_of_week_nights : int 2 3 1 2 1 2 4 5 0 1 ...
## $ type_of_meal_plan : num 1 0 1 1 0 2 1 1 0 0 ...
## $ required_car_parking_space : int 0 0 0 0 0 0 0 0 0 0 ...
## $ room_type_reserved : num 1 1 1 1 1 1 1 4 1 1 ...
## $ lead_time : int 224 5 1 211 48 346 121 44 0 30 ...
## $ arrival_year : int 2017 2018 2018 2018 2018 2018 2018 2018 2018 2018 ...
## $ arrival_month : int 10 11 2 5 4 9 7 10 9 11 ...
## $ arrival_date : int 2 6 28 20 11 13 6 18 11 26 ...
## $ market_segment_type : num 0 1 1 1 1 1 0 1 1 1 ...
## $ repeated_guest : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_cancellations : int 0 0 0 0 0 0 0 0 0 0 ...
## $ no_of_previous_bookings_not_canceled: int 0 0 0 0 0 0 0 0 0 0 ...
## $ avg_price_per_room : num 65 106.7 60 100 94.5 ...
## $ no_of_special_requests : int 0 1 0 0 0 1 1 3 0 0 ...
## $ booking_status : num 0 0 1 1 1 1 0 0 0 1 ...
## $ distance : num 0.259 0.376 0.28 0.292 0.315 ...
## $ weights : num 1 1 1 1 1 1 1 1 1 1 ...
## $ subclass : Factor w/ 11885 levels "1","2","3","4",..: 4094 4864 7313 9726 10076 10445 8117 5498 11266 1073 ...
## - attr(*, "distance")= chr "distance"
## - attr(*, "weights")= chr "weights"
## - attr(*, "subclass")= chr "subclass"table(bookingmatched$booking_status)##
## 0 1
## 11885 11885We will now compare the lead time after matching. For a room of similar price and percentages of weekend nights and children, the lead time remains largely different compared to that of cancelled rooms. Hence, lead time truly does play a part in the cancellation of rooms.
tapply(bookingmatched$lead_time, bookingmatched$booking_status, mean)## 0 1
## 62.28128 139.21548with(bookingmatched, t.test(lead_time~booking_status))##
## Welch Two Sample t-test
##
## data: lead_time by booking_status
## t = -71.872, df = 19944, p-value < 2.2e-16
## alternative hypothesis: true difference in means between group 0 and group 1 is not equal to 0
## 95 percent confidence interval:
## -79.03234 -74.83607
## sample estimates:
## mean in group 0 mean in group 1
## 62.28128 139.21548Prescriptive analytics
Provide Recommendations Based on Other Analytics: As an alternative to bagging/ Random Forest, we also tried using boosting with the C5.0 method, which grows trees in succession. At each successive iteration, the new model is built and improved based on past models. We obtained an optimal in-sample ROC of 0.9466660, using the optimised arguments of trials = 20, model = tree, and winnow = TRUE.
Optimising Logistic Regression Models - stochastic gradient descent (SGD): To optimise the logistic regression model, we can use SGD, which minimises the loss function, and seeks to find an optimal model.The advantage of SGD in our logistic regression model is that since the loss function is convex and there is only one minimum, the gradient descent is guaranteed to find the global minimum loss. Another advantage of SGD is that unlike gradient descent, it does not run into the issue of too many computations with large datasets, which will be beneficial if we want to scale our project.
Discussion on findings
- Key Findings
Firstly, our CART model has pointed out that lead time, average price per room, number of special requests, market segment, and number of adults are significant variables. Meanwhile, the findings from confounding factors in our logistic model is that room bookings with higher room prices, and booked during weekend nights along with children in the bookings have a high likelihood of getting cancelled
- Recommendations
Based on the analysis done, our team has the following recommendations:
- The hotel should reduce the lead time on when a customer is able to book a room.
- The hotel should consider overbooking when faced with bookings with longer lead times in order to maximise utilisation.
- There should be a focus on upholding service standards in the hotel due to a large percentage of bookings receiving special requests.
- The hotel can focus more efforts on trying to get customers to rebook (e.g., provide vouchers, offer travel plans, utilise pre-arrival emails to build excitement)
- Limitations
- Our dataset is relatively small (36,275 data points in total), and is even smaller when further split into train and test sets.
- As a result of the small dataset, the training dataset is also small and is overfitting our CART model. The cross validation produced a very small cp value which resulted in a very complex tree.
- We had 3 variables that potentially have time elements: arrival year, arrival month, arrival date. We could potentially conduct time series analysis using these elements because it is also a potential important factor in hotel bookings. For example, seasonality is one factor which might be correlated with the number of cancellations the hotel experiences.
- A significant proportion of our independent variables were categorical variables compared to numerical or other types of variables, which might possibly affect the rigour of our models.
- Future plans for our project
Besides increasing the number of data points through data collection, collecting data on new and different variables such as more time series data (helps to determine seasonality), purpose of stay (i.e. leisure, business etc. will help segment and cluster different groups of customers), any upcoming events happening nearby (i.e. huge events will affect hotel bookings).
Next, it is to expand on the details collected for Market segment variable - bookings are not only made through the hotel’s own website but through online travel agency (OTA) websites as well. In our analysis, a bulk of customers who cancelled their bookings belong to the online segment but there was no way to find the reason behind its huge proportion of cancellations.
Having more information may better explain the proportion of bookings cancelled by new users. Booking through OTA websites could mean having little to no relationship with the hotels that they booked and users could possibly have booked several hotels in advance and cancel a few closer to the reservation date.