In this forum post we will cover how to create a Report to see how many Sales Leads are being converted to Opportunities by each User, this can be as a real number or a percentage, over a given time period.

Why would you want to see this?

This report will allow sales professionals to understand how many outstanding leads they have yet to convert, or for management to understand how effective their sales team are at converting leads to opportunities, aiding in more effective forecasting and targeting of leads that need to be generated in order to convert to an opportunity.

How do I create this Report?

Step 1. Set up the report

Firstly we need to create a new blank report against Sales Leads, in the Details tab we will first create the following columns:

• Assigned to name
• Sales Lead Name
• Sales Lead reference
• Status
• Created at

Further to this by drilling through “Audit Records” we want to add:

• Audit Id
• Record Id
• Field Changes > New Value
• Field Changes > Field Name

Note: You may see some duplicates at this stage, this is because the same lead may have multiple audit records against it, depending on what has happened to them in the past, so do not be alarmed by this.

Step 2. Add Criteria to the report

From the Criteria tab we can also only choose to show leads that have been created or updated in a specific time period, such as those that were created in the current calendar year, or those that were Updated within the current quarter.

Step 3. Create the Summary View

We are going to create a Summary view to see how many leads each User has Converted. Firstly we are going to create a Calculated Column titled “Leads Converted” and enter the following formula:

COUNT( DISTINCT  IF(PARENT('Status') LIKE '%Converted%',  PARENT('Sales Lead reference'), null))

This formula outputs the reference number for each converted sales lead, then totals the number of unique lead numbers. Essentially telling us how many different leads have been set to Converted.

Additionally, you can also tick Display summary to show the grand total below this column to see how many leads have been totalled across your sales team.

We can now add a grouping column, grouping by Assigned to name, this will then give you the subtotal for each user.

This should give you an output that looks like something similar to the following:

Step 4. See the conversion rates

We can now create another calculated column called Total leads, to see the total number of leads assigned to each User. We do this with the following formula:

COUNT(DISTINCT PARENT('Sales Lead reference'))

This formula counts the number of unique reference numbers and will again show the subtotal for each User. Again we can choose to total this column as well.

Now to see the Conversion Rate, create a third calculated column and enter the following:

(COUNT( DISTINCT  IF(PARENT('Status') LIKE '%Converted%',  PARENT('Sales Lead reference'), null)) 
/ COUNT(DISTINCT  PARENT('Sales Lead reference'))) *100

This takes the number of leads the user has converted, divides it by the total assigned to them and then multiplies this by 100.

You can display this as a percentage by selecting the Advanced tab and selecting Display as percentage.

This should then give us an output that resembles the following:

Here you can see each user, the number of leads they have converted and how this compares as a conversion rate.

If this is then something that you would want to target your sales professionals on you can additionally add Conditional or CSS styling to make the rows stand out. You can find further information about how to do this in our Knowledge Base.