Data Visualization with Looker Studio

How long you can get insight from this image?

Data shown in tables with more than 100,000 datas

Then, compare it to this one

Total Booking for two hotels

Human understand image faster than writing. With proper data visualization you can deliver you insight and received faster by readers. That’s the importance of data visualization.

There are so many data visualization software out there such as Looker Studio (used to called Google Data Studio), Tableau Public, Power BI, etc. But, today I will make viz in Looker Studio by Google (Cloud Base and FREE!)

I already made one please check it here, I also wrote about SQL and Python.

We gonna this dataset. Make sure to make a copy of the dataset and save it in your own google drive or local drive. Let’s jump to Looker Studio!

On Looker Studio click Blank Report then add data, if you find difficulties when connecting data from Gdrive, just upload the data with .csv format from local drive.

First view after adding data

Google data studio is basically a drag and drop program. But, in some case you might need to create new calculation field.

1. Add page is to add more pages, you can later manage your page by clicking between range numbers shown, Add data is to add more data, Add a chart contained many type of chart that you can choose, you also can add control for you chart, url, image, text box, lines, box and change the theme of your dashboard.
2. If you click one of your chart, then you can change the type by clicking number 2
3. Data Source used by current chart
4. Date Range used by current chart to change it just click in the middle or drag and drop from number 7
5. Dimension is a group-able field. As you can see in the table shown, it group the data based on hotels or in column chart it is the x axis
6. Metric is computed field or in column chart is the y axis
7. In number 7 you will see all dimension and metrics field that you have
8. Add fields use when you want to add new calculation field
9. Parameters works as a variable which can be added by the person who used your chart

Create viz about the number of booking of both hotels

My Dimension will be hotel, and total booking as metric, then sort it by the number of booking count. To change color and setup your visual click on Style

City Hotel has more booking

Create Viz based on origin country of customer then divide it as Local and Inbound

Since country contained the name of country then we should make new calculation field by Add a field then write following formula :

Both hotel is in Portugal so portuguese will be categorized as Local, else will be inbound

Click Done and drag and drop in Dimension, you can edit you field name show in chart by hover to icons beside field name.

How to edit shown fields name

tourists who come from outside the country of Portugal (Inbound Tourist) have made more booking

Create ADR Trend Weekly from both hotels and analyze

To change the date into year hover to dates icon beside arrival date and click. Choose type, then date & time, and choose ISO Year Week

Then set up the adr to show average and currencies. Aggregation use average, since the hotel is in Portugal so I will use Euro as currencies, by clicking on type, currencies then Euro.

ADR trend between two hotel is similar, City Hotels which has more bookings made more revenue than Resort Hotel. But, in certain month Resort Hotel managed to rise their average ADR which is in August and December in every year. In this same month City Hotel got higher cancellation rate. I assume Resort Hotel made better offers for customers. Should make deeper investigation.

Show Cancellation Rate from both hotels per month

I use Time Series chart. Set up the type of Dimension into Year Month then make new calculation field like earlier.

Change Cancel Rate type into percent

For City Hotel, the most cancellation rate happens in July 2015 almost three times than Resort Hotel. The lowest cancellation rate happen in November 2015 which is 24.25%. The trend between two hotels is pretty similar in September 2015 — June 2016, even though the difference is still twice as big as a City Hotel. In August 2017 City hotel succeed to make cancellation rate lower than Resort Hotel. Cancellation Rate tend to rise in December, February, April, a little bit in August and October. December should be a holiday season which is a little bit suspicious why the cancellation rate is rising where it should be lowered.

In the other hand, Resort Hotel has lower cancellation rate than City Hotel. it might be the impact of their cancellation policy. Further investigation should be made. Their highest rate is at 43.44% (September 2015) and lowest rate at 13.39% (January 2016).

Show total cancel and cancellation rate based on Market Segment

I use combo chart, then I need to put two metrics which are Cancel Rate and Count of Total Cancel. Remember you can always change your field calculation by hover to the side of field name. I also choose market segment as dimension

Highest Cancelation Rate resulted by Undefined Market Segment but the total cancel is only two, because of customer of this type cancel all bookings made. Let’s see from Online TA even though it has highest total cancel but the rate cancellation is in the middle range.

To Lower the cancellation rate Hotels Management need to make more booking and make sure the guest doesn’t cancel. It can be done by creating tight cancellation policy, suggesting to change booking time and persuade customer to keep their booking.

Create another visualization based on the data given

I chose to visualize relation between lead time and adr, I gonna use scatter plot diagram

Lead time and ADR have a positive relation, which means customer who wait and keep the bookings most likely will proceed and check-in.

Meals Ordered Chart

Since restaurant is contributed to hotels revenue, I would like to visualize types of meal ordered by customers. Put Meal as dimension and booking count as metrics, then I change name of booking count to Total Order.

Next we need to make filter, to show data only from customer who proceed booking. By scrolling down and choose add filter. And create filter as follows :

Breakfast is favorite meals chosen by customers.

From the datas above, I make several suggestions and recommendations :

1. City Hotel already got more booking, but in August and December they got high cancellation rate while in August and December Resort Hotel made higher ADR. We can conclude that part of CH customer who is cancelled moved to RH. Management should review their cancellation policy so they can keep their customer or made better offer (additional benefit, such as parking spot, transportation coupons, trip discount, meals discount, etc) for customer who already booked for those particular months.

2. One of the biggest sources of income in a hotel is the restaurant, in order to attract customer to eat in restaurant management can offer discount for other meals type than breakfast. Since breakfast is the most favorite meal chosen by customers.

3. Biggest booking made by inbound customers, management can made some contract with Travel Agents and offer special price. Like it’s shown in page 3, most cancellation made by TA online. Tighter cancellation policy could included when arranging the contract or deposit should be made with booking

We already make column chart, time series chart, combo chart and scatter plot. To show proportions we can also use pie chart / doughnut chart but it should be less than 5 categories.

choose your color wisely and be consistent with the category that it represent.

Make a clean and simple chart is preferable since it’s easier to read and understand. Make sure to know who are your target or stakeholders and make chart according to they needs.

Reach me at Linkedin or email me amaliawira87@gmail.com

I also made stories about SQL and Pyhton if you are interested. Cheers!