Build period comparison metrics

In this guide, I will walk you through detailed steps on how to calculate period comparison metrics with Holistics modeling.

info

We released Period Comparison feature to perform this function without modeling required.

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. If you are comparing other periods, simply replace 1 year with the period of your choice e.g. 1 quarter or 1 month.

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

You need generate a list of all dates between  [min, max] of opportunity_completed date.  (using PostgreSQL's generate_series function) and combine with dim_market to create join_key.

with D as (
  select
    generate_series (
            min({{ #D.opportunity_completed}}), 
            max({{ #D.opportunity_completed}}),
            '1d')::date as date_d
  from
    {{ #historical_data 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) as join_key
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

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.