Perspectives on Salesforce.com

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:

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.

I have setup a knowledge base using my Salesforce org to contain solutions for Arrowpointe products. Going forward, this will be the official location for help documentation on all Arrowpointe products. It is currently well populated with information on Arrowpointe Maps and Info Center. I am in the process of populating it for Auto vCard, User Adoption Dashboard and the Akismet anti-spam solution.

The direct URL to the knowledge base is:

http://www.arrowpointe.com/support/kb/

The Arrowpointe Maps AppExchange package was upgraded to include 2 new tabs to let you directly visit the Routing & Map Reports functionality. It was also upgraded to utilize a more modern version of the API.

If you are an admin using Arrowpointe Maps, you’ll see a warning at the top of the screen like below:

To upgrade, simply:

1. Go to AppExchange and click Get It Now. You DO NOT need to uninstall the old version.
2. Follow the prompts on the screen to sign in and install the application as you install any other application. The screen will indicate it’s an upgrade.
3. Once completed, you should add the new Map Reports and Routing tabs to the Arrowpointe Maps custom application so that your users can access that functionality directly. If you don’t do this final step, they can still access this functionality from the front page of Arrowpointe Maps.

Submit a Case from the application’s front page if you have any problems.

In the latest version of Arrowpointe Maps, there is functionality that lets you create maps by pulling data directly from your Salesforce.com Reports.

This capability essentially lets you map anything in Salesforce.com that has an address or is related to something with an address. Bottom line is that if you can get an address into your report, you can put that data onto a map.

I created a video explaining this functionality. Click the image below to watch it.

About Arrowpointe Maps

Arrowpointe Maps is an on-demand mapping platform that facilitates a conversation between Salesforce.com & MapQuest allowing for easy deployment of mapping capabilities in your organization and providing end-users a simple means for mapping their data. Arrowpointe Maps is configurable and can be tailored to your organization, so that your users can work with their information in a meaningful way.

The official location for information on Arrowpointe Maps is its product page at http://www.arrowpointe.com/maps. There, you will find answers to the most frequently asked questions, screencasts and links to its AppExchange page.

Version 1.0.9 of Arrowpointe Maps was just released. You can read about all the improvements on the Change History page.

The major changes are:

• You can now create maps by pulling data directly from your Salesforce.com Reports. I’ll post a video of this functionality soon. This fully addresses the popular idea expressed here + a bit more. This functionality is being marked as “beta” due to Salesforce not having a true API to access report data.
• You can now email your driving directions from the application. You can email up to 10 addresses at once, including an option to BCC yourself.
• Added a Created By filter option on Map Pages. It works just like the Owned By filter option that’s existed for a while, but it bases the filter on the creator of a record. Edit your Map Pages and check the Created By checkbox to add this option to your Map Pages.
• Added a Query Size Limit at the Subscription level. It defaults to 2000 records. This means that Arrowpointe Maps will return up to 2000 records for every query. If a query for more records is made, the application will return the first 2000 records and the end-user will be provided with a message explaining what happened. An increase to the query limit can be requested by submitting a Case to us. This change was made to eliminate the issues of run away queries that freeze a browser.
• The improved map marker dot and star images were re-implemented, so the maps look more professional.

All the functionality described above is available now. A new AppExchange install will be available later this week, which will be recommended for everyone. The new download includes 2 new tabs to let you go directly to Routing & Map Reports, without needing to visit the Arrowpointe Maps front page first, and also upgrades the Salesforce API version used by the application. Once that package has been released, I’ll let you know by: making a new blog post, sending all subscribers an email and adding a message to the application for Admins to see if they are using an older install version.

About Arrowpointe Maps

Arrowpointe Maps is an on-demand mapping platform that facilitates a conversation between Salesforce.com & MapQuest allowing for easy deployment of mapping capabilities in your organization and providing end-users a simple means for mapping their data. Arrowpointe Maps is configurable and can be tailored to your organization, so that your users can work with their information in a meaningful way.

The official location for information on Arrowpointe Maps is its product page at http://www.arrowpointe.com/maps. There, you will find answers to the most frequently asked questions, screencasts and links to its AppExchange page.