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. 

16 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. nike air max shoes, http://www.nikeairmaxshoess.co.uk/
    ralph lauren shirts, http://www.poloralphlaurenshirts.us.com/
    ralph lauren uk, http://www.ralphlaurenoutletuk.org.uk/
    nike mercurial, http://www.nikemercurial.org/
    air max 2015, http://www.airmax2015.in.net/
    tory burch outlet, http://www.toryburchoutletonline.in.net/
    moncler jackets, http://www.nobis.us.com/
    oakley outlet, http://www.oakleyoutlet.in.net/
    moncler coats, http://www.moncler.us.com/
    true religion jeans, http://www.truereligionjeansoutlet.com/
    coach handbags, http://www.coachhandbagsoutletonline.us.com/
    winter jackets, http://www.cybermonday2015.us.com/
    michael kors handbags, http://www.michaelkorshandbagsoutletstore.us.com/
    ray ban sunglasses, http://www.raybansunglass.com.au/
    cheap nhl jerseys, http://www.nhljerseys.us.com/
    ray ban sunglasses, http://www.raybansunglass.co.uk/
    cyber monday 2015, http://www.blackfriday2015.in.net/
    michael kors outlet, http://michaelkors.outletonlinestores.us.com/
    oakley sunglasses, http://www.oakley.in.net/
    louis vuitton handbags, http://www.louisvuittonhandbags.org.uk/
    louis vuitton outlet, http://www.louisvuittonoutlet.in.net/
    black friday deals, http://www.barbourjacketsuk.co.uk/
    cheap nba jerseys, http://www.nbajerseys.net/
    oakley sunglasses, http://www.oakleysunglassesdiscount.us.com/
    rolex watches, http://www.rolexwatches.in.net/
    michael kors factory outlet, http://www.michaelkorsfactoryoutlets.in.net/
    cheap jordans, http://www.cheapjordanshoes.in.net/
    cheap wedding dresses, http://www.cheap-weddingdresses.net/
    ralph lauren outlet, http://www.ralphlaurenoutlet.in.net/
    1010maoqiuyun

    ReplyDelete
  4. But how to complete the requirement.Through configuration or customization..

    ReplyDelete
  5. 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
  6. اليكم ارضيات ثلاثية الابعاد وهى عبارة عن مواد ابوكسية عالية الجودة ذات صلابة اقوى من الفولاذ ولها لمعان كازجاج ودرجة شفافية جميلة جدا دون اى تكلفة عالية وتكون ارضيات ثلاثية الابعاد ناطقة كالرخام بل وافضل منه لسهولة تركيبها وعدم تعرضها للخدش كالرخام او التلف ان استعملتم المواد الكحولية وتغطى جميع المساحات الممكنة وتلا تقتصر فقط على الميتلك بل يوجد بها العديد من الاشكال والرسومات الجميلة والرائعة .
    لماذا عليك اختيار ارضيات ثلاثية الابعاد فى الرياض ؟
    من الان قل وداعا للسيراميك والبرسلين والرخام انت الان فى مرحلة التطور والجمال والفن الراقى فانت تنعم بما توصل له العلم الحديث من افكار وما توصل له عالم الديكورات من من اشكال فمن الان قل وداعا للباركيه ومشاكل الماء فتجد ان ارضيات ثلاثية الابعاد فى جدة تعمل على عزل الارضيات عن السطح ولا يوجد بها مسام كارخام او تمتص الماء كالباركيه انما هى عازل حراري ومائى للارضيات وتتميز بالاشكال الرائعه التى تجعل من منزلك التحفة الفنية .
    ولمعرفة المزيد يرجى زيارة الرابط التالى
    https://beerm-ksa.com/%D8%A7%D8%B1%D...9%D8%A7%D8%AF/

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

    ReplyDelete
  8. I like the post format as you create user engagement in the complete article. It seems round up of all published posts. Thanks for gauging the informative posts.
    cara menggugurkan kandungan

    ReplyDelete