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 actually analyze this data so that it’s useful?
It’s one thing to work your fingers to the bone to nail down the best feedback practices and choose from a variety of tools to encourage your audience to speak up. A whole other thing is to turn data into actionable, resonating insights that lead your customer experience approach towards a whole new level.
In the following article, we’re going to guide you through the realms of a new adventure called “survey data analysis”. We’ll show you some of the most effective ways to untangle the message hidden behind data with tools like Excel, Google Sheets, and Data Studio, and analyze their pros and cons.
Step by step, we’ll guide you through specific Excel and Google Sheets hacks and tips, which work universally, regardless of the survey tool you use on a daily basis.
Last, but not least, we’ll help you choose a method that suits your needs best, and inspire you with actionable tips on how you can start converting your newly-derived insights into to-do lists and a teamwork plan.
So, buckle up and get ready to uncover the wealth hidden in those spreadsheets!
For starters, ask yourself the following:
Do I already know what I want to get out of survey data analysis?
A seemingly obvious question, yes. Still, if you don’t put enough thought into it, this is where you can set yourself up for failure. Even before you actually launch that data analysis tool to put the numbers into play.
Time-sensitivity and metrics correlation – these are just two out of the many factors that can influence the outcome of your analytics efforts. We’ll get to these and other intricacies soon, but first – given the specificity of survey data – let’s assume you want to do one of the following:
- compare a segment of customers to another – for example, check if dividing users into two segments, “daytime shoppers” and “evening shoppers”, makes sense in terms of how well they evaluate your customer care in CES surveys,
- track results within one group of users and see how they were affected by a given action or time,
- see a breakdown of popularity of answers.
Part I: Survey Data Analysis with 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 export the desired file in a .xls or .csv format and open it in Excel, you’re all set to start.
Here’s a thing you might want to do the minute you see the file…
Tip #1: Clearing blank data rows
While reasons vary, the reality is that a lot of survey respondents provide answers only to part of your questions. This, in itself, may result in a survey data file that resembles a checkerboard. And then there’s also another common formatting flaw – empty rows dispersed randomly across the entire file.
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. According to Spreadsheeto, this may be done through:
- Selecting Column A and clicking the “Filter” icon.
- Choosing “(Blanks)” from the list.
- Repeating steps 1 and 2 for the remaining columns.
- Selecting all the filtered rows, clicking “Delete” > “Delete Sheet Rows”.
- Clearing the filters…and done!
This approach takes this…
Instantly more actionable.
Tip #2: Counting the number of blanks in a range – COUNTBLANK
Having already read about blanks in your file, you might also be interested in finding out how many empty cells there actually are per row, column, or entire file. In terms of your survey data analysis, it means that you want to know how many answers weren’t provided for each question asked.
Here’s a quick tip from Exceljet that’ll help curb your curiosity – and, most importantly, provide insight into the effectiveness of your questions or audience targeting.
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 =COUNTBLANK(first_cell:last_cell). In the example below, it corresponds to cells B5 and G5.
- 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 absolutely 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 – through removing blank cells – you can shift data from one row to another, thus making more harm than good.
Now that we got this covered, here’s a formula to instantly start making sense of your numbers. Tons and tons of numbers, mind you.
Tip #3: Converting numbers to ranges – 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 derive 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.
Here’s an example of how this can work with your survey responses:
Let’s say you asked your respondents to provide information on the number of employees hired at his/her organization. Your goal is to understand how many respondents fall into the category of the small, medium, and big companies (according to your definition of these, of course).
Let’s assume you define the size of business according to the ranges below:
Once you’ve defined this, you apply and copy the formula with the following syntax to the entire column:
In terms of the table below, this corresponds to:
As you can see, VLOOKUP instantly assigned the responses according to your definition.
The best thing?
This is just one of the countless ways you can measure various data to derive insights about 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.
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 it here.
Tip #4: Converting “Yes/No” Answers to 1/0
Oftentimes, you’ll want to convert two-choice answers to categorical questions to a numerical value.
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.
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.
- Let’s 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 all else is assigned 0. This simple formula won’t work for questions with more than two options, or where more than one type of answer is crucial for user feedback analysis (we recommend this ExcelForum thread for more advanced use cases).
With text transformed into numerical values, you’ll likely also be interested in:
Tip #5: Calculating Correlation Between Survey Results with CORREL
I’m guessing a lot of your analytics work has to do with comparing how certain customer feedback trends over time, right?
In the previous tip, we showed you how to extract numerical data from a “Yes/No” answer.
Now, if you want to uncover what the 0-1 answers you extracted actually say in relation to your NPS score, one of the most efficient ways is using the CORREL formula.
Here’s the syntax:
How does this work in real life?
Take a look at this example by ExtendOffice:
As you can see, two columns have been selected – or, more accurately – the cells that hold data. Now, a numerical value is displayed. But what does it actually mean?
If you insert a line chart, all will be graphically revealed:
Last, but not least…
Tip #6: Comparing data from two Excel spreadsheets
Sometimes you just want to correlate two columns, two sheets, and sometimes it’s about entire files. Luckily, the folks at Microsoft noticed this need among users and – depending on your Excel version – you’ll likely see something like this in the main menu:
As the process is rather lengthy, we’ll leave it to the masterminds behind the software to guide you through the journey, and will skip to the result:
In all honesty, while it’s nowhere near an automated comparison experience, maybe it’s something that seems viable for your specific feedback analysis needs?
Don’t want to compare and merge data from multiple sheets manually? Read on to learn how you can make this process quicker with our integration with Google Sheets.
Summary: 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 wrap it up:
- almost endless analytics possibilities with advanced use
- widely used; it has kept its hegemony for years and will likely be the program to use for many years to come.
- not an ideal solution if you’re working on data with a team – if you’re not using Office 365, there’s always the risk of overwriting data, as you’re not working on the same draft real time
- 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).
Speaking of… let’s carry on to:
Part II: Survey Data Analysis with 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.
We think you’ll like this one…
Tip #1: Saving 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.
For example, let’s assume you run regular NPS surveys:
You want to store ALL survey response data for NPS surveys your organization carried out between 2018 and 2020. Ideally, you’d like each new survey response export to save to a separate worksheet/tab of a single file called “NPS surveys 2018-20”.
With Survicate’s integration with Google Sheets, this is all possible.
You only need to do two things in your panel:
- Enable our Google Sheets integration.
- Choose the sheet you want to integrate into the menu of the survey or create a new one.
- I’m sure we all agree this is an invaluable time-saving hack, especially compared to the lengthy file comparison process described in Part I.
Now, 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:
Tip #2: 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 go back to the example in Tip #1 and see where Data Studio can take us next.
To recap, 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 where the magic lies – 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:
- Go to “Data Sources” and select “Google Sheets” from the list.
- Find the name of your source file on your survey list.
- Select the new Worksheet you want to update your report with.
- Hit “Connect” in the upper right corner.
- Double check if there’s anything you want to correct in the worksheet before you upload the data – say, even simply convert a “number” to “currency” field.
- Hit “Add to report” in the upper-right corner.
- Voilà! 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.
Summary: 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 to consider 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 <3
- 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 here for a more in-depth comparison).
Part III. What’s Next – Turning Survey Results into Objectives
In this guide, we’ve already led 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.
You might be wondering:
Now that we’ve visualized certain patterns, how do we act upon them? And how do we incorporate this into our customer experience strategy?
What you’re inquiring about is known as data synthesis, and – if carried out efficiently – it’s where you and your team get a lot of those invaluable “Aha!” moments.
In the words of UX Matters:
“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 each apparent pattern
Now’s the time to review what your efforts in Excel/Google Spreadsheets have revealed. See any surprising numbers or peculiar correlation 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 to 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 group 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. Last, but not least:
- 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.
Summary – Mastering Survey Analysis
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 your 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.
With Survicate, you can:
- export data in a multitude of forms,
- integrate with tools for seamless updates,
- use our user-friendly, built-in analytics tool, where data is both visually appealing and actionable – a great place to kick-off your analytics adventure!
Looking for more tips? Get inspired by our Feedback Academy resources and learn about what it takes to create a superb customer experience.