The Retail Merchandise Report

This is a tool that has delivered results for every retailer I have worked with over the years. It can be used to inform product level actions daily, without the need of an analyst to interpret the data. The actions will not individually make a major impact on your bottom line but cumulatively over a year, then it makes a big difference. All through using data to work smarter in your day-to-day job.

Quick note: I triple checked after writing most of this blog post to discover I had already written it ten years ago. The basic principles have not changed during that time but I do go into more details here. So, I thought still worth publishing but the original post can be found on the Ayima website.

Every retailer has (or should have) data on their product range, what is selling the most and which products bring in the most revenue/profit. This information is used to inform all manner of actions e.g. choosing products to feature on the homepage & in newsletters, the sequence in which products are listed on product list pages, price changes, inventory management, future buying decisions, etc.

The problem is, while this data is actual sales, it does not include lost or potential sales. These decisions are being made with an incomplete picture.

As an example, the top selling products in one week might be chosen to feature on the homepage, in newsletters and are shared on social media. Which products sell the most the next week? The products that were featured on the homepage, in newsletters and on social media. The circle continues…

What if though, you could identify the products that will generate the most incremental sales from being featured, not just the products currently with high sales. The smarter action is to choose these products, maximising the return from the promotional space. Similarly, you can find products that are underperforming. Investigate why, identify the reason, fix that issue and increase sales. Product by product, small change by small change but with a big impact cumulatively.

Contents of the Retail Merchandise report

To do all this, you need a Retail Merchandising report. It is a product level report (matching the granularity at which products are displayed in product pages) containing data on:

  • Views of the product page
  • Sessions in which the product page was viewed
  • The number of sessions in which each product was added to basket
  • The add to basket method (if there are options besides from the product page)
  • Transactions in which each product was included
  • Total units sold and revenue generated

With that data, you can create a report like this.

Screenshot of a sample Retail Merchandise report

You already know which products had the most sales and generated the most revenue, so that data is not the focus in this report. Instead, it is sorted by product sessions, representing a different method to define your most popular products. These might align to sales numbers but rarely do.

The key column is the Product Page Success Rate, calculated as the sessions in which the product was added to basket from the product page, divided by the sessions in which the product page was viewed. Or, if people view a product, how likely are they to add that product to their basket.

Using the Retail Merchandise report

All things being equal, especially at higher numbers of views, the Product Page Success Rate should be similar-ish across all products. Maybe some variation by product category or price level but within a range for those categories. What you have here are powerful internal benchmarks. Focus on products with a Product Page Success Rate above or below the standard range.

Look at the example report. Most products have a success rate around 20%. But product 4 has a Product Page Success Rate of only 6.5%. Something is wrong there. This deserves further investigation because if there is an issue that can be fixed, sales would triple for the fourth most viewed product in the product range, delivering €50k additional revenue.

That issue is unlikely to be something that would appear in a website optimisation report. If the issue was due to the product page layout, the language, currency or technical issues, it would affect all products, not only selected products (unless there is a skew in the segment of people who view certain products).

From experience, the six most likely causes of a low Product Page Success Rate are:

  • Out of stock in some sizes/colours
  • Price higher than perceived value
  • Poor product images
  • Poor product information
  • Negative product reviews/rating
  • Marketing campaign using product as the landing page

The first time I created this report, many many years ago for a (at the time) leading UK online department store, I asked a colleague for her thoughts on why five dresses had low success rates, sharing the relevant product pages. From memory, her response to three of the dresses was simply “ugly photo” and the other two were “too expensive”.

What is a list of the products where a reshoot of the product images would lead to a 20%+ increase in sales of that product worth to you??

The most common issue I see is products out of stock in some SKUs. This report allows you to calculate the cost of lost sales due to low inventory, potentially providing the business case for investing in higher inventory levels. It can justify an additional order for some items by showing the demand is still there. With work, this data can even be used to estimate potential sales (actual + lost) for all products, with this information more useful than actual sales data in planning for the future.

Screenshot of a product with SKUs out of stock

Then flip the report around. Look for products that are in a sweet spot, represented by a higher-than-normal Product Page Success Rate. These products are in stock, they are perceived to be good products, especially at that price point – could they sell more than they currently do if seen by more people?

Product 11 is an example of this with a Product Page Success Rate of 32.8%.  This product sells more units per visitor – your challenge is to get it more exposure. Get it on the homepage, promote on social media, include in your newsletter.

Product 7 is another product that stands out with a low Checkout Completion Rate, the purchase is completed less often after it is added to basket.  Another potential win if a fix can be identified and made there. Is there an issue with delivery costs or timelines for that product?

Extending the Retail Merchandise report

The obvious improvement here is to add conditional formatting, so that that data points above or below a range are highlighted. Filters are more useful, searching for products with at least X views and a Product Page Success Rate less than (or greater than) Y.  As mentioned previously, this can be more useful when filtered by product category or other product classifications.

The challenge is identifying the reason why a product is under or over performing.  It can be done manually but it would be great to have information to aid your investigation at your fingertips within this report.  Some ideas for that are:

  • Price type (full price or reduced)
  • Price level (high, medium, low)
  • Product age
  • Availability (% SKUs out of stock)
  • % visitors interacted with product page
  • % visitors interacted with product images
  • % visitors interacted with product information
  • % visitors selected colour/sizes but didn’t add to basket

Operational Points

The Retail Merchandise report is something that could, and would ideally, be used daily. In fact, it could even be used in the afternoon to review sales in the morning. The challenge there is traffic levels being high enough for patterns to be clear. As a rule of thumb, products need to be viewed in at least 50 sessions for the data to be reliable, preferably 100+.

That might be possible daily but it also might mean weekly or worse. Note that the longer the date range, the more chance of a change in circumstance for a product that impacts the data e.g. stock level changes.

It is important to define what a product is for your business. Is it colour/size specific or are they just SKUs for a product? My rule of thumb is to match yoru definition of a product across the logic used for what appears on a product page.

The right tracking needs to be in place for this data to be available. The minimum data points you need are:

  • Views and sessions of each product page
  • Sessions in which the product was added to basket
    • Including the add to basket method if it can be done via methods other than the product page
  • Transactions, units and revenue for each product

Two critical points here are that product names must be consistent through the process so they can be matched up and that you need sessions (or unique views/unique dimension combinations in GA) as the metric, not view/instances. This is a major flaw in GA Enhanced Ecommerce as the reporting there is based on instances.

Life is easier if this data is captured in specific variables e.g. custom dimensions and events in GA. I have used workarounds many times, transforming the data in Excel so that the product names can match across views, adds and purchases. I do not recommend this approach…

Screenshot of the GA EE report for the Google Merchandise store

Note there are numerous issues with the tracking set-up for the Google Merchandise store, several on display in this screenshot.

The tracking needs to be extended if you want to produce this report for different product categorisations (remember you need session metrics for each level). The tracking needs to be much more advanced if you want data that will aid investigations e.g. price levels, availability, interactions with product pages.

I have referred to Google Analytics multiple times but this solution works with any Digital Analytics tool. Just capture the necessary information in the relevant variables.

I also referred to Excel a few times. That is the tool I have always used with this report but it should be doable with any advanced data visualisation tool.  I believe, with some more recent releases to Data Studio, it should be possible there now – you need to be able to filter on metrics for this report to be effective. I haven’t had the chance to build a Data Studio version of this report as yet though.

Way back when I first worked with this report in my Logan Tod days, a version was built in Tableau. The great thing with that version was it was converted into a scatterplot. Within Tableau you could then visually highlight different groups of products based on their views and their Product Page Success Rate, defining different actions for products in the four quadrants.

Conclusion

The Retail Merchandise report is not new, it is not sexy, it is not powered by ML or AI. It is not going to turn around a retail business. But it is a simple to use (with some training) report that contains incredibly actionable insights that help ecommerce teams work smarter. Each action will not make a big difference but cumulatively, they will.

Please get in touch if you want support getting a Retail Merchandise report running within your business, plus to hear about the other techniques and tools I use provide to help retailers achieve better results.

This Post Has 2 Comments

  1. Nitesh

    Hi Peter
    Thank for sharing this. I am trying to apply some of your tricks with the brand I work.
    What is the ‘sessions’ in the 1st the screen grab? Is that the session in which in product detail page was viewed for that product? Or is it if product impression was shown anywhere in the listing?

    1. Peter O'Neill

      Hi Nitesh, you are welcome and great to hear you are testing this report out. Session is sessions in which each product page was viewed. Best options are unique page views (or unique custom dimensions) of the product page. Unless you are capturing product page views in a custom dimensions, content grouping or event (if using GA), in which case that can be used. It is not impressions on a product listing page.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.