Hi Team,
We are capturing survey responses in Adobe Campaign Classic v7. The responses are captured in a schema in the below format(The survey results with the ID end up in a schema separate from the recipients making it very hard to see who completed the survey):-
Survey ID
| Recipient ID | Question ID | Questions | Response ID | Responses |
|---|
| survey-1 | rid1 | q1 | Do you like us? | r1 | Yes |
| survey-1 | rid1 | q2 | Do we have competitive advantage? | r2 | Yes |
| survey-1 | rid2 | q1 | Do you like us? | r1 | No |
| survey-1 | rid2 | q2 | Do we have competitive advantage? | r2 | No |
| survey-2 | rid1 | q1 | Which products do you most like? | r1 | pdt1 |
| survey-2 | rid1 | q2 | What product features do you like best? | r2 | feature1 |
| survey-2 | rid1 | q3 | How reliable is our product? | r3 | very |
| survey-2 | rid2 | q1 | Which products do you most like? | r1 | pdt2 |
| survey-2 | rid2 | q2 | What product features do you like best? | r2 | feature2 |
| survey-2 | rid2 | q3 | How reliable is our product? | r3 | can't say |
Now we have to generate a report per survey which needs to have all survey questions as columns and all recipient details and responses in rows.
Report format:-
| RecipientID | Survey ID | Do you like us? or q1 | Do we have competitive advantage? or q2 |
| rid1 | survey-1 | Yes or r1 | Yes or r2 |
| rid2 | survey-1 | No or r1 | Yes or r2 |
We have tried to build custom report but we are stuck on how to put questions and responses as separate columns as they are dynamic and different for different surveys.
Any pointers will be appreciated.
