I was taking a look at this pattern here:
One of the downsides to using that approach is that it doesn’t really work with cohort type funnels. For example, Of the users who joined during this period, how many of them ordered a product? Looking at distinct is not going to work because you still aren’t joining by the cohort date.
To solve this I use a different model where I have the events as columns as they occur. For example in a users table:
|user_id|user_joined_dt|user_ordered_dt|
To answer the cohort style question, I simply just have to look at the top level column (for example user_joined_dt) and use a case statement to determine if user_ordered_dt is not null.
It leaves me with something along the lines of:
|user_joined_dt|number_of_distinct_users_joined|number_of_distinct_users_who_ordered|
However I also want the KPI style queries which is event based on my dashboards (similar to the events union created above):
|dt|users_joined|users_ordered|
So my question is what is a potential pattern (view/model) which I can use that will allow me to have 1 single looker model that I can explore in both ways? Is this possible?