1 of 9

Internship Report, Summers 2021

Intern name: Vishesh Soni

Project Guide: Aditi Rawat

Campus: IIT(BHU), Varanasi

Date: 21/07/2021

2 of 9

Project 1 - Hub-level Channel-wise

Performance Management Analytics

Problem Statement:

To predict and dashboard key Business Metrics for Hub Level Channel wise Performance Management of SEA countries- Indonesia, Malaysia, Philippines, Thailand & Vietnam

Business Context:

The project is important to give insights and key actionables to Revenue heads regarding the performance of various Channels on a macro level, i.e. in all the hubs of a country.

The Project may also help them in Macro level Root Cause Analysis of Performance issues in the country.

Key Deliverables:

Accurate MTD Prediction and live Dashboarding of the 4 key business metrics namely, Sellable Room Nights(SRNs) per day, %Occupancy, Average Room Rate & Realised GMV for each hubs of SEA countries.

Strategy to perform Macro Level Root Cause Analysis to find insights to generate key actionables by the Revenue Heads

Timeline:

9 June- 30 June

So, the Intern was mainly divided into two projects apart from minor tasks for getting a deeper understanding of the dataset and getting used to the tech stack being used like Presto, Hive, Rstudio & Data Platform.

Coming to the first Project, It was about predicting the key Business Metrics for this day based on Month to Date data. It was tabulated on Hub level and Channel wise for the SEA countries.

There were mainly 4 Key Business metrics for each Channel on a hub level, namely�1) srn per day, 2) % Occupancy, 3) Average Room Rate, 4) Realised GMV�These 4 Business Metrics give a brief overview about the Channel Performances. Just to make it clear, there were 5 channels recognised-

Growth(App & Web) 2. OTA 3. MM 4. Walkin 5. Rest

How can this data help the Revenue Team?

So, as I told these metrics give a brief overview about the Performance of various channels, hence they may use it to get insights and then key actionables for the performance of various channels.

It also helps them in Root Cause analysis of any significant movement in the metrics for a given day.

So my key deliverables were accurate MTD prediction of these metrics and this was a challenge as the dataset was live and new hotels are added everyday which may affect the aggregated stats while analysis.

Another Key deliverable was to provide a strategy to perform Macro Level Root Cause Analysis of any performance issue in the country.�

3 of 9

Project Details

Live Dashboard having predicted Hub-level Channel wise Revenue Performance Management Metrics for current day.

Having described it, let me show you a snapshot of the live dashboard made in Google sheets.

As you may see the first column shows the Hubs corresponding to each country

And then each row represents different aggregated stats for that hub

The first 4 columns show the overall predictions which include performance of all the channels.

And then Occupancy, ARR and Realised GMV are listed out for each channel.

To understand it, you may read it as follows-

For example, Bali Nusra is a hub for country Indonesia. You may see that on 20th July, 3964 srns are predicted along with 7.2% occupancy approximately and 12$ ARR. These predicted stats give a Realised GMV Projection at 3500$ approx from all the channels.

Similarly, These stats may be found out for each Channel in that hub as well. Suppose any Channel’s occupancy is predicted to jump, the supply resources may be directed there to absorb that demand successfully.

4 of 9

Procedure

Wrote an R script that fetched Property level booking data of SEA countries from Hive database connected to Metabase through SQL Queries

Pre-processed the data by using imputing methods for columns having Missing or NaN values and handled some edge cases.

Predicted Channel wise trending Key Business Metrics(SRN, URN, GMV_USD, %Occupancy & ARR) on Hub level for Channel Performance Analysis.

Automated data population from this query into the google-sheet via Google Sheets API

Used Excel functions to make a customisable dashboard from the live data in the google-sheet to help Revenue Head generate insights from the Dashboard.

The Dashboard may be used for Channel Performance Analysis as follows:
Firstly, any considerable decline or jump in metrics for given hub may be tracked by overall hub level data column.

(srn per day, trending occ, trending arr, trending GMV USD)

Then other columns may also be studies to isolate the problem to one or two columns.
To deep dive further, Channel wise data may be studied to find the channel causing this decline/jump.
Then out of the three metrics (namely Occupancy, ARR & GMV), it may be further found which metric is causing the jump and then this insight may be used to find key actionable w.r.t Channel Performance.

Coming on to the key activities performed in the project, R, SQL, Python, Presto, Hive and Google Sheets API were included in the techstack.

Wrote an R script that can automate the data fetching, pre-processing and Dashboarding steps.

Used many R libraries to perform these processes.

Coming to the need of pre-processing the data, as I told you earlier it is a live data. Hence, If a hotel has live date later than some date of this month then values would be 0 for other stats which might affect the value of other aggregated stats like % occupancy and ARR.

Hence, suitable imputing methods were used in such instances to provide accurate predictions.

After reshaping data to the desired format, it is updated in the google sheets via the Google Sheets API. Further, I used excel functions like VLOOKUP and sumifs to make customised dashboard.

Now coming on to a particular use case of the dashboard. It may be used for macro level Root cause analysis regarding the Channel Performance.

Designed a strategy to use it for such analysis.

1. The first 4 columns may be used to find any significant change in the metric for given hub.

2. Then other overall hub level columns may be tracked to isolate the problem to 1 metric

3. After isolation, channel wise metrics may be studied one by one to find the metric causing this significant change in the overall metric.

4. In this way, the team may find the main metric causing this change on that channel and may generate key actionables accordingly.

5 of 9

Project 2 - Hub,Channel & Property

level Performance Management

Problem Statement:

To dashboard key Business Metrics DoD for current month on Hub, Channel & Property level for Indonesia & Malaysia

Business Context:

The project helps the Revenue team in tracking key Business metrics DoD for a given month to find Insights and perform Analysis on different levels to find

The Project may also help them in Macro level Root Cause Analysis of Performance issues in the country.

Key Deliverables:

Live Dashboarding of following Business Metrics on Hub, Channel & Property level in different sheets.

Timeline:

5 July - 20 July

1. Realised GMV	5. ARR
2. SRNs	6. RevPar
3. URN	7. BRN
4. %Occupancy	8. % Realisation

6 of 9

Project Details

Live Dashboard showing DoD values of Key Business Metrics for current month on Hub level for Indonesia & Malaysia

7 of 9

Project Detail

Live Dashboard showing DoD values of Key Business Metrics for current month across different channels for Indonesia & Malaysia

8 of 9