__STYLES__
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
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.
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 %.
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.