Percent of customers with an opportunity

Question

Hello, 
 I'm looking for help, please, creating a 'Percent of "Prospect" Accounts with an Open Opportunity' report. 
 A couple of variables I need to account for. 
 1. A given territory has anywhere from 20 to 100 accounts 
 2. I'm trying to generate a report that would only look at 'Prospect' accounts 
 3. I'm using the 'Tag' field to denote Prospect accounts 
 Example: A territory with 100 general accounts. Within the 100 accounts there are are 20 Prospects. Of the 20 Prospects, 2 have an Open Opportunity. The desired result in this example would be 10% (2 of 20). 
 Thanks in advance.

Brian Botts · Answer

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