Cancellations forecast night by night, and a resale level the revenue manager can approve
A dashboard for two hotels that lose a third of their bookings to cancellation. Built and tested on 119,390 real bookings.
Executive summary
Cancellations forecast night by night within 5.9 rooms, against 9.2 for a flat rate, and a resale level that earned €1.52M with 421 walked guests against €1.21M and 1,068.
Situation
Two Portuguese hotels lose about a third of bookings to cancellation, most through online travel agents. Overbooking is set by hand from one historical rate.
Approach
Each booking scored for cancellation risk, trained before 2017 and tested on 40,619 arrivals in 2017; scores summed into rooms freed per night and a resale level tested on past seasons.
Result
The forecast misses by 5.9 rooms a night against 9.2. The suggested level earns more and walks fewer guests than any flat level. Late, firm-looking cancellations remain the main miss.
The business problem
A hotel revenue manager decides each night how far to overbook and what rate to hold for the rooms that will come back. Cancellations differ by segment: group blocks, online travel agency bookings on free cancellation and direct bookings behave very differently. Revenue systems such as IDeaS and Duetto forecast cancellations by segment for this reason, and properties that act on those forecasts resell rooms that would otherwise go empty.
I measured how well a booking-level cancellation score, built from what the hotel knows at booking, sets an overbooking level night by night against a flat rate, and how many guests each plan would have walked.
- Team
- One revenue manager, front-office manager, reservations staff
- Scale
- About 150 to 250 bookings a day across channels
- Systems
- Opera, Mews or Cloudbeds PMS, a channel manager, IDeaS, Duetto or a spreadsheet forecast
- Constraint
- Overbooking and walk policy set by management; guest data under the Privacy Act or GDPR
What was built
A nightly job that reads new and changed bookings from the property-management system, scores each for cancellation risk, sums the scores into expected cancellations per night, and suggests a resale level that maximised net revenue on past seasons. The revenue manager sees the high-risk list, the forecast and the suggested level, and decides. Deposit requests or reminder emails can then go to high-risk bookings only.
Results
| Forecast of rooms freed per night | Average miss (rooms) |
|---|---|
| Model (sum of booking scores) | 5.9 (95% CI 5.4–6.3) |
| Rule of thumb by channel | 6.0 |
| Flat historical rate | 9.2 |
Who cancels
Ranking quality
| Method | AUC (95% CI) | PR-AUC | Brier |
|---|---|---|---|
| Rule: segment × deposit history | 0.707 (0.702–0.712) | 0.626 | 0.187 |
| Logistic regression | 0.871 (0.868–0.875) | 0.825 | 0.146 |
| Gradient boosting | 0.887 (0.884–0.891) | 0.844 | 0.142 |
Where it misses: 6,942 cancellations scored low. Their median lead time was 74 days against 151 for those caught, and 61% had made a special request, which usually signals a firm booking.
Data and method
- Source
- Antonio, Almeida and Nunes (2019), Hotel booking demand datasets, Data in Brief 22
- Size
- 119,390 bookings, a city and a resort hotel in Portugal, arrivals 2015-07-01 to 2017-08-31
- Split
- Train on 78,589 arrivals before 2017; test on 40,619 arriving in 2017 (15,734 cancelled)
- Audit
- 31,994 exact duplicates, 180 zero-guest bookings, 1,959 zero-rate stays; reported and handled
The rule of thumb scores bookings by channel and the guest's deposit history. Logistic regression was chosen over gradient boosting on validation. The reservation-status field gives the answer away and was removed; with other booking-time leaks AUC would read 0.895, and with a random split 0.952. The reported figures use neither.
Fairness
Country of origin lifts AUC from 0.852 to 0.887. Domestic (Portuguese) bookings cancel at 57% against 17% to 35% for other markets. A hotel may prefer to drop country and accept the lower ranking quality rather than treat guests differently by nationality; deposit rules based on nationality would also raise discrimination concerns.
Assumptions and limitations
- Walk cost of twice the nightly rate; each hotel sets its own
- Two Portuguese hotels, 2015 to 2017, before the pandemic changed booking windows and flexible-rate habits
- Revenue figures use the recorded average daily rate; discounts and channel commission are not netted off
- The effect of deposit requests on cancellations is not tested here
What a hotel would do next
- Week 1: export two years of reservations with status history from the PMS
- Week 2: refit and backtest the resale level on the last season
- Weeks 3 to 6: show the suggested level alongside the current one; the revenue manager chooses
- Then: compare walks, empty rooms and net revenue month on month
Technical depth
The nightly forecast is the sum of booking probabilities for each arrival night, so it updates as bookings change. Resale levels are tested by replaying the test year night by night. Calibration matters here because the probabilities are summed; logistic regression is better calibrated than the boosted model on this data.