Splendor Hotel Groups Booking Data Analysis
GitHub Here
This project involves a thorough analysis of a comprehensive dataset, featuring intricate details of bookings, guest demographics, distribution channels, and financial metrics. The aim is to extract meaningful insights that will not only inform operational improvements but also contribute to the overall success of SHG in delivering unparalleled hospitality.
Outline:
Methodology
Data cleaning and manipulation were performed using Jupyter Notebook. During this process, it was noted that 488 entries in the 'Country' column were missing, a small fraction compared to the total of 119,390 rows. Although minimal, these entries are linked to the crucial 'Revenue' column for this analysis. Therefore, I assigned a placeholder value, "Unknown", to these missing entries.
Following the data wrangling and analysis in Jupyter Notebook, the cleaned dataset was exported and stored in a new folder on Microsoft OneDrive. This folder was then connected to Excel, establishing it as the primary data source. This setup ensures that any new data added to the OneDrive folder automatically updates in the Excel sheet. The data in Excel was formatted as a table, facilitating the integration of any newly added files into the worksheet. Additionally, an automatic refresh feature was activated to update the Excel-based dashboard every 24 hours, maintaining the data's currency.
Findings
1. Booking Patterns
A. What is the trend in booking patterns over time, and are there specific seasons or months with increased booking activity?
The chart shows a sharp decline from January to Jun, suggesting a lower demand period post-New Year celebrations. Bookings rise noticeably in July and August, and again in October, possibly due to holiday seasons and end-of-year festivities. The graph highlights January, July, and October as the months with increased booking activities.
Further analysis showed that the number of bookings increased in 2016 with 58,543 bookings after a lower number in previous years. 2017 showed a slight drop with 26,565 bookings, but the numbers still remain high compared to 2013 and 2014. This suggests that 2016 was a particularly busy year for bookings, while 2017, despite a decrease, also saw a relatively high number of bookings.
B. How does lead time vary across different booking channels, and is there a correlation between lead time and customer type?
This shows that bookings made through Offline Travel Agents typically have the longest lead time, followed by Offline Travel Agents. Direct bookings and those through Corporate bookings have shorter lead times, while the 'Undefined' category has the shortest.
To check for correlation between lead time and customer type, I used the formula below.
from scipy import stats
#convert 'Customer Type' to a numeric category type
df['Customer Type Cat'] = df['Customer Type'].astype('category').cat.codes
#Group 'Lead Time' data by 'Customer Type'
groups = df.groupby('Customer Type Cat')['Lead Time'].apply(list)
#ANOVA
f_value, p_value = stats.f_oneway(*groups)
print(f"F-value: {f_value}")
print(f"P-value: {p_value}")F-value: 1366.498149578901
P-value: 0.0
The F-value and P-value of suggest a statistically significant relationship between the booking channel and the lead time. A high F-value typically indicates a strong correlation, and a P-value of 0.0 (or close to 0) suggests the results are statistically significant, meaning the observed relationship is unlikely due to random chance. This statistical evidence supports the visual trends seen in the chart, where different booking channels are associated with different lead times.
2. Customer Behavior Analysis
A. Which distribution channels contribute the most to bookings, and how does the average daily rate (ADR) differ across these channels?
Online Travel Agent channel contributes the most to bookings, with more than 70,000 bookings. This could be due to the convenience and accessibility of booking online. Offline Travel Agents also have a notable number of bookings, likely due to their personalized service and expertise in handling complex arrangements. Direct bookings and Corporate channels show fewer bookings, which might reflect the more targeted nature of these channels, while the 'Undefined' category has the least.
How does the average daily rate (ADR) differ across these channels?
Distribution Channel ADR
Corporate 69.325204
Direct 106.654983
Offline Travel Agent 87.145444
Online Travel Agent 108.567791
Undefined 46.240000The Online Travel Agent and Direct channels appear to command higher ADRs compared to Corporate and Offline Travel Agent channels, with the 'Undefined' category having the lowest ADR. This suggests that bookings made through online agents and direct agreements might be more lucrative on a per-day basis and could be targeted for increased revenue.
B. Can we identify any patterns in the distribution of guests based on their country of origin, and how does this impact revenue?
Portugal emerges as the predominant contributor in terms of guest numbers and revenue, as evidenced by its representation with the largest bubble, showcasing a robust market presence. Following Portugal, the United Kingdom contributes a notably smaller number of guests, with France, Spain, Germany, Italy, Ireland, Belgium, Netherlands, and Switzerland trailing with progressively fewer guests. This pattern highlights Portugal's dominance in the market. The bubble sizes for other countries illustrate varying guest numbers and corresponding revenue levels, all of which are substantially lower in comparison to Portugal's contribution. Further analysis revealed that for most countries, the number of guests directly impacts revenue, with notable exceptions such as Brazil and China. These countries surpass the Netherlands and Switzerland in guest numbers yet lag behind in revenue generation. This discrepancy underscores the significant variance in average daily rates across different nations, indicating that factors beyond mere guest count play a critical role in determining revenue.
3. Revenue Optimization
A. What is the overall revenue trend, and are there specific customer segments or countries contributing significantly to revenue?
The line graph tracks how much revenue was made from 2013 to 2017. There's a notable increase leading into 2016 with a peak, which then drops before another sharp rise. The end of 2017 shows a sharp decline in revenue. The chart shows that the highest and lowest revenue generated often happen around the start or end of the year.
Further analysis revealed that Transient customer type brings in the most money, making over 20 million Naira, while other types make 5 million or less. Looking at countries, the top ten bring in most of the revenue, with Portugal at the top. Since Portugal has the most guests, it also makes the most money — over 8 million Naira. The UK, France, and Spain also add a good amount, with the UK making more than 4 million and France, Spain and Germany each making over 2 million Naira.
B. Can we identify optimal pricing strategies based on the Average Daily Rate (ADR) for different customer types and distribution channels?
Transient customers booked through Online Travel Agents have the highest ADR, and also for the Undefined and Direct category, suggesting they may be willing to pay more. Contrastingly, the Corporate and Contract segments show lower ADRs across most channels.
This indicates that pricing strategies could be optimized by targeting higher rates where customer willingness to pay is greater, such as with Transient customers online, and possibly offering competitive rates for Corporate and Contract segments to maintain volume.
4. Geographical Analysis
A. How does the distribution of guests vary across different countries, and are there specific countries that should be targeted for marketing efforts?
The scatter plot above shows that Portugal has both the highest number of guests and the highest revenue, making it a significant market. The United Kingdom, France, and Spain also contribute considerable numbers of guests and revenue but to a lesser extent. Other countries like Germany, Ireland, Italy, Belgium, Netherlands, and Switzerland represent smaller markets in terms of both guests and revenue.
Given Portugal's dominance in both guests and revenue, it would be a primary target for continued or increased marketing efforts. Additionally, the UK, France, and Spain show potential for growth and could be key targets for marketing campaigns aimed at increasing guest numbers and revenue.
B. Is there a correlation between the country of origin and the likelihood of cancellations or extended stays?
Pearson's Correlation Coefficient: -0.01357721562391987
P-value: 2.7123162033839883e-06
The Pearson's Correlation Coefficient of approximately -0.014 suggests a very weak negative relationship between the country of origin and the likelihood of cancellations or extended stays. This value indicates that there is almost no linear correlation between these two variables. However, the very low P-value (below 0.05) suggests that the correlation coefficient is statistically significant. This means that even though the relationship is very weak, it is unlikely to be due to random chance.
This implies that other factors beyond the country of origin are likely more influential on the likelihood of cancellations or extended stays. Therefore, while the country of origin has some level of association with cancellation or stay length, it might not be a strong enough basis to inform strategies or marketing efforts.
5. Operational Efficiency
A. What is the average length of stay for guests, and how does it differ based on booking channels or customer types?
On average, guests spend about 3.43 nights at the hotel. Guests who book through corporate channels tend to have the shortest stays, averaging just 2.38 nights. In contrast, those who book through offline travel agents often stay longer, with an average of 3.92 nights, which is the longest stay among all the channels.
When breaking down the length of stay by the type of customer, contract customers stay the longest, with stays averaging 5.32 nights, which suggests they are likely in town for business matters that require a longer presence. Group customers have shorter visits, with an average stay of 2.88 nights, which might reflect the brief nature of their travel, possibly for events or conferences. Transient customers, who are likely individual travelers, stay an average of 3.45 nights, and transient-party, have stays averaging 3.06 nights.
B. Are there patterns in check-out dates that can inform staffing and resource allocation strategies?
The line graph starts with a high number in January, which then declines steadily until May. There's a significant dip in June, followed by a sharp increase in July, which could suggest a seasonal trend or event that affects guest departures. Afterward, the numbers decline again through to November.
This pattern indicates that staffing and resources may need to be adjusted to accommodate the higher volume of check-outs in January, July, and october. With potential scaling back during the quieter months that follow each peak.
6. Impact of Deposit Types
A. How does the presence or absence of a deposit impact the likelihood of cancellations and revenue generation?
It appears that bookings with "Non Refundable" deposits have a higher cancellation rate compared to those with "No Deposit" or "Refundable" deposits. This might seem counterintuitive, as one would expect non-refundable deposits to deter cancellations.
The average revenue for the no deposit category is quite high, suggesting that not requiring a deposit does not necessarily result in lower revenue. This could be because customers who do not want to commit to a deposit may be willing to pay higher rates later. Also, Bookings that require a non-refundable deposit typically have slightly higher or comparable average revenue to those with no deposit.
The average revenue for bookings with a refundable deposit is notably lower than the other two categories. This may indicate that while customers are attracted to the flexibility of a refundable deposit, they might spend less overall, or these bookings might be associated with lower-rate rooms or services.
To understand why Refundable category has revenue lower than other two categories, further analysis was carried out.
Deposit Type ADR
No Deposit 103.525067
Non Refundable 89.964017
Refundable 76.209753It's clear that customers like the option of a refundable deposit because it gives them the freedom to change their plans and the bookings often have lower prices. On the other hand, bookings without a deposit or with a non-refundable deposit bring in more revenue, likely because the prices are higher. When customers don't pay a deposit or pay over the counter, they usually end up paying more.
B. Can we identify any patterns in the use of deposit types across different customer segments?
The trend across all customer types is that No Deposit options are most common, which might indicate a general policy or preference among customers. Non Refundable deposits are the next most common choice, particularly among Transient and Transient-Party customers. Refundable deposits are very rarely chosen across all customer types, suggesting that this option may be less important to customers.
7. Analysis of Corporate Bookings
A. What is the proportion of corporate bookings, and how does their Average Daily Rate (ADR) compare to other customer types?
The proportion of corporate bookings is calculated to be 5.59% of the total bookings. This indicates that a relatively small fraction comes from the corporate sector.
Also, the Average Daily Rate (ADR) for corporate bookings stands at approximately 69.35. When compared to the ADRs of other customer types, which are 87.55 for Contract, 83.49 for Group, 107.01 for Transient, and 86.08 for Transient-Party, it can be observed that corporate bookings have a lower ADR than all other customer segments.
This disparity in ADR could be due to various factors, such as negotiated corporate rates, which are often lower due to volume-based discounts or long-term agreements between the hotel and corporate clients. The lower ADR for corporate bookings might also reflect a strategic pricing approach aimed at attracting and maintaining a steady flow of business travelers, who may offer more consistent occupancy rates and potentially spend on other hotel services.
In contrast, the Transient segments, which typically represent individual travelers, might not have the same negotiating leverage or might make bookings based on seasonal rates, leading to a higher ADR.
Conclusion
The number of guests and their countries of origin play a vital role in revenue generation, with notable disparities in average daily rates between countries like Brazil and China compared to the Netherlands and Switzerland. Additionally, the impact of booking channels on lead times and the resulting revenue is clear, with online travel agents contributing significantly to bookings and higher average daily rates.
The analysis also highlights the importance of seasonality in booking patterns, indicating fluctuating demand across different months. The data also suggests that certain customer types, particularly transient customers, are more lucrative in terms of revenue generation. Lastly, the study reveals the influence of deposit types on cancellation rates and revenue, highlighting the higher revenue associated with non-refundable or no deposit bookings.
Recommendation
Considering Portugal's strong market position, both in terms of guest influx and revenue generation, it would be a primary target for continued or increased marketing efforts. Additionally, the UK, France, and Spain show potential for growth and could be key targets for marketing campaigns aimed at increasing guest numbers and revenue.
Putting in place flexible pricing that changes with the seasons can help make the most money during peak months. Targeting transient customers through tailored marketing and loyalty programs could also enhance revenue. Finally, reconsidering deposit policies, perhaps by encouraging non-refundable or no deposit options, might reduce cancellations and increase revenue.
Comments