How to build a closed funnel graph?

[igor-okuyama]

I have data from Google Analytics in my Bigquery project which is linked to Looker. I was trying to figure out how to create a closed funnel analysis (similar to what GA4 does, but I want to do it in Looker), in which each user must start from the first step and go through the next ones, in order to be counted in the funnel metrics. I've seen some examples of building funnels only by counting the number of events of each step, but this does not enforce the users to sequentially go through each step or start from the first step. Is there an easy way to create closed funnels?

I understand that it is needed to build a query with many joins (as done in this link), but I was wondering how to do this in Looker in an easy way, maybe without having to copy this large code and adapt it to every funnel report with distinct event steps.

[fcharles]

Hi Igor,

I see many ways that we could achieve that, in order to have it consistent all over the board, I would probably create a case clause and then generate the current step (or an array of steps like the example you shared).
If that is using lots of queries to produce such result (like you mention that it would require many joins), I would probably on top of that, take advantage of persistent derived tables, so users can consult the status without loosing performance. 

[igor-okuyama]

Hi, @fcharles ! I get your point. But do you know if there are any Looker extensions that could help to build funnels in a more visual way? I was looking for a simple UI that could help users to select the events and filters for each step, and it would generate a query and a graph. This would help me to replace tools such as GA4 reports or Mixpanel, and I could have all my product analytics data in Looker. If there are not any extensions available, do you think it is possible to build one? Or maybe it is on the Looker team's roadmap?

[fcharles]

Would some of these help maybe?

[igor-okuyama]

This funnel chart is great when you already have the funnel metrics calculated. If you simply want to count some events disregarding whether a person went through all steps, then it is easy to implement in Looker. But if you want to require that all users who went through step 2 also went through step 1 in the correct order of time, then one needs to build a clumsy custom query.

[paulo_ordonha]

I also need this solution in my data visualization case. 😞