[Analytic Block] Simple Funnel

[cutler]

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 insights that would be hard to draw out of each individual table. When solving this problem with traditional SQL, your result set has some limitations – it is not drillable or combinable. However, using Looker, both these issues are resolved - the combined data is both drillable (you can see the more granular data that made up your combined data) and combinable (i.e., you would be able to roll up by month and still COUNT DISTINCT users).

Ideal Data Types

This block works with transactional data (that is, data that is time-based). This could include events that occur on a website, purchases of items or services, and many more. We can use this block to track the sequence of these events and see how users go from one to the next.

Expected Output

Let’s take a look at a simple example. We are going to combine two transactions: users signing up (users being created) and users buying something (orders being created). We are starting by combining just two transaction for simplicity’s sake, but we could imagine including many more: users visiting the web site (event data), users corresponding via email, support tickets, users opening email, and so forth.

###Simple funnel in action:

Funnel by Date measuring Signups, Orders and Active Users

The beauty of this approach is that all the counts drill:

Explore Data in Full Screen

Funnel by State measuring Signups, Orders and Active Users

And you can use any dimension for analysis:

Explore Data in Full Screen

Try it yourself!

To create the funnel, we’ll query each of the transactions and combine them using a SQL UNION statement. There is a bit of magic here, in that we don’t UNION the entire transaction but a subset. LookML’s templated filters {% condition %}, will expand to a logical expression that will keep us from having to UNION the transactions tables in their entirety.

{% condition event_time %} u.created_at {% endcondition %}

This templated filter guarantees that we don’t accidentally query all of time, but rather just the time period we need for this query.

In the model file:

- include: ecommerce.view.lookml
#
# Declare how the tables are linked
#
- explore: funnel
  hidden: true          ## ignore
  always_filter:
    event_time: 30 days ago for 30 days
  joins:
  - join: users
    foreign_key: user_id
  - join: orders
    foreign_key: order_id

In the view file:

- view: funnel
  derived_table:
    sql: |
      SELECT 
        u.created_at as event_time
        , 'SIGNUP' as event_type
        , u.id as user_id
        , NULL as order_id
      FROM users u
      WHERE
        {% condition event_time %} u.created_at {% endcondition %}
      UNION
      SELECT 
        o.created_at as event_time
        , 'ORDER' as event_type
        , o.user_id as user_id
        , o.id as order_id
      FROM orders o
      WHERE
        {% condition event_time %} o.created_at {% endcondition %}

  fields:
  - dimension_group: event
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.event_time
    
  - dimension: user_id
    type: int
  
  - dimension: order_id
    type: int
    
  - dimension: event_type
  
  - measure: count_signups
    type: count
    filters:
      event_type: SIGNUP
    drill_fields: [users.id, users.name, users.created_time]
    
  - measure: count_orders
    type: count
    filters:
      event_type: ORDER
    drill_fields: [users.id, users.name, users.created_time]
    
  - measure: count
    type: count
    drill_fields: [users.id, users.name, users.created_time]

Further Analysis

There are a lot more options to this type of analysis. We can bring in many more events or transactions, filter them in a variety of ways, see how the funnel changes over time or by location, etc… Remember, this is a simple funnel, and much more complex analysis can also be done on similar types of data with similar patterns.

[anika1]

Love this pattern! Just implemented it, and I ended up getting better performance removing the templated filters and persisting the funnel table. I was working in MySQL and the funnel had about 8 million rows - could have been specific to this use case.

[teejay]

Thank you! I think this helps with another question I posted - I will give this a try

[greese]

Hello, I realize this article was written a while ago.  I am interested in building this in Looker using Snowflake. Are there visual examples that you can share?  Thanks!