Data exploration for survey with multiple choice questions?


I am quite new to metabase so apologies if I am not using the right vocabularies.

I have some data coming from a survey tool (kobotoolbox) that I loaded into a postgresql database. We have quite a bit of multiple choice questions which are represented as boolean columns within our main data tables, for example:

org com_social_media_facebook com_social_media_twitter com_social_media_youtube com_social_media_whatsapp
org1 1 1 0 1
org2 1 0 0 1
org3 1 1 1 1

I figured that this was not ideal and thus generated some unpivoted views using SQL:

create view survey_com_social_media as select s.uuid, t.social_media
from survey s
  cross join lateral (
(s.com_social_media_facebook, 'facebook'),
(s.com_social_media_twitter, 'twitter'),
(s.com_social_media_youtube, 'youtube'),
(s.com_social_media_whatsapp, 'whatsapp'),
(s.com_social_media_telegram, 'telegram'),
(s.com_social_media_linkedin, 'linkedin'),
(s.com_social_media_instagram, 'instagram'),
(s.com_social_media_snapchat, 'snapchat'),
(s.com_social_media_tiktok, 'tiktok')
  ) as t(value, social_media)
where value notnull and value = 1

which gave me something like this

org_id social_media
org1 facebook
org1 twitter
org1 whatsapp
org2 facebook
org2 whatsapp
org3 facebook
org3 twitter
org3 youtube
org3 whatsapp

Is this a good representation of the data?

After I join the tables, I am able to generate a question of the distribution amongst the different answers, and to filter by other properties of my org table.

However, as I am currently still exploring the data, I find that metabase’s xray fonctionality for my view is not very helpful. It does not display the field social_media, only some of the field available in the joined main table.

Is there a better way to represent the data which would give me more valuable automated data exploration?

Hi @ni-ka
The X-rays depend heavily on how you have setup Admin > Data Model, and specially the Field Type.
And it is auto-magic, so it might not work for everyone’s data.
There’s a lot of work being put into the upcoming 0.39 release (2-3 months away) to separate the Field Type into four different types, which will allow X-rays (but also other things like formatting) to work much better.

Thanks @flamber for the quick reply. Other than for the xray fonctionality do you think the way I represented the data makes sense?

@ni-ka I’m not sure how you want to represent your data, but the Bar chart you have created seems pretty good - perhaps Pie chart would also work well for this type of data.

@flamber unfortunately it’s multiple choice so the values can be in multiple categories, i could use a pie chart but would have to calculate percentages