Qualtrics - associate responses based on question's answer

PhilD
PhilD Member
in Connectors

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

SurveyIDResponseIDQuestionAnswer
145Dept HR
145Volume100 
145StatusGreen 
145Date5/3/2019
1752Dept  Marketing
1752Volume175
1752StatusYellow
1752Date5/3/2019
166DeptResearch
166Volume548
166StatusRed
166Date5/3/2019
145DeptHR
145Volume125
145StatusYellow
145Date5/4/2019

 

 

Example desired outcome

SimpleChartExample.png

 

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

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • PhilD
    PhilD Member

    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_
  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • PhilD
    PhilD Member

    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.

     

     

  • DDalt
    DDalt Member

    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! 

  • DDalt
    DDalt Member

    @PhilD did this solution work for you?

  • PhilD
    PhilD Member

    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.

  • DDalt
    DDalt Member

    @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! 

     

     

  • PhilD
    PhilD Member

    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.

Categories