The Hotel Reservations Analytics
The project aims to modernize data infrastructure and reporting for hotel reservations by developing three interactive dashboards with data accessed via live database connection. This sample project is based on a hypothetical client request and synthetic dataset.Results
Request
A hypothetical client from hospitality industry requested to modernize data infrastructure and reporting for hotel reservations. It involves replacing an inefficient Excel-based system with a robust, flexible solution, and automating report generation with live database connection. The project requires developing three interactive dashboards for Global KPIs, Reservation Acquisition, and Guests, with features like total reservations, guest demographics, and performance trends. Interactivity with filters for arrival date, market segment, and customer type is emphasized, alongside adherence to the company's branding and design aesthetics.
Solution
Approach
Existing data was transferred from Excel spreadsheets to a newly designed cloud-based relational database, ensuring structured and centralized data storage, data integrity, scalability, and security. The data model was normalized to the third normal form for optimal structure and efficient data retrieval.
A 3-page dashboard was implemented in Google Looker Studio, chosen for its availability as a free tool, ease of use, and maintenance, making it suitable for a non-technical client.
Data visualization highlights
- The 'Global KPIs' page includes multiple scorecards and various charts for a quick, at-a-glance assessment of the business situation. Key metrics covered include reservations by quarter and country, guest demographics by meal type, and new vs. repeat reservations.
- The 'Customer Acquisition' page focuses on agent performance, top B2B clients, and reservations per channel and segment over time.
- The 'Guests' page provides key summary metrics about guests, visualizing trends over time, guest breakdowns, and room type changes.
Global filters enable easy and intuitive data slicing in any report page by parameters like date range, market segment, customer type, and duration of stay.
Technical Challenge: Implementing the 'Guests breakdown' visualization posed a challenge due to data structure limitations dictated by data granularity. This was resolved by creating an additional data source using a custom SQL query.