How can I create a Lead to Closed Won Opportunity Conversion Rate Report? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Thomas LopezThomas Lopez 

How can I create a Lead to Closed Won Opportunity Conversion Rate Report?

I feel like I am always very close to accomplishing this, but am always missing one piece. We use Salesforce Enterprise. I apprecaite any advice!
Best Answer chosen by Thomas Lopez
Steve MolisSteve Molis
Okay I built mine using the same report type "Leads with Converted Lead Information" 

On my org I use Tom Tobin's "Power of Own" technique on all Objects, that allows me to get the distinct count of Leads, Opportunties, etc, which makes building the Report Summary Formulas a LOT easier 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://trailblazers.salesforce.com/answers?id=9063000000048RbAAI

All Answers

Steve MolisSteve Molis
Can you post a screenshot of the Report, Settings, and Filters that you're using, the results you're getting, and a mock-up of what you want?  For questions related to Reports and Dashboards those are really helpful 
Steve MolisSteve Molis
Are you looking for something kinda like this? 
User-added image
Thomas LopezThomas Lopez
I'm not sure that is exactly, but I would LOVE that report too. Basically, I am trying to track, per sales rep, what our conversion rates are from lead to closed won opporunity. I'm currently using a "Leads with Converted History Report. I am posting what I have on my outline and filters right now, as well as the formula. Basically all my report is saying right now is how many closed won opportunities we have per person. And the conversion rate is 100% . User-added imageUser-added imageUser-added imageUser-added image
Steve MolisSteve Molis
Okay I built mine using the same report type "Leads with Converted Lead Information" 

On my org I use Tom Tobin's "Power of Own" technique on all Objects, that allows me to get the distinct count of Leads, Opportunties, etc, which makes building the Report Summary Formulas a LOT easier 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://trailblazers.salesforce.com/answers?id=9063000000048RbAAI
This was selected as the best answer
Steve MolisSteve Molis
And these are the Formula that I used 

Lead Convert Rate %
IF(Lead.Lead__c:SUM = 0, 0,
(CONVERTED:SUM / Lead.Lead__c:SUM)
)

Convert to Opportunity Rate %
IF(CONVERTED:SUM = 0, 0,
(Opportunity.Opportunity__c:SUM 
 / 
 CONVERTED:SUM))

Opportunity Win Rate %
IF(CLOSED:SUM  = 0, 0 ,
( WON:SUM / CLOSED:SUM) 
)


 
Thomas LopezThomas Lopez
I just created the custom field and got to step 3. Is this where I put the formulas? 
User-added image
Steve MolisSteve Molis
Okay first things first DON'T name the fields "Power of One

Name each field after the Object you're creating each field on, Lead, Account, Opportunity, Person, Woman, Man, Camera, TV, etc...  


 
Steve MolisSteve Molis
PS.  the other Formulas that I posted are Report Summary Formulas 

Documentation Add a Summary Formula Column to a Report 
https://help.salesforce.com/articleView?id=building_custom_summary_formulas.htm&type=5 (https://help.salesforce.com/articleView?id=building_custom_summary_formulas.htm&type=5)
Thomas LopezThomas Lopez

I really appreciate all your help! So I am going to create a custom field (following the power of 1 article), call it "Lead" if I'm under the Object, Lead. 

Then, I can include that field in my lead report. As well in that report, I can add a summary formula with the (lead) forumla you provided above. Does that sound right. 

I am qutie new to reports, so I really appreicate you bearing with me. 

Steve MolisSteve Molis
Yep! 

Don't forget to add the Power of One fields to the other objects as well
Thomas LopezThomas Lopez
Thank you! When creating the Custom Field, what do I need to put in the Step 3: Enter Formula Box when creating the Custom Field? 
Steve MolisSteve Molis
1
Thomas LopezThomas Lopez
Thank you! I have created a custom field for lead and Opportunity and have the fields present in my report. I added 2 of the 3 summary reports you listed above, but when trying to add the "Convert to Opportunity Rate %" Formula, I am getting this error:

 Field Opportunity.Opportunity__c:SUM does not exist. Check spelling.

Any ideas?
Steve MolisSteve Molis
What did you name the Opportunity Power of One field? 

Also, don't just copy&paste the formulas that I posted, build them in the Report Formula Editor using the Insert Field and Insert Funxction buttons
Thomas LopezThomas Lopez
I got it to work, thank you so much!
Steve MolisSteve Molis
No problem (you owe me a beer) 


PS.  if you're all set, can you do me a favor and mark this closed & resilved by clicking the Best Answer link on the solution.

Thanks,
SteveMo