Visualizing trending product SKU’s in a dashboard
In our last blog we’ve shared a code that calculates trend measures based on a standard Google Analytics export schema. If you haven’t read it yet, you can find it here. This blog shows how the output from the previous blog can be used in a dashboard that gives you a quick overview on what’s hot and what’s not.
To create a dashboard we first need a dataset on which we can do the trend analysis. For this purpose we will use the public dataset for Google Analytics in BigQuery, which is an example dataset of a Google Analytics export to BigQuery. In our last blog we showed how to do trend analysis on search keywords, sadly this dataset only contains 12 search keywords and just over 800 searches and is too limited to do a trend analysis. Therefore, we will apply the trend analysis on the number of sold productSKU’s.
A change like this in the script is fairly easy to make and shows the flexibility of this analysis. The code for this specific dashboard can be found here and the dataset itself can be found in the public datasets in google cloud.
The general setup
The dashboard is pretty straightforward. We wanted it to show you exactly what you need to gain insights and nothing more to keep it simple. Sure, many adjustments or additions can be made but those will mostly serve the need for deeper insights or added features. This is soly about finding the most trending productSKU’s in the data. See what’s hot and what’s not so you can act on it. Let’s have a look at the dashboard.
The top graph in the dashboard shows the moving average for the long term (Moving Average = 15 periods), the short term (Moving Average = 5 periods) and the actual sales . Without any filters applied, it shows whether there is a trend in the total number of sold items. In this case we can see that the short-term (light blue) line is moving just above the long-term (darkish blue) line (For explanation on this theory, we refer to our previous blog). This indicates that more people are buying the products and the trend is slightly moving upward.
The tables below the line chart show the best and worst ten products based on absolute trend measures and the relative trend measures compared to the total dataset. The absolute trend indicates how strong a product is trending based on its own trend measures. The relative trend measures also takes into account how the overall sales and other products are doing. Both are interesting since the absolute numbers show mostly products that suddenly made a move up or down and the relative numbers indicate under- or outperformers in your set.
Identifying Gainers
We can dive deeper into the data with this dashboard. Let’s pick one of the best products from the Trend Gainers and enter it in the Products Filter. We zoomed in on GGOEGEHQ72499 which happens to be a 2200MAH Micro Charger.
Now the trendlines show the long- and short term trend of this specific product. What is interesting, apart from that the product took a jump in sales, is that this specific jump stands out since both long-term and short-term are moving higher. Whit earlier spikes, this was not happening and we probably were just lucky on that day, now it seems like more is going on. We could have even seen this at an earlier stage since the curl upwards of the trend measure was visible and validated from the 24th of July and is still going strong. Depending on the type of products and industry, this could indicate that perhaps there should be more focus on the stock of this product, the marketing efforts, biddings on Google etc. That’s why this dashboard is very useful. It filters the products that need your attention and helps you to see what are meaningless spikes in the sales numbers and when a trend is starting or ending, and will do so in a very understandable way.
Spotting Losers
On the other side of the spectrum is the product GGOEGBMC056599, which is a waterproof gearbag, as the top loser in relative terms.
This product was actually trending upward in July. Although sales were going up and down, you can see that when the sales were again dropping at the end of July, the trend measures crossed. It became clear, by the downward crossing of the short trend under the long trend on the 24th of July, that perhaps the hype was over because this spike down wasn’t just another bump in the rollercoaster but apparently more meaningful and severe than the other spikes. This showed to be correct in the following days. Someone that is watching this dashboard often will notice these trends and movements much sooner. You will get a feeling of how these things evolve within your business. What should be done is again depending on the product, setting and industry.
Last but not least….
This dashboard is showing only the top 10 most strongly trending productSKU’s in a specific section. If your sales are all trending in roughly the same way, or if you have a huge number of products on your site, it might be wise to look at more than just the top ten.
The dashboard is available via the following link: https://datastudio.google.com/s/oYDMcO4u20M
With the link you can import the dashboard in your own GCP-environment and connect your own dataset to it, make adjustments or add to what we’ve build already. If you find new ways to use it, please let us know! We hope this can be of help to you and your company.
Edit: Some remarks
Some readers were not able to copy the dashboard, therefore we added this section. To copy the dashboard first make sure you are logged in with a Google account, then use the copy-button in the top bar (shown below). Let us know if you have any issues with the query or the dashboard.
English 
Nederlands