We’ve created a new table that combined all of our data:
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
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.
First, add a column that counts the total number of rows in
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
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
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.