Count how many times the word is repeated and create a chat

[carlos_lara]

Hi there! 👋

Context: I'm running feature preference survey and I want to create a dashboard on Looker so I can share with the entire company.

Problem: One of the fields is set to multiple choice and as result, when a person selects more than one option, they are represented on the same row in Google Sheets, separated only by comma. 

Ex: What are your favorite features?

Answer 1: dashboards
Answer 2: dashboards, integrations
Answer 3: integrations, pivot table, dashboards
Answer 4: pivot table, integrations, dashboards, timeline

Im trying to create a chart that represents how many times a feature was mentioned, but my difficulty is that the charts are looking like image 1, instead of image 2

(Image 1)

(Image 2)

What is the best way to count how many times a feature was mentioned and create specific metrics and dimensions for that?

Disclaimer: I dont have much experience with advanced filters and regex, but I can try!

[GavinW]

I would say your best bet is to do this where you have the data, as it is ultimately a data problem rather than a visualisation problem. I would suggest that you go to Goolge Sheets (which I presume is where the form responses are) and basically just use a function like 'COUNTIF' to check each result row for each value of interest (dashboards, integrations, pivot tables etc.). If you don't know how to do that, try Gemini or ChatGPT or whatever and it will definitely be able to describe how to write such a function.

Then, in Studio, you will just have nice easy data to visualise - a feature as the dimension, and a number as the metric/measure.

[carlos_lara]

Thanks for your suggestion Gavin! But, if I want to create a quarterly view from this data, what would you suggest, as using =countif(range; "*feature*") would provide me the count but not the time dimension on Google Sheets ☹️

[GavinW]

I think it would work fine, but of course you would need to repeat the exercise on each quarter of data and bring it together in a single sheet to then point Studio at.