How to create a 4-5-4 Retail Calendar with SQL and dbt in four easy steps

We were tasked with creating a 4-5-4 retail calendar for our client in the e-commerce industry. Searching the web for inspiration, we found this article by Claus Herther, providing a very good starting point.

However, we realized that this article had been written in 2018, before Calogica released its awesome package dbt_date, which greatly simplifies things. With this new package, we can just create the calendar in four easy steps.

Note: this procedure has been tested successfully in Snowflake.

Follow these steps to create a 4-5-4 retail calendar with SQL and dbt.

STEP 1: install the package dbt_date

First create the packages.yml file with the following dependencies as specified in the documentation

packages.yml

packages:
  - package: calogica/dbt_date
    version: [">=0.5.0", "<0.6.0"]    # <see https://github.com/calogica/dbt-date/releases/latest> for the latest version tag

And then install the dependencies by running:

dbt deps

NOTE: If you are running this package in a database other than Snowflake, BigQuery, Redshift or Postgres, you might need to install spark_utils according to the documentation.

STEP 2: add vars to the dbt_project.yml

In our case, we set “America/New_York” as the timezone

dbt_project.yml

vars:
    "dbt_date:time_zone": "America/New_York"

STEP 3: build a time dimension

The third step is to build a time dimension (just like in the original article), but with the dbt_date package. We can do this by using the get_date_dimension macro:

dates.sql

{{
    config(
        materialized = 'table'
    )
}}
with
dates as (
    {{ dbt_date.get_date_dimension("2015-01-01", "2025-01-01") }}
)
select *
from
    dates
order by 1

STEP 4: build the retail_calendar table

In the original article, we had to build the fiscal_year_dates macro. Now, we can just use get_fiscal_periods, with the difference that the parameter week_start_day is equal to 1 instead of 0:

retail_calendar.sql

{{
    config(
        materialized = 'table'
    )
}}
-- year ends in January = 1
-- weeks start on Sunday = 1
with
fp as (
    {{ dbt_date.get_fiscal_periods(ref('dates'), year_end_month=1, week_start_day=1) }}
),
fiscal_year_dates as (select * from fp
),
retail_periods as  (
    select
        date_day as calendar_date,
        fiscal_year_number as retail_year_number,
        week_start_date,
        week_end_date,
        fiscal_week_of_year as retail_week_of_year,
        fiscal_week_of_year-1 as week_num,
        -- We count the weeks in a 13 week period
        -- and separate the 4-5-4 week sequences
        mod(week_num::float, 13) as w13_number,
        -- Chop weeks into 13 week merch quarters
        least(trunc(week_num/13),3) as quarter_number,
        case
            -- we move week 53 into the 3rd period of the quarter
            when fiscal_week_of_year = 53 then 3
            when w13_number between 0 and 3 then 1
            when w13_number between 4 and 8 then 2
            when w13_number between 9 and 12 then 3
        end as period_of_quarter,
        (quarter_number * 3) + period_of_quarter as retail_period_number
    from
        fiscal_year_dates
)
select
    calendar_date,
    retail_year_number,
    week_start_date,
    week_end_date,
    retail_week_of_year,
    dense_rank() over(
        partition by retail_year_number, retail_period_number
        order by retail_week_of_year) as retail_week_of_period,
    retail_period_number,
    quarter_number+1 as retail_quarter_number,
    period_of_quarter as retail_period_of_quarter
from
    retail_periods
order by 1,2

That’s it! Now you have your own 4-5-4 retail calendar. Enjoy!

Contact Us