Opportunity Pipeline Stage Time Stamp

[Kenny]

I am interested in reporting how long an opportunity takes to close (number
of days) as it moves through the sales pipeline. Ideally we would like to see
the number of days between each pipeline phase. Then we can analyze how long
opportunities stay at each phase. Has anyone done anything like this before?

[Jarrett]

Hi Kenny,

You could do simple jscript behind a checkbox that will do a timestamp
when checked. When that phase is complete click the checkbox.

Create a nvarchar field to store the timestamp. Calling this FIELD1 in
code
Create a bit field and under properties change it to checkbox. Calling
this FIELD2 in code

Onload place this code

crmForm.all.FIELD1.onclick = function()
{
if (crmForm.all.FIELD1.DataValue == true)


{
var myDate=new Date()
crmForm.all.FIELD2.DataValue=myDate.toString();
crmForm.all.FIELD2.disabled = true;
}

else
{
crmForm.all.FIELD2.DataValue = null;
}
}

if you want it to look clean you could not show the label for the
field that holds the timestamp and put this code onload:
crmForm.all.FILED1.style.backgroundColor = "eaf3ff";
crmForm.all.FIELD1.style.border = "0px";

This will make it where when you click the checkbox only the timestamp
will show, not the white text area or the box outline.

Hope this helps!

Jarrett Coleman
Dynamics Four

[Dave]

Kenny,
Yes, I've done this many times before. The solution proposed by
Jarrett may get that one timeframe, but as you mentioned, you really
want to know how long opportunities stay in EACH portion of the
pipeline. What you need is auditing. (I also personally don't like
solutions such as those proposed by Jarrett that requires users to
remember to do something to make an important process work correctly,
particularly when you can add automation behind the scenes that will
accomplish the same thing, and even more, without user intervention.)

In CRM 4.0, you can accomplish the basic data capture task using
Workflow, or an add-on from c360. (There may be a simpler, more
elegant solution coming in CRM 5.0). But in any event, if you want to
get this done today, the fastest cheapest method would be to create a
workflow based auditing solution. (Note: there is a workflow solution
somewhere up on codeplex, I believe, but that just creates one table
for auditing all entities, which isn't as nice as one for each
entity.) Try this:

1) Create a custom entity (Opportunity Audit).
2) Create an attribute in the Opportuity Audit entity for each
attribute you want to track, or audit. For most of my clients, they
have a lot of custom attributes, so I just re-create the entire
schema, so I can audit changes to any and all fields. (To do this, I
will export the custom opportunity audit entity after creating it, and
export the opportunity customizations, and do copy and paste in the
customizations.xml to duplicate the fields from one entity to
another. Just the attribute section is usually sufficient, and only
copy the custom attributes so you don't duplicate status, e.g..)
-- One caveat: you cannot copy a picklist from one entity to another
within workflow, so make all picklists text attributes in the audit
entity, and just move the value of the currently selected picklist to
the corresponding text field within the workflow.
3) Throw the fields onto the audit form, and publish your custom audit
entity.
4) Create a workflow against Opportunity. Check the "Record status
changes" box. (For a full audit, click the "Record attributes change"
box, and select all fields.)
5) Click Add Step, and 'Create Record", and select "Opportunity Audit"
as the record type to create.
6) Click on Set Properties. Then, move from field to field, and
select the field from the opportunity that should populate the
opportunity audit record. (Each field on the opp audit form should
have a yellow field that has the corresponding opportunity field value
appearing in each.) These fields should all be named the same, so this
is a pretty simple task. Again, note the caveat about picklists, you
have to move picklist values to a corresponding audit text field.
(Just move the picklist field in the set properties section of this
create step to the text field... The current value of the picklist
will move into that text field.)
7) Publish your workflow. Now, every time you save an opportunity
record, you will create an Opportunity Audit record.
8) If you create a 1:M link from Oppy to Oppy Audit, you can see all
of the changes made to the opportunity on the left hand nav bar. You
can also grant or restrict this view to users via normal role
permissions.
9) Create an SSRS report that loops through all Opportunity Audit
records (sorted by modified date within opportunity guid), and
calculates the number of days between status changes... This is not a
trivial report to write, and is best done using a SQL stored
procedure, and then using the output of that sproc as your SSRS report
source. (You may need to account for cases where the opportunity goes
"backward", and probably need business to make the call whether or not
that factors in to the equation.) I typically create a permanent
table (in another DB), and run a sproc to populate that table. I
usually add one date field and one integer field for each status.
Status1Date, DaysInStatus1, Status2Date, DaysinStatus2, etc... Then,
it becomes a simple select from that table to create the report.

But when you have finished all this, you can create all kinds of nice
reports that show (by sales rep, product mix on the oppy, or deal
size, e.g.) how long it takes to move from the first status code to
the second, from the second to the third, etc.

HTH,
Dave

-------------------------------------------
David L. Carr, President
Visionary Software Consulting, Inc.
Certified Microsoft CRM 4.0 Consultant
Cell: 503-351-4207
Email: ***@earthlink.net
http://www.vscrm.com