4250 Executive Square, 2nd Floor
San Diego, CA 92037

From The Blog

How To Use A Retail Calendar To Accurately Compare Sales

Authored January 13, 2019

How often have you heard retailers, restaurants and other companies talk about “comp” sales? For many, this happens monthly or at least quarterly to outline how their stores are performing year over year.  Outlining this year over year performance though can be misleading if done incorrectly and hence the use of Retail Calendar.

Why Use a Retail Calendar?

Because sales are not even across every day of the week (depending on your sales channel they will be higher or lower on Saturday/Sunday than during the week) retailers had difficulty comparing year over year sales until the 1940s when the use of a Retail Calendar became common.  Just look at March of 2018 vs. March of 2019 (5 Saturdays and 4 Sundays vs 5 Saturdays and 5 Sundays) – is trading a non-Sunday for a Sunday good or bad for you when comparing year over year performance.

The Retail Calendar accounts for this by creating 4 quarters with 91 days 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 thus ensuring that sales for the same comparable time period have the same number of weekdays and weekends.

But wait, the math doesn’t add up… 91 X 4 = 364 not 365 which is the number of days in a year!  Yes, the Retail Calendar doesn’t equate perfectly to a Fiscal Calendar which is why once in a while you have a Retail Calendar with 53 weeks.

How To Combine Your Retail Fiscal Year Calendar And Retail Sales Data

 The NRF (National Retail Federation) 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.

So how do you incorporate this into your Data Warehouse and combine it with a fiscal calendar?

Step 1: Download the appropriate calendar from the NRF for the time period you want.  Step 2: Create a table with the retail calendar information (day, day of year number, week and week day number) like this…

Step 3: Use the code here to generate the daily calendar which couple with the LookML will create a calendar view that allows you to both look at data on a Fiscal Calendar basis but also a Retail Calendar basis and easily incorporate the proper Year over Year comparisons.

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 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”

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.

Need help with Retail Analytics?  Daasity specializes in Direct to Consumer analytics helping companies with their omnichannel analytics and data warehousing. Contact us to learn more.

Do Share!