KPI Queries Vs Funnel (Cohort) Queries

[brad]

I was taking a look at this pattern here:

[Analytic Block] Simple Funnel

About This Block A simple funnel is a great way to look at events or transactions. Starting with just a few events, you can quickly get a sense of how users traverse your site or application. By adding in more events, you can add much more depth to the picture. Many data sets include multiple different kinds of transactions, which are often written into separate tables in the database. A funnel consolidates multiple transactional tables into a single table for easy analytics - providing insigh…

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?

[Michael_Kaminsky]

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:

Templated Pivots for Custom Aggregates

When building an interactive Kaplan Meier dashboard for my organization, I wanted to allow users to choose to pivot on a number of a different dimension (i.e., select the variable to use for cohort comparison). Unfortunately, this seemed impossible because the choice of variable to pivot on has to happen in a subquery/CTE before a few CTEs worth of reasonably complicated SQL transformations are applied. By hacking the templated filter feature, I was able to come up with a solution. - view: KM …

[lloydtabb]

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.

[Analytic Block] User Loyalty and Other User Attributes

##About This Block Loyalty facts are attributes computed over the lifetime of each user, which helps us segment and understand our customer base. Segmenting customers/users by their behavior helps us explore the most important characteristics that differentiate each user group, and how we can target them for the behavior changes we want. For example, what common threads can we find among users who bought one item but never returned – did they all buy the same item or types of items? Did they al…

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

[brad]

@lloydtabb

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?

Thanks!

[lloydtabb]

@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
  filters:
    type: created
  drill_fields: event_detail*

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

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

[brad]

@lloyd

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?

Thanks!

[lloydtabb]

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
  joins:
  - join: orders
     sql_on: ${users.id} = ${orders.user_id}
     relationship: one_to_many

Still not positive I’m answering your question.