Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Qualtrics Survey Scoring Column

Not planned
Kyle Yee User
  • Edited

The Qualtrics connector schema here (https://docs.google.com/presentation/d/1YPPs_nmfPA7Uh02dQH4j-ViFfF2qniNMexwR3qfvY88) does not have any column for the Qualtrics-level score computed for a survey response (described more here: https://www.qualtrics.com/support/survey-platform/survey-module/survey-tools/scoring/). While it is technically possible to re-compute this survey level score by sifting through `QUESTION_RESPONSE` rows and pulling out the appropriate values, this adds extra complexity to a fairly simple use-case of the Qualtrics data.

Is it possible to add a `score` column to the `SURVEY_RESPONSE` table to easily fetch the Qualtrics-computed score, if there isn't already an easy way to fetch this data? 

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Ray User

    Hi Kyle,

    I appreciate the request and the complexity of the recreation of this computed metric. Despite that, we do still need to maintain our commitment to sync only normalized, lowest-level data. You can read more about our shared responsibility model here.

    Going forward, this looks like it could be a great opportunity for Fivetran to provide value in the form of Transformations. As you may know, we publish a number of dbt modeling packages that layer on top of our connectors and help transform data. When we look at creating a package for Qualtrics, we'll make sure the response-scoring use case is considered!

  • Kyle Yee User

    Adding some more information here, we need to execute the following query (involving 3 separate joins) in order to get something close to Qualtrics survey scores: 

    WITH scoring_questions as (
    SELECT s.id as survey_id, q.selector, q.question_text, s.survey_name, q.data_export_tag as survey_type, q.id as question_id FROM qualtrics.question as q
    LEFT JOIN qualtrics.survey as s
    ON s.id = q.survey_id
    WHERE s._fivetran_deleted = FALSE
    AND q.data_export_tag IN ('nps', 'csat')
),
survey_question_responses as (
    SELECT try_to_numeric(qr.value) as score, sr.survey_id, qr.question_id, sr.id as response_id, sr.end_date as response_time, sr.recipient_first_name, sr.recipient_last_name, sr.recipient_email FROM qualtrics.question_response as qr
    LEFT JOIN qualtrics.survey_response as sr
    ON qr.response_id = sr.id
)

SELECT * from survey_question_responses as sqr
RIGHT JOIN scoring_questions as sq
WHERE sqr.survey_id = sq.survey_id AND sqr.question_id = sq.question_id AND score IS NOT NULL

    This query is both expensive and brittle. With a `score` column in the `SURVEY_RESPONSE` table, we would be able to simplify this to something like: 

    SELECT score from qualtrics.survey_response