Revenue Insights in Hospitality Domain

PROBLEM DEFINITION

AtliQ Grands owns multiple five-star hotels across India. They have been in the hospitality industry for the past 20 years. Due to strategic moves from other competitors and ineffective decision-making in management, AtliQ Grands are losing its market share and revenue in the luxury/business hotels category. As a strategic move, the managing director of AtliQ Grands wanted to incorporate “Business and Data Intelligence” to regain their market share and revenue. However, they do not have an in-house data analytics team to provide them with these insights.

Their revenue management team had decided to hire a 3rd party service provider to provide them with insights from their historical data.

This project is part of the challenge organized by CodeBasics Team. I would like to thank all the mentors at CodeBasics.

For project files, please visit my GitHub Profile: https://github.com/UnnimayaBalachandran/Revenue-Insights

DATA OVERVIEW

We have three dimension tables and two fact tables. Details are given below:

Column Description for dim_date:

date: This column represents the dates present in May, June and July.

mmm yy: This column represents the date in the format of mmm yy (monthname year).

week no: This column represents the unique week number for that particular date.

day_type: This column represents whether the given day is Weekend or Weekeday.

Column Description for dim_hotels:

property_id: This column represents the Unique ID for each of the hotels.

property_name: This column represents the name of each hotel.

category: This column determines which class[Luxury, Business] a particular hotel/property belongs to

city: This column represents where the particular hotel/property resides in.

Column Description for dim_rooms:

room_id: This column represents the type of room[RT1, RT2, RT3, RT4] in a hotel.

room_class: This column represents to which class[Standard, Elite, Premium, Presidential] particular room type belongs.

Column Description for fact_aggregated_bookings:

property_id: This column represents the Unique ID for each of the hotels.

check_in_date: This column represents all the check_in_dates of the customers.

room_category: This column represents the type of room[RT1, RT2, RT3, RT4] in a hotel.

successful_bookings: This column represents all the successful room bookings that happen for a particular room type in that hotel on that particular date.

capacity: This column represents the maximum count of rooms available for a particular room type in that hotel on that particular date.

Column Description for fact_bookings:

booking_id: This column represents the Unique Booking ID for each customer when they booked their rooms.

property_id: This column represents the Unique ID for each of the hotelS.

booking_date: This column represents the date on which the customer booked their rooms.

check_in_date: This column represents the date on which the customer check-in(entered) at the hotel.

check_out_date: This column represents the date on which the customer check-out(left) of the hotel.

no_guests: This column represents the number of guests who stayed in a particular room in that hotel.

room_category: This column represents the type of room[RT1, RT2, RT3, RT4] in a hotel.

booking_platform: This column represents in which way the customer booked his room.

ratings_given: This column represents the ratings given by the customer for hotel services.

booking_status: This column represents whether the customer cancelled his booking[Cancelled], successfully stayed in the hotel[Checked Out] or booked his room but not stayed in the hotel[No show].

revenue_generated: This column represents the amount of money generated by the hotel from a particular customer.

revenue_realized: This column represents the final amount of money that goes to the hotel based on booking status. If the booking status is cancelled, then 40% of the revenue generated is deducted and the remaining is refunded to the customer. If the booking status is Checked Out/No Show, then the full revenue generated will go to hotels.

DATA PRE-PROCESSING

Calculated Measures:

Revenue, Total Bookings, Total Capacity, Total Succesful Bookings, occupancy %, Average Rating, No of Days, Total cancelled bookings, cancellation %, Total checked out, Total No show bookings, No show rate %, Booking % by platform, Booking % by room class, ADR(Average Daily Rate), Realisation %(successful "checked out" percentage overall bookings happened), RevPAR(Revenue Generated per available room), DBRN(Daily Booked Room Nights), DSRN(Daily Sellable room nights), DURN(Daily Utilized room nights), Revenue WoW change %, Occupancy WoW change %, ADR WoW change %, RevPAR WoW change %, Realisation WoW change %, DSRN WoW change %.

DATA MODELING

VISUALIZATIONS AND INSIGHTS

Main Dashboard:

KPI Cards: Revenue, RevPar, DSRN, Occupancy %, ADR, Realisation

Slicers: By City, By Room Class, By Month, By Week Number

Additional Dashboards:

Important Insights Generated:

Mumbai generated the highest revenue: 660.6M

The rating was highest for Delhi: 3.8 whereas Average Rating for the brand is 3.62

Weekends generated more revenue - 2bn

AtliQ Exotica performs better compared to all 7 types of properties with 320 Million in revenue, a rating of 3.62, an occupancy percentage of 57 % and a cancellation rate of 24.4%.

Elite-type rooms have the most booking and as well higher cancellation rates.