Behold the Power of Salesforce Report Summary Formulas Part II!

Behold

In our previous post “Behold the Power of Salesforce Report Summary Formulas!”, we showed you how you could calculate a 3-month moving average of Closed Won Opportunities. In this installment, we’ll cover the second example: Figuring out the Conversion rate of all leads over a period of time.

First, we will create a report on Leads with Converted Lead Information.

Salesforce Formula 1

Then, select your Date Field and Range for your report. Ideally, you would focus your Date Field on the Created Date of the lead – the Range can be set to All Time so every lead is processed in the system (truly giving you a lead conversion rate for all leads that have been ever entered into Salesforce) or maybe you would prefer to only look at this year, or the previous year.

Salesforce Formula 2

Next, change your report format over from a Tabular report to a Summary report and use the Converted field as the grouping field for your report.

Salesforce Formula 3

Lastly, we need to add a new Report Summary Formula into our report to calculate the percentage of converted leads to unconverted leads. To do this, click on the Add Formula link

Salesforce Formula 4

When creating the formula, you will need to give it a name and determine its format. For this scenario, we chose Percent as the format with two decimal places. Since we have a grouping setup on our report using the Converted field, we are presented with the option of where to display the results of the formula. This is an important step as the location of the formula result will determine what summary values the formula can access in the report. Make sure that Converted grouping is selected. Lastly, we need to provide the formula that will do all the heavy lifting. Use the following for your formula:

RowCount/PARENTGROUPVAL(RowCount, GRAND_SUMMARY)

What this formula says is this: – Take the row count of the Converted group, – Then divide that number by the row count in the Grand Summary parent grouping (in our case, the total number of converted and unconverted leads).

Here is a visual of what your Custom Summary Formula should look like once saved:

Salesforce Formula 5

Once you save this Formula, it will appear at the very end of your columns – keep in mind that report summary formulas will always be the last columns in your report. Once you save your formula and run your report, you should see something like the following:

Salesforce Formula 6

 

Go ahead and click on Hide Details to collapse all the records so you can see your new % Converted formula field in action!

Salesforce Formula 7

From here, you can easily change the Time Frame on your report to see the conversion rate of created leads in more specific time periods, like the Current FY, the Current and Previous FQ – or, add a chart to the report and use it as a dashboard component for your executives!

You can extend this functionality further to see who has been converting the most amount of leads with just a few clicks and a quick edit to our formula. If we edit our report, we next can add the Lead Owner as our primary grouping, pushing the Converted grouping down as a secondary grouping.

Salesforce Formula 8

Then, we’ll edit the formula and replace what was there with the following:

RowCount/PARENTGROUPVAL(RowCount, LEAD_OWNER)

Which is now dividing the row count of the Converted grouping by the row count of the Lead Owner grouping (and not the Grand Summary).

Salesforce Formula 9

This in turn will calculate the percentage of leads that person owns that they have converted compared to the percentage that they have not converted. Find out who your lead champions are and congratulate them!

Salesforce Formula 10

About Ledgeview Partners

Comments are closed.