This project is about find the best combination squad for T20 World Cup

1. Fact Bookings Table (Customer wise Distribution):-

   • 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 full revenue generated will goes to hotels.

2. Fact Aggregate Bookings Table (Property wise Distribution) :-

   • 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.

3. Dim Date table :-

   • 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.

4. Dim Hotels table (Hotel Info Table):-

   • 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.

5. Dim Rooms table:-

   • 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.

1. Loading the Data from CSV files into PowerBI Desktop.

2. Performing Data Cleaning such as Removing the NULL values and unwanted data.

3. Adding the columns such as Week Number for every week and day_type as "Weekday" or "Weekend" as for Hotel Chain Weekends as Friday and Saturday

4. Creating Measures for Total Revenue Generated, Total Bookings, Total Capacity, Total Successful Bookings, Percentage measures for all Columns, ADR(Average Daily Rate), RevPAR(Revenue Per Available Room), DBRN(Daily Booked Room Nights), DSRN(Daily Sellable Room Nights), DURN(Daily Utilized Room Nights), Measures to Calculate Week on Week Changes for each parameter.

SUM(), COUNT(), AVERAGE(), DIVIDE(), DATEDIFF(), CALCULATE(), ALL(), HASONEFILTER(), SELECTEDVALUE(), FILTER()

1. ADR (Average Daily Rates)

   • It is ratio for total revenue by total bookings
   • Average revenue for each booking done

2. RevPAR (Revenue Per Available Room)

   • It is ratio for total revenue by Total capacity
   • Average revenue for each available room

3. DBRN (Daily Booked Room Night)

   • It is ratio for total no. of bookings by total no. of days

4. DSRN (Daily Sellable Room Night)

   • It is ratio for total no. of rooms by total no. of days

5. DURN (Daily Usable Room Night)

   • It is ratio for total no. checked out by total no. of days

6. ALOS (Average Length of Stay)

   • It is ratio for total no. days stayed by Total Bookings

Tech: PowerBI Desktop, PowerBI Service, Power Query

Libraries: PowerBI DAX

Web Scrapping and Analysing data. Data transformation in power query. PowerBI Chart creation. Adding Measures using DAX Functions.

1. There are different levers to improve revenue in hotel industry one of the lever is pricing such as the room prices for weekday is less as the demand is less but same rooms prices go up in the weekends due to more demand.

2. As the May, June and July these 3 months are summer vacations in India still data suggests that the overall revenue by weeks i.e RevPAR percentage has been stable as throughout even without applying any kind of sales strategies for weekdays and weekends. They are selling the rooms at static prices as per the room categories as ADR is constant throughout.

3. There are 3 types of pricing strategies use by the Hotel Management. Flat Pricings, Weekday/Weekend Pricings and Dynamic Pricings.

4. To check strategy for pricing for these hotels we can see the table for weekday and weekends metrics. We can see the weekends have higher occupancy than Weekdays still the ADR is almost same for both sections. This proves that this hotel chain is using Flat price strategies. There is a big opportunity for application of dynamic or weekend pricing strategy for revenue increase.

5. How does the pricing influence the occupancy ? When you see a drop in rates i.e ADR we see an increase in occupancy whereas if we increase the rates we see an decrese in occupancy. This strategy is somewhat affected by the region and the market elasticity in that region.

6. In Hotel chain Management there is a principle called "Pareto Principle" i.e 80-20 Rule i.e 80% revenue drop is because of 20% worst performing hotels or 80% revenue is generated by 20% of the best performing hotels. If we fix the problem statement for worst performing hotels the revenue increases automatically.

7. So if we see the hotels with lowest occupancy have the lowest ratings as well and the cancellation was also slightly higher than the other hotels. This tells us the particular hotels that needs to be checked i.e kind of service, kind of rooms and other criterias.

8. In Realisation % and ADR by Platform Graph we can see the that the average prices for offline are highest and that for online platforms is lesser as offline purchase there is no commission included hence there are chances of revenue increase if we drop the prices in offline mode or use a dynamic pricing but in a different way as providing coupons and free goodies.

9. Pricing can be one of the strategies to increase the revenue whereas we can increase that night stay and many more.

• Dataset Download