Question 89315 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate monthly close conversion rate of total customer created in same month vs how many of them converted in sale – Postgresql

Table create script:

CREATE TABLE data (account_id int, cust_status varchar(25), amount int, date date);

INSERT INTO data VALUES
    ('1', 'first_login', '1000', '01/01/2021'),
    ('1', 'sale',        '500',  '28/01/2021'),
    ('2', 'first_login', '2000', '07/03/2021'),
    ('3', 'first_login', '1000', '01/06/2021'),
    ('3', 'sale',        '2000', '05/08/2021'),
    ('4', 'first_login', '5000', '18/06/2021'),
    ('4', 'sale',        '3000', '18/09/2021'),
    ('5', 'first_login', '5000', '02/06/2021'),
    ('6', 'first_login', '2000', '11/06/2021'),
    ('7', 'first_login', '1000', '22/06/2021'),
    ('8', 'first_login', '3000', '29/06/2021');

Here is data from that table:

acc_id cust_status amount date
1 first_login 1000 01/01/2021
1 sale 500 28/01/2021
2 first_login 2000 07/03/2021
3 first_login 1000 01/06/2021
3 sale 2000 05/08/2021
4 first_login 5000 18/06/2021
4 sale 3000 18/09/2021
5 first_login 5000 02/06/2021
6 first_login 2000 11/06/2021
7 first_login 1000 22/06/2021
8 first_login 3000 29/06/2021

I need to calculate what is the conversion rate for customer every month.

Total account has created as first_login in month and how many of them converted into sale later.

I tried to calculate the result based on the formulas but stuck at how to make self/inner join in order to bring desired result.

Result should look like:

close rate close vol rate month
100% 50% January
0 0 March
33.3% 29.41% June

As in June there are 6 account created as first_login and only 2 of them later converted into sale.

  • conversion close rate = count(sale)/count(first_login)*100
  • conversion close vol rate = sum(sale)/sum(first_login)*100

Thank you in advance 🙂

Tags:

2

Answers


  1. 0

    I imported your data to DBFIDDLE and here is a working demo

    The following query shall get you the output in Expected format.

        SELECT 
    CASE 
        WHEN COUNT(CASE WHEN cust_status = 'sale' THEN account_id END) = 0 THEN 0
        ELSE ROUND(100.0 * COUNT(CASE WHEN cust_status = 'sale' THEN account_id END) / NULLIF(COUNT(CASE WHEN cust_status = 'first_login' THEN account_id END), 0), 2)
    END AS close_rate,
    CASE 
        WHEN SUM(CASE WHEN cust_status = 'sale' THEN amount END) = 0 THEN 0
        ELSE ROUND(100.0 * SUM(CASE WHEN cust_status = 'sale' THEN amount END) / NULLIF(SUM(CASE WHEN cust_status = 'first_login' THEN amount END), 0), 2)
    END AS close_vol_rate,
   to_char(date_trunc('month', date), 'Month') AS month
FROM data
GROUP BY date_trunc('month', date)
ORDER BY date_trunc('month', date);

    The query with your fomulae will throw a division by zero error if there are no first_login events for a particular month. In order to handle this, I have used the NULLIF function to return a null value instead of zero if the denominator is zero.

    This gives same output as manually calculated output :

    close rate close vol rate month
    100% 50% January
    0 0 March
    0 null June
    null null August
    null null September
    Login or Signup to reply.
  2. 0

    Try to do a left join and aggregate using filters with sum and count functions as the following:

    select 
  coalesce
  (
   round
   (
     count(*) filter (where t2.cust_status ='sale')/
     nullif(count(*) filter (where t1.cust_status ='first_login'), 0)::numeric *100
    , 2
   ), 0
  ) as "close rate",
  coalesce
  (
    round
    (
      sum(t2.amount) filter (where t2.cust_status ='sale')/
      nullif(sum(t1.amount) filter (where t1.cust_status ='first_login'), 0)::numeric *100
      , 2
    ), 0
  ) as "close vol rate",
  to_char(t1.date, 'YYYY-MM') as "month"
from tbl_name t1 left join tbl_name t2
on t1.acc_id = t2.acc_id and
   t1.cust_status <> t2.cust_status
group by to_char(t1.date, 'YYYY-MM')
order by to_char(t1.date, 'YYYY-MM')

    The nullif is used to avoid division by zero, and coalesce is used to replace nulls by zero.

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search