Hotel Analytics with SQL

Using SQL to Analyse Hotel KPIs

Michael Grogan

--

Source: Photo by Francesca Saraco from Unsplash

The hotel industry relies on several unique KPIs to gauge performance. One of the most important of these is RevPAR — which stands for revenue per available room.

  • RevPAR = Occupancy (%) * Average Daily Rate (ADR)

In addition, hoteliers can also use GOPPAR (or Gross Operating Profit per Available Room) to calculate room profitability.

  • GOPPAR = Operating Income/Number of Available Rooms

Often, hotel businesses can find it challenging to keep up with these metrics and identify key revenue and profitability trends over time. However, using SQL and data visualization together can be quite an effective way of analysing these metrics.

In this example, the use of SQL to calculate RevPAR and GOPPAR metrics on data from a hypothetical hotel database will be illustrated. The Python visualisation library, Seaborn, is also used to illustrate such information via heatmaps and boxplots.

Data

This example will work with two separate tables.

One contains the ADR, Occupancy, and Number of Rooms for three separate hotel brands (Brand 1, Brand 2, and Brand 3), as well as the relevant year and period.

Table name: Brands

>>> select * from brands;

brand | adr | occupancy | rooms | year | period
---------+--------+-----------+----------+------+--------
Brand 1 | 141.00 | 58.00 | 2062.00 | 2021 | Q1
Brand 2 | 74.00 | 72.00 | 10346.00 | 2021 | Q1
Brand 3 | 119.00 | 59.00 | 5551.00 | 2021 | Q1
Brand 1 | 113.00 | 74.00 | 2064.00 | 2021 | Q2
Brand 2 | 60.00 | 73.00 | 10354.00 | 2021 | Q2
Brand 3 | 124.00 | 71.00 | 5549.00 | 2021 | Q2
Brand 1 | 136.00 | 92.00 | 2064.00 | 2021 | Q3
Brand 2 | 60.00 | 80.00 | 10346.00 | 2021 | Q3
Brand 3 | 109.00 | 88.00 | 5551.00 | 2021 | Q3
Brand 1 | 130.00 | 66.00 | 2062.00 | 2021 | Q4
Brand 2 | 89.00 | 71.00 | 10351.00 | 2021 | Q4
Brand 3 | 127.00 | 70.00 | 5548.00 | 2021 | Q4
Brand 1 | 137.00 | 64.00 | 2061.00 | 2022 | Q1
Brand 2 | 78.00 | 64.00 | 10349.00 | 2022 | Q1
Brand 3 | 123.00 | 64.00 | 5551.00 | 2022 | Q1
Brand 1 | 120.00 | 75.00 | 2063.00 | 2022 | Q2…

--

--

Michael Grogan

Statistical Data Scientist | Python and R trainer | Financial Writer | michael-grogan.com