Building a Customer Feedback Analysis Dashboard in Google Sheets

Customer feedback is one of the most important metrics to increase sales or analyze business. By analyzing customer feedback, you can improve products, services, and customer experience. Google Sheets offers powerful tools and features to build dashboards. In this article, we will show how to build a customer feedback analysis dashboard in Google Sheets.

Let’s consider small business customer feedback data to visualize feedback trends, identify key issues, and make data-driven decisions.

Step 1. Set Up Your Data in Google Sheets

Organize your customer feedback data in Google Sheets. To create a dashboard, insert the following columns in Google Sheets.

• Date
• Product Name
• Customer Name
• Rating (1-5 scale)
• Feedback
• Feedback Category (Quality, Performance, Delivery, Design)

Remove Duplicates: To build a dashboard you must clean your dataset.

• Select the data range.
• Go to the Data tab >> from Data clean-up >> select Remove duplicates.

Remove any blank cells or inconsistent data, remember to check spelling also.

Step 2. Analyze Customer Feedback Data

Create Key Metrics

You can find out key metrics from customer feedback data. Use Google Sheets functions like COUNT and AVERAGE to create key metrics.

Count Total Feedback: 

• Insert the following formula in cell I2.

Formula:

=COUNTA(E2:E51)

This formula will show the total feedback count from the selected range.

Calculate Average Rating:

• Insert the following formula in cell I3.

Formula:

=AVERAGE(D2:D51)

This formula will show the average rating.

Output:

Overall Satisfaction Analysis Using Pivot Table

• Select the entire data range (e.g., A1:E51)
• Go to the Data tab >> select Pivot Table.

• In the Create Pivot Table dialog box >> select Existing Sheet >> select Location.
• Click OK.
• In the Pivot Table Editor;
  • Rows: Select Product Name.
  • Values: Select Rating; Summarise by: AVERAGE
  • Values: Select Rating; Summaries by: COUNT

Feedback Category Breakdown Using Pivot Table

• Select the entire data range (e.g., A1:E51)
• Go to the Data tab >> select Pivot Table.
• In the Create Pivot Table dialog box >> select Existing Sheet >> select Location.
• Click OK.
• In the Pivot Table Editor;
  • Rows: Select Feedback Category
  • Values: Select Feedback Category; Summaries by: COUNT
  • Values: Select Rating; Summaries by: AVERAGE

Step 3. Visualize Your Data

To visualize the customer feedback analysis, you insert charts.

Let’s insert a Column Chart showing the average rating by product name.

• Select the Product Name and Average Rating columns from the Pivot Table.
• Go to the Insert tab >> select Chart.

• In Chart Editor >> from Chart Type >> select Column Chart.

Let’s insert a Pie Chart showing the distribution of feedback categories.

• Select the Feedback Category and Average Rating columns from the Pivot Table.
• Go to the Insert tab >> select Chart.
• In Chart Editor >> from Chart Type >> select Pie chart.

Step 4. Advanced Techniques

You can perform sentiment analysis by using Google Natural Language or GPT for Sheets. or LLMs like OpenAI, Claude, etc.  These can analyze sentiment in the feedback text.

If you don’t have access to these options, you can use Google Sheets formulas or Apps Script to analyze the percentage of positive, negative, or neutral feedback.

Positive Sentiment: We are analyzing positive sentiment based on the ratings. If ratings are greater than or equal to 4 count it as positive.

• Select cell M2, and insert the following formula.

Formula:

=COUNTIFS(D2:D51, ">=4") / COUNT(D2:D51)

This formula will count the number of positive feedback. Then select Percentage (%) to calculate positive feedback.

Output:

Negative Sentiment: If ratings are less than 3, count it as negative.

• Select cell M3, and insert the following formula.

Formula:

=COUNTIFS(D2:D52, "<3") / COUNT(D2:D52)

This formula will count the number of negative feedbacks. Then select Percentage (%) to show negative feedback.

Output:

Neutral Sentiment: If ratings are greater than or equal to 3 and less than 4, count it as neutral.

• Select cell M4, and insert the following formula.

Formula:

=COUNTIFS(D2:D51, ">=3", D2:D51, "<4") / COUNT(D2:D51)

This formula will return the total count of neutral feedback based on the logic. Then select Percentage (%) to get the natural feedback.

Output:

Step 5. Build the Dashboard Layout

Once all the analysis and calculations are done, you can create a new sheet as a “Dashboard”. Then organize all the calculated sections.

Customer Feedback Analysis:

• Average Rating Across All Products.
• Total Number of Feedback Entries.
• Overall Satisfaction Analysis Pivot Table.
• Feedback Category Breakdown Pivot Table.

Product Performance:

• Column Chart of Average Ratings by Product.
• Pie Chart of Feedback Categories by Average Ratings.

Formatting:

• You can use conditional formatting to highlight specific feedback categories or low ratings.
• Color-code ratings (Red for 1-2, Yellow for 3, Green for 4-5).
• Highlight rows requiring follow-up.

Final Dashboard:

This dashboard will help you identify key metrics to improve or prioritize. For example, it shows the Table Stand feedback is poor, you should focus on enhancing its quality, design, and delivery. On the other hand Office Desk rating is 5 but feedback is only one. You can focus on increasing its sales.

Conclusion

The customer feedback analysis dashboard will transform the business data into dynamic strategic insights. This dashboard will help you to track the product quality, delivery, and other services briefly. It will also help you to make informed decisions to improve overall performance and customer satisfaction. By following the above steps, you can build a dynamic customer feedback dashboard. Explore more options to customize it based on your data type and requirements.