Using spreadsheets for ratings analysis and reporting No ratings yet.

Multi Rating Pro itself has limited data analysis and reporting capabilities built in. But it does have a little known feature to export ratings to a CSV file, thus enabling you to use spreadsheets for all of your data analysis and reporting needs. This article assumes you have a basic knowledge of spreadsheets and how to setup a PivotTable and a PivotChart in Microsoft Excel.

Let’s assume your ratings data spreadsheet looks like this.

ratings-csv-formatted

Click here to download a copy of the raw unformatted CSV file.

Here are some examples of questions you can answer and custom reports you can create with spreadsheet software such as Microsoft Excel or Google Sheets.

  1. What is the average rating given per user?
  2. What is the count of ratings given per user?
  3. What is ratio between anonymous and logged in user ratings?
  4. Filter ratings by a selected rating item value
  5. Create a report of ratings over time for specific posts
  6. Create a bar chart of selected rating item values for a specific rating form and post

What is the average rating given per user?

Create a PivotTable, with Username and Post Title rows, and then add values for the average star ratings and a count of Rating Entry Id’s. Note the (blank) users are anonymous.

pivot-table-average-rating-per-user

pivot-table-average-rating-per-user2

What is the count of ratings given per user?

Create a PivotTable, with Username row and count of Rating Entry Id’s as the value. Note the (blank) users are anonymous.

pivot-table-count-ratings-per-user

pivot-table-count-ratings-per-user2

What is ratio between anonymous and logged in user ratings?

You can use the formula =COUNTIF(Table3[User Id], 0) to count all User Id’s which equal 0 (meaning an anonymous user). Likewise if you want to count ratings by logged in users only, you can modify the formula to count values not equal to 0 as follows =COUNTIF(Table3[User Id], <>0). Note if you have not formatted the data as a table, then you will need to replace Table3[User Id] with your range e.g. A2:A15 for column A and rows 2-15.

ratio-anonymous-vs-logged-in

Filter ratings by a selected rating item value

Add filters to the column headers, then filter by one of the rating item values. In the following example, all ratings where a user selected 1 star for the rating item Aroma will be filtered.

filter-specific-rating-item-values

Create a report of ratings over time for specific posts

Create a new PivotChart using the Date/Time field as the x axis, Post Title as a column and a count of the Rating Entry Id field as the value. Then group the Date/Time field in the PivotTable by days.

pivot-chart-count-post-rating-entries-per-day

You can easily change the chart type. Here’s the same PivotTable shown as a line with markers chart.

pivot-chart-count-post-rating-entries-per-day-line-with-markers

Create a bar chart of selected rating item values for a specific rating form and post

Create a new PivotChart, add the Rating Form Id and Post Title fields as filters, set the the x axis to a rating item field and the value set to a count of the rating item field. This PivotChart will show how many users selected each rating item value, and you can easily filter by post and rating form.

pivot-chart-count-selected-rating-item-values-bar

Conclusion

Multi Rating Pro has a tool that allows you to export rating entries to a CSV file. As you have seen above, you can then quickly generate reports using spreadsheet software to visually present and analyse your data in meaningful ways.

Leave a Reply

Your email address will not be published. Required fields are marked *

Option 1 Option 2 Option 3