How To Clean Qualtrics Data In Spss

Cleaning Survey Information

In this post, we volition discuss the steps to clean survey data or survey results from whatever survey software so you can analyze them in whatever BI or information visualization tool. Cleaning survey data is an essential footstep before visualizing survey data in any information visualization tools such as Tableau, PowerBI, Qlik Sense or Spotfire.

I'm Tyler Lubben, founder of VizualSurvey, a survey dashboard and presentation consulting company where nosotros can collect your survey information from any survey software so y'all tin visualize information technology in any of our Tableau, Ability BI, Spotfire or Qlik Sense dashboards. This article will evidence you some of the chief bug you have when you lot're trying to visualize or analyze survey data from whatever of the popular survey tools such as Google Forms, Qualtrics, QuestionPro, SurveyMonkey, SurveyGizmo…. as well every bit how y'all tin solve these issues by cleaning survey results in excel and making dashboards in a data visualization tool.

Survey Data Problems

The purpose of getting survey data is to go accurate insights. To achieve this, we need to be able to visualize survey data accurately. Nearly of the survey tools take their own reporting tool, and you can come across basic analytics of your survey if you sign upwards for the highest-paid plan. To do a deep analysis like combining multiple surveys from different times or from different survey software and visualizing it in ane dashboard, you need Tableau, Power BI or another data visualization tool. You will go many options like filtering, cross-analysis of different questions, and other extensive features only possible through a custom data visualization tool. Information technology sounds skillful just information technology is hard to do, you tin't just download a CSV file, and connect your data to any data visualization tool. You will demand to clean your survey information offset, learn how to employ a information visualization tool, then create a dashboard for that specific dataset. So when you lot consign a CSV file from a survey data tool, you take a lot of bug, and some of the most important ones are discussed beneath.

one. Each Survey Software Outputs Data Slightly Different

Each survey software is different from their question type structure to how they export the data. They organize responses in their own style, so when yous download your survey results from one survey software more likely it will be a totally different structure from another survey software.

Google Forms:

If we wait at the exported CSV file from Google Forms, nosotros volition see it doesn't incorporate respondent IDs, which is needed to count distinct respondents.

In the case of multiple choice questions where people tin can choose more one reply, all answers are in one column just separated by a comma (,). This causes a problem when you try to visualize survey data in any data visualization tool. Another common question type (matrix questions) in Google Forms, is structured with the question and what was in the rows in the header and the text that was in the columns in the jail cell.

Figure: Google Forms Questions

SurveyMonkey:

If y'all export a survey response file from SurveyMonkey, you will see a double header in the CSV file, which is not ideal for a information visualization tool. For multiple choice question types, you will see different columns for different answers which is different compared to Google Forms.

Effigy: Double Header in SurveyMonkey CSV File.

Other Survey Tools:

You will also detect the same problem in other survey tools like in Getfeedback; you will see double header like SurveyMonkey. In QuestionPro, you will get questions in the third row and multiple unnecessary columns that you need to fix manually before visualization.

So survey responses are structured in unlike ways in different survey tools, which is not perfect for data visualization. Only in one case y'all outset using a single survey tool, the process becomes more comfortable for you when visualizing the survey results in a data visualization tool.

2. Different Structure Depending on Question Type

Depending on the question type, the structure of the output is going to exist different in the rows and columns. Let's become back to Google Forms CSV file to encounter what I mean.

Single Response Question:

Allow'southward look at our single response question (what is your gender?) where people can choose only i reply (male/female), and you will see the answer is in one cavalcade.

Effigy: Unmarried Response Question in Google Forms

Numeric Question Blazon:

For numeric question type on Google Forms (How likely is it that yous would recommend this software to a friend or family?), you get a numeric value (1 to 10) instead of  text in a single column.

Figure: Numeric Question Type in Google Forms

Matrix Question Type:

For the matrix question type on Google Forms, you get multiple columns for each row in that matrix. "Whether you use these features available in this software?" Also, you will see rows of that question like graphical presentation, offline way, online mode, etc. are attached in the column name similar "Whether y'all utilise these features available in this software?[Online mode]".

Figure: Matrix Question Type in Google Forms

Multiple Choice Question Blazon:

In the multiple-choice question type, we get one column containing many answers separated by a delimiter comma (,).

Then we can see that the structure of the rows and columns for a question is different depending on what the question type is. This gives us some other problem that we demand to solve to analyze survey data in excel or whatever data visualization tool.

Figure: Multiple Option Question Type Google Forms

We can see here that the structure of the rows and columns for a question is different depending on what the question blazon is. This gives united states of america some other problem that we need to solve to clarify survey data in excel or whatsoever data visualization tool.

3. The Construction of the Output is Non Optimal

Another affair is the overall output of survey data from the survey tools is not optimal for putting that information into a BI tool or information visualization tool. Survey data needs to be pivoted into iii core columns resp id, questions, and answers to go far ready for visualization. One time the data is pivoted we volition add additional columns exterior of the original 3 cadre columns to make things a lot easier. I telephone call this a crosswalk or helper file.

Software's to Solve Survey Information Bug

I'm going to teach the best method to clean survey data while doing it manually in Excel. Doing it in Excel is very deadening compared to using a tool designed to walk you lot through the process, as well equally automating it. There are some tools outside of Excel which can make your life easier, which I have listed below.

1. VizualSurvey

VizualSurvey does all of this for you using our innovative survey information cleaning tools so your tin start visualizing survey data in a dashboard quickly without having to create or learn a data visualization tool. Our VizualSurvey tool works in simple three steps

1. Send us your survey data or let u.s.a. program it: Transport usa your survey data from whatsoever survey software or allow us program the survey for you using our survey software or some other survey tool of your pick.
2. We make clean and transform your survey data using of exclusive data cleaning tools: Our survey software allows u.s.a. to provide you a cute survey dashboard and presentation speedily and then you don't accept to wait months.
3. Start visualizing your survey data in a custom dashboard and presentation: We create an interactive survey dashboard and/or presentation so you can start visualizing your survey results instantly.

If you desire to brand things even easier y'all can utilise VizualSurvey Online to create and programme your surveys, and then you can skip the information cleaning procedure entirely.

ii. Tableau Prep

Tableau Prep is a general information cleaning tool that you tin use similar Excel to clean any information but you will have to learn the all-time technique for cleaning survey information and program it yourself.

3. Alteryx

Alteryx is another tool used for handling large datasets. Using multiple functions, you lot tin make your data ready for graphical representations. Again you lot need to know how to use it, and the methods to clean survey data since information technology'due south a general data cleaning tool.

4. Excel

Everyone knows Excel only the process of cleaning survey information using Excel is very difficult and manual, which I will show y'all now every bit information technology's available to everyone compared to other tools. Excel is practiced for storing data, simply it's not a data visualization tool. Unfortunately, many people try to clarify data using graphs and charts using Excel.

Best Method to Clean Survey Data

And then far, we have discussed the main problems people see with survey data; now we will see what the all-time practices are when it comes to cleaning survey data. We are going to go through the entire information cleaning and information prep process for a Google Forms survey that we conducted. This article will reveal what methods we used and why we did it.

1. Export Survey Data

As Google Forms is free, you can download or consign survey data in a CSV file. Some software such as SurveyMonkey, you need a paid plan to export survey responses. Still, we volition only discuss Google Forms survey responses to make the process easier for yous.

2.  Delete Columns That are Not Needed

If nosotros look at our Google Forms survey CSV file, we can see the first column is Timestamp, which is generated by Google Forms. So every bit the "Timestamp" column is an extra column  we will not use nosotros can delete this column and whatever other questions or columns y'all don't want to analyze. We desire to go along only the questions we care most or else it is extra work for no reason.

3. Add Respondent ID If Not Present

Another thing is we have no respondent id cavalcade hither. Nearly survey software does, Google Forms don't for some reason. And so let'south add together a column for the respondent id. Information technology just has to be a distinct id of some sort so that we can count distinct IDs. Here I am putting a distinct row id for each row.

Figure: Respondent Id Column Added

iv. Carve up Multiple Pick Column if Needed

Now for multiple choice questions in Google Forms, you can see all the answers are in a single column separated by a delimiter. Nosotros need to split them out in order to count each of these answers for each respondent. To do this, Permit's follow this process.

• Highlight the multiple-choice column.
• Click on "Data" and and then go to (text to columns). A pop up box volition open up.
• In the box, put a cheque on delimited and click adjacent.
• Select comma and click finish.

So now, we have separate columns for each answer with no header on the new ones and then nosotros need to copy the header text into all the new empty column headers.

Figure: Splitting Cavalcade for Each Answer

v. Copy Columns into a New Tab and Create a Crosswalk

At present, I'm going to brand a new worksheet and copy all the headers going across except respondent ID and paste (transpose paste) it in the new worksheet. Create an extra row for the header and name information technology Questions.

Effigy: Creating a Crosswalk Worksheet.

Now let'south add a few additional columns called "question number", "final question text", "question type", and "matrix row". Now, if you see the questions from Google Forms, you volition meet the question number is associated with them. For some question types, for example, the matrix and multiple choice question type has multiple cavalcade headers associated with but ane question number. This ways these are not a separate question, and so we accept to group them together. In the question number column give a single number to a single question this will group the same question columns together by giving them the same question number.

Figure: Grouping the Aforementioned Question

Next is the terminal question text column, most of the time what is in the header field is non what the end user saw. This is where you will make clean up the text that the end user volition come across as the question. The main matter is for every question number y'all will take the aforementioned exact concluding question text, so for the matrix question all the columns associated with that question number will have the aforementioned final question text.

Figure: Final Question Text

The final one is the matrix row, which helps u.s.a. with the matrix question types when trying to visualization that question blazon. Here nosotros will put the values that was in the rows of matrix question and paste it in this column. You will discover the values that was shown on the rows at the stop of the original question column, this is sometimes different depending on your survey software.

Figure: Question Type and Matrix Row Column

The skillful thing about this crosswalk is we only accept to do this once, so at this point nosotros are all done and its time to movement to the next footstep.

6. Add Tableau Extension

Now download the Tableau Excel extension and install it in your PC then you can use Tableau 7.0 Excel Add-in in your excel canvas. That'southward going to exist used to reshape the information for us.

7. Reshape data

Ok, now let'due south become back to the original dataset. Now using the Tableau add-in, we will reshape the data. Follow this process.

• Click on the B2 cell, which is the beginning respond to the first question.
• Click on Tableau add together on and click reshape information.
• A dialog box volition pop up. Just click, ok.

Effigy: Reshape Information

The data will then be pivoted into 3 columns into a new worksheet. The showtime column is for respondent id, the second cavalcade header is questions and the 3rd column header is answers. These are the 3 core columns that we demand.

8. Do a VLOOKUP to bring in the Helper Columns

At present nosotros are going to do a unproblematic VLOOKUP Excel role to lookup and bring in the data from the crosswalk tab we created. Ok, let'south wait at VLOOKUP Syntax beginning.

• VLOOKUP Syntax = VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• lookup_value = This is the value to search for (value/cell reference).
• table_array = This is the tabular array from which to recollect a value.
• col_index_num = This is the cavalcade in the table from which to retrieve a value.
• range_lookup = Approximate match (default). FALSE = exact match.
• At present to do VLOOKUP, let's follow the process.

Step i: Kickoff copy and paste boosted column headers from "Crosswalk" worksheet to the original worksheet. Select the B2 jail cell and make it absolute $B2 for only the column to wait for questions.

Figure: Cleaning Survey Data Using VLOOKUP

Step ii: At present become to "Crosswalk" worksheet, and select from A2 to E18 for table_array, and absolute the entire thing. At present we accept to select a column from which to retrieve the information. The counting starts from the leftmost column in the table array, which is 1. And so we type 2 to go a matching value from column B. For range_lookup we will type faux as we want an verbal lucifer.

So it volition be =VLOOKUP($B2,Crosswalk!$A$ii:$E$18,ii,false)

Press enter fundamental to complete formula, and you will see one in D2 cell. on the original worksheet which is under "Question Number" column.

Figure: Cleaning Survey Information Using VLOOKUP

Pace iii: Now copy the same formula to other columns to the correct of information technology. We just have to change "col_index_num" to get values for these columns.

For "Final Question Text" column it will exist: VLOOKUP($B2,Crosswalk!$A$2:$E$xviii,3,false).  As we typed three, it will get value from column C of "Crosswalk" worksheet.

For "Question Type" cavalcade it will be: VLOOKUP($B2,Crosswalk!$A$2:$E$18,4,false). As we typed 4, it will go value from cavalcade D of "Crosswalk" worksheet.

For "Matrix Row" column it will exist: VLOOKUP($B2,Crosswalk!$A$2:$E$xviii,5,faux) For this column, if it finds value in cavalcade E, it will bring it in. Otherwise, it volition show 0, which is ok.

Figure: Cleaning Survey Information Using VLOOKUP

The Basic on How to  Visualize Survey Data

And then this is the basic process and method to make clean survey data, of course it tin can get a lot more complicated then this. In this part of the weblog, we volition see how you can visualize the new survey dateset in Tableau. Y'all can download Tableau Public for complimentary if you have not nonetheless merely I am just going to give y'all an overview of what to do once you go your data cleaned.

1. Count Distinct Respondent Id's

Connect your data to Tableau, and you can see all the column names on the left side. Offset, we need to create a new field chosen respondents, which is a singled-out count of respondent ID.

Effigy: Creating Distinct Respondent Id

2. Add a Question Number or Final Question Text Filter

Now let's go ahead and throw out Question Number, Question Type, Final Question Text and Answers in the rows, and drag "Respondent" from measures to the ABC column. Click on the question number and select show filter from the sub card.

Figure: Excel to Tableau Reporting

3. Separate Charts by Question Type

We volition make a new canvass for each question type. Then for the text box question type, we will create a new sheet that contains all the questions that accept the aforementioned question type in this case text box. For nil code, we will do a map chart, and for a unmarried response, nosotros can create a bar chart. For the date, nosotros can create a line chart or expanse chart. For numeric question type, nosotros can testify the median, boilerplate, etc. The main point here is each question type will have its own canvas/chart which is fine since the data is structured the same for a question type, and then you can but flip through each of the questions associated with that question type in a single chart.

4. For matrix Question Type

For the matrix question, we have to exercise some additional piece of work. Here you need to drag the answer field in columns and matrix row in row. See the picture below.

Figure: Matrix Row Question in Tableau

Here we have completed a basic survey information cleaning in excel and to requite you an thought of how you can set up survey results or responses for a data visualization tool. Over again nearly of the time, your survey data will exist more complicated than this with many question types, and you may need to connect multiple excel sheets in Tableau. The process volition be very difficult for you if you lot are non a data visualization expert or familiar with these methods. Information technology's as well time-consuming. That's why VizualSurvey was created to speed upward the process and exercise all the hard work for you. If you are interested in trying it out merely ship us a message.. Run into you lot next time!

The simple and quick solution for amazing survey dashboards & Interactive Presentations

Source: https://vizualsurvey.com/blogs/cleaning-survey-data/

Posted by: marshallscon1994.blogspot.com

Share this post