Connector Improvement: SurveyMonkey response_answer TimestampAnswered
My team is working to ingest our survey data from SurveyMonkey using the SurveyMonkey connector. We noticed after working with the data and the ERD that it is not possible to link response_answer records to the proper question_history and response_history records. This is not possible because the response_answer record doesn't have a timestamp on it that allows you to join to the proper SCD record in the previously mentioned history tables.
When I reached out to support, they suggested to use whatever the current version of the questions and responses. This suggestion makes sense for the response because you don't really care about a response until someone is done with it, but the question that was served at the time of the response answer is important to answer questions like: "what options were shown in the choice question when this question was answered?"
I can't imagine that SurveyMonkey doesn't surface this field on their end and hope that we can get that passed through in the Fivetran connector.
Thanks for the thoughtful request. It was in fact a design choice to limit this table to the latest values rather than history as responses are not expected to change meaningfully. Your example of reviewing options available at the time of response does make sense though. While we don't have any plans to change this right now, we may look into making more tables available for history mode in the future as part of a broader effort. We'll update this thread once we have more to share.
Thanks for the response! Understood that it was intentional, but the response_answer itself doesn’t need to be a history table. Even if it’s just tracking the current version (which I agree it should), you need to know what time the answer was submitted to join to the proper dimensions (question_history and response_history). Our hack right now is to use the updated_at timestamp on the response_history record to figure out what question_history record to use. Is this the recommended approach?
Please sign in to leave a comment.