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.

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

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

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

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

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.

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:

Year-over-Year comparison by month

Year-over-Year comparison by market

Final Dashboard