What are DWA ‘Qualtrics_v3_surveys: Views' naming convention changes’?
The current Qualtrics Views provided to clients are generated at the survey level that are built out of qualtrics_v3_surveys. Most of our clients are using these views for querying and getting insights of the survey responses.
Why did Two Circles want to update the Views?
Some column names in these Views are excessively long, which causes readability issues and prevents clients from querying these columns effectively.
What Clients are affected by this change?
All Two Circles DWA clients currently using Qualtrics DWA integration tables and views are impacted by this update.
What’s new or changing?
Currently the Qualtrics_v3_Surveys.Views column names are populated using questiontext field, we will be updating the applicable column names with questionname field, so that it will be easy for clients to write SQL Queries from the views.
Example
Before the change
If a view named Post Game Survey currently has the following columns:
responseid (varchar(50))
how do you feel about the half time event that is provided by our local university students (float8)
how likely are you to buy tickets to an upcoming game because of the $3 value food menu? (float8)
game (varchar(4000))
email (varchar(4000))
enddate (varchar(4000))
After the change
The columns will be updated to the following standardized names:
responseid (varchar(50))
qid1_1 (float8)
qid2_3 (float8)
game (varchar(4000))
email (varchar(4000))
enddate (varchar(4000))
Below are illustrated images of current state and future state.
Current State:
Future state:
What is not affected or included?
Frequency of ingestion remains unchanged.
No new tables are being added.
No changes to tables in any of the four (qualtrics, qualtrics_v3, qualtrics_v3_surveys, and qualtrics_v3_legacy) Schemas
How does a client enable this feature?
The updated Qualtrics views will be automatically deployed to all customers currently using Qualtrics integrations.
Clients will be notified of their individual implementation window.
How to map the survey question description with question fields in the View/s
The template query below connects question fields in the qualtrics_v3_surveys view with their corresponding descriptions in qualtrics_v3.surveys table. This allows to enrich raw response data with appropriate metadata, ensuring that each question response is paired with its correct, human-readable description.
Below is a template:
WITH base AS (
select distinct
s.responseid,
s.processed_date,
-- force all unpivoted columns to the SAME type
s.qid12_1::VARCHAR(65535) AS qid12_1,
s.qid12_2::VARCHAR(65535) AS qid12_2,
s.qid12_3::VARCHAR(65535) AS qid12_3,
s.qid12_4::VARCHAR(65535) AS qid12_4,
s.qid15_text::VARCHAR(65535) AS qid15_text,
s.qid27_file_id::VARCHAR(65535) AS qid27_file_id
-- you can add all the question fields for the survey, the above listed ones are just for exampl.
-- Enter the question field(any field starting with 'qid..' except the ones that have this format 'quid+numericvalue_do'(ex:qid10_do))from the view
FROM "qualtrics_v3_surveys"."enter view name" AS s
),
responses_long AS (
select distinct
responseid,
processed_date,
question_id,
responses
FROM base
UNPIVOT INCLUDE NULLS (
responses FOR question_id IN (qid12_1, qid12_2, qid12_3, qid12_4)
-- you can add all the question fields for the survey
)
),
schema_long AS (
select distinct
prop_name AS question_id,
prop_value.description AS question_description
FROM "qualtrics_v3".surveys v,
UNPIVOT v.response_schema_values.properties AS prop_value AT prop_name
WHERE lower(v.id) = 'enter id of the survey'
)
select distinct
r.responseid,
r.processed_date,
r.question_id,
r.responses,
s.question_description
FROM responses_long r
LEFT JOIN schema_long s
ON s.question_id = r.question_id;
Internal Release Notes:
https://koresoftware.atlassian.net/wiki/spaces/DWA/pages/4827414551/Data+Integrations+Internal+Release+Notes+-+Qualtrics