Hi everyone.
This is my entry for this challenge. Thank you so much.
3 Likes
Hi everyone, how are you doing?
Please find below my entry for this challenge installment.
First of all, huuge congrats everyone - absolutely phenomenal reports, just mind blowing how quickly you raise a bar…Well done friends.
Here a brief description of my report:
Reversely to my previous report (challenge #21) I wanted to switch between my standard dark-themed dashboard to ligh-theme layout, with more simplistic report structure.
It’s a single page report (all made within one page), with three subpages (analyses) in total: main view, breakdown by agent, breakdown by localization. I leave some space for future further development. It was a really interesting database (congrats @FPastor, amazing challenge) and I would definitely like to put a few additional pages with different insights.
Some features implemented in the report:
View #1:
Overall view (timelines):
Two different slicer selections with several KPIs, that are separately displayed on two column charts. It leaves end-user with possibility to select and compare two different, relevant metrics.
Additionally, there is possibility to: a) change date granularity, b) display given constant line (either min, avg, max, some of them or none) and c) higlight lowest and highest value in respect to granularity and metric selection.
Bar chart (on the right side) allows to show two important metrics (in that scenario so called Gross Revenue and Net Revenue) on one chart, and with selected breakdown (by hotel type, segment, etc) - to give a quick insights. We can also choose whetever we want to show only PY values, only current values, YoY nominal variance or relative variance, all with different color formatting options.
We can also have two buttons (left/right arrows) to change so called “quick insights” (switching between views)
View #2: & View #3
Both views combine tabluar views (which shows aggregated value for given period for given category: localization or agent). It’s accompanied by small multiple column charts to display given localization or agents on timelines
I will be revealing more niuances along the way.
Below I’m sharing link to interactive version.
All best to everyone!
9 Likes
Hi everyone, this is an entry submitted by Lindsay Kriss via email.
Here’s how Lindsay Kriss described it:
"I really enjoyed this challenge. It was a very interesting dataset with some interesting insights I didn’t expect. I approached this looking at it from how the company would increase future revenues and decrease cancellations/no-shows. "
You can view the full report here
Lindsay Kriss
Please feel free to give Lindsay Kriss your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Wanderson Barbosa via email.
Here’s how Wanderson Barbosa described it:
"I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it."
You can view the full report here
Wanderson Barbosa
Please feel free to give Wanderson Barbosa your feedback on his report.
1 Like
Hi everyone, this is an entry submitted by Azadeh Rad via email.
Here’s how Azadeh Rad described it:
I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it. Thank you so much for providing this opportunity to practice."
You can view the full report here
Azadeh Rad
Please feel free to give Azadeh Rad your feedback on his report.
1 Like
Hi everyone, this is an entry submitted by Wan Teck Chung via email.
Here’s how Wan Teck Chung described it:
I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it. Thank you so much for providing this opportunity to practice."
You can view the full report here
Wan Teck Chung
Please feel free to give Wan Teck Chung your feedback on his report.
1 Like
Hi everyone, this is an entry submitted by Shamali Sawant via email.
Here’s how Shamali Sawant described it:
"I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it. Thank you so much for providing this opportunity to practice."
You can view the full report here
Shamali Sawant
Please feel free to give Shamali Sawant your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Solar Zhu via email.
Here’s how Solor Zhu described it:
“I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it. Thanks to Federico and work with the error bars visualization tricks.”
You can view the full report here
Solar Zhu
Please feel free to give Solar Zhu your feedback on her report.
2 Likes
Hi everyone, this is an entry submitted by Argenis via email.
Here’s how Argenis described it:
“I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it."
You can view the full report here
Argenis
Please feel free to give Argenis your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Sebastiaan Gaastra via email.
Here’s how Sebastiaan Gaastra described it:
“I really enjoyed this challenge. It was a very interesting dataset with some interesting insights. I hope you like it.”
You can view the full report here
Sebastiaan Gaastra
Please feel free to give Sebastiaan Gaastra your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Filip Krzykoski via email.
Here’s how Filip Krzykoski described it:
“In the report I have exploited the idea of field parameters as much as I could. When it comes to “twinkle” Logo it is a GIF Wallpaper for each page with Hotel Neon Theme. Hope you will like it.”
You can view the full report here
Filip Krzykoski
Please feel free to give Filip Krzykoski your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Muhammad Ahmed via email.
Here’s how Muhammad Ahmed described it:
"Please see my entry for Challenge 22. Excellent Dataset, I’ve learned a lot as always. Hope you like it."
You can view the full report here
Muhammad Ahmed
Please feel free to give Muhammad Ahmed your feedback on her report.
1 Like
Hi everyone, this is an entry submitted by Pa Pa via email.
Here’s how Pa Pa described it:
“Please see my entry for Challenge 22. Excellent Dataset, I’ve learned a lot as always. Hope you like it.”
You can view the full report here
Pa Pa
Please feel free to give Pa Pa your feedback on his report.
2 Likes
Hi everyone, this is an entry submitted by Omar Sherif via email.
Here’s how Omar Sherif described it:
“It was an amazing experience and challenging in the same as i managed to complete it in 2 days! I performed a report doing customer analysis, revenue analysis, and Agents Performance Analysis”
You can view the full report here
Omar Sherif
Please feel free to give Omar Sherif your feedback on his report.
1 Like
Hi Enteprise on Demand Subscribers,
Enterprise DNA Challenge 22 is officially closed.
Another remarkable EDNA Challenge.
First and most important, we have reached over 2274 group members during data challenge 22!!
Let us revisit some highlights happened during Challenge 22:
Our data challenge hub grew up to 2274 members within 7 months.
We launched 40 + posts in EDNA Challenge Community Group.
We engaged 58,048 post views.
We interacted over 300 comments.
We accepted 51 finals entries.
Our participants have produced well 200+ analysis pages.
Our participants’ reports contain more 375+ DAX measures, well over 100+ Navigation buttons, Filed Parameters, Bookmarks and Tool tips.
The variety of native and customs visuals in the reports is a mastery of Data Visualization.
All highlights cannot be achieved without your dedication. You are the owner of our community; we love your passion and motivation on your data journey!
Welcome your invitations to your colleagues or friends, we can be better and stronger with more data friends!
Congratulations to all participants, you have done excellently well. Enterprise DNA thanks you for your efforts and dedication.
A round of applause to the Enterprise DNA Community Lead Team for a tremendous effort in the making and coordination of this challenges.
Let us say goodbye to Challenge 22 and wait for the winner announcement and Challenge 23 soon!
Federico Pastor
Head of Platform Experience and Data Challenges
Participant Entries_C22.pdf (6.0 MB)
1 Like
Really nice, clean report!
3 Likes
Hi everyone
Apologies for posting this with some delay.
Just want to go through some of the data cleaning steps and some assumptions used while attempting Challenge 22. The section wise details are
- Data Anomalies
This part has already been highlighted by a few challenge participants and is related to the reservation status portion. There are 2 columns in the raw dataset which indicate this. is_canceled and reservation_status.
On closer inspection of these 90 records, it was seen that reservation_status column was giving the incorrect picture of these records as the Check-Out records could not have a reservation_status_date which was less than the arrival_date as shown in the image.
So we decided to split the Fact table into 2 separate fact tables named Hotel Revenue and Cancellations with is_cancelled = 0 records (89108 records) filtered in Hotel Revenue and is_cancelled=1 (52477) in Cancellations. This also allowed us to retain fewer columns in Cancellations table as some of the columns were not required for revenue calculation analysis.
Another anomaly in the dataset for Checkout records was some of the discrepancies in Nights(Weekday) and Nights(Weekend) records like highlighted below. The Arrival Date and reservation_status_date columns have the same date value but Nights(Weekday) and Nights(Weekend) show a stay of 7 days.
In order to check for such discrepancies, another column named Duration of Stay was calculated by subtracting Arrival Date from reservation status date using
= Table.AddColumn(#“Filtered Rows”, “Duration of Stay”, each Duration.Days([reservation_status_date] - [Arrival Date]))
It was found that some of records had negative values for this column.
On filtering the records it was found that there was a logical discrepancy in the data entries for December 2019. For some records that had Arrival Date in December 2019 and reservation status date in 2020, the system had mistakenly put the reservation status date as 2019.
In order to remove the error for these records, a condition was applied where the reservation status date for these records was corrected by adding 365 to the Arrival Date
Table.AddColumn(#“Changed Type1”, “Checkout Date”, each if [Duration of Stay] < 0 then Date.AddDays([reservation_status_date],365)
** Duration of Stay** column was used for subsequent calculations and where the Arrival Date and reservation status date were the same (Duration of Stay = 0), the value was replaced as 1 so that revenue calculations do not result in 0 output.
- Data Model
Based on the data cleaning methodology above, the data model was modified to have separate Fact tables for Checkouts and Cancellations
- DAX Calculations
For all calculations, the discount rate was applied on both the Room Rent as well as Meal charges.
The logic used to compute Room Rent was to multiply Avg Daily Rate with duration of stay and discount applicable for that market segment.
Room Rent =
SUMX(‘Hotel Revenue’,
(‘Hotel Revenue’[AVG Daily Rate] * ‘Hotel Revenue’[Duration of Stay] * (1 - RELATED(‘Market Segment’[Discount]))))
The Room Rent for Checkouts was filtered based on date to just compute values of Room Rent generated from Checkouts.
Room Rent Checkout =
VAR CurDate =
LASTDATE ( ‘Dates’[Date] )
VAR CheckOut =
CALCULATE (
[Room Rent],
FILTER ( ALL ( ‘Dates’ ), ‘Dates’[Date] == CurDate ),
USERELATIONSHIP ( ‘Dates’[Date], ‘Hotel Revenue’[Checkout Date] )
)
RETURN
CheckOut
For the Meal Cost, similar logic was applied on a per Adult basis only.
Meal Cost = SUMX(‘Hotel Revenue’, ‘Hotel Revenue’[Adults] * RELATED(‘Meal Cost’[Cost]) * (1 - RELATED(‘Market Segment’[Discount])))
Meal Cost Checkout =
VAR CurDate =
LASTDATE ( ‘Dates’[Date] )
VAR CheckOut =
CALCULATE (
[Meal Cost],
FILTER ( ALL ( ‘Dates’ ), ‘Dates’[Date] == CurDate ),
USERELATIONSHIP ( ‘Dates’[Date], ‘Hotel Revenue’[Checkout Date] )
)
RETURN
CheckOut
The total revenue generated was computed by adding the Room Rent and Meal Cost and aggregated on a daily basis
Rev. Generated (Checked Out) = [Room Rent Checkout] + [Meal Cost Checkout]
Total Revenue Generated =
SUMX(
ADDCOLUMNS(
VALUES(‘Dates’[Date]),
“Rev. Generated”, [Rev. Generated (Checked Out)]
),
[Rev. Generated]
)
Just shared these assumption and data cleaning steps with the community to get feedback and a better understanding of the dataset. I think there were a few other things that could have been done but were not done due to time constraints.
Kindly provide feedback or suggestions for further improvements.
Best Regards
Team MAK2
2 Likes
Sorry I have been unable to reply to you sooner. Thank you for your input.
We do normally provide a basic data dicitionary within our challenges documentation and expect participants to exercise due research in the specific subject matter and or ask questions here and in the LinkedIn group to clarify any doubt they may have. Just like real work!
I hope this helps and once more thanks for your input.
Federico
Hi @Alvi and Team MAK2 members,
Thanks for sharing very interesting inside of your work.
My assumption for this data set dates were:
-Arrival date - date of arrival
Reservation status date - date of Reservation Status - date when reservation status changed
So to calculate Reservation date I use Lead time but also there are some discrepancies.
So I calculate Reservation Date = Arrival Time - Lead time, but I checked also Reservation status date and used min of this too (and correct Lead time). Back and forwards approach 
To compare with Meal charges I use Meal revenue term - and calculate for Adults but add also for Children (since I personally always need to paid for them too (how much they eat no wonder - but that is another story). Only I didn’t paid when they were babies.
I didn’t get idea to separate Checkout and Cancellations in separate tables but I use indicator Ind Revenue instead - to have 0 for both is_cancelled + error in booking (to have Check-out status with nights and 0 guests also separated) to calculate everything afterwards.
Very interested how same data set can cause different approaches.
Best regards,
Maja
3 Likes
Thanks for sharing this @mspanic.
I agree the approaches can be different and it is always good to share your underlying assumptions as well as any data cleaning or transformation steps that have been applied.
2 Likes