KPI Queries Vs Funnel (Cohort) Queries

[brad]

I was taking a look at this pattern here:

A Simple Funnel using a Filtered Derived Table

This example is based the ecommerce dataset and includes both the orders and users views. Many data sets include multiple different kinds of transactions. These transaction are often written into separate tables in the database. A funnel is a table that somehow combines these multiple transactional tables into a single table for analytics. In this simplified example, we are going to combine two transactions: users signing up (users being created) and users buying something (orders being cr…

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: K…

[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.

Simple User Loyalty and Other User Facts

In this example we compute a derived fact table based on order_items, computing dimensions like lifetime_revenue, liftime_orders, first and latest order time. From there we build out tiers (buckets) that let us analyze both our entire population and what's happened recently (ordering patterns) with that population. # Uses the common Ecommerce Views - include: ecommerce.view.lookml - explore: order_items joins: - join: orders foreign_key: order_id - join: users f…

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.