It seems that a large number of customers that use Salesforce CRM would be interested in answering some very basic questions like:

  • Who are my top 25 customers?
  • What’s my win percentage on this account?
  • Can you show me all my repeat customers and when the last time I closed a deal with them?

But when your data is not structured properly or you are segmenting your data in a funky way it can take a lot of sleuthing to answer them.

Fortunately you are using Salesforce and it's very easy to add a few fields which can instantly answer these questions in addition to looking like a hero!

All of these fields are based on the assumption that you’re using standard Salesforce Accounts & Opportunities on Group Edition or higher.  Account is representing the prospect or customer organization and Opportunity representing each deal.

Field 1 - Date of last won deal

This field will calculate the date of the last won deal.  This value can come in handy when evaluating repeat customers (i.e. if date of last won deal is six months ago, time to follow up).

Create a “Rollup/Summary” field type.  The field to evaluate is the “Close Date”, the operation is “MAX”, and the filter should be “Won = True”

Field 2 - Count of Opportunities

Want to know how many times you’ve engaged with this customer and tracked a deal?  This field will provide a gross value for that.

Create a “Rollup/Summary” field type on the Account.  Evaluate the Opportunity object, and run a “Count” operation.  That’s it!

Field 3 - Count of Won Opportunities

This will give a count of the # of Opportunities that have been won.  Helpful when identifying top customers or repeat customers.

Same method as above, but add a criteria filter for “Won = True” and only won deals will be counted.

Field 4 - Total Value of Won Opportunities

Want to know how much business  this Account has done with your company?  This field will tell you in a flash.  It’ll sum up the total amount of won Opportunities which will aid in identifying top Accounts.

Create a Rollup/Summary field on the Account object which summarizes Opportunities.  Evaluate the field “Amount” and calculate the “SUM”.  Add a filter for “Won = True”.  BAM!

Field 5 - Win Percentage

Sometimes it’s nice to see which accounts have a higher chance of winning deals than others, especially when making decisions on which sale to put energy into.  This simple formula will calculate that.

Create a formula field on Account.  You’ll be evaluating two fields - The Count of Opportunities and the Count of Won Opportunities as referenced above.  The basic calculation is:  Count of Won Opportunities / Count of Total Opportunities, with output as a percentage.  

One Caveat - If there are zero Opportunities, you’ll be dividing by zero which will look bad on a report.  Therefore, a little IF statement logic will sort that out.

IF(Count_of_Opportunities__c > 0, Count_Won_Opportunities__c / Count_of_Opportunities__c,0)

So what to do with all this Knowledge?

One thing that’s great about Salesforce is that the fun does not stop here.  With a few more minutes, reports can be built using these fields to increase visibility further and give some nice statistics.  Here’s a few ideas:

  • Top 25 Accounts - Report on account & sort on “Total Value of Won Opportunities”.  Limit results to 25 rows.
  • Neglected Accounts - Report on Account.  Use Criteria where Count of Opportunities greater than 1, and last sale date is not within past 6 months (or whatever value makes sense)
  • Repeat Customers - Report on Accounts.  Sort on number of won deals.
  • Winningest Accounts - Report on Accounts, sort on Win Percentage.
If your Salesforce instance does not have these fields yet, it’s a quick win to increase knowledge of your customers and prospects, and only takes a few minutes to set up.  On top of that, it opens up a myriad of reporting options to help you better manage your pipeline, sell more, win that trip to Tahiti, and take over the world.


Creating a rollup/summary field -

A ton of reporting videos from Dreamforce -