In the previous blogs in this series, I introduced the role data plays in decision-making (Part 1), the importance of having a data strategy (Part 2), and how to use multiple data sources in that data strategy (Part 3).
As I stated earlier, two types of data exist: quantitative and qualitative data. Quantitative data’s major advantage is that it can be easily analyzed using mathematical formulas. Quantitative data enables us to use logic and apply mathematical formulas to segment and compare the data as well as charts to assist in visualizing the findings. Quantitative data falls more to the scientific right on the decision-making spectrum introduced in Part 1 of this blog series.
Many surveys use a five-point Likert scale. This scale asks respondents to rate a statement by selecting one of the following:
- Strongly Agree
- Neither Agree nor Disagree
- Strongly Agree
Questions or statements that use this type of scale are quantifiable because a value is associated with each individual answer. This format also makes it easy to compare and contrast responses against each other.
Qualitative data, such as free form text responses or interview verbatim portions of a survey, are in contrast anecdotal in nature and need to be treated differently. These qualitative answers can be very powerful because they have a direct line of sight to the target audience’s thoughts, feelings, and emotions. But these text answers create a problem for many. Some companies find it difficult to digest large data sets derived from survey text questions or interview comments and quotes collected over a long period of time.
I experienced this problem during a recent project. This post focuses on how I converted qualitative data into quantitative data. I assembled the data into a spreadsheet and used a technique called quantitative data coding. My next post, “Part 5: Making Qualitative Data Count,” will describe how this code process allowed the data to tell its story in a meaningful way. Below are the steps I followed.
Paste all verbatim responses into the first column in a spreadsheet (Figure 1).
Create a list of top categories of information the survey or interview targeted across the top (Figure 2).
Read each response and determine if there is a sub-category (Figure 3).
“Code” each comment (Figure 4). Essentially, this step links each verbatim comment with one or more sub-categories. To do this, read each comment and place an “x” in the sub-categories that best represent the verbatim. It is possible to have multiple sub-categories annotated for a single comment. If no sub-categories accurately reflect a comment, add a new sub-category. As a rule, try to limit the total number of sub-categories to 15 or less. Having more than that will increase the degree of difficulty and time it takes to code the entire data set.
Add up each column (Figure 5). Excel can be very helpful in auto-calculating each column. To count the number of “x’s” in column C, begin by inserting a row just under the headers. Next, place the following formula in cell C2.
Note that in this formula, C18 represents the last row with data. If you paste a lot of data into this sheet and the last row of data is below C18, right click on the C18 in the formula (to change that element), and then click on the cell in column “C” of the last row with data. Press Enter. Copy cell C2 and paste into all sub-category columns in row 2.
Copying this formula to the other cells will provide you a view of the total mentions for each sub-category. This process helps you clearly identify the top issues identified in the survey.
If you think you may present to your leadership team, they may not be interested in this level of detail. Rather, they will likely be interested in a summary by category (versus sub-category).
I find it helpful to code the sub-category first, and then let Excel calculate the major category totals. Having both levels of detail provides the flexibility to have a high-level summary discussion and a detailed discussion, depending on the needs of your audience.
In this step we will summarize the data. It is important that the major category is only counted once for each relevant verbatim (row) (Figure 6).
In this case, I entered a one (1) in the category column for each time an “x” appeared in any of its related sub-categories. You can see that in cell B3, a one (1) was entered even though that row has an “x” in two sub-categories (one in “Investing in Employee” and the other in “Communication”).
This process keeps us from “weighting” one category too heavily in comparison to other categories. If you were to add up each “x” in the MANAGEMENT SUPPORT sub-categories (1+5+3+1+2) in cell C2, you would get a total of 12. This value reflects the sum of all relevant mentions for all MANAGEMENT SUPPORT sub-categories. However, we see that only seven verbatim rows address some kind of MANAGEMENT SUPPORT type issues. Again, this is necessary to prevent “double counting.”
Because I used numbers in the major categories (rather than an “x” as I did in the sub-categories), I changed my Excel formula slightly.
WARNING: Be sure to only enter a “1” in the category summary column for each relevant verbatim (row).
Tell me how you analyze your qualitative data. Also look for Part 5: Making Qualitative Data Count to learn how I created a graphic that unlocked the data secrets for the business leadership team.
Latest posts by Rocky Ellens (see all)
- Tools for a Better Data Strategy - June 12, 2018
- Decision Before Data - January 25, 2018
- CAPABILITIES: An Often Overlooked Strategic Management Tool - February 8, 2017