fct_survey_question_responses


Description

A Survey Question Response represents an individual answer provided by a respondent to a survey question in Lookout.

This model aggregates responses along with respondent roles and completion status, enabling analysis on survey results, gaining insights into the experiences and needs of their Members.

For more information on the Surveys Model, see fct_surveys.

Purpose

This model is designed to answer the following questions:

  1. What responses have been provided to survey questions?
  2. Who responded to the surveys, and what are their roles? (respondent_role)
  3. When were the surveys completed? (survey_completed_at)
  4. Were any exceptional options selected in the responses? (option_exceptional)

Respondent Roles (respondent_role)

Respondents can have various roles within the system:

  • Member
  • AuthRep
  • BillingRecipient
  • EmergencyContact
  • Sharer
  • Staffer

See the Glossary of Terms in the Lookout Manual for more information about roles.

Exceptional Responses

Some multiple-choice options can be marked as exceptional. The option_exceptional field indicates if the selected option was set as exceptional. This can help in identifying responses that may require further attention.

Limitation: The option_exceptional field does not surface exceptional responses to other question types at this time.

Survey Completion

The survey_completed_at field indicates when the survey was completed by the respondent. This can be used to track if the survey was completed, as to not report on incomplete surveys.

Text and Date Responses

Please note, text and date responses are not captured in fct_survey_responses, as they may contain Personal Identifiable Information (PII), and are therefore excluded. To access text and date responses, please use the Data Export Tool via the Lookout Admin App.

Related Models

  • For details about Surveys, see fct_surveys.
  • For details about Survey Questions, see fct_survey_questions.

Columns

Column Type Children Parents Comments
survey_request_response_id int8

The unique identifier for the survey request response, E.g.: 1.
survey_id int8
fct_surveys.survey_id Implied Constraint R

The ID of the survey containing this response, E.g.: 123.
survey_question_id int8
fct_survey_questions.survey_question_id Implied Constraint R

The ID of the survey question containing this response, E.g.: 1.
survey_request_id int8

The ID of the survey request containing this response, E.g.: 123.
membership_id int8

The ID of the membership for this response, E.g.: 1.
numeric_value int8

The numeric response provided for analog_scale and number question types, E.g.: 42
selected_option int8

If the question was multiple_choice, the option selected by the respondent, E.g.: 42. DEPRECATED, use selected_options instead.
selected_options text

If the question was multiple_choice, the comma-separated option or options selected by the respondent, E.g.: 1, 3.
selected_responses text

If the question was multiple_choice, the comma-separated list of responses chosen by the respondent, E.g.: First option, Third option
respondent_role varchar

The role of the respondent. One of: AuthRep, BillingRecipient, EmergencyContact, Member, Sharer or Staffer.
survey_completed_at timestamp

The date and time the survey was completed (in UTC), E.g.: 2021-03-17 22:59:01.116801.
option_exceptional bool

If the selected option was set as exceptional.
company_id int8
dim_companies.company_id Implied Constraint R

The company ID, E.g.: 345.

Indexes

Constraint Name Type Sort Column(s)
fct_survey_question_responses_pkey Primary key Asc survey_request_response_id
fct_survey_question_responses_company_id_idx Performance Asc company_id
fct_survey_question_responses_survey_id_idx Performance Asc survey_id

Relationships

Close relationships within degrees of separation