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.

Historical Data

Table historical_data {
field id integer
field opportunity_created date
field opportunity_completed date
field destination_market varchar
field commission_price_usd real
}

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 model
  • bookings : booking model
  • bookings_ly : last year booking model
  • bookings_yoy : year-over-year booking model

Modeling approach

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

Bookings Model

select
{{ #D.id }},
{{ #D.opportunity_completed }} as date_d,
{{ #D.dim_market }} as dim_market,
{{ #D.commission_price_usd }}
from
{{ #historical_data as D }}
where
{{ #D.opportunity_completed }} is not null

In the booking model, create one custom dimension for join key and one measure to calculate revenue

Model bookings {
--Base field
field id integer
field date_d date
field dim_market varchar
field commission_usd real
--Custom field
field join_key = "CONCAT(dim_market,'_',date_d::varchar)"
measure revenue = "SUM(commission_price_usd)"
}

Create Last Year Bookings model

bookings_ly - modified version of the bookings model to account for previous years

Booking Last Year Model

select
{{ #bookings.id }},
({{ #bookings.date_d }} - '1 year' :: interval) :: date as date_d,
{{ #bookings.dim_market }},
{{ #bookings.commission_price_usd }}
from
{{ #bookings }}

In the booking_ly model, create one custom dimension for join key and one measure to calculate revenue

Model bookings_ly {
--Based Field
field id
field date_d date
field dim_market varchar
field commission_usd
-- Custom Field
field join_key = "CONCAT(dim_market, '_',date_d::varchar)"
measure revenue_ly = "SUM(commission_price_usd)"
}

Create Dimension model

dims - a created common model to be the base of JOIN operations

Dim model

with D as (
select
{{ #D.date_d }}
from
{{ #dim_dates D }}
),
B as (
select
distinct {{ #B.dim_market }} as dim_market
from
{{ #bookings B }}
)
select
{{ #D.date_d }},
{{ #B.dim_market }},
CONCAT({{ #B.dim_market }},'_',{{ #D.date_d }}::varchar)
from D
cross join B

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.

Year over Year model

select
{{ #D.date_d }},
{{ #D.dim_market }},
{{ #BA1.revenue }} as revenue,
{{ #BA2.revenue_ly }} as revenue_ly
from
{{ #dims D }}
left join {{ #bookings BA1 }} on {{ #D.join_key }} = {{ #BA1.join_key }}
left join {{ #bookings_ly BA2 }} on {{ #D.join_key }} = {{ #BA2.join_key }}
group by
1, 2

Create the 1 custom dimension for the join_key 3 measures to calculate total revenue, revenue last year and revenue year-over-year.

Model bookings_yoy {
--Base Field
field date_d date
field dim_market varchar
field revenue real
field revenue_ly real
--Custom Field
measure sum_revenue = "SUM(revenue)"
measure sum_revenue_ly = "SUM(revenue_ly)"
measure revenue_yoy = "sum({{$this.sum_revenue}}) / NULLIF(sum({{$this.sum_revenue_ly}}), 0) - 1"
}

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:

Date d dataset Year-over-Year comparison by month

Market Dataset Year-over-Year comparison by market

Final Dashboard