Qualtrics - associate responses based on question's answer

Using the Qualtrics API connector, I bring in the 'Survey Responses' report that depicts the user's answer to each question. When surfaced using the API, the data is returned with each question/answer as a row (vs. each response as a row as when extracted to Excel). This has the obvious benefit of allowing one to combine multiple surveys in one dataset as the number of columns will always be the same. The difficulty I'm having is how to associate all of the question/answers for a given response by using the answer of one of the questions (in my example 'Dept').

For example, the simple table below depicts how a survey's data is structured In DoMo using the API connector. Because 'Dept' in this case is just one question/row, the only way to associate all of HR's other answers is by the 'ResponseID' as shown.

Example table

SurveyID	ResponseID	Question	Answer
1	45	Dept	HR
1	45	Volume	100
1	45	Status	Green
1	45	Date	5/3/2019
1	752	Dept	Marketing
1	752	Volume	175
1	752	Status	Yellow
1	752	Date	5/3/2019
1	66	Dept	Research
1	66	Volume	548
1	66	Status	Red
1	66	Date	5/3/2019
1	45	Dept	HR
1	45	Volume	125
1	45	Status	Yellow
1	45	Date	5/4/2019

Example desired outcome

I considered using a MySQL data flow to build a table of response IDs and Depts and then join this to the main table but I'm a bit new to MySQL (and DoMo) and wasn't quite sure if this is the way to go.

Any examples or insight much appreciated. Thanks!

Find more posts tagged with

Comments

ST_Superman

I feel like there needs to be an extra column in this dataset somewhere. Otherwise, there is no way to distinguish between the two different dates for the HR survery results. Is there a batchID field, or something similar that is missing from the sample dataset you gave?

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

PhilD

Thanks @ST_-Superman-_

Yes, there are other columns as my example was meant to just communicate what I needed.

Here is a full list of the columns returned by the 'Survey Responses' report on the Qualtrics API

Survey Name

Survey ID

Answer Value

Recipient First Name

IP Address

Start Date

Question Key

Question ID

Location Accuracy

Question

Location Longitude

External Data Reference

Status

Finished

Response ID

Location Latitude

Answer

End Date

Recipient Last Name

Recipient Email

Response Set

Question Text

Question Type

Question Selector

Question SubSelector

doesForceResponse

Choice Description

Choice Recode Value

Sub-question Text

Choice Text

_BATCH_ID_

_BATCH_LAST_RUN_

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

ST_Superman

Thanks, that's helpful.

Do you know which field, or fields, could act as a unique identifier? From your previous example, it does not appear that either SurveyID or ResponseID are unique. Maybe some combination of IP address, email, start date, end date?

Basically, you need to uncollapse the "Question" and "Answer" columns, but in order to do so, there needs to be a unique identifier that would identify each survey.

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

PhilD

I did see the "collapse" and "uncollapse" options in the ETL transform but could not seem to get the options it supplied to do what I needed. Based on your feedback, this is likely due to not having a unique ID. If I'm understanding you correctly, it sounds as if the truly unique ID would be used to iterate the function properly and write the value to the matching rows. This makes now that I think about it.

When I get a chance, I will try building up a unique ID using some method such as the ones you suggested, then try to apply the "uncollapse" on the appropriate columns to see if that works.

Thanks for the reply, it is helpful.

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

DDalt

Hey Phil,

I was able to create a data set with one row per response for a Qualtrics "Survey Responses" report using the settings in the attached PDF.

In your new output data set, you can use a beastmode to calculate number of responses: COUNT(`Response ID`) and then Department should now be a dimension that you can drop into the Filter section of the Analyzer.

Let us know if this works!

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

DDalt

@PhilD did this solution work for you?

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

PhilD

Hi @DDalt

Unfortunately I haven't actually had time to try this but it is on my list. One thing that comes to mind is that to do this would require pulling each survey's data separately (as each would have a different number of questions). Not a deal breaker, just an observation.

I will definitely be doing this and will update the thread with my results/findings/observations. Thanks for following up.

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

DDalt

@PhilD Okay - I see now that you can use the connector to "Pull data for all surveys" and that you are wanting to pivot the data based on this big data set.

I think all we would need to do is, as @ST_-Superman-_ mentioned, add a unique identifier. To do this, we could combine the Survey ID and Response ID as a new column and call it 'Unique_Identifier'

Then, in your Uncollapse Columns tile you put 'Question' for "Select the columns that has the columns labels you want" and 'Unique_Identifier' as "Select the column(s) that identify a row"

I'm not sure how many columns you will want to create after that step. I would just start simple and try to create an output that just has three columns: Unique Identifier, Survey Name and Department. This will allow you to count the number of responses by Survey Name (beast mode: COUNT(`Unique_Identifier`) and ensure that you have the proper number of rows per response. If the counts from your beastmode match your number of responses for each survey in Qualtrics, you should feel good and can start adding from there.

Let us know how it goes!

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf

PhilD

This is proving to not really be a usable solution. This is too bad because DoMo could have been extremely useful in our organization. We have lots and lots of Qualtrics surveys and the built-in Qualtrics reporting is not as good as DoMo but I have spent some time trying to figure this out and I'm pretty much convinced that the API is designed only to report on things like how many responses you are getting, not to analyze the actual content of the responses. Even if the uncollapse function could work (I personally couldn't get it to work) having to basically re-write every survey in order to hard-code the transposition is simply not doable on a large scale.

I thank those that tried to help.

Qualtrics One Row per Response Magic ETL.pdf

Qualtrics One Row per Resonse for All Surveys.pdf