top of page
dash3.png

ATLIQ GRANDS HOSPITALITY 

Problem Statement

​

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.

Dashboard Requirement

The desired values will be updated according to the sample mockup image provided.

 

  • Filters will be using in the dashboard

  • KPI will include RevPar, ADR, DSRN, Realization, Revenue, Occupancy %

  • There will be separate filtering on weekdays and weekends

  • In hotel industry weekends are Friday and Saturday

  • Will be using in the dashboard Week on week change

  • Will be include Channel level split which is booking.com, expedia vs.

  • In hotel industry weekends are Friday and Saturday

​

Task:

​

I am a data analyst who has been provided with sample data and a mock-up dashboard to work on the following task. 

  1. Create the metrics according to the metric list.

  2. Create a dashboard according to the mock-up provided by stakeholders.

  3. Create relevant insights that are not provided in the metric list/mock-up dashboard.

Data Organized

This dataset was downloaded from codebasics.io website and stored in Hospitality Domain folder on PC.

It contains 5 data files: dim_date.csv, dim_hotels, dim_rooms, fact_aggregated_bookings.csv, and fact_bookings.csv.

There is also a sample dashboard image mock up dashboard_atliq grands.png.

Data Cleaning & Transformation

Microsoft Power Query and Power BI will be used

​

  • Duplicates were checked with remove duplicates function

  • Gaps were checked with TRIM function

  • Blank and NULL checked with filter function

  • In our day_type column in the dim_date data shows that weekends are Saturday and Sunday, so this column has been removed.

Data Analyzing

     1. Calculated Columns

​

  • wn column was created in the dim_date dataset.

 

wn = WEEKNUM(dim_date[date])

 

  • day_type column was created in the dim_date dataset.

 

day type = 

  VAR wd = WEEKDAY(dim_date[date],1)

  RETURN

  IF(wd>5,"Weekend","Weekday")

 

     2. Measures

​

  • Revenue = SUM(fact_bookings[revenue_realized])

  • Total Bookings = COUNT(fact_bookings[booking_id])

  • Total Capacity = SUM(fact_aggregated_bookings[capacity])

  • Total Succesful Bookings = SUM(fact_aggregated_bookings[successful_bookings])

  • Occupancy % = DIVIDE([Total Succesful Bookings],[Total Capacity],0)

  • Average Rating = AVERAGE(fact_bookings[ratings_given])

  • No of days = DATEDIFF(MIN(dim_date[date]),MAX(dim_date[date]),DAY) +1

  • Total cancelled bookings = 

CALCULATE([Total Bookings],fact_bookings[booking_status]="Cancelled")

  • Cancellation % = DIVIDE([Total cancelled bookings],[Total Bookings],0)

  • Total Checked Out = 

CALCULATE([Total Bookings],fact_bookings[booking_status]="Checked Out")

  • Total No Show Bookings = 

CALCULATE([Total Bookings],fact_bookings[booking_status]="No Show")

  • No Show % = DIVIDE([Total no show bookings],[Total Bookings],0)

  • Booking % by Platform = 

DIVIDE([Total Bookings],

CALCULATE([Total Bookings], 

ALL(fact_bookings[booking_platform])))*100

  • Booking % by Room class = 

DIVIDE([Total Bookings],

CALCULATE([Total Bookings], 

ALL(dim_rooms[room_class])))*100

  • ADR = DIVIDE( [Revenue], [Total Bookings],0)

  • Realisation % = 1- ([Cancellation %]+[No Show %])

  • RevPAR = DIVIDE([Revenue],[Total Capacity],0)

  • DBRN = DIVIDE([Total Bookings], [No of days],0)

  • DSRN = DIVIDE([Total Capacity], [No of days],0)

  • DURN = DIVIDE([Total Checked Out],[No of days],0)

  • Revenue WoW change % = 

VAR selv = IF(HASONEFILTER(dim_date[wn]),

SELECTEDVALUE(dim_date[wn]),MAX(dim_date[w]))

VAR revcw = CALCULATE([Revenue],dim_date[wn]= selv)

VAR revpw =  CALCULATE([Revenue],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

RETURN

DIVIDE(revcw,revpw,0)-1

  • Occupancy WoW change % = 

VAR selv = IF(HASONEFILTER(dim_date[wn]),

SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))

VAR revcw = CALCULATE([Occupancy %],dim_date[wn]= selv)

VAR revpw =  CALCULATE([Occupancy %],

FILTER(ALL(dim_date),dim_date[wn]=selv-1))

RETURN

DIVIDE(revcw,revpw,0)-1

  • ADR WoW change % = 

VAR selv = IF(HASONEFILTER(dim_date[wn]),

SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))

VAR revcw = CALCULATE([ADR],dim_date[wn]= selv)

VAR revpw =  CALCULATE([ADR],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

RETURN

DIVIDE(revcw,revpw,0)-1

  • Realisation WoW change % = 

VAR selv = IF(HASONEFILTER(dim_date[wn]),

SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))

VAR revcw = CALCULATE([Realisation %],dim_date[wn]= selv)

VAR revpw =  CALCULATE([Realisation %],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

RETURN

DIVIDE(revcw,revpw,0)-1

  • DSRN WoW change % = 

VAR selv = IF(HASONEFILTER(dim_date[wn]),SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))

VAR revcw = CALCULATE([DSRN],dim_date[wn]= selv)

VAR revpw =  CALCULATE([DSRN],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

RETURN

DIVIDE(revcw,revpw,0)-1

Data Modelling
data1.png
data2.png
Data Sharing

     1. Visualization

​

Microsoft Power BI was used to visualize data.

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​     2. Insights

 

  • When we look at the trend by key matrix table, RevPAR and Occupancy fluctuating but ADR does not fluctuating, it is observed that Atliq Grands does not clearly apply any price strategy. They just apply a fixed price strategy.

  • Also, when we look at the week on week table, we observe that the ADR values are almost the same. This supports that Atliq Grands does not use any pricing strategy.

  • In the table where we list the hotels, when we list the occupancy rate from lowest to highest, we also observe that the average rating is low.

  • When we look at ADR rates according to Booking Platform channels, it is observed that Atliq Grands does not implement any promotion and pricing strategies, including on its own websites.

​

     3. Results

​

  • Promotions, dynamic pricing strategy or weekday and weekend pricing strategy can be used depending on the occupancy rate in summer seasons such as May, June and July or in such months.

  • It is observed that hotels with low Occupancy Rates also have low Average Ratings, so the expectations of the service provided by the hotels are not met. Accordingly, the comments can be read and examined, the problem can be evaluated and resolved.

  • Since Atliq Grands does not give commission on its website, promotions can be given to those who make hotel reservations on their website.

dash3.png

I'M BURAK KARAKAYA, A DATA ANALYST.

I'M HERE TO SHARE THE EXPERIENCES AND SKILLS I'VE GAINED THROUGHOUT MY CAREER. 

YOU CAN DOWNLOAD MY CV TO LEARN MORE ABOUT ME AND GET IN TOUCH.
THANK YOU, AND I APPRECIATE YOUR INTEREST!

B

© 2024 by Burak KARAKAYA. Powered and secured by Wix

bottom of page