How I do Complex Funnel Analysis in Just Five Lines

Funnel analytics are commonly used to identify users’ progress through defined steps towards a specific goal. Typically, it helps in identifying conversion drop-downs between specific events in the funnel, be it micro-conversions or macro-conversions, and reveals which stages of the user activity may or may not contribute to attaining the desired goal.

As a data scientist, I’m challenged with the more complex business questions on a daily basis. As a proactive analyst looking to identify user behaviors patterns or predict trends, I have to move beyond the more limiting two dimensional funnel analytics visualization, in order to drill down or compare behaviors between user profiles or periods to get deeper insights.
Funnel widgets are useful for high level analysis, but for more advanced analysis, when multiple dimensions are required, or if you need to define the funnel scope – you must turn to behavioral functions.

Adding dimensions to funnel analytics

Have you ever tried to add a Time dimension to common funnel analysis?
It’s impossible, but completely necessary when striving for deeper analytical insights.
How would you use multiple filters across the whole funnel, or compare differently segmented funnels?
It’s virtually impossible with visual funnel analysis and to construct such complex queries can become quite cumbersome when using the usual SQL.

My life as an analyst using CQL is much simpler!

CoolaData realized the need to create a structured and agile query language in order to stake its claim as the shortest time to insight.
That’s why we’ve developed the CoolaSQL (CQL)— a behavioral extension of the popular SQL that facilitates the queries syntax and easily gets to behavioral functions for real behavioral analytics.

The power of CQL is best demonstrated with funnel analysis; instead of the complex join and union in your queries, with CQL all you need to do is add the statement CLUSTER FUNNEL followed by its relevant parameter. It’s that easy.

Add the time dimension to funnels

The time dimension is typically missing in the visual funnel widget. By adding the STEP_DURATION to the funnel, you can see the time it took to complete the step, which reveals valuable user behavior that can be transformed to better business decisions.

Here is how adding the step duration is easily queried with CQL functions:

SELECT step_name,user_count,step_duration
FROM cooladata
WHERE date_range (last 30 days)
CLUSTER FUNNEL USER STARTS WITH “User_Login”
FOLLOWED BY “Item_Add”
FOLLOWED BY “Checkout_Payment”
END CLUSTER

Funnel analysis results

Such insights could become valuable in many cases, like for example, a gamer that completes levels faster is more likely to convert and become a paying user. Or conversely, if the time it takes to move between steps is too long , it might indicate a technical problem with that step or something else that delays the player from moving forward.

Set the funnel scope: By User, Session or Time frame

In deep analysis you would want to define the scope of the funnel, and with my CQL it’s already a function.
You could choose to define the CLUSTER FUNNEL BY USER to look at the funnel within the total user lifetime (across multiple sessions), or BY SESSION to limit it to a single session, which could highlight the different behaviors between users with potential to engage.
An interesting KPI to look at could be the time frame in which the user completed the whole funnel. With CoolaData,  it’s easy to define the funnel scope for a different time frame simply by specifying the number of days e.g. 7 days.

Here’s how you set the funnel scope with CQL

SELECT STEP_NAME, USER_COUNT
FROM cooladata WHERE date_range(last 30 days)
CLUSTER FUNNEL BY  [ USER | SESSION | n DAYS ]
STARTS WITH “User_Register”
FOLLOWED BY “Item_Add”
FOLLOWED BY “Checkout_Payment”
END CLUSTER

In search for valuable insights we often need to look at different funnel scopes, and with CQL you just change the function parameter and re-run it.

Funnel - User scope and session scope

The multi-dimentional funnel sliced by conditions

With the CQL syntax it’s easy to drill down by multiple dimensions, or add various conditions.
In this example we’ll add the drill down by USER_REGION and the condition for users who came from Facebook:

SELECT STEP_NAME, USER_COUNT,STEP_DURATION , USER_REGION
FROM cooladata
WHERE date_range (last 3 weeks)
AND referral=’Facebook’
CLUSTER FUNNEL BY 7 days
STARTS WITH   “User_Login” FOLLOWED BY “Item_Add”
FOLLOWED BY “Checkout_Payment”
END CLUSTER

Funnel by X dimensions - results
Adding dimensions and conditions helps to highlight the differences between segments’ behaviors in the funnel.

Funnel comparison between different periods

To achieve a funnel comparison, you would normally take the results from a few different periods with many condition lines and process the results in a temporary table. With the CQL, as you can see below, you can achieve a funnel comparison between 2 years (2013, 2014) with just a single join between the two funnels;

select year_2013.step_name , users_2013, users_2014 , users_2013/users_2014 as trend from ( SELECT STEP_NAME, USER_COUNT users_2014
FROM cooladata
WHERE date_range(BETWEEN 2013-01-01 AND 2013-12-31)
CLUSTER FUNNEL BY user
STARTS WITH “User_Register”
FOLLOWED BY “Item_Add”
FOLLOWED BY “Checkout_Payment” END CLUSTER ) year_2013
join
( SELECT STEP_NAME, USER_COUNT users_2014
FROM cooladata
WHERE date_range(BETWEEN 2014-01-01 AND 2014-12-31)
CLUSTER FUNNEL BY user
STARTS WITH “User_Register”
FOLLOWED BY “Item_Add”
FOLLOWED BY “Checkout_Payment”
END CLUSTER

Funnel comparison results

Having an agile query language like CoolaSQL is essential for complex yet flexible funnel analysis.
CoolaSQL helps to get quick actionable insights to complex business questions by supporting proprietary clauses, functions, and special fields.

Let us demonstrate the CoolaData abilities, register for private demo.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *