How to build year-over-year metrics
In this guide, I will walk you through detailed steps on how to calculate year-over-year metrics with Holistics modeling.
Context
You are working for a travel agency. Let's say you have a table/model called historical_data
which records all of your bookings and their value.
Your Business Users want to measure the growth in revenue as a percentage of the previous year. As an analyst, you want to create the solution in a modular way so it can be used to generate other reports via drag-and-drop without additional coding.
- Calculate Year-over-Year Revenue comparison for each destination (country).
- Calculate the revenue growth for the same period last year.
High-level approach
You want a good amount of flexibility for the business users to perform this themselves so that they can apply further adjustment, filtering, or aggregation as they see fit.
The solution also needs to be dynamic so comparisons can be made across a time period, not limited to just the current year and the previous year.
The general approach is to join bookings
, bookings_ly
, and dims
table using a created common join_key
field in all 3 models (concatenated destination market and date).
Measures and calculated dimensions will be created as and when necessary and can be reused in other reports.
We create 4 models:
dims
: dimension modelbookings
: booking modelbookings_ly
: last year booking modelbookings_yoy
: year-over-year booking model
Basically, you will need a bookings
model to calculate the revenue in the current period (revenue), another model (called bookings_ly
) to calculate the revenue but in the same period last year (revenue_ly).
In order to get the single entry point for 2 measures revenue and revenue_ly, you need a dimension model (called dims
model) and join it with bookings
and bookings_ly
.
Next, if you want to calculate the Year-over-Year growth (%), it's necessary to create another model bookings_yoy
, join all three models above (dims
, bookings
, bookings_ly
) and calculate revenue_yoy (= bookings / bookings_ly).
Finally, create the dataset, add the bookings_yoy
model to the dataset and it will be ready to be explored.
Detailed Steps
Create Bookings model
bookings
- extracted bookings from the raw data
In the booking model, create one custom dimension for join key and one measure to calculate revenue
Create Last Year Bookings model
bookings_ly
- modified version of the bookings
model to account for previous years
In the booking_ly
model, create one custom dimension for join key and one measure to calculate revenue
Create Dimension model
dims
- a created common model to be the base of JOIN operations
Create Year-over-Year growth model
Using the join_key
which is created for all three models, we combine all 3 models into a single bookings_yoy
model for YoY% calculations.
Create the 1 custom dimension for the join_key 3 measures to calculate total revenue, revenue last year and revenue year-over-year.
Create Dataset
Business users can now drag and drop to explore the data. They can also use filters and set conditions so that they can do comparisons across different years or drill down into specific markets, all without any additional code:
Year-over-Year comparison by month
Year-over-Year comparison by market