Como crear un Retail Calendar 4-5-4 con SQL y dbt

Nos encargaron crear un retail calendar 4-5-4 para un cliente nuestro que tiene un e-commerce. Buscando inspiración en la web, encontramos este artículo de Calogica, que nos dio un buen punto de partida. Sin embargo, nos dimos cuenta de que este artículo había sido escrito en 2018, antes de que Calogica lanzara la increíble librería dbt_date, que nos permite crear el calendario en cuatro sencillos pasos. Nota: este procedimiento ha sido probado con éxito en Snowflake.

Paso 1: instalación de librerías

Primero se crea el archivo packages.yml con las siguientes dependencias, como se especifica en la documentación. 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

Luego se instalan las dependencias:

dbt deps

NOTA: Si se está ejecutando este paquete en una base de datos que no sea Snowflake, BigQuery, Redshift o Postgres, es posible que se deba instalar spark_utils de acuerdo con la documentación.

Paso 2: agregar variables al dbt_project.yml

En nuestro caso definimos la zona horaria (timezone) como «America/New_York». dbt_project.yml 

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

Paso 3: construir una dimensión temporal

El tercer paso es construir una dimensión temporal (como en el artículo original), pero con el paquete dbt_date podemos hacer esto usando el macro get_date_dimension (en nuestro caso elegimos materializar la tabla): 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

Paso 4: crear la tabla retail_calendar

En el artículo original, se tenía que construir la macro fiscal_year_dates. Ahora, podemos simplemente usar el macro get_fiscal_periods, con la diferencia de que el parámetro week_start_day es igual a 1 en lugar de 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

¡Eso es todo! Ahora tenés tu propio retail calendar 4-5-4. Contáctanos