Improve the Ratings for Your Products

Periodically we receive requests from our customers asking us to help with some internal review analysis. These sort of requests are great because it gives us new ideas on how to improve the product but also because it's just fun helping our customers solve interesting problems.

For this post, we are going to help answer the following: how many reviews do I need to improve the average rating of my product and what does that breakdown look like by stars?. The answer to this question will vary depending on how many reviews the product already has along with the current break-down by stars. Luckily Reviewbox provides detailed review exports so we should be able to answer this pretty easily.

First you'll need to login to your Reviewbox account and export your reviews. If you don't have a Reviewbox account

A) Select a date range of interest and run a review export.
You can optionally set various filters to limit the reviews to specific brands, product categories, or any other custom field you've previously set up.

p1

B) Open this file in excel.
After downloading the review export you'll need to open the file in Excel. You can also do this analysis in another system if you prefer.

  1. Select all of your data
  2. Insert a pivot table

A dialog box will pop up asking you to create the table in a new sheet, just click 'OK'.
p2

C) Get the frequency of reviews by star rating and product ID.
Now we're going to do some statistics using pivot tables.

  1. You can do this by dragging the fields under 'Field Name' to the appropriate rows, columns, and values boxes.
  2. By default, the table will show you the total number of reviews by product and star rating. You can remove the grand totals from the rows and columns by clicking on the 'Design' tab.
    p3

D) Add another pivot table using steps B1 and B2. Then calculate the average rating by product and the total number of reviews by product.
You can change the field settings of a value to get an average by clicking on the grey information circle to the right of the field name.
p4

E) Next, copy these two tables into a new sheet where the row labels match up. You can delete this column later.
p5

F) At this point, you have a table that shows the count of star rating by product, the average star rating by product, and the total number of reviews by product for the timeframe you exported.
If you are still reading, hang in there! This is the fun part :)
p6

G) Copy and paste this table right next to the existing table. This is where we're going to play with the data.

  1. Select this cell in the new table.
  2. Add this formula
    p7

H) Last formula. Almost there!

  1. Select this cell in the new table.
  2. Add this formula.
    p8

I) Now play around with the number of different star ratings to see how the average is affected.
For example, if we add 8 more reviews to the first product, one 1-star, one 2-star, five 4-stars, and one 5-star the new average rating will be 3.7 stars.

p9

Whew, that was a lot! But you made it!

Now if you want to skip a few steps, we've made this spreadsheet available for you. You can download it and play around with it yourself (assuming you have access to your detailed review information).

Here's the link to the spreadsheet.

If the idea of playing around with spreadsheets isn't appetizing for you, our plan is to roll out this tool into Reviewbox in the near future. So stay tuned and please get in touch with us if you'd like to learn more about managing all your reviews!

Hillary Rivera

Knoxville, Tennessee https://getreviewbox.com

Subscribe to The Daily Review

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!