Dashboard for Sales Trends in Retail

Overview

Retail is probably the most talked about industry when it comes to disruption these days. Empty malls are a common blog topic and unusually high number of bankruptcies span across all subsectors. Some of the familiar names that filed for bankruptcy in the last few years span from well know Sears, ToysRUs, Limited Brands to lesser known Aerosoles, Gander Mountain and the Walking Company. Given all this hyper activity and my interest in Retail, when I had to pick a topic for my Visualization project I quickly jumped on to a readily available Retail dataset for exploration. Exploring this dataset has helped me understand the industry better and has also thrown some surprise insights at me.

Dataset Details

Federal Reserve of Saint Louis publishes various data series related to Retail Sales data every month. They also have this data available via API and excel add-in from their FRED system. Same data is also available from U.S. Bureau of the Census. I have downloaded the data available till Oct 2018 for my project and building Shiny Dashboard WebApp.

Exploring the data using the WebApp, we can observe that Inventory data is available only for very few Sub Sectors. Even though we can analyze the inventory levels for these Sub Sectors, we will not be able to draw overarching conclusions at Retail level. 

Dashboard Purpose

I quickly realized that one of the aspects of building a dashboard is to be clear about the purpose of the dashboard as that impacts the type of interactivity to be built. Some of the ways a dashboard can be used are:

1. Constant data points like metrics but changing data: For example, a dashboard showing metrics and current work progress on a shop floor. The goal here is not to explore data using the dashboard, but to consume insights
2. Constant data and constant metrics: This is more of a onetime dashboard.  Presenting research findings could be an example here. Here the researcher has done all the exploratory analysis and presenting a set of conclusions. Again, the goal here is to consume insights and not explore data in depth. Another example is this blog post where insights as of 2018 Q3 data are being posted.
3. Explore Data that is refreshed regularly: Analyzing Retail Sales data (topic of this blog) is an example. As fresh data gets loaded every month, it is not entirely clear what new insights are waiting to be found unless someone explores the data. So, the dashboard should help explore the data by slicing and dicing the data and applying some statistical measures. Tools like Tableau are well suited for the task. The WebApp I have built using R and Shiny tries to emulate this to a limited extent.

Retail Level Observations 

Retail Industry as a whole is growing at faster clip than the economy. It has grown from $3.5 trillion in 2007 (pre-2008 crisis) to about $4.5 trillion at end of 2017 as seen from the chart below. This is about 2.9% annualized growth, well above U.S. GDP growth over same time, for lack of better comparison. So, the numbers do not support the notion of disruption as generally perceived and understood. Retail industry is not going to diminish in size substantially or vanish in a traditional sense of “disruption”, like tape, CD/DVD manufacturers being disrupted by streaming or potential of blockchain to disintermediate entire industries. Disruption here actually means the customer interaction models and tool used (like AI, newer delivery models etc) are changing and the is going to change the mix of dominant players. But Retail as industry is consumption based and the net retail dollars are not going anywhere.

eCommerce vs Overall Retail

eCommerce accounts for about 9.8% of retail sales at end of 2018 third quarter. Note that eCommerce data is available only at Quarterly granularity. But “Non Store Retailers” sub sector loosely corresponds to eCommerce. So that sub sector data can be used for further analysis.

Even though eCommerce growth has moderated compared to early 2000’s, it is still growing 4 times faster than overall retail. What is surprising is that Retail excluding eCommerce is still growing at a 2.6% rate. This is not bad given the size of the industry. Another interesting insight is, that the eCommerce is not at all impacted in the Retail slowdown in 2015-2016.

Analyzing Sub Sectors

Barring for few changes, the Top 5 and Bottom 5 sub sectors remained fairly stable since 1992. We can see “Non Store Retailers” (read as eCommerce) emerging into Top 5.

However, positions with respect to relative size has shifted considerably for many sectors over a period. Tree Maps are useful to quickly compare relative size of large number of sub sectors. The snapshots for years 1992, 2005 and 2017 demonstrate the shift in relative positions.

It is always interesting and useful for industry analysts to know which Sub Sectors are both growth and revenue leaders and vice versa. The following chart shows the same. 

For this Growth and Revenue leader analysis above, it is better to look at limited time range (like the seasonality chart mentioned later) to still get the average numbers than point numbers and at the same time avoid skewing current trends by very old data. From the following examples, one can see how the leaders change based on the time period used.

Seasonality with Retail Data

In analyzing Retail sales and inventory data, one would generally like to understand Level, Trend and Seasonality. Level corresponds to base level on top of which Trend and Seasonality can be explained. Trend shows either upward movement or downward movement from the level over the selected period. This Trend is clearly seen in chart below. But decoding seasonality from a simple chart showing sales over period is bit difficult and error prone. 

Here I have used Linear Model to derive seasonality chart shown below. First step is to apply Linear Model (LM, LOESS etc) as applicable for the data. Next step is to collect the error terms from the model. Error terms are essentially what remains after removing the Level and Trend information from each month sales, and explains the deviations from perfectly fit model. The final step is to collect the terms by period (month of the year here) and use appropriate statistic (median in this case) to show the variation over that time intervals. 

When the data series spans a long duration, one caution while calculating current seasonality is to watch for any changes in trends of seasonality. Using all the available data all the time can mask shifting seasonality trend. 

Ideas for Future Development

Having the app pull the data at regular intervals direct from FRED would be a nice feature to add. Similarly integrating other retail data available like retail employment, eCommerce data by Subsector would make data more complete and increase the usefulness of this app. One advantage of using R (or something similar) is that all the data exploratory and statistical methods are available to play with. Hence a dashboard like this can be enhanced to create insights that can act as trigger points further actions as new data arrives. Though a lot more can be done with this WebApp, the current version showcases that a lot can be achieved quickly using simple toolset these days.

Links

FRED Economic Data from St. Louis Fed

Shiny Dashboard WebApp