Potential Solution for Reporting on Salesforce Opportunity Approvals

To all who read this:

I'm not a developer, I have no idea how to write code, however I have a really good understanding of what a trigger is, when it should be used and etc.  If you're curious, I'm a Technical BA at a large financial services firm here in NYC.  

Here's our challenge:

We rolled out approvals on opportunities January of 2008.  Our user base has grown immensely as well as our use of opportunities and enforcement of getting approvals on discounted opportunities.

As we are enhancing our approval process we need to be able to answer certain questions:

How long does it take for an opportunity to be approved?  We currently do pull up some information from the approval process through field updates on the opportunity but that is not detailed enough.  

We want to really break it down.  How many times is an opportunity submitted for approval?  How many times is rejected before finally approved.  Why is it being rejected?  How many times are users being forced to re-submit due to pricing or product changes (we have validation forcing users to do this).  Who is the approver on the opportunity and how long does it take that one person to approve on avg? Who is the approver changed to?   Since we really can't answer these questions with out of the box reporting, I think I may have found a solution which would require a custom object and some APEX.

We have done some similar things with opportunity history reporting and case history reporting have found that these designs work well for us and how we need to look at the data.    

I posted something similar on Salesforce Answers, but wanted to also put it here.  Selfishly because I always wanted to start a blog, so I guess this is also my first post.

Here is the design, would love feedback if you've done this before. Did it work?  What challenges did you have?  

Design for Approval History Reporting

Problem: Salesforce currently doesn’t allow for reporting or metrics on approvals.  Our business needs to be able to report on how long it takes for one approval to happen, how many times it was submitted/rejected/approved, how many different approvers it went to.

Potential Solution:

Add a custom object called “Approval History Reporting” to be a child object of opportunities through a master detail relationship.

Add the following fields to the custom object:

Approval History Start Date/Time

Approval History End Date/Time

Approver – lookup to user

Approver Changed to – lookup to user

From Approval Status (picklist) – Submitted, Approved, Rejected, Recalled

To Approval Status (picklist) – Submitted, Approved, Rejected, Recalled

Approver Comments text field

Age = formula field to calculate Approval History End – Approval History Start, show number in hours

How this object will work

Records will be inserted and/or updated when certain things happen.

Scenario 1: Opportunity is submitted for approval

Upon initial submission of an opportunity to an approval process a record will be inserted into the Approval History Reporting with the following fields populated:

Approval History Start Date/Time – Time opportunity was submitted for approval

Approver – user selected as approver

From Approval Status = Blank

To Approval Status = Submitted

Scenario 2: Opportunity is Approved by Approver:

Existing record with To Status of Submitted will update to have an Approval End Date/Time of the time the stage changed

New record will insert with the following information:

Approval History Start Date/Time – Time opportunity was approved

Approver – user selected as approver

From Approval Status =Submitted

To Approval Status = Approved

Comments = any comments entered by approver

Scenario 3: Opportunity is Rejected by Approver:

Existing record with To Status of Submitted will update to have an Approval End Date/Time of the time the stage changed

New record will insert with the following information:

Approval History Start Date/Time – Time opportunity was Rejected

Approver – user selected as approver

From Approval Status =Submitted

To Approval Status = Rejected

Comments = any comments entered by approver

Scenario 4: Opportunity is Recalled by User:

Existing record with To Status of Submitted will update to have an Approval End Date/Time of the time the stage changed

New record will insert with the following information:

Approval History Start Date/Time – Time opportunity was Recalled

Approver – user selected as approver

From Approval Status =Submitted

To Approval Status = Recalled

Comments = any comments entered by approver

Scenario 5: Approver is reassigned

Existing record that is submitted with no End time will have the Approver Changed To lookup update to the new approver

Scenario 6: Opportunity is Recalled and User Submits Again

New record will be inserted with the following information

Approval History Start Date/Time – Time opportunity was submitted for approval

Approver – user selected as approver

From Approval Status = Recalled

To Approval Status = Submitted

Scenario 7: Opportunity is Rejected and User Submits Again

New record will be inserted with the following information

Approval History Start Date/Time – Time opportunity was submitted for approval

Approver – user selected as approver

From Approval Status = Rejected

To Approval Status = Submitted

Scenario 8: Opportunity is Approved and user hits validation due to product/pricing changes and is forced to submit again

New record will be inserted with the following information

Approval History Start Date/Time – Time opportunity was submitted for approval

Approver – user selected as approver

From Approval Status = Approved

To Approval Status = Submitted

** For Clarification on Scenario 8, our validation is on the stage.  If a user tries to close and an opportunity hasn't been approved or products/pricing has changed they will be forced to submit again.

One item to note is I'm also considering maybe adding a type field and inserting records when approvers change.  I'm not sure if that's necessary right now but that maybe an option for anyone trying to get more reporting on approvers and how often they change.