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.

``````1.0 * SUM(is_checkout) / COUNT(user_id),