An advantage of online surveys is that respondents frequently offer more (and longer) survey comments than they do on paper-and-pencil surveys. And survey comments are where we may find our most significant sales opportunities or our most useful program improvement ideas.
But analyzing survey comments can intimidate us. We think that to analyze survey comments we have to read all of them. And there is no software that will tell us, “Your average comment was . . . “
If we read just 10 survey comments though, we might summarize them pretty easily. We could report that 3 were about product improvements, 5 simply said the product was “fine,” and 2 were requests for added features. Then we could report the 3 problems and the 2 additional features requested.
One of my clients is a nonprofit umbrella organization. They have a program providing services to homeless youth. The director of that program bemoaned once that she had over 300 surveys from youth who were living on the streets and she had no idea how to summarize their comments and, no less, do it quickly. She had a board meeting in three days and had to report on what the youth had to say.
I asked my client to give me the spreadsheet with the survey responses and I would provide a useful summary for her at least one full day before her board meeting.
You too can do it – reliably and quickly. Here’s how:
- Randomly select 50 survey comments as a representative sample for all of the comments. (To do this, we’re going to invoke Excel’s very easy to use RAND function.)
- Insure the representativeness of those 50 randomly selected survey comments.
- Develop categories from the first 10 surveycomments.
- Apply those categories to all 50 survey comments.
WHEN TO NOT USE THIS PROCESS ALONE:
This process is for giving an audience a trustworthy and useful description of comments overall. If your survey data includes key respondents or your audience has particularly important decisions to make, you may choose to analyze and describe some survey comments separately.
Create Your Survey Comment Spreadsheet
If you’ve gathered your survey responses with Alchemer, you can download your data into a spreadsheet and, after you’ve cleaned it up, a portion of it might look like this:
Of course, our spreadsheet had many more columns than this but for analyzing the comments responding to the question, “What is your biggest concern living on the streets?” this is what we need to get started. Note that we have saved the survey comments as well as enough identifying information to see what age, gender and experience (measured in months homelessness) are represented by each comment.
We’re going to use the random number function to guarantee that we’re getting a completely random selection of comments. (This example assumes the use of an Excel spreadsheet.) To do that, we add a column and enter =RAND( ) in each row of the spreadsheet. Use of this random number function gives each comment a unique identifier and allows us to sort them randomly.
STOP – before you go any further, preserve your random numbers! (If you do not do this, your numbers will update when you manipulate a cell in the RNUM column, the number in the cell will change and you will lose your randomness.) To preserve the random values:
- Highlight the random number (RNUM) column.
- Type CTRL-C.
- Type:
ALT-E
S
V
Then click “OK” at the bottom of the “Paste Special” box.
NOW – sort the file using RNUM as the only sort key.
Then, take the first 50 rows as your randomly selected comments.
Check for Representativeness in Survey Comments
In the case at hand, we knew approximately how long, how many youth stayed homeless, how old they were, and what their distribution was by gender.
In your file of survey comments, look at those identifiers you selected and saved in the columns to the left of the comments. Based on those identifiers, do your first 50 comments represent all of those who took your survey? Most likely they do. As long as I select at least 50 comments, the randomization process has always given me a representative sample. It would probably work with 30 comments, but the difference in effort between summarizing 50 comments versus summarizing 30 comments is pretty small. After 30, you’re “on a roll” and the increased reliability you get by boosting your sample from 30 to 50 consumes very little time.
If your first 50 randomly selected comments, however, are not representative of your population as a whole on any of your selected identifiers, just reinsert your =RAND( ) function and resort the file. You’ll get another 50 and you can check them again.
Categorize Your Survey Comments
Read the first 10 survey comments only and enter a word or phrase that summarizes what the comment is about. Don’t dwell on this step too long or you’ll drive yourself nuts. Think about your audience and topical areas they need to know about. Try to use those topical areas as categories in this step.
You are completely normal (at least insofar as this activity goes) if you have trouble deciding on the categories or themes that will most effectively capture the essence of the first ten comments in your file. That’s exactly why you’re stopping after 10 comments! Do not look at comment-11 or any of the comments after that.
Now, use the categories that worked for those first 10 comments and apply them to comments 11 through 50. Some comments are going to be easy to categorize and some are going to be difficult. Follow these rules:
| When… | a comment is easy to categorize | pick a category and move on |
| a comment could fit multiple categories | pick one and move on | |
| a comment fits none of the categories | skip it and move on | |
| a comment fits a category but is about “something else” also | assign category but also write that “something else” as the column header in the next column |
Once you’re done with all 50 comments, go back to that “something else” column and put asterisks next to each comment that is about that “something else.” You may have 2 or 3 “something else” columns.
Quality Control for Survey Comments
OK, so far so good. You went quickly and most of your comments are categorized – plus you’ve also identified those comments within each category that are about money (or about product improvements or that compliment a person in your organization or . . . ). The trouble is, you went so quickly that you have too little confidence in what you did and you’d be very nervous about reporting your summary to anyone who mattered! Not to worry . . .
Let’s do some quality control. This step is easy and will make you feel great – increased confidence and all that. Sort the file by Category. Read through the comments within each category. Do they hold together as a group? Re-label them if they don’t. Are all of the concepts captured that you want to capture? You can always add other “something else” columns to make sure you’ve got everything.
It is common during quality control to tweak labels – maybe to find a new category or to merge two categories together. You will also find a category for the comments that went unlabeled your first time through the comments. You should have about ten percent unlabeled comments. That would be normal if you have categories that fit your data.
Summary of Survey Comments
All-in-all, analyzing survey comments is like anything else. There is a learning curve and you will develop your own tricks after you’ve done it once or twice, and then you will be able to enjoy the paradox of being able to do it more quickly AND with higher quality.
One last tip – this is an extraordinarily appropriate application for Excel data filtering. Excel “data filtering” will allow us to sort our file by categories or it will allow us to isolate our attention on only those comments within a given category. If you’re not familiar with Excel data filtering, highlight the top row of your spreadsheet, go to “Data” and then “Automatic filter.” Now let the computer be your teacher. Play with the selection options in each column and you’ll see what I mean!
So, we have answered the question, “How in the world do we make sense of all these comments?” We did it by:
- Creating a spreadsheet with comments and adding identifying information for each comment.
- Randomizing the comments and using the first 50.
- Creating categories from the first 10 comments.
- Labeling the next 40 quickly and not driving ourselves nuts over getting everything perfect.
- Sorting and doing some quality control, re-labeling as needed.
- Finishing everything within 90 minutes when we had planned 5 hours, we left work early and stopped for a beer on the way home.
Many respondents want the opportunity to add comments to their surveys and we can quickly analyze them and make them useful to our audiences.
I hope this was helpful. Although maybe an even more useful question to have answered would have been, “How in the world did they get three hundred surveys from homeless teenagers in the first place?” Well, that would be a story around a different campfire.
