Analyzing funnels in an SQL database is not a hard task when you know the right queries. Using SQL language, you can dive into complex funnels and event flow analysis to gain insights into your users’ behavior. In this article, we’re going to tell how we, at Statsbot, use SQL queries for conversion funnel analysis.

Why to build funnels

Funnels are an extremely useful and helpful data tool that can provide you a great overview about your product. In a nutshell, funnels are representing several events your users perform one after another. Calculating how many unique users made each event could show you a conversion rate between each step, so you could actually localize a problem down to a certain stage.

At Statsbot, we measure how many people signed up, connected integration, and used the Scheduled Report feature. Users who performed all these actions are considered as successfully on-board.

Let’s take this process as an example and see how we can actually create this funnel with SQL. The following queries are written for PostgreSQL. You can just copy them and build your funnel right away.

Step 1 — Signup event

In our DB we have a table called Bots. Any user who signed up to Statsbot has a unique ID number and they get added to this table, so bots = users in our case.

In order to get the number of signups, we simply need to choose Bots table, apply the date range we need, and specify the function, which is count.

SELECT count(*)

FROM bots

WHERE created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’

We can see now that during this time we got 745 signups.

Step 2 — Calculating Integration connected event

Next, we need to find out how many out of those 745 users connected at least one integration.

In our DB we have a table called “Integrations” that contains an integer value representing how many integrations have been connected by user.

We now need to make a following SQL query to get the result. Note that we are querying a number of integrations connected by users out of those who signed up in May, which we defined in Step 1.

SELECT count(*)

FROM bots

WHERE

(SELECT count(*)

FROM integrations

WHERE integrations.bot_id = bots.id)>0

AND created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’

Awesome! The result is 104 integrations that had been connected by users who signed up in May.

Step 3 — How many users scheduled at least one report?

Okay, the next step is exactly the same as the previous one. We just add another event from a scheduled_reports section with the same condition to complete this nested query.

SELECT count(*)

FROM bots

WHERE

(SELECT count(*)

FROM integrations

WHERE integrations.bot_id = bots.id)>0

AND

(SELECT count(*)

FROM scheduled_reports

WHERE scheduled_reports.bot_id = bots.id)>0

AND created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’

The result is 61 users have scheduled at least one report.

Step 4 — Putting all pieces together for creating a funnel analysis

This is where the most interesting part comes in. Let’s take all the logic we’ve worked on together and create the conversion funnel — our initial goal.

SELECT count(*) AS Step1,

count(*) FILTER(

WHERE (

SELECT count(*)

FROM integrations

WHERE integrations.bot_id = bots.id

) > 0

) AS Step2,

count(*) FILTER(

WHERE (

SELECT count(*)

FROM integrations

WHERE integrations.bot_id = bots.id

) > 0 and (

SELECT count(*)

FROM scheduled_reports

WHERE scheduled_reports.id = bots.id

) > 0

) AS Step3

FROM bots

WHERE created_at BETWEEN '2017-05-01' AND '2017-05-31'

This is what our final result looks like after visualization.

This funnel gives us the insight that we have some problems with onboarding and connecting the first integration. Then, we can make some changes and build the same funnel in one week to measure whether the conversion rate increases or decreases.

Further, we can detect 43 users who connected the data source but didn’t schedule the report yet. They are in churn risk, and we need to get in touch with them quickly to get back on track.

You should also remember one important thing: we created a funnel, which is affected by a slowly changing dimension problem. In our case, users are signed up in May, but at the same time they could add more integrations and schedule more reports during their customer life cycle. This might result in changes to the numbers in Step 2 and Step 3.

If you have an event based system, then a slowly changing dimension problem can be eliminated. For example, we use Mixpanel to build the same funnel this way:

SELECT

COUNT(

DISTINCT CASE WHEN cur_signup_time IS NOT NULL

THEN distinct_id END

) Step1,

COUNT(

DISTINCT CASE WHEN cur_signup_time IS NOT NULL AND cur_int_time IS NOT NULL

THEN distinct_id END

) Step2,

COUNT(

DISTINCT CASE WHEN

cur_signup_time IS NOT NULL AND cur_int_time IS NOT NULL AND cur_new_schedule_time IS NOT NULL

THEN distinct_id END

) Step3

FROM (

SELECT

distinct_id,

"time",

COALESCE(signup_time,

LAG(signup_time) IGNORE NULLS OVER(PARTITION BY distinct_id

ORDER BY time)

) cur_signup_time,

COALESCE(int_time,

LAG(int_time) IGNORE NULLS OVER(PARTITION BY distinct_id

ORDER BY time)

) cur_int_time,

COALESCE(new_schedule_time,

LAG(new_schedule_time) IGNORE NULLS OVER(PARTITION BY distinct_id

ORDER BY time)

) cur_new_schedule_time

FROM

(

SELECT

"time",

distinct_id,

(CASE WHEN event = 'Signup'

THEN time END) signup_time,

(CASE WHEN event = 'Integration connected'

THEN time END) int_time,

(CASE WHEN event = 'New Schedule'

THEN time END) new_schedule_time

FROM mixpanel.mixpanel_export

WHERE "time" > '2016-05-01' AND "time" < '2017-01-01'

ORDER BY distinct_id, "time"

) event_times

ORDER BY distinct_id, "time"

) event_windows

These 4 steps are easy to follow to build your first funnel in SQL. We hope this will be a good start to customizing your analysis with SQL power!

YOU’D ALSO LIKE: