If you’re reading this post, you’ve likely succeeded in gathering a substantial amount of customer feedback. Nicely done!
Right after the initial joy of observing rising response rates and seeing how the “total responses” counter quickly shifts from two to three and on to four digits, you start asking yourself: This is great, but how do I analyze survey data so that it’s useful?
In the following article, we’ll show you some of the most effective ways to untangle the message hidden behind data with tools like Survicate, Excel, Google Sheets, and Data Studio and analyze their pros and cons.
We’ll also show you how Survicate breaks down survey results data, giving you ready-to-use graphics for your next stakeholder meeting!
What is Survey Analysis?
Survey analysis is the process of analyzing customer insights. The goal is to turn numbers into objectives that your teams can use to improve and grow your product, customer experience, customer support, and overall business.
P.S. If you track your Net Promoter Score, it's a good practice to benchmark it against industry averages. You’ll need to know how to calculate NPS and we recommend you check out our NPS calculator to quickly make sense of the numbers and NPS formula.
Once you are done with your Net Promoter Score calculation, you’ll need to see if you keep improving and keep the dialogue with the customers going - but benchmarking your score will let you discover if your NPS score is good in your industry and lets you maintain a competitive edge.
As soon as you begin getting your first responses, turn to your “Analyze” tab in the Survicate dashboard.
This panel will provide you with the most important metrics, like:
The total number of responses so far
Date of the last response you received
The average completion rate
Total and unique views of your survey
Graph and bar charts visualizing rating scale questions responses
Bar charts detailing single and multiple choice answers
All of your text answers (with demographic data if you enabled integrations!)
A word cloud showing the most popular words in text answers
You can even exclude words to really zoom in on what you want to analyze.
All this info can be exported into .cvs or .xls.
How to Analyze Data from a Survey in Excel
If you’re not an Excel pro, here are a few tips that might motivate you to upgrade your Excel data analysis skills. And believe us when we say this – it’s worth becoming friends with this Microsoft data superstar.
So, without further ado, let’s get right to it!
Once you get your responses from a survey, export the desired file in a .xls or .csv format. Then, open the survey results in Excel. It’s time to format the data to make it more analysis-friendly.
Tip #1: Crear blank data rows with a filter
A lot of survey respondents provide answers only to some of your questions. This may result in a survey data file that resembles a checkerboard.
So, how do you delete empty rows and make sure you’re not accidentally deleting a row that only appears to be empty at first glance?
While you’ll find a lot of different ways online, we believe the safest way is to identify blanks consecutively, column after column.
Let’s go through the steps of making this:
Look more like this:
To start, select Column A and click the “Filter” icon.
A filter will appear on the top row of the first column. Click on it and choose “(Blanks)” from the list.
Repeat these steps for the remaining columns.
Select all the filtered (blank) rows. The numbers will appear in red on the left. Right-click and choose “Delete” > “Delete Sheet Rows”.
Clear the filters. All that will remain are rows with at least one field filled out. Your data will be instantly more actionable.
Tip #2: Count the number of blanks in a range with COUNTBLANK
You might also want to find out how many empty cells there are per row, column, or the entire file. In terms of your survey data analysis, this will tell you how many answers weren’t provided for each question.
COUNTBLANK allows you to quickly define and test a range against empty cells. Let’s assume you want to inspect several rows.
Create a new column at the end of your file. Enter the formula:
In our survey analysis example, this would correspond to cells A2 and D2.
When you hit “Enter”, you’ll see the answer. You can now drag the formula down to as many rows as applicable.
It’s that simple!
Note: If you’re an analytics newbie, your initial instinct might prompt you to delete all blank cells and retain answers only. We advise you not to do this unless you’re certain this won’t hurt your data structure or desired results.
This approach might backfire if your feedback collection strategy isn’t designed to handle respondent anonymity. This is because, by removing blank cells, you can shift data from one row to another.
Now that we got this covered, here’s a formula to instantly start making sense of your numbers.
Tip #3: Convert numbers to ranges with VLOOKUP
This will do wonders if you want to segment responses based on numerical values in a selected column. It is also a great first step to deriving data in a way that can be used for more intricate analysis.
Data Scientist Ashutosh Nandeshwar recommends using the VLOOKUP formula to quickly overview what the numbers in your file stand for.
Example: Let’s say you asked your respondents to provide information on the number of employees hired at their organization. Your goal is to understand how many respondents fall into the category of micro, small, medium, and so on companies.
Let’s assume you define the size of businesses according to the ranges below:
0-49 - Micro
50-99 - Small
100-199 - Medium
200-499 - Large
500-999 - Huge
1000+ - Enterprise
Once you’ve defined this, apply and copy the formula with the following syntax to the entire column:
In terms of the table below, this corresponds to:
Use the dollar ($) sign to keep the formula from shifting the numbers.
To make things simpler to understand, column C shows what formulas were inserted in column B.
As you can see, VLOOKUP instantly assigned the responses according to your definition.
The best thing? This is just one of the countless ways to measure various data to derive insights from your audience.
Imagine where VLOOKUP can take you in terms of measuring the number of purchases a user has made, the value of their subscription plan, or the number of sessions they initiated with your software, to name just a few.
PRO TIP: With Survicate, you can go even a step further in grouping your respondents and pass custom respondent attributes from your website or app.
This way, you export a survey report that includes information you believe compliments your results or knowledge on the specific user best. You can read more about targeting based on respondent attributes in our Help Center article.
Tip #4: Convert binary answers to binary numbers
Oftentimes, you’ll want to convert two-choice answers to categorical questions to a numerical value.
Example: Let’s say you’ve noted an increase in customer satisfaction (say, through an NPS survey) and want to find out how much it’s been influenced by a recent integration with a popular tool.
Let’s assume you’ve singled out a group of users who’ve tried the integration and sent a survey asking them: “Are you satisfied with our integration with X?”
The results are in and they look more or less like this:
The easiest way to convert text values into numbers is by applying the following formula:
Let’s get to it. Create a new column at the end of your sheet.
Assume Column C holds all your “Yes/No” answers. Enter formula =--(C2=”yes”) into a cell in the new column.
Drag the entire formula down and watch the binary magic happen.
Note that the formula recognizes all “yes” answers and assigns them a numerical value of 1, while everything else is assigned 0.
This simple formula won’t work for questions with rating scales of more than two options or where more than one type of answer is crucial for user feedback analysis.
Tip #5: Calculate the correlation between survey results with CORREL
Now, if you want to uncover what the 0-1 answers you extracted actually say in relation to your NPS, CSAT, or CES score, one of the most efficient ways is using the CORREL formula.
As you can see, two cells have been selected. Now, a numerical value is displayed. But what does it actually mean? If you insert a line chart, all will be graphically revealed:
You can see how the users who have activated the new integration tend to give higher scores.
Pros and cons of survey data analysis with Excel
Hopefully, you just learned a couple of useful formulas to guide you through your future user feedback analysis.
So, is Excel the best solution for you? Let’s sum up the pros and cons.
Almost endless analytics possibilities with advanced use
Widely used; it has kept its hegemony for years and will likely be popular for many years to come
Not an ideal solution if you’re working on data with a team – if you don’t use Office 365, there’s always the risk of overwriting data
Not especially convenient if you want to regularly export and update real-time data (if this is the case, then you’ll like our Google Sheets solution)
Comparing different spreadsheets is impossible without add ons
How to Analyze Results from Surveys in Google Sheets
For the occasional spreadsheets user, Excel and Google Sheets appear to do more or less the same. Fact is, most Google Sheets formulas are either identical or very similar in syntax to the Microsoft software.
For the sake of focusing on Google Sheets-specific tips, here’s a cheat sheet from G-Suite that describes the similarities and differences, including the formulas and functions we described in Part I.
So, without further ado, let’s take a look at how Google Sheets can be used differently from Excel as far as survey data analysis goes.
Let’s connect our Survicate account with Google Sheets. Go to the Connect tab in your panel.
And search for the Google Sheets integration.
You can send previous responses…
Or create an entirely new sheet.
You can save data from several surveys to one spreadsheet
Here at Survicate, we are often asked for tips on how you can export your survey responses to a single spreadsheet.
Certainly, while some surveys are best saved to a single file, this doesn’t apply to all use cases. Especially if you run regular surveys of the same type and want instant access to all previous response data – not just your recent export.
You want to store all survey response data for NPS surveys your organization carried out between 2018 and 2022. Ideally, you’d like each new survey response exported to save to a separate worksheet/tab of a single file called “NPS surveys 2018-21”.
With Survicate’s integration with Google Sheets, this is all possible. Just click “View Sheet” in the “Connect tab” to get your up-to-date information.
Saving surveys to a single file is not where the benefits of Google Sheets integration end. Here’s one that will take your standard analytics' charts and tables to a whole new level.
Consider survey data visualization with Google Data Studio
Data Studio is another unbeatable Google Suite perk that puts basic spreadsheet histograms to shame.
If you’re not familiar with the tool, it turns your .xls/.csv file into real-time graphics reports that transform data into visual, actionable insights.
Here’s a short video by Google which shows how you can connect your Google Sheets with Data Studio.
Let’s imagine you’ve just run an NPS survey and want to visualize it aesthetically for your next stakeholder meeting. You also want to run the data by results from the previous quarter.
After you’d uploaded the Google Sheets file, you’ll quickly see the magic – you don’t need to move any data into charts manually or create any custom formulas. All is synchronized with your file, in real-time.
Looks great, doesn’t it?
Still, what if you’ve already uploaded the .xls file before, but with survey responses from the previous quarter? How can you upload the newest Worksheet?
It’s very simple:
1. Go to “Data Sources” and select “Google Sheets” from the list.
2. Find the name of your source file on your survey list.
3. Select the new Worksheet you want to update your report with.
4. Hit “Connect” in the upper right corner.
5. Double-check if there’s anything you want to correct in the worksheet before you.
6. Upload the data – say, even simply convert a “number” to a “currency” field.
7. Hit “Add to report” in the upper-right corner.
You can now choose between your older survey responses and the newest worksheet and decide which data sources you want to compare or use for specific tables and charts.
Pros and cons of survey data analysis with Google Sheets
Now that we’ve shown you our two favorite tricks for real-time analysis (hats off, Google), it’s time to summarize the pros and cons before you start analyzing data with Google Sheets.
Google Sheets are free (or reasonably priced for Enterprise accounts)
Can be worked on by multiple users (useful for remote teams)
Can be easily transformed into striking Data Studio visuals
Data can be sent and updated in a matter of seconds, to both new and previously created files
You can incorporate survey results into your company’s other key metrics such as revenue, registration, or churn rates, and create stunning visuals for all critical data
If you’re a state-of-the-art data analyst, you might find Excel much more powerful; but for basic-to-intermediate survey analysis, Sheets win at versatility (read this comparison for a more in-depth details).
How to Perform Survey Results Analysis in Survicate
Our own results tab can transform most of the raw data into analyzable metrics and statistics.
Depending on what kind of survey you ran, you may even be able to analyze open-ended questions.
And if you make good use of integrations, a lot of data like demographics can be captured automatically.
How to Turn Survey Results Into Objectives
In this guide, we’ve already let you through our favorite features, functions, and formulas available across Microsoft and Google data analysis and reporting software.
Assuming you have now learned a couple of new things about optimizing your survey feedback analysis process, it’s time to focus on what your next steps are.
“Too often, (…) we focus purely on analysis—and the identification of facts—and ignore synthesis, which often occurs organically during analysis.”
As you can see, this relates as much to UX research methodology, as it does to survey response analysis efforts. So, let’s take a look at some tips that might help you organize your work effectively.
Write down patterns
Now’s the time to review what your efforts in Excel/Google Spreadsheets have revealed. See any surprising numbers or peculiar correlations of data?
Write down every single hypothesis that comes to mind
What might seem to be of minor importance now, might reveal itself as part of a much more significant pattern later on.
Discuss what insights these patterns reveal
Is this a recurring trend?
Is it something worth acting upon now, or putting it to the test of time?
What would this piece of data say if we contested it with other notable information?
These are all effective ways to engage your team in processing and synthesizing data. You’ll be surprised to see how far you go with some of your best minds set on the task.
Validate your insights beyond the team
If you’re still hesitant as to whether the results are worth acting upon, extend your search. Ask members of your extended team for their opinion or send follow-up questions to a selected segment of your audience.
Talking to other team members will often reveal a fresh point of view on the matter while asking your users might uncover the missing elements of the puzzle.
In the case of the latter, we recommend taking your time to formulate questions in a way that doesn’t leave any doubt and clearly validates or disallows your hypothesis.
Unresolved? Give it some time
If there’s a result or pattern you don’t think should be dismissed but you can’t find the source of, don’t give up. Write it down and correlate the results with several of your future response results. What isn’t apparent now, might reveal itself in months to come.
In this post, we’ve shown you several actionable steps and tools that may prove valuable in your future survey analysis endeavors.
Now that you know your way around survey data analysis, you might also like our tips on increasing survey response rate to collect even more actionable feedback.
Decided to give Excel a chance to become your “go-to” tool? Found some unbeatable advantages of Google Sheets? Or maybe fell head over heels for Data Studio capabilities?
Regardless of where you take your data analysis, Survicate will help you master the art of effective feedback collection.
NET PROMOTER, NPS, AND THE NPS-RELATED EMOTICONS ARE REGISTERED U.S. TRADEMARKS, AND NET PROMOTER SCORE AND NET PROMOTER SYSTEM ARE SERVICE MARKS, OF BAIN & COMPANY, INC., SATMETRIX SYSTEMS, INC. AND FRED REICHHELD.