Hi Everyone,
This is my entry for this challenge.
First thanks to Enteprise Challenge Team for putting time count down (so I realize that I had much more time then I initially thought and @FPastor for putting model for this challenge and save me a lots of time so I manage to finish this submission on time. Also thanks for this interesting data set and challenge.
First I spent some time to analyze the data and to explore discrepancies among the data from data source.
Therefore some of the values are corrected like:
• Reservations – are divided: Reservations (all), Cancellation (which are cancelled in ‘normal procedures’ either via Cancellation / No show) , Error Reservation (which are In status Checked-out but without guests or without nights)
• There for Revenue is also corrected – to simplify the process I add indicator Ind InRevenue 0/1
• Agents countrows -1 (since Agent key =1 / ID=0 is actually represents null or No Agent)
• Lead time – since Reservation Date = Arrival Time – Lead time sometimes after Reservation Status I use min of this too and correct Lead Time
Additional I add hemisphere for countries (from web data sources) to calculate seasons.
Overview
Since „Clients like KPI’s“ & „Client is interested in Agent’s performance, overview“ – I add two types of KPI’s for :
• External ( Revenue, Guests, Nights, Reservations)
• Internal (Agents, Data Quality / Error bookings)
Also since „Client is interested in any other insights providing further information on Customers type (family with children, single or couples visitors) etc.„ – I additional add Clients Category
• Couple (2 Adults)
• Single (1 Adults)
• Group ( 2+Adults)
• Family with children ( Adults+ Children/Babies)
• Other (believe it or not there are also only Children and Babies with no Adults) – I suppose it is in additional rooms / reservations but I separate them details can be see later
For all Revenue, Guests, Nights KPI’s I use only stays figures (using this Ind InRevenue 0/1 indicator).
Since max Reservation Statuses are in September, Reservation Date in August 2020:
• I use YTD for Time Comparison. Also I add comparison by Arrival Date – and find out that Revenue (for realized reservation/stays) in 2020 is greather then Mar-Jul
• Use KPI’s that can be calculated no matter partially year data such as :
o Average Revenue per Guest (ARPG)
o Average Guest per Stay (AGPS)
o Average Length of Stay (ALOS)
o % Stay (realization of reservations)
Since I use Reservation Date – also had dates in 2017
Agents
In table use Top 5 Agents + No Agents + Others
- Add additional row for Others (+calculations)
- Little bit re-calculate Ranking (so I can sort by it to have Top 5 + No Agents, Other order)
Also add Free Stay (since some of reservations are in status Check-out with ADR =0)
Data Quality
With 2 types that I found
Revenue
I add this ‘summary book’ to show split by room and meal revenue + additional No Show and Non-Refund Cancellation
All charts dimensions are clickable and may use for filter.
Guests
Also ‘summary book’ to show split by new / return customers
Nights
Also ‘summary book’ to show split by weekend / weekday
- how much nights are only weekend and how much combined WE + WD
Reservations
Since there are so many countries – Top 20 and Pareto technique is used.
I also add lead time categories to see in which one are most cancellations.
Regarding color pallete I tried something new this time – I find inspiration in my Mom’s strawberry homemade ice cream and my Parents garden – blueberries, rasberries and blue geen shovel (with some minor adaptations in shades).
Link to full report:
What do you think?
Happy to have your comments and feedback.
Thank you.
Best regards,
Maja