How to read the results spreadsheet

All the response data is collected to an Excel spreadsheet that is divided into tabs. In this article, we explain how the data is organized. As an example,  we  are using the results data from a demonstration questionnaire; you can view the questionnaire here.

Tab overview:

  • Respondents: contains all responses to non-map based questions and is listed according to respondent ID, so one row = one respondent's answers. The columns represent the questions and answer options. The way the data is organized makes it easy to analyze further with for example the pivot table function in ExcelWhen analysing your data, we strongly suggest focusing on this tab when analysing non-map questions. 
  • Responses: contains the responses to the questions listed by response, one row = one response to a question.
  • Element Response Sheet: this sheet simply lists every map question in your survey, and which tab contains the data for each of them.
  • Tabs with names such as "geo-point-4f7jgk": each of these tabs contains responses to one map question.

Below, you can find more detailed information on what each column heading stands for:

Respondent information

standard.png

Respondent ID — This is an ID that we generate for each respondent when they first answer a question in the questionnaire. If you have turned the Maintain respondent session setting on, the ID will remain the same for the respondent even if they fill the questionnaire in several sessions (if browsing from the same device). The respondent ID also helps in identifying spam.

Publication ID — Every time that you publish a version of your questionnaire, it is given a publication ID. For example, if you go back to fix an error in the published questionnaire and then re-publish it, it will be given a different publication ID. This way you can always tell which version of the questionnaire the respondent has replied to.

Submitted — This is the date and time when the respondent submitted their responses by clicking the Done button. The dates and times are in the UTC (GMT+0) time zone)

Please note that sometimes respondents may not understand that they need to submit their answers by clicking 'Done!', even if they have answered the entire questionnaire. This is not a problem in Maptionnaire, as we record the responses given regardless of whether they are submitted in the end or not.

First active — The date and time when the respondent first accessed the questionnaire. The dates and times are in the UTC (GMT+0) time zone)

Publication consent — This will be listed as either TRUE (=consent) or FALSE (=no consent). If you have not turned on any of the settings that ask for publication consent, consent is automatically assumed.

Language — The language that the questionnaire was taken in.

Multiple choice questions

The columns that follow the essential data explained above contain the answers to your non-map-based questions. In the next section we list each question type and explain how the responses to them will be listed in the spreadsheet. For instructions on how to visualize the data in these questions, please see this article.

Choose one and Poll

radiobutton.png

Each answer option is given a value, with the first one getting the value 0, the second 1, the third 2, etc. For example, in the results for the question above, 'Woman' would be marked as 0 in the results data sheet, and therefore if the respondent has chosen '0', it means 'Woman'. The column that lists the answer chosen by the respondent is followed by a column giving the option identifier number for that answer option, and the next the label or name of that option. They are followed by columns for each of the answer options. In these, the text TRUE or FALSE is used to denote whether the respondent has (=true) or hasn't (=false) chosen that option.

This is what it looks like in the Excel spreadsheet (click the image to view a larger version):

results_choose_o.png

The first column (V) shows the number of the option chosen; the second (W) the option identifier of it, the third (X) the name of that option in the survey. The following columns go through each option (1 column = 1 answer option), with FALSE = option not chosen by this respondent and TRUE = option chosen by this respondent.

Choose multiple 

choosemultipl_exampleanswer.png

Instead of using numbers, the responses are categorized as either true (=option chosen by the respondent) or false (=option not chosen by the respondent). For example, the response in the image above would be listed as [false, false, false, true, false] in the results data.

Similarly to Choose one or Poll, each answer option also has its individual column with TRUE or FALSE according to whether that option was chosen or not.

This is what it looks like in the Excel spreadsheet (click the image to view a larger version):

results_choose_m.png

The first column (AF) lists all the answer options and what each respondent chose in response. The following columns go through each option (1 column = 1 answer option), with FALSE = option not chosen by this respondent and TRUE = option chosen by this respondent.

Multiple choice grid

In the first column, you will see all the responses that this respondent has given to this question, for example [0,1,3,2,-1]. The numbers stand for the answer options (from left to right), with the first option from the left being 0, the second 1, etc. -1 means that the respondent did not choose any option.

For example, if the responses to the question below are [0,1,3,2,-1], that means that the respondent cycles daily, uses a private car 1-3 times/week, never carpools, and walks 1-3 times/month, but did not respond to the final question on public transport. 

radiobuttongrid_results_sheet.png

After this column, there are columns for each subquestion, always followed by a column stating the answer option that this respondent chose for that subquestion (e.g. 'Daily' to '...by bicycle'). You can edit the "option identifier" names in the response settings of each subquestion in the questionnaire editor. Please remember that you need to do this before publishing the questionnaire as changing the option identifier while data is being collected may affect the data! 

Other question types

Number and open questions are simply presented as the number or text that the respondent has entered.

The range question answers are presented in brackets, e.g. [3200, 6650] for 3200-6650. The next two columns state the minimum and maximum values of this range.

The slider question responses are listed as a value. This indicates where on the scale the respondent has chosen to moved the slider. For example, if the minimum value is 0 and the maximum 100, and the response data shows 50, this means that the response is either 50 (if you asked for a number) or neutral or similar (if you asked another type of question).

File upload/Images and other files uploaded by respondents – The Excel sheet contains a link to the file(s) the respondent has uploaded.

Map questions

Element Response Sheet — This spreadsheet is a directory of the map responses spreadsheets. It lists each spreadsheet containing map data and shows which question on which page they are linked to.

point_responses.png

Map question tabs — As stated, the response data to a map question is always presented under its own tab. One tab = one map question. It includes also the response data on any follow-up or pop-up questions. The first six columns in the sheet follow the same structure as outlined above. The additional columns are:

Index — Shows the order in which the respondent placed the map markings, if they have placed several to answer the same question. E.g. 2 means that it is the second marking that the respondent has left to that question.

Wkt — The coordinates for the marking. The Wkt coordinates are compatible with for example QGIS. To export your survey data to ArcGIS, we recommend downloading the map responses as shapefiles.

The columns that follow the coordinates contain the responses related to the pop-up questions.

If you are looking to export the response data to a GIS software, please note that Maptionnaire currently uses the WGS84 (EPSG:4326) coordinate system for all its data. 

How many people visited our questionnaire?

The results file contains all responses left by respondents to the questionnaire, regardless of whether they have submitted their responses at the end or not (by clicking the Done button). If you would like to find out how many visitors you had to your questionnaire, go to Team settings and the tab named Reports. Download the questionnaires report. It's a CSV file, so you need to organize the data into columns on Excel. The column titled All respondents shows all visitors to the questionnaire, regardless of whether they left any data or not.

Was this article helpful?
0 out of 0 found this helpful

Comments

2 comments
  • Is there a tutorial on how to convert the map question (polygons) into ArcGIS shapefile? 

    0
  • Hi Michelle,

     

    First of all, apologies for the delay in replying! When taking your survey data to ArcGIS, we recommend using the shapefile download option. Please see this article on downloading shapefiles for further information: https://support.maptionnaire.com/hc/en-us/articles/4402730169362-Download-shapefiles-and-geoJSONs 

    0

Please sign in to leave a comment.