Friday, January 17, 2014

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. 

25 comments:

  1. You may even be able to have a child object call Approval Event attached to the initial approval history object for easier reporting instead of numerous Approval history records. Either would work but I think have a child record for Recalls/Approvals/Denials feels cleaner from a data standpoint. (twitter.com/lifewithryan)

    ReplyDelete
    Replies
    1. Interesting Idea, definitely something to consider. Having a custom object opens up all sorts of possibilities like this!

      Delete
  2. This is great, thanks for posting! Are you using the SFDC out-of-the-box approvals? We don't use it but I believe it begins with the owner clicking the "submit for approval" button. I'm interested to learn more about your process and tracking, great work.

    ReplyDelete
    Replies
    1. Yes we are! Currently we force users to get an approval through a validation rule where if the approval status (custom field) on opportunity that updates from the approval process is anything but approved they can't close the deal in certain circumstances. We also have a trigger that updates that field if products/pricing change after the status is approved (sneaky :))

      The biz is re-defining a lot of the business logic for approvals, once they do that we are going to use APEX when it hits a certain stage and certain discount % we are going to auto route the approvals based on pricebook and/or product information. The biz is still figuring out the routing for all the product lines, so this probably won't happen until the Spring.

      Let me know if you need anymore details.

      Delete
    2. Thanks again! We are going to investigate using approval workflows and your technique would be a great addition. Currently we use custom fields and validation rules, but the business logic is getting more complex and so I am looking for a cleaner solution.

      Delete
    3. You're welcome. I had a conversation with our CST last week regarding our overall requirement to route approvals at the product level rather than the user level. He's going to put this in front of the PM for approvals to see what they think. I'll keep you posted, hoping they come up with something out of the box so we don't have to build more complexity to something that's already extremely complex.

      Delete
  3. But how to complete the requirement.Through configuration or customization..

    ReplyDelete
  4. You may even be able to have a child object call Approval Event attached to the initial approval history object for easier reporting instead of numerous Approval history records.
    goldenslot
    GCLUB Casino
    Gclub
    GClub casino

    ReplyDelete
  5. https://cherfeldman.blogspot.co.id/2014/01/the-best-thing-i-built-on-salesforce-my.html?showComment=1509633993763#c4173002506522136212

    ReplyDelete
  6. Me and my companions have completely appreciated this blog.Read More

    ReplyDelete
  7. So the custom object is created...!! I created a record too and submitted for approval , but it's showing up in the related list of case object. Please help

    ReplyDelete
  8. Keep your choices open while acquiring such solutions as there are numerous service companies that vows lot yet fails to deliver the desirable results. buy geo targeted facebook likes

    ReplyDelete
  9. Hi Cheryl! I'm a novice SF admin and have been tasked with creating reports and dashboards related to our approval processes. We use a variety of approval processes on opportunities before they can enter Presentation & Negotiation. I've created a report type with Process Instances and Process Nodes, but the unique process ID is less than visually appropriate for dashboards. I'd love to figure out a way to report on individual, unique approval process and the opportunities they're correlated to without making visible the process ID. Apex and advanced formulas are beyond my expertise. I'm not able to build the Approval History object on my own - I can't figure out how to pull the approval process data into the object's fields. Any help you could provide would be immensely appreciated!! Thank you

    ReplyDelete
  10. My friend mentioned to me your blog, so I thought I’d read it for myself. Very interesting insights, will be back for more!
    Designer Outlet Sales

    ReplyDelete
  11. This article is very helpful, I wondered about this amazing article.. This is very informative.
    “you are doing a great job, and give us up to dated information”.
    Skip Hire Plymouth

    ReplyDelete