5

Our Marketing are interested to know how long each lead was in each status, from New to Qualified.

Eventually, they want to reduce the numbers to as short as possible.

We have about 5 statuses they are interested in.

I want to create (just like the oppty age) a report that can summarize for each lead, how many days it was in each stage.

The Lead History report type could have what I want. But how can I create a meaningful report form the following?

enter image description here

  • assuming I ignore the "Last Transfer Date" -

Taking an example lead

enter image description here

I know the dates it changed status: 2/1, 23/1 and 1/2, but how can I calculate the number of days it was in each status?

Improve this question
3
  • 1
    Do you have any side effects of reaching the Qualified stage? Setting some custom field, sending out an email message... Or you might want to play with analytic snapshot (make helper object that will be your homemade history table but you'll be able to have some formula that calculates days between...
    – eyescream
    Commented Jan 23, 2014 at 14:42
  • Nothing. It's all manual.
    – Saariko
    Commented Jan 23, 2014 at 18:22
  • @Saariko did you get report created? I would be interested to get more information on how you implemented it. I am trying to do the same am having trouble visualizing the data the way I was hoping. Any screenshots and formulas you used to accomplish this would be extremely helpful.
    – user8499
    Commented May 23, 2014 at 2:23

3 Answers 3

Reset to default
3

The best idea I've heard for this utilized custom fields. Essentially you created one date field for each stage you're interested in and use workflows or triggers to populate when the Lead enters that stage. You could then find the duration of each stage by comparing the fields.

Example:

  • EnteredStage1Date__c
  • EnteredStage2Date__c
  • EnteredStage3Date__c

If you have the data in your history table you could back-fill these fields and report off of them. In the past I've used this for Opportunities but it should work fine for Leads.

Edit

As sfdcfox points out in the comments, this isn't the most robust solution since flip-flopping stages invalidates the data. A more robust solution would be a trigger that provides a cumulative tracker so that switching to an earlier stage only adds to the counter instead of resetting it.

Improve this answer
4
  • 1
    That works great so long as users are not allowed to flip back and forth between status values. I think a more complete solution would be numerical fields and a last status change date field, with a trigger to keep the numbers up to date.
    – sfdcfox ♦
    Commented Jan 23, 2014 at 14:14
  • That is definitely a concern; user's going to earlier stages kind of invalidates the data. I'll update my answer with something more robust.
    – Mike Chale
    Commented Jan 23, 2014 at 14:30
  • 1
    Workflow will be fine as long as the fields will be set to Default: 0 (and data fix for all historical data). That way the workflow formula can safely go "me = me + 7 hours" without running into "me" being null.
    – eyescream
    Commented Jan 23, 2014 at 14:35
  • 1
    Most Lead processes are pretty much one-way. I think it's likely that just the fields and workflow field updates as Mike described them initially might be good enough for Saariko.
    – Jeremy Nottingham
    Commented May 23, 2014 at 4:45
2

Unfortunately, the opportunity stage history report is a "special" report designed to show stage durations, etcetera. There's no equivalent report type that does the same function for custom history tracking. You could achieve this with an offline database such as MySQL, or a reporting tool such as Crystal Reports.

Improve this answer
1
  • argh. I posted an idea on that. but lets see if others come with a non default answer
    – Saariko
    Commented Jan 23, 2014 at 13:43
0

Here is a formua I use to show how long a lead is in open Status. Maybe you can adapt this.

IF(ISPICKVAL(Status, "Open"), ROUND(NOW() - CreatedDate, 0), null)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .