In this guide, I will walk you through detailed steps on how to calculate period-over-period metrics with Holistics modeling. Note - We are developing a period-over-period comparison feature to perform this function without modeling required.
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.
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
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 model
bookings: booking model
bookings_ly: last year booking model
bookings_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
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 (
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.
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. If you are comparing other periods, simply replace
1 year with the period of your choice e.g.
1 quarter or
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
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.
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
Business users can change the date aggregation here to change the length of the period being compared e.g.
Year will compare the whole of 2021 to 2020,
Quarterwill compare Q1 2021 to Q1 2020, Q2 2021 to Q2 2020, and so on.