k1TxErNS

1DROP TABLE IF EXISTS tmp_audience;
2CREATE TEMP TABLE tmp_audience AS
3SELECT
4c.source_key_value as lead_id,
5  COALESCE(c.a_accountid, c.a_convertedaccountid) AS account_id,
6  c.created_date::TIMESTAMP AS created_date,
7  COALESCE(af.customer_fit, 'unknown') AS segment
8
9FROM
10  crm_contacts c
11
12LEFT JOIN
13  analysis_scoring_customer_fit af
14  ON c.source_system = af.source_system
15  AND c.source_system_object = af.source_system_object
16  AND c.source_key_name = af.source_key_name
17  AND c.source_key_value = af.source_key_value
18
19WHERE
20  -- Change based on your audience
21  c.source_system = 'salesforce'
22  AND c.source_system_object = 'Lead'
23  AND c.a_leadsource IN ('Inbound', '', 'Platform','Instant SecurityScorecard','Deal Registration', 'Partner', 'InstantSSC')
24    AND c.created_date BETWEEN (GETDATE() - 90) AND (GETDATE() - 30)
25;
26
27
28
29DROP TABLE IF EXISTS tmp_conversion;
30CREATE TEMP TABLE tmp_conversion AS
31SELECT
32  o.id as opp_id,
33  o.a_accountid AS account_id,
34  o.a_createddate::TIMESTAMP AS a_createddate,
35  o.a_amount AS mrr
36FROM
37  crm_opportunities o
38WHERE
39-- Change depending on the criteria that define a conversion
40  o.source_system = 'salesforce'
41  AND o.a_type = 'New Business'
42  AND o.a_probability::FLOAT >= 50
43  AND o.a_amount::FLOAT > 0
44;
45
46
47
48-- Try not to mess with this unless you need to
49SELECT
50  a.segment,
51  COUNT(DISTINCT a.lead_id) AS "Population",
52  COUNT(DISTINCT c.opp_id) AS "Conversion",
53  SUM(c.mrr) AS "MRR",
54  COUNT(DISTINCT c.opp_id)::FLOAT / COUNT(DISTINCT a.lead_id)::FLOAT AS "Conversion Rate",
55  COUNT(DISTINCT a.lead_id)::FLOAT / (SELECT COUNT(DISTINCT lead_id) FROM tmp_audience)::FLOAT AS "Population Percentage",
56  COUNT(DISTINCT c.opp_id)::FLOAT / (SELECT COUNT(DISTINCT c.opp_id) FROM tmp_conversion c INNER JOIN tmp_audience a ON c.account_id = a.account_id AND a.created_date <= c.a_createddate)::FLOAT AS "Conversion in number - Percentage",
57  SUM(c.mrr)::FLOAT / (SELECT SUM(c.mrr) FROM tmp_conversion c INNER JOIN tmp_audience a ON c.account_id = a.account_id AND a.created_date <= c.a_createddate)::FLOAT AS "Conversion in $amount - Percentage"
58FROM
59  tmp_audience a
60LEFT JOIN
61  tmp_conversion c
62  ON a.account_id = c.account_id
63-- The lead must have been created before the conversion date to make sure we are not counting a lead that has been added to an account that had already converted
64  AND a.created_date <= c.a_createddate
65
66GROUP BY
67  1
68ORDER BY
69  CASE a.segment
70    WHEN 'low' THEN 4
71    WHEN 'medium' THEN 3
72    WHEN 'good' THEN 2
73    WHEN 'very good' THEN 1
74    ELSE 5
75  END ASC
76;