This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

How to combine a retail calendar with retail sales data

on ‎03-27-2022 11:03 PM

LookerExperts
Staff
0 0 723

This content, written by Dan LeBlanc, was initially posted in Looker Blog on Jan 13, 2019. The content is subject to limited support.

How often have you heard retailers, restaurants, or other companies talk about “comp” sales? In retail, comparable store sales indicate the performance of a company based off of sales from the previous period. For many, this happens quarterly or even monthly, and outlines how a store is performing year over year. However, year over year performance can be misleading if calculated incorrectly, which is why the use of a retail calendar is so important.

Why use a retail calendar?

Depending on your sales channel, sales may not be even across every day of the week due to varying dates between different months and years. Historically, this has made it difficult to compare year over year sales until the 1940s, when the use of a became common.

The retail calendar allows you to compare sales across different time periods to help predict sales. Retail calendars do this by creating four quarters with 91 days in each, in either a 4-5-4 or a 4-4-4 format (i.e. a month with 4 weeks followed by a month with 5 weeks and finally a month with 4 weeks). This ensures that every month has the same number of weekends as the same month in the prior year, so that sales for the same comparable time period have the same number of weekdays and weekends. Additionally, the National Retail Federation (NRF) uses a 4-5-4 calendar and adjusts the start of the calendar year to ensure that major holidays are reflected in the same time period for proper comparisons.

How to combine your retail fiscal year calendar and retail sales data

Below is a step-by-step process detailing how to combine the two calendars to view all your retail sales data in one place.

Step 1: Download the appropriate for the time period you want.

Step 2: Next, create a table in your database with at least the following retail calendar information:

Retail Calendar Information To Download

  • Day
  • Day of Year Number
  • Week
  • Week Day Number

Step 3: From here, use the SQL code below to determine the current week and current week day number.

-- GENERATION OF THE RETAIL CALENDAR BASED ON NRF CALENDAR AND PIVOTS

--------------------------------------------------------------------------------
-- INITIAL CODE
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS drp.calendar
(
calendar_date TIMESTAMP WITHOUT TIME ZONE,
retail_day_of_week BIGINT,
retail_week BIGINT,
retail_week_day_number VARCHAR(64),
calendar_month BIGINT,
retail_year BIGINT,
this_yesterday INT,
this_week INT,
this_month INT,
this_quarter INT,
this_year INT,
yesterday_last_year INT,
this_week_last_year INT,
this_month_last_year INT,
this_quarter_last_year INT,
this_year_last_year INT,
yesterday VARCHAR(32),
week VARCHAR(32),
wtw VARCHAR(32),
month VARCHAR(32),
mtm VARCHAR(32),
quarter VARCHAR(32),
year VARCHAR(32),
_loaded_at  TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY(calendar_date)
)
SORTKEY(calendar_date)
;


--------------------------------------------------------------------------------
-- MAIN CODE
--------------------------------------------------------------------------------
TRUNCATE TABLE drp.calendar;

INSERT INTO drp.calendar
WITH
    yesterday AS
(
    SELECT *
    FROM calendar.retail_calendar
    WHERE calendar_date = DATEADD(DAY, -1, CONVERT_TIMEZONE('UTC', 'PST',GETDATE())::DATE)
),
    date_flag AS
(
SELECT
    this_year.calendar_date AS this_year_date,
    last_year_week.calendar_date AS last_year_date,
    last_year.calendar_date AS last_year_date_2,
    this_year.retail_day_of_week,
    this_year.retail_week,
    this_year.retail_week_day_number,
    this_year.calendar_month,
    this_year.retail_year,
    CASE WHEN this_year.calendar_date = yesterday.calendar_date THEN 1 ELSE 0 END AS yesterday,
    CASE WHEN this_year.calendar_date = yesterday.calendar_date THEN last_year_week.calendar_date ELSE NULL END AS yly,
    CASE WHEN this_year.retail_day_of_week <= yesterday.retail_day_of_week AND this_year.retail_week = yesterday.retail_week
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS this_week,
    CASE WHEN this_year.retail_day_of_week <= yesterday.retail_day_of_week AND this_year.retail_week = yesterday.retail_week-1
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS last_week,
    CASE WHEN this_year.retail_day_of_week <= yesterday.retail_day_of_week AND this_year.retail_week = yesterday.retail_week
         AND this_year.retail_year = yesterday.retail_year THEN last_year_week.calendar_date ELSE NULL END AS wly,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year AND this_year.calendar_month = yesterday.calendar_month
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS this_month,
    CASE WHEN DATE_PART(day, this_year.calendar_date) <= DATE_PART(day, yesterday.calendar_date) AND this_year.calendar_month = yesterday.calendar_month-1
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS last_month,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year AND this_year.calendar_month = yesterday.calendar_month
         AND this_year.retail_year = yesterday.retail_year THEN last_year.calendar_date ELSE NULL END AS mly,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year AND this_year.calendar_quarter = yesterday.calendar_quarter
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS this_quarter,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year AND this_year.calendar_quarter = yesterday.calendar_quarter
       AND this_year.retail_year = yesterday.retail_year THEN last_year.calendar_date ELSE NULL END AS qly,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year
         AND this_year.retail_year = yesterday.retail_year THEN 1 ELSE 0 END AS this_year,
    CASE WHEN this_year.retail_day_of_year <= yesterday.retail_day_of_year
         AND this_year.retail_year = yesterday.retail_year THEN last_year.calendar_date ELSE NULL END AS ly
FROM calendar.retail_calendar this_year
LEFT JOIN calendar.retail_calendar last_year_week
    ON this_year.retail_day_of_week = last_year_week.retail_day_of_week
    AND this_year.retail_week = last_year_week.retail_week
    AND this_year.retail_year = last_year_week.retail_year + 1
LEFT JOIN calendar.retail_calendar last_year
  ON this_year.calendar_date = DATEADD(year, 1, last_year.calendar_date)
    AND DATEPART(MONTH, this_year.calendar_date) = DATEPART(MONTH,last_year.calendar_date)
CROSS JOIN yesterday
)
SELECT
    retail_calendar.calendar_date,
    rc.retail_day_of_week,
    rc.retail_week,
    rc.retail_week_day_number,
    rc.calendar_month,
    rc.retail_year,
    COALESCE(rc.yesterday, 0) AS this_yesterday,
    COALESCE(rc.this_week, 0) AS this_week,
    COALESCE(rc.this_month, 0) AS this_month,
    COALESCE(rc.this_quarter, 0) AS this_quarter,
    COALESCE(rc.this_year, 0) AS this_year,
    CASE WHEN week.yly IS NOT NULL THEN 1 ELSE 0 END AS yesterday_last_year,
    CASE WHEN week.wly IS NOT NULL THEN 1 ELSE 0 END AS this_week_last_year,
    CASE WHEN month.mly IS NOT NULL THEN 1 ELSE 0 END AS this_month_last_year,
    CASE WHEN quarter.qly IS NOT NULL THEN 1 ELSE 0 END AS this_quarter_last_year,
    CASE WHEN year.ly IS NOT NULL THEN 1 ELSE 0 END AS this_year_last_year,
    CASE
        WHEN rc.yesterday = 1 THEN 'This Year'
        WHEN CASE WHEN week.yly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'Last Year'
    END AS Yesterday,
    CASE
        WHEN rc.this_week = 1 THEN 'This Year'
        WHEN CASE WHEN week.wly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'Last Year'
    END AS Week,
    CASE
        WHEN rc.this_week = 1 THEN 'This Week'
        WHEN CASE WHEN week.wly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'This Week Last Year'
        WHEN rc.last_week = 1 THEN 'Last Week'
    END AS wtw,
    CASE
        WHEN rc.this_month = 1 THEN 'This Year'
        WHEN CASE WHEN month.mly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'Last Year'
    END AS Month,
    CASE
        WHEN rc.this_month = 1 THEN 'This Month'
        WHEN CASE WHEN month.mly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'This Month Last Year'
        When rc.last_month = 1 THEN 'Last Month'
    END AS mtm,
    CASE
        WHEN rc.this_quarter = 1 THEN 'This Year'
        WHEN CASE WHEN quarter.qly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'Last Year'
    END AS Quarter,
    CASE
        WHEN rc.this_year = 1 THEN 'This Year'
        WHEN CASE WHEN year.ly IS NOT NULL THEN 1 ELSE 0 END = 1 THEN 'Last Year'
     END AS Year,
     GETDATE() AS _loaded_at
FROM calendar.retail_calendar
LEFT JOIN date_flag rc ON rc.this_year_date = retail_calendar.calendar_date
LEFT JOIN date_flag week ON retail_calendar.calendar_date = week.wly
LEFT JOIN date_flag month ON retail_calendar.calendar_date = month.mly
LEFT JOIN date_flag quarter ON retail_calendar.calendar_date = quarter.qly
LEFT JOIN date_flag year ON retail_calendar.calendar_date = year.ly
ORDER BY retail_calendar.calendar_date
;


-- DATA VALIDATION
INSERT INTO drp.data_validation
SELECT
    CAST('010: Retail Calendar' AS TEXT) AS tablename,
    COUNT(*) AS total,
    COUNT(DISTINCT(calendar_date)) AS unique,
    NULL AS last_synced_at,
    MAX(_loaded_at) AS last_load_date
FROM drp.calendar
;
  • The code does this by looking at the current date and finding the corresponding dates for that same week and week day number from last year.

This allows you to easily filter out the days that correspond to the current year and previous year, making for easy year over year comparison.

Important Note: You will need to run this code daily as either a Looker Persistent Derived Table (PDT) or as a scheduled job in your database to ensure that the flags in the table are updated daily to show the correct year over year values for yesterday, this week, and last week.

Step 4: Now you’ll need to incorporate the LookML below with the daily calendar table, which will create three sets of dimensions:

  • Calendar: this has all the different dimensions using the fiscal calendar
  • Retail: this has all the different dimensions from the retail calendar
  • Pivots: these allow you to show the year over year comparisons by simply filtering and choosing “This Year” and “Last Year”
  view: calendar {
  sql_table_name: drp.calendar ;;
  dimension_group: calendar {
    type: time
    convert_tz: no
    timeframes: [raw, time, date, day_of_week, day_of_week_index, week, month, month_name, month_num, quarter, year]
    sql: ${TABLE}.calendar_date ;;
  }
  # Filters
  filter: previous_period_filter {
    type: date
    convert_tz: no
    description: "Use this filter for period analysis - i.e. select This Year vs. Last Year for the Period using Pivot #6 "
  }
  dimension: previous_period {
    type: string
    label: "6 - Pivot by Previous Period Selected"
    description: "The reporting period as selected by the Previous Period Filter and Add a Filter for 'Dimension is Not Null'"
    group_label: "Pivots"
    sql:
      CASE
        WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
          THEN
            CASE
              WHEN ${calendar_date} >=  {% date_start previous_period_filter %}
                AND ${calendar_date} < {% date_end previous_period_filter %}
                THEN 'This Period'
              WHEN ${calendar_date} >= DATEADD (year, -1, {% date_start previous_period_filter %} )
                AND ${calendar_date} < DATEADD (year, -1, {% date_end previous_period_filter %} )
                THEN 'This Period Last Year'
            END
          END ;;
  }
# Dimensions
  dimension: yesterday {
    type: string
    label: "1 - Pivot by Yesterday"
    description: "Pivot Dataset by Yesterday: TY vs LY. To Use, Pivot This Field and Add a Filter for 'Dimension is Not Null'"
    group_label: "Pivots"
    sql: ${TABLE}.yesterday ;;
  }
  dimension: wtw {
    type: string
    label: "2 - Pivot by Week"
    description: "Pivot Dataset by Current Week: TY, LY, & LW. To Use, Pivot This Field and Add a Filter for 'Dimension is Not Null'"
    group_label: "Pivots"
    sql: ${TABLE}.wtw ;;
  }
  dimension: mtm {
    type: string
    label: "3 - Pivot by Month"
    description: "Pivot Dataset by Current Month: TY, LY, & LM. To Use, Pivot This Field and Add a Filter for 'Dimension is Not Null'"
    group_label: "Pivots"
    sql: ${TABLE}.mtm ;;
  }
  dimension: quarter {
    type: string
    label: "4 - Pivot by Quarter"
    description: "Pivot Dataset by Current Quarter: TY vs LY. To Use, Pivot This Field and Add a Filter for 'Dimension Is Not Null'"
    group_label: "Pivots"
    sql: ${TABLE}.quarter ;;
  }
  dimension: year {
    type: string
    label: "5 - Pivot by Year"
    description: "Pivot Dataset by Current Year: TY vs LY. To Use, Pivot This Field and Add a Filter for 'Dimension is Not Null'"
    group_label: "Pivots"
    sql: ${TABLE}.year ;;
  }
  dimension: retail_day_of_week {
    type: number
    label: "Retail Day of Week"
    description: "Day of Week of Selected Date Based on Sunday Week Start (NRF Calendar)"
    sql: ${TABLE}.retail_day_of_week ;;
  }
  dimension: retail_week {
    type: number
    label: "Retail Week"
    description: "Retail Week Based on the NRF Calendar"
    sql: ${TABLE}.retail_week ;;
  }
  dimension: retail_week_day_number {
    type: string
    label: "Retail Week Day Number"
    description: "Retail Day of Week Based on the NRF Calendar"
    sql: ${TABLE}.retail_week_day_number ;;
  }
  dimension: retail_year {
    type: number
    label: "Retail Year"
    description: "Retail Year Based on the NRF Calendar"
    sql: ${TABLE}.retail_year ;;
  }
# Hidden Dimensions
  dimension: this_month {
    type: number
    hidden: yes
    sql: ${TABLE}.this_month ;;
  }
  dimension: this_month_last_year {
    type: number
    hidden: yes
    sql: ${TABLE}.this_month_last_year ;;
  }
  dimension: this_quarter {
    type: number
    hidden: yes
    sql: ${TABLE}.this_quarter ;;
  }
  dimension: this_quarter_last_year {
    type: number
    hidden: yes
    sql: ${TABLE}.this_quarter_last_year ;;
  }
  dimension: this_week {
    type: number
    hidden: yes
    sql: ${TABLE}.this_week ;;
  }
  dimension: this_week_last_year {
    type: number
    hidden: yes
    sql: ${TABLE}.this_week_last_year ;;
  }
  dimension: this_year {
    type: number
    hidden: yes
    sql: ${TABLE}.this_year ;;
  }
  dimension: this_year_last_year {
    type: number
    hidden: yes
    sql: ${TABLE}.this_year_last_year ;;
  }
  dimension: this_yesterday {
    type: number
    hidden: yes
    sql: ${TABLE}.this_yesterday ;;
  }
  dimension: yesterday_last_year {
    type: number
    hidden: yes
    sql: ${TABLE}.yesterday_last_year ;;
  }
}

Step 4: Once you’ve done all this, you can use the code to generate a daily calendar. This will create a calendar view that will allow you to look at retail data on a fiscal and retail calendar basis for seamless year over year analysis.

The end result

By combining a fiscal and retail calendar, conducting year over year analyses not only becomes easier for your organization, but also becomes more useful. With an accurate view of your year over year retail sales data, sales forecasting, monitorization of trends, and goal-oriented planning can be based on concrete data, giving strategic direction to monthly and quarterly initiatives.

Want to learn more?

Interested in learning more about using your retail calendar and retail data together at your organization? Contact the team to learn about omnichannel analytics and data warehousing solutions for retailers, or how Looker is helping retailers improve their customer experiences and increase sales with their eCommerce data.

0 Likes
Version history
Last update:
‎03-27-2022 11:03 PM
Updated by: