Hotel Reservation Analysis
A property management software company needed insights into customer booking behavior to optimize revenue and improve digital services.
Key questions included:
- Which booking rates do different customer segments prefer?
- What drives online check-in adoption?
- Which guest segments generate the highest revenue per occupied space?
Executive Summary
Booking Rate Preferences
- Male guests prioritize flexibility (58% choose Fully Flexible rates)
- Female guests are more price-conscious (higher non-refundable rate adoption)
- Business segments show stronger patterns than demographics
Online Check-in Challenge
- Critically low adoption at 5.92%
- OTA guests most likely to adopt (9% rate) vs. corporate guests (2% rate)
- Unknown guests never use online check-in (0% adoption)
Profitability Insights
- Female Leisure travelers most profitable ($63.46 per night per capacity)
- Unknown FIT travelers occupy significant space with minimal returns ($13.47 per capacity)
Data Schema
Rates data table
| Column | Data Type | Description |
|---|---|---|
| rate_id | STRING | Primary key, unique identifier for each rate type |
| rate_name | STRING | Full descriptive rate name |
| short_rate_name | STRING | Abbreviated rate name for analysis |
| settlement_action | INTEGER | Rate settlement behavior indicator |
| settlement_trigger | INTEGER | Trigger condition for rate settlement |
| settlement_value | DECIMAL | Settlement calculation value |
| settlement_type | INTEGER | Type classification for settlement processing |
Reservations data table
| Column | Data Type | Description |
|---|---|---|
| start_utc | TIMESTAMP | Reservation start date/time |
| end_utc | TIMESTAMP | Reservation end date/time |
| created_utc | TIMESTAMP | Booking creation timestamp |
| night_count | INTEGER | Total nights booked |
| night_cost_sum | DECIMAL | Total revenue for stay |
| occupied_space_sum | INTEGER | Total capacity units occupied |
| guest_count_sum | INTEGER | Total number of guests |
| rate_id | STRING | Foreign key linking to rates table |
| gender | INTEGER | Guest gender (1=Male, 2=Female, 0/NULL=Unknown) |
| age_group | INTEGER | Guest age category (0=Unknown, 25=25-35, etc.) |
| nationality_code | STRING | ISO country code |
| business_segment | STRING | Distribution channel (ie "OTAs", "Leisure", "Direct Business") |
| is_online_checkin | BOOLEAN | Online check-in usage flag |
Assumptions and Limitations
- All reservation states included: Analysis includes cancelled reservations under the assumption that booking intent existed regardless of cancellation reason
- No duplicate validation performed: Assumes dataset has been pre-cleaned by data platform/engineering team with no booking ID duplicates
- Nationality analysis limited: Only included countries with 40+ bookings for reliability
Booking Rate Choices by Customer Segments
Gender-Based Analysis
Our hotel serves a male-dominated customer base with significant unknown demographics:
Rate Preferences by Gender
Male guests prioritize flexibility, while female guests show more price sensitivity. Unknown guests follow entirely different booking patterns, likely representing corporate or agent bookings rather than individual travelers.
Male Guests - Flexibility Focused
- Fully Flexible rate dominates at 58% of male bookings
- Clear preference for maximum booking flexibility over discounts
Female Guests - Balanced Approach
- Fully Flexible rate leads at 47% but less dominant than males
- Non-Refundable rates at 18% - significantly higher than males
- More price-conscious, willing to accept restrictions for better rates
Unknown Gender - Early Planning
- Early-60 days rate dominates at 50% - dramatically different pattern
- Fully Flexible secondary at 27%
- Suggests advance corporate booking or travel agent reservations
Age Group Analysis
60.78% of bookings have unknown age data, limiting the reliability of age-based insights.
Rate Preferences by Age
While all age groups prioritize flexibility, younger travelers show highest price sensitivity, while older travelers prefer advance planning discounts. However, the large proportion of unknown age data (60.78%) limits the reliability of these insights for business decision-making.
Consistent Flexibility Preference Across All Ages
- All age groups prefer Fully Flexible rates (44-55% within each group)
- Young travelers (0-25) most price-sensitive: 24.36% choose Non-Refundable rates
- Middle-aged travelers (25-45): Balanced between flexibility and discounts
Data Reliability Limitations Age groups 55+ have insufficient sizes for reliable business insights:
- 55-65 group: Only 65 total bookings
- Over 65 group: Only 16 total bookings
Pattern suggestions for these groups (older travelers preferring early booking discounts) cannot be considered reliable for business decision-making.
Nationality Analysis
Our hotel attracts a diverse international clientele, though 43.82% have unknown nationality:
Rate Preferences by Nationality
With 44% unknown nationality data and several countries having small sample sizes (46-72 bookings), insights for smaller markets should be considered preliminary. Business decisions should focus on the larger markets (US, GB and DE).
European Guests - Flexibility Seekers
- German guests lead flexibility preference at 71% Fully Flexible (154 bookings)
- Czech guests at 76% (67 bookings) and Slovak guests at 64% (72 bookings) Fully Flexible
- British guests show high flexibility demand at 65% (187 bookings)
US Guests - Balanced Value Approach
- Fully Flexible preferred at 45% but significantly lower than Europeans
- Higher price sensitivity: 16% choose Non-Refundable rates
- Unique preference for Direct Booking rates at 8% (highest among all nationalities)
Business Segment Analysis
Business segments are relatively balanced across our hotel's distribution channels:
Rate Preferences by Business Segment
- OTA channels drive flexibility demand - guests pay premium when unable to contact hotel directly
- FIT travelers most price-sensitive - plan ahead for discounts
- Corporate segments balance flexibility with advance planning
- Leisure travelers show most diverse booking patterns
Online Check-in Analysis
Overall Adoption Challenge
Online check-in adoption is critically low at just 5.92%, indicating significant barriers to digital adoption or limited system availability:
By Business Segment
OTA Channels Lead Digital Adoption
- OTAs: 9.07% online check-in rate (562 total bookings)
- OTA Netto: 7.80% online check-in rate (551 total bookings)
- Leisure: 8.42% online check-in rate (499 total bookings)
Traditional Channels Show Resistance
- Direct Business: 2.20% online check-in rate (318 total bookings)
- FIT: 0.97% online check-in rate (516 total bookings) - surprisingly lowest
- Film: 0% online check-in rate (55 total bookings)
By Gender
There's a consistent low adoption accross genders:
Unknown guests never use online check-in, likely representing corporate bookings or travel agent reservations where end guests handle their own check-in.
Online Check-in by Weekday
Saturday shows the highest online check-in adoption rate at 12.33%, though this is based on a small denominator of only 146 total bookings.
The small denominator (148 total online check-ins) makes detailed analysis unreliable:
- Saturday's high rate based on only 18 online check-ins
- Daily variations likely represent statistical noise rather than meaningful patterns
- Not enough data for confident business decisions
Average Night Revenue per Occupied Capacity Analysis
Methodology
Average night revenue per occupied capacity calculated as:
(night_cost_sum / night_count) / occupied_space_sum
This metric provides the average night revenue per single occupied capacity unit (bed/space), normalizing for both stay length and room capacity to enable true profitability comparison across guest segments.
By Gender
While female guests show the highest revenue per capacity, they represent only 14.4% of total bookings (360 out of 2,501), making this the smallest size among the 3 gender segments. Male guests drive volume with 51.8% of bookings, while Unknown guests represent 33.8% with significantly lower profitability.
Cross-Segment Analysis: Gender × Business Segment
- Male OTAs and Male OTA Nette appear as the largest bubbles in the high-volume, high-profitability quadrant.
- Female guests consistently outperform across all business segments despite representing smaller booking volumes.
- Unknown segments systematically underperform, particularly in FIT and Film channels.
Most Profitable Guest Segments
- Female Leisure travelers: $63.46 per night per capacity (72 bookings, 2.88% of total bookings, $38.7K total revenue)
- Female OTA guests: $62.82 per night per capacity (77 bookings, 3.08% of total bookings, $41K total revenue)
- Male OTA guests: $59.24 per night per capacity (477 bookings, 19.07% of total bookings, $282K total revenue)
Least Profitable Guest Segments
- Unknown Film segment: $4.18 per night per capacity (16 bookings, 0.64% of total bookings, $18.3K total revenue)
- Unknown FIT travelers: $13.47 per night per capacity (340 bookings, 13.59% of total bookings, $68.3K total revenue)
Conclusions and Recommendations
Data Quality Improvements
- Improve demographic data collection: 34% unknown gender, 61% unknown age limits analysis reliability
- Enhance nationality capture: 44% unknown nationality prevents market-specific strategies
- Focus on business segment data. Most reliable and actionable for decision-making
Online Check-in Priority Investigation
- System usability review required: 5.92% adoption suggests fundamental barriers
- Partner with OTA platforms for promotion (highest current adoption at 9%)
- Investigate corporate booking processes - Unknown guests represent 34% of bookings but 0% online check-in
Revenue Optimization Strategy
- Target Female Leisure segment for premium offerings (highest profitability)
- Reevaluate FIT channel strategy - 21% of bookings but lowest revenue per capacity
- Optimize OTA partnerships - balance volume (Male OTAs) with profitability (Female segments)