I need script report or query report for lead transaction

ERPNext Reports
,
1

Hi All,
i need customized report for Lead , based on “Lead owner”,“product type”, “timeline”(quarterly,half-year,yearly) which means how many lead got and how many converted in last 6months,1 year,last year
Thanks in Advance

2

please check the syntax or sample.

SELECT
    lead_owner AS "Lead Owner:Link/User:150",
    request_type AS "Request Type:Data:150",
    COUNT(name) AS "Total Leads:Int:100",
    COUNT(CASE WHEN status = 'Converted' THEN 1 END) AS "Converted Leads:Int:100",
    COUNT(CASE WHEN status != 'Converted' THEN 1 END) AS "Unconverted Leads:Int:100",
    COUNT(CASE WHEN DATE(creation) BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE() THEN 1 END) AS "Leads Last 6 Months:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATE(creation) BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE() THEN 1 END) AS "Converted Last 6 Months:Int:150",
    COUNT(CASE WHEN DATE(creation) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() THEN 1 END) AS "Leads Last Year:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATE(creation) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() THEN 1 END) AS "Converted Last Year:Int:150",
    COUNT(CASE WHEN DATE(creation) < DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN 1 END) AS "Leads Older:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATE(creation) < DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN 1 END) AS "Converted Older:Int:150"
FROM
    `tabLead`
GROUP BY
    lead_owner
ORDER BY
    lead_owner;

add you logic according to the scenario.

1 Like
3

can we add ageing like 30,60,120 bro
Thanks in Advance

4

Please check it.

SELECT
    lead_owner AS "Lead Owner:Link/User:150",
    request_type AS "Request Type:Data:150",
    COUNT(name) AS "Total Leads:Int:100",
    COUNT(CASE WHEN status = 'Converted' THEN 1 END) AS "Converted Leads:Int:100",
    COUNT(CASE WHEN status != 'Converted' THEN 1 END) AS "Unconverted Leads:Int:100",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS "Leads <= 30 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS "Converted <= 30 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS "Leads 31-60 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS "Converted 31-60 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS "Leads 61-120 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS "Converted 61-120 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS "Leads > 120 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS "Converted > 120 Days:Int:150"
FROM
    `tabLead`
GROUP BY
    lead_owner
ORDER BY
    lead_owner;
1 Like
5

i need deal query report also like above
Thanks in advance

6

You have to learn and develop it yourself.

Reference:

1 Like