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
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.
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.
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.
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.