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 retail calendar 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 calendar from the NRF 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.
Find the code here.
- 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”
Find the code here.
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.
About Daasity
Daasity is transforming the way companies access and use their data. It is the first and only company to design a proprietary platform specifically for the direct-to-consumer industry that makes business-critical data accessible and usable for strategic decision-making. The company’s mission is to make business-critical data accessible for all DTC brands. Visit www.Daasity.com or LinkedIn.
