How to data model the output of Response API calls

Hello DataPalla, 

Let me start by saying I’m not a data professional so take my suggestion with a grain of salt. That said, I do love using BI tools to visualize internal org data for my roles and it’s kind of a hobby so I’ll allow my self to add in my 2 cents to this conversation, although most likely as our community grows someone will give you a better answer than mine :) 

I will assume that you have only one form, if you have more, then I would suggest you follow DB normalization and create more tables. For instance, you could create Responses→ Questions→ forms tables. If you have only one form I would just take the simple road: 
 

Using the response definition here I would store it in a responses table like this: 

• Token → Primary Key (The landing_id and token values are the same.)
• Network ID → This one is a must too and really useful to avoid duplicates, you can force it to be unique and discard the insertion if you already have a record for your user, but this depends on what type of form it will be. If you want to keep only the last submission in case there is a duplicate then you’d have to have another logic there. Lastly you can be nasty and just add everything to DB and then do the filtering in your BI tool. 
• I won’t list all the session data but you can store user_agent, browser and all that stuff if you needed. 
• One column for each question with the ID of the question merged into the title, so if 2 questions have the same title you can differentiate them.  
• Our timestamps (landet_at, submitted_at)
• Your timestamps (created_at, updated_at...)

Regarding the question title with the ID merged, note that many times form creators will ask the same questions in different groups.

For instance, let’s check this sample form:

Help us review your yearly salary since 2018! 

 

Question group: Let’s start with 2018 → ok!

      → Child question: Where did you work? 

      → Child question: What was your position? 

      → Child question: What was your yearly salary in K?

 

Question group: Perfect, let’s review 2019 → let’s go!

      → Child question: Where did you work? 

      → Child question: What was your position? 

      → Child question: What was your yearly salary in K?

 

Question group: Perfect, let’s review 2020 → let’s go!

…

…

Now when you are checking the data in a BI tool if you don’t have an ID you won’t know if the reply is for 2018, 19 or 3005 because question groups are not passed into the response. Therefore, at the very least when you create the question title column you should merge the id into the title. 

• question_title → ($id) $text 

Then if you don’t like the name you can rename it in your BI to something like “Salary in 2018”

Ok and I think that should be it for a quick start!