Derive a Rating using Formulas

On a recent project, I was helping my client prioritize their lead data. They were having trouble with the default Rating field in Salesforce because 1) it required a user to populate the record and 2) the rating should change with the passing of time.

I came up with a formula that takes the Lead Status and Expected Close Date (a custom field) into account in order to derive a Rating value on a Lead. This client happens to use Leads for their entire sales cycle and converts Leads once they are sold, but this concept would apply to equally to Opportunities as well.

Requirements

The simplified requirements are to derive a Rating value as follows:

Lead Status / Days to Close 0 to 30 days 31 to 60 days 61 to 90 days over 90 days
New New New New New
Contacted Warm Warm Cold Cold
Interested Warm Warm Warm Cold
Presentation Hot Warm Warm Cold
Sent I/O Hot Hot Hot Warm
Sold Won Won Won Won
Lost – No Contact Lost Lost Lost Lost
Lost – No Budget Lost Lost Lost Lost
Lost – Not Interested Lost Lost Lost Lost

They plan to manage their pipeline closely using the derived Rating field. They want the Rating to get "better" as time passes, so that they can reinforce the use of the Expected Close Date field. For example, if there is a Lead in Presentation status and it gets forgotten by Sales, that lead’s rating will eventually get to "Hot" as the Expected Close Date approaches or is passed. Management will be asking about that Lead and it will either be accurate or the Expected Close Date will be updated to better reflect reality. Over time, they will really be able to trust the Expected Close Date field, which is a new field for them.

Solution

To accomplish this, I did the following:

  1. Added a custom field on Leads called Expected Close Date. This would be filled out by the end-user and acts like the Close Date does on an Opportunity.
  2. Added a custom formula field called Days to Close that calculates how many days away the Expected Close Date is. The formula is as follows:

    Expected_Close_Date__c – TODAY()

    This field was added to the Page Layout because it is pretty useful in and of itself. Also, the client is on Professional Edition, so we wanted this field available in reports and that’s how you make it visible.

  3. Added a custom formula field called Rating (derived) that calculates a rating value using the requirements from the table above. I use this formula approach (using the CASE function) whenever I am doing some kind of matrixed formula result.
    CASE(1,

    IF(ISPICKVAL( Status ,"New"), 1, 0),"New",

    IF(AND(ISPICKVAL( Status ,"Contacted"), Days_to_Close__c <= 60), 1, 0),"Warm",
    IF(AND(ISPICKVAL( Status ,"Contacted"), Days_to_Close__c > 60), 1, 0),"Cold",

    IF(AND(ISPICKVAL( Status ,"Interested"), Days_to_Close__c <= 90), 1, 0),"Warm",
    IF(AND(ISPICKVAL( Status ,"Interested"), Days_to_Close__c > 90), 1, 0),"Cold",

    IF(AND(ISPICKVAL( Status ,"Presentation"), Days_to_Close__c <= 30), 1, 0),"Hot",
    IF(AND(ISPICKVAL( Status ,"Presentation"), Days_to_Close__c <= 90), 1, 0),"Warm",
    IF(AND(ISPICKVAL( Status ,"Presentation"), Days_to_Close__c > 90), 1, 0),"Cold",

    IF(AND(ISPICKVAL( Status ,"Sent I/O"), Days_to_Close__c <= 90), 1, 0),"Hot",
    IF(AND(ISPICKVAL( Status ,"Sent I/O"), Days_to_Close__c > 90), 1, 0),"Warm",

    IF(ISPICKVAL( Status ,"Sold"), 1, 0),"Won",

    IF(ISPICKVAL( Status ,"Lost – No Contact"), 1, 0),"Lost",
    IF(ISPICKVAL( Status ,"Lost – No Budget"), 1, 0),"Lost",
    IF(ISPICKVAL( Status ,"Lost – Not Interested"), 1, 0),"Lost",
    "")

  4. The Lead Views and Reports we made for reps are driven by this derived field. By doing this, they have 1 simple value to prioritize on. Also, as new Lead Statuses emerge or if we re-define what "Hot" means, we only need to modify the formula. When we do, all the reports and views automatically pick it up since they are not hardcoding filters on the Lead Status of Expected Close Date fields.

Does anyone else use an approach like this? Any suggestions to make it better?

Depending upon complexity, it is possible to hit formula size limits. Especially considering the need to use the ISPICKVAL function for the Lead Status. Vote on this idea to fix the ISPICKVAL issue.

2 Comments

  1. Paul Said,

    March 28, 2008 @ 9:15 am

    Great idea, although you’ll have to make sure the data is updated or else something may move to hot even if the deal has stalled and nobody has gotten back to the rep.

    As for the formula, Salesforce hasn’t fixed ispickval for if() functions, but they have fixed it for the case() function (where it was really needed). So, if you want a much simpler version then you can do this:

    Case( Status ,
    “New”,”New”,
    “Contacted”, IF( Days_to_Close__c <= 60, “Warm”, “Cold”),
    “Interested”, IF( Days_to_Close__c <= 90, “Warm”, “Cold”),
    “Presentation”, IF( Days_to_Close__c <= 30, “Hot”, IF( Days_to_Close__c <= 90, “Warm”, “Cold”)),
    “Sent I/O”, IF( Days_to_Close__c <=90, “Hot”, “Warm”),
    “Sold”, “Won”,
    “Lost – No Contact”, “Lost”,
    “Lost – No Budget”, “Lost”,
    “Lost – Not Interested”, “Lost”,
    “”)

  2. Scott Hemmeter Said,

    March 28, 2008 @ 9:26 am

    @Paul:

    That is brilliant! I had no idea that ISPICKVAL wasn’t needed in the CASE function. That saves me lots of heartache and significantly reduces the risk of exceeding the formula size limit. Thanks!

RSS feed for comments on this post