KPI Queries Vs Funnel (Cohort) Queries

(Brad Ruderman) #1

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:

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:


However I also want the KPI style queries which is event based on my dashboards (similar to the events union created above):


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?

Override PDT On Specific Looks in Dashboard
(Michael Kaminsky) #2

I’m not sure if I understand your use-case exactly, but I think you can achieve what you want using templated pivots, which I describe here. This is sort of hacky, but it’s working for us:

(lloyd tabb) #3

So one way to do cohort analysis us to continue to use the funnel described in the first pattern and to build out a user fact table and join it in. The fact table will allow you to cohort the users on just about anything.

The pattern below has simple cohort facts (like lifetime number of purchases), but you can also do more complex things like building at user_brands table that would have all the brands a particular user has purchased and join it in with a one_to_many join.

I’m not sure I’m exactly understanding what you are trying to accomplish, but I hope this helps.

(Brad Ruderman) #4


There is a fundamental thing I am trying to figure out here. I want to be able to display data in 2 ways. For example our sales dashboard we have an events model. Each record is an event, the data looks like:

| sales_rep | created_dt | activity_dt | status |

Example data might be:

Brad | 2015-09-28 | 2015-10-02 | scheduled
Brad | 2015-09-20 | 2015-09-22 | complete
Lloyd | 2015-09-21 | 2015-09-23 | complete

And I want to display a table (for the month of september):

| sales_rep | events_created | events_scheduled | events_complete |

Thus the table would look like:

| Brad | 2 | 1 | 1 |
| Lloyd| 1 | 1 | 1 |

The difference is that the events_created column is based on the created_dt, while the events_scheduled/complete is based on the activity_date.

In order to do this I have to pivot the current model to be in the form of:

| sales_rep | dt | type | status
| brad | 2015-09-28 | created | scheduled
| brad | 2015-10-02 | scheduled | scheduled
| brad | 2015-09-20 | created | complete
| brad | 2015-09-22 | scheduled | complete
| Lloyd | 2015-09-21 | scheduled | complete
| Lloyd | 2015-09-23 | scheduled | complete

I am just trying to understand how others accomplish this type of analysis within looker. Does everyone represent the same data in 2 models?


(lloyd tabb) #5

@brad Would this work for you? I think it gives you what you want.

- measure: count
  type: count
  drill_fields: event_detail*

- measure: created_count
  type: count
    type: created
  drill_fields: event_detail*

- measure: scheduled_count
  type: count
    type: scheduled
  drill_fields: event_detail*

  event_detail: [id, sales_rep, created time, type, activity_time, status]

(Brad Ruderman) #6


That would work assuming I have the data transformed in the secondary form. Currently I don’t. I am trying to understand how other companies accomplish these 2 different types of analysis. Do they create 2 models for each data set? How do they educate their users when to use which data set? How do they maintain these 2 data sets, using derived tables?


(lloyd tabb) #7

Yes, the easy way to do this is to have a funnel explore (you are using the word model, but I think you mean ‘explore’) and a transaction explore.

You can explain the funnel view as the ‘cash accounting’ method, which is what happened in a period of time. Essentially, in any given time period how much came in, how much went out, how many signed up, how many bought. No Cohorts.

The transactional ‘accrural accounting’ view will let you look at entities and what happened to them, independantly of time. Of the people that signed up in 8 weeks ago, how many of them eventually ordered.

With the explore below, you can filter by user sign up month, pivot out order month and look at that cohort over time.

- explore: users
  - join: orders
     sql_on: ${} = ${orders.user_id}
     relationship: one_to_many

Still not positive I’m answering your question.

Always_filter challenge