One interesting question came up a month or so ago on the Salesforce Success Community page, and it reminded me of some customizations I have done for some clients in the past. And the time saver centres around measuring the time between a lead creation (first touch) and opportunity close.
With salesforce.com native, you can measure how long a lead was a lead until it was converted, and how long an opportunity was open before it closed. But, for those opportunities that came directly from a lead, to determine the time from lead creation to opportunity close requires a couple of different reports to be merged outside of Salesforce. The good news is, with two custom fields, you can fix this issue! To do this:
1. Create a custom date field on the lead object called Lead Creation Date, and set the default value to Today(). (I know this is a redundant field from the native Created Date field, but bear with me…). Also, remember that this will not automatically set for the leads that exist already. To fix this situation, run the Data Loader (or Excel Connector if you are on Professional Edition) and do an export on Lead ID, CreatedDate, and Lead_Creation_Date__c, then simply update by setting Lead_Creation_Date__c to the original CreatedDate.
2. Create a custom date field on the opportunity object called Lead Creation Date.
3. Back on the Leads object, click the Map Lead Fields button on the Lead Custom Fields section, and set the Lead.Lead Creation Date field to write to the Opportunity.Lead Creation Date field, and save.
What this will do is when a lead is converted AND an opportunity is opened, the opportunity custom field will now be populated with the original lead creation date. From this, you can:
- create formula fields, determining the number of days open since lead creation
- create formula fields, determining the pre-opportunity stage length of time (it took x days for this lead to convert into an opportunity)
- create workflow rules or email alerts on opportunities that had a lead opened more than x days or months ago (to track stale opps)
- create reports and dashboards on the opportunity cycle from lead creation (pre-opportunity) to opportunity close, from one report.
As always, hope this helps and hope you are having a great summer!
2 Comments
Hi Nik, We’re in the middle of going through this issue now and we had to rule out this kind of process because there’s one major point of failure… Merging duplicates… If Lead A has a date of 7/21/2011 and Lead B has a date of 10/31/2009, and when the Leads are merged if the date from Lead A is selected, then the reliability of this kind of date field cannot be trusted because it will show 7/21/2011 but has really been in the DB since 10/31/2009. We’re working on a custom object that would behave in a similar manner to Campaigns… When two records are merged together (or Leads converted to Contacts), the combined Lead would have two date records and we can roll the earliest date up to the Lead/Contact using Roll-up Summaries. There is a little more to it than that, but that’s the conceptual idea. Then it doesn’t matter how many times duplicates merge together, we keep a history and can make sure we always grab the earliest date.
True, merging dupes could cause an issue, but if you are using the manual merge leads, you would be able to choose which lead date you wanted to choose, as they would be different values. If you have gone the route of Apex or an AppExchange application, you should be able to set the parameters so that the earliest created date is chosen during the de-dupe action.