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:
Funnel by State measuring Signups, Orders and Active Users
And you can use any dimension for analysis:
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.
