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!
Comments
-
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?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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_ 0 -
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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.
1 -
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!
1 -
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.
0 -
@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!
0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive