gwJRTVSV

1DROP TABLE IF EXISTS tmp_audience;
2CREATE TEMP TABLE tmp_audience AS
3SELECT
4  cc.id,
5  MIN(COALESCE(cc.convertedaccountid, cc.accountid)) AS sf_accountid,
6  MIN(COALESCE(cc.original_created_date__c, cc.createddate)) AS created_date,
7  MIN(CASE
8    WHEN COALESCE(cc.mk_lead_score__c::FLOAT, 0) >= 85 THEN 'A'
9    WHEN COALESCE(cc.mk_lead_score__c::FLOAT, 0) >= 50 THEN 'B'
10    ELSE 'C'
11  END) AS lead_grade
12FROM
13  crm_contacts cc
14WHERE
15  -- Timeframe for the analysis
16  COALESCE(cc.original_created_date__c::DATE, cc.createddate::DATE)BETWEEN '2019-02-01' AND '2019-02-28'
17    AND COALESCE(cc.isdeleted, 'false') = 'false'
18    AND COALESCE(cc.lead_source_original__c, cc.leadsource, 'null') NOT IN ('Outbound', 'Lead List')
19    AND cc.source_system_object = 'Lead' 
20GROUP BY
21  1
22;
23
24-- conversion = deal opened
25DROP TABLE IF EXISTS tmp_conversion;
26CREATE TEMP TABLE tmp_conversion AS
27SELECT
28  a.id,
29  o.createddate AS opp_open_date
30FROM
31  tmp_audience a
32INNER JOIN
33  crm_opportunities o
34  ON o.accountid = a.sf_accountid
35;
36
37SELECT
38  a.lead_grade,
39  COUNT(DISTINCT a.Id) AS "Population",
40  COUNT(DISTINCT c.Id) AS "Conversion",
41  COUNT(DISTINCT c.Id)::FLOAT / COUNT(DISTINCT a.Id)::FLOAT * 100 AS "Conversion Rate",
42  COUNT(DISTINCT a.Id)::FLOAT / (SELECT COUNT(DISTINCT Id) FROM tmp_audience)::FLOAT * 100 AS "Population Percentage",
43  COUNT(DISTINCT c.Id)::FLOAT / (SELECT COUNT(DISTINCT c.Id) FROM tmp_conversion c INNER JOIN tmp_audience a ON c.Id = a.Id AND a.created_date <= c.opp_open_date::TIMESTAMP) ::FLOAT * 100 AS "Conversion Percentage"
44FROM
45  tmp_audience a
46LEFT JOIN
47  tmp_conversion c
48  ON a.id = c.id
49  AND a.created_date <= c.opp_open_date
50GROUP BY
51  1
52ORDER BY
53  1
54;