Learn
Usage Funnels
Build a Funnel from Multiple Tables 3

We’ve created a new table that combined all of our data:

browser_date user_id is_checkout is_purchase
2017-12-20 6a7617321513 1 0
2017-12-20 022d871cdcde 0 0

Here, 1 represents True and 0 represents False.

Once we have the data in this format, we can analyze it in several ways.

Let’s put the whole thing in a WITH statement so that we can continue on building our query.

We will give the temporary table the name funnels:

WITH funnels AS ( SELECT DISTINCT b.browse_date, b.user_id, c.user_id IS NOT NULL AS 'is_checkout', p.user_id IS NOT NULL AS 'is_purchase' FROM browse AS 'b' LEFT JOIN checkout AS 'c' ON c.user_id = b.user_id LEFT JOIN purchase AS 'p' ON p.user_id = c.user_id) SELECT ______ _____________ _____________;

Notice how the whole previous query is put inside the parentheses ( ).

Let’s query from this funnels table and calculate overall conversion rates.

Instructions

1.

First, add a column that counts the total number of rows in funnels.

Alias this column as ‘num_browse’.

This is the number of users in the “browse” step of the funnel.

2.

Second, add another column that sums the is_checkout in funnels.

Alias this column as ‘num_checkout’.

This is the number of users in the “checkout” step of the funnel.

3.

Third, add another column that sums the is_purchase column in funnels.

Alias this column as ‘num_purchase’.

This is the number of users in the “purchase” step of the funnel.

4.

Finally, let’s do add some more calculations to make the results more in depth.

Let’s add these two columns:

  • Percentage of users from browse to checkout
  • Percentage of users from checkout to purchase
1.0 * SUM(is_checkout) / COUNT(user_id), 1.0 * SUM(is_purchase) / SUM(is_checkout)

You can also give these columns aliases for more readability.

Folder Icon

Sign up to start coding

Already have an account?