Percent of customers with an opportunity

Hi Brian Botts ,

I was checking the requirement for your report and I didn't see a way to do that via standard Reports of Sugar. However, using direct SQL queries with Enterprise Reports, you could get it.

This is what I did in a demo environment with demo data:

My demo data is based in UK so all Accounts have Billing Address Country = "United Kingdom" (this would be your Territory).

Then I selected 20 Accounts and assigned them the Tag "Prospect".

I created two simple reports in the standard reports, one to show the number of accounts with Tag "Prospect" for the specific territory and another one to narrow that result to the ones with at least an open Opportunity (Sales Status not Closed). With this, and being logged as an admin, I used the "Show Query" option and click on the Preview button in the report:

With the queries the system provided for both reports, I build a Custom Query in Enterprise Reports to show the results of those two reports together, in 2 columns.

This was my query:

SELECT COUNT(DISTINCT  accounts.id) uk_prospects, (SELECT COUNT(DISTINCT  accounts.id)
FROM accounts
 INNER JOIN  tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
 AND l1_1.bean_module = 'Accounts'
 INNER JOIN  tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0
 INNER JOIN  opportunities_accounts_1_c l2_1 ON accounts.id=l2_1.opportunities_accounts_1accounts_idb AND l2_1.deleted=0

 INNER JOIN  opportunities l2 ON l2.id=l2_1.opportunities_accounts_1opportunities_ida AND l2.deleted=0

 WHERE (((accounts.billing_address_country='United Kingdom'
) AND (l1.name LIKE '%Prospect%'
) AND (l2.sales_status NOT IN ('Closed Won','Closed Lost') OR  l2.sales_status IS NULL 
))) 
AND  accounts.deleted=0) prospects_opp
FROM accounts
 INNER JOIN  tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
 AND l1_1.bean_module = 'Accounts'
 INNER JOIN  tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0

 WHERE (((accounts.billing_address_country='United Kingdom'
) AND (l1.name LIKE '%Prospect%'
))) 
AND  accounts.deleted=0

And this was my result:

As you see, I get the 20 Accounts belonging to United Kingdom "territory" that have the Tag "Prospect" as expected, and out of those, 8 have at least one open Opportunity which is a 40%

Then I created a new custom query that will show the result of dividing those 2 values (prospects_opp / uk_prospects) and show it on a % format:

SELECT 
CONCAT(ROUND((SELECT COUNT(DISTINCT  accounts.id)
FROM accounts
 INNER JOIN  tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
 AND l1_1.bean_module = 'Accounts'
 INNER JOIN  tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0
 INNER JOIN  opportunities_accounts_1_c l2_1 ON accounts.id=l2_1.opportunities_accounts_1accounts_idb AND l2_1.deleted=0

 INNER JOIN  opportunities l2 ON l2.id=l2_1.opportunities_accounts_1opportunities_ida AND l2.deleted=0

 WHERE (((accounts.billing_address_country='United Kingdom'
) AND (l1.name LIKE '%Prospect%'
) AND (l2.sales_status NOT IN ('Closed Won','Closed Lost') OR  l2.sales_status IS NULL 
))) 
AND  accounts.deleted=0)  /
(SELECT COUNT(DISTINCT  accounts.id) uk_prospects FROM accounts
 INNER JOIN  tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
 AND l1_1.bean_module = 'Accounts'
 INNER JOIN  tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0

 WHERE (((accounts.billing_address_country='United Kingdom'
) AND (l1.name LIKE '%Prospect%'
))) 
AND  accounts.deleted=0) * 100,2),"%") as percentage

And here the result:

If you wish to show all 3 columns in the report, then you could use the following SQL query:

SELECT 
    COUNT(DISTINCT accounts.id) AS uk_prospects,
    (
        SELECT COUNT(DISTINCT accounts.id)
        FROM accounts
        INNER JOIN tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
        AND l1_1.bean_module = 'Accounts'
        INNER JOIN tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0
        INNER JOIN opportunities_accounts_1_c l2_1 ON accounts.id=l2_1.opportunities_accounts_1accounts_idb AND l2_1.deleted=0
        INNER JOIN opportunities l2 ON l2.id=l2_1.opportunities_accounts_1opportunities_ida AND l2.deleted=0
        WHERE accounts.billing_address_country='United Kingdom'
        AND l1.name LIKE '%Prospect%'
        AND (l2.sales_status NOT IN ('Closed Won','Closed Lost') OR l2.sales_status IS NULL)
        AND accounts.deleted=0
    ) AS prospects_opp,
    CONCAT(
        ROUND(
            (
                SELECT COUNT(DISTINCT accounts.id)
                FROM accounts
                INNER JOIN tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
                AND l1_1.bean_module = 'Accounts'
                INNER JOIN tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0
                INNER JOIN opportunities_accounts_1_c l2_1 ON accounts.id=l2_1.opportunities_accounts_1accounts_idb AND l2_1.deleted=0
                INNER JOIN opportunities l2 ON l2.id=l2_1.opportunities_accounts_1opportunities_ida AND l2.deleted=0
                WHERE accounts.billing_address_country='United Kingdom'
                AND l1.name LIKE '%Prospect%'
                AND (l2.sales_status NOT IN ('Closed Won','Closed Lost') OR l2.sales_status IS NULL)
                AND accounts.deleted=0
            ) / COUNT(DISTINCT accounts.id) * 100, 2
        ),
        "%"
    ) AS percentage
FROM accounts
INNER JOIN tag_bean_rel l1_1 ON accounts.id=l1_1.bean_id AND l1_1.deleted=0
AND l1_1.bean_module = 'Accounts'
INNER JOIN tags l1 ON l1.id=l1_1.tag_id AND l1.deleted=0
WHERE accounts.billing_address_country='United Kingdom'
AND l1.name LIKE '%Prospect%'
AND accounts.deleted=0;

This would be the result:

I hope this helps but let me know if you have any additional questions.

Best,

Francesc

This is so helpful. Thank you Francesc! 

This is so helpful. Thank you Francesc!