How to Calculate Conversion Rate in MySQL?

Last updated on May 17th, 2020 at 06:13 am

It is important to calculate conversion rate for every business. Here’s how to calculate conversion rate in MySQL. You can also use this query for funnel analysis in PostgreSQL, SQL Server & Oracle.

How to Calculate Conversion Rate in MySQL?

Let’s say you have 3 tables

signups(user_id, date_joined) – contains all users who have signed up on your website

shopping_cart(user_id,product_id, date_added) – contains all users who have added at least 1 product to their shopping cart, along with their cart items.

purchases(user_id,product_id,date_purchased,purchase_amount) – contains all users who have purchased at least 1 product, along with each product’s price.

To calculate conversion rate, we have shown only necessary columns in our tables, they may always contain more columns.

We want to measure conversion rates at 2 points in our conversion funnel

When users add at least 1 item to shopping cart – % of users who have added at least 1 item to cart
When users purchase at least 1 item – % of users who have purchased at least 1 item

Bonus Read : How to Calculate Retention Rate in MySQL

First, we calculate total number of signups, for say, past 30 days.

mysql> select count(*) as signups from signups 
where date_joined > now() - interval 30 day;
+---------+
| signups |
+---------+
|    2130 |
+---------+

Bonus Read: How to Fill Missing Dates in MySQL?

Next, we calculate total number of users who added at least 1 item to their cart in past 30 days

mysql> select count(distinct user_id) as carts from shopping_carts
 where date_added > now() - interval 30 day;
+---------+
|   carts |
+---------+
|     416 |
+---------+

Make sure you use distinct in above query, to count unique users only

Next, we calculate number of users who have purchased at least 1 item in past 30 days.

mysql> select count(distinct user_id) as purchases from purchases 
where date_purchased > now() - interval 30 day;
+----------+
|purchases |
+----------+
|      136 |
+----------+

Bonus Read : How to Calculate Monthly Sales in MySQL

Now that we have the 3 counts, we can use these queries in multiple ways. If you want to calculate only conversion rate percentages, then here’s the SQL query to calculate conversion rate for shopping cart creation

mysql>select 
     (select count(distinct user_id) from shopping_carts 
      where date_added > now() - interval 30 day)
       /
     (select count(*) as signups from signups 
      where date_joined > now() - interval 30 day) * 100 
     as 'Shopping Cart conversion';
+--------------------------+
| Shopping Cart conversion |
+--------------------------+
|                    19.53 |
+--------------------------+

and for purchases

mysql> select 
      (select count(distinct user_id) as purchases from purchases 
       where date_purchased > now() - interval 30 day)
       /
      (select count(*) as signups from signups 
       where date_joined > now() - interval 30 day) * 100
      as 'Purchase conversion';
+---------------------+
| Purchase conversion |
+---------------------+
|                6.38 |
+---------------------+

Bonus Read : SQL Query to Compare Product Sales By Month

If you want to create conversion funnel, then here’s the combined query to get all the 3 above counts in a single table

mysql>select 'signups' as `funnel stage`, count(*) as `number of users` 
from signups
where date_joined > now() - interval 30 day
UNION
select 'carts' as `funnel stage`,count(distinct user_id) as `number of users` 
from shopping_carts 
where date_added > now() - interval 30 day
UNION
select 'purchases' as `funnel stage`,count(distinct user_id) as `number of users` 
from purchases 
where date_purchased > now() - interval 30 day

+-------------+-----------------+
|funnel stage | number of users |
+-------------+-----------------+
|  signups    |      2130       |
|  carts      |       416       |
|  purchases  |       136       |
+-------------+-----------------+

To calculate conversion rate, you can easily plot these numbers on a column chart or funnel chart using a charting tool. Here’s an example of the above data plotted in a column chart using Ubiq.