How Can I Get Matrix Question Data?

Question

How can I get data for Typeform matrix questions?

Environment

Connector: Typeform

Answer

To get Typeform matrix question data, follow the steps below:

Retrieve matrix question and its rows

We sync high-level matrix question data to the FORM_FIELD_HISTORY table. For each matrix question, we sync:

• One parent record where type=matrix
• One child record per row, where type=multiple_choice

To retrieve the parent record and its rows, run the following query. Replace the placeholder <parent_field_id> with the ID of the parent record:

SELECT
  form_id,
  id,
  form_parent_field_id,
  type,
  title,
  _fivetran_active
FROM form_field_history
WHERE (id = '<parent_field_id>'
       OR form_parent_field_id = '<parent_field_id>')
  AND _fivetran_active is true;

Retrieve response values

To get the selected answers for each matrix row, join the following tables and filter by a parent_field_id value:

• RESPONSE_ANSWER_CHOICE
• FORM_FIELD_CHOICE_HISTORY
• FORM_FIELD_HISTORY

Run the following query, replacing the placeholder <parent_field_id> with the same value from step 1.

SELECT ffh.form_id, ffh.form_parent_field_id, ffh.type, rac.field_id, ffh.title, ffch.label
FROM response_answer_choice as rac
JOIN form_field_choice_history as ffch
ON rac.choice_id = ffch.id
JOIN form_field_history as ffh
ON ffch.field_id = ffh.id
WHERE ffh.form_parent_field_id = '<parent_field_id>' and ffh._fivetran_active is true
ORDER BY ffh.id