Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Reporting on Opportunities not 'updated' in x days

Jump to Best Answer
  • 1.  Reporting on Opportunities not 'updated' in x days

    Posted 13 days ago
    We have a requirement to create a view or report which will return:

    Opportunities that have been assigned from a specific team member to any other team member within the last 5 days
    AND
    Opportunity has not been updated (i.e. not modified and without any activities regarding it that were created in the last 5 days)

    I can pull this easily if I ignore activities
    Viewbut I don't know how best to exclude opportunities where emails or phonecalls have been tracked but the opportunity itself has not been modified.

    I could create a workflow to update a field on the opportunity record whenever an activity is created but this will negatively impact other reports that depend on the modify date not being affected by activity records.

    Creating an intermediate opportunity-activity entity, updated via workflow on activity creation could work but I don't want to be running thousands of workflows and taking up extra storage space if there's an easier way to do this.

    Any advice welcome

    Thanks in advance,
    Sean

    ------------------------------
    Sean O Malley
    AIG
    ------------------------------


  • 2.  RE: Reporting on Opportunities not 'updated' in x days

    GOLD CONTRIBUTOR
    Posted 13 days ago
    Sean, I dont think there is any easy way apart from updating a custom field on opportunity based on activity creation. And You can do that either through a workflow, or a system job. Also, something that I have not explored earlier, but it may be possible, is, using rollup fields. You can try that too.

    ------------------------------
    Sunil Raheja
    Aha Apps
    Glen Allen VA
    https://www.ahaapps.com/microsoft-dynamics-crm/
    ------------------------------



  • 3.  RE: Reporting on Opportunities not 'updated' in x days

    Posted 11 days ago
    Hi Sean,

    If you have online deployment, perhaps you can consider proposing/using Sales Insight that comes with many nice reports to assist with winning the opportunity, such as predictive Lead and Opportunity scoring, which may fit your use case to identify neglected opportunities.

    If you are keen on not creating/updating data unnecessarily (and also using workflows), then you can easily replicate and modify the OOTB Neglected Leads report as your base, which also take Notes and Quotes creation into considerations if you are using on-premise, otherwise you will have to convert the SQL query and its business logic into a combination of dataset and filters for an online implementation.

    Alternatively, using the Advanced Find (and also view, and potentially dashboard) can be as simple as creating the Opportunity-Activity entity that stores all your reporting criteria. It's essentially a 1:1 to Opportunity but in CRM you can create it as N:1 (i.e. lookup to Opportunity), but I would rename it to say, Opportunity Neglect Report. You'll need to:
    a) Create Opportunity Neglect Report entity,
    b) Add fields - Date Reassigned, Last Activity Date (drive your Advanced Find query based on these two fields),
    c) Workflow/Plugin
    1. Set Date Reassigned - Before opportunity for user x reassigned to another user/team.
    2. Set Last Activity Date - Create function for any Activity/Note/Quote created for Opportunity to update the Last Activity Date

    Hope that helps.

    ------------------------------
    Jacky Chen
    Consultant
    Chamonix Consulting
    adelaide
    ------------------------------



  • 4.  RE: Reporting on Opportunities not 'updated' in x days

    Posted 8 days ago
    Sunil, Jacky,

    Thank you both for the advice.

    I think creating the Opportunity-Activity entity is probably the best option for us.

    ------------------------------
    Sean O Malley
    AIG
    ------------------------------



  • 5.  RE: Reporting on Opportunities not 'updated' in x days

    GOLD CONTRIBUTOR
    Posted 8 days ago
    Sean, Just keep in mind that with this approach you are going to create multiple intermediate records per opportunity. So you will have to keep in mind for an archival/purge strategy for these records. Also for the workflows that you will run to create the intermediate entity records, make sure you have those as background workflows and also check the checkbox to delete the successful instances of workflow (in order to not have those lying around unnecessarily)

    ------------------------------
    Sunil Raheja
    Aha Apps
    Glen Allen VA
    https://www.ahaapps.com/microsoft-dynamics-crm/
    ------------------------------



  • 6.  RE: Reporting on Opportunities not 'updated' in x days

    Posted 8 days ago
    Thank Sunil,

    The plan is to
    1. Set the relationship behaviour to Configurable Cascading and set everything to "Cascade None"
    2  Set up a bulk delete job to delete any intermediate records, related to 'won/lost opportunities which have a modified date > x months'

    Regards,
    Sean

    ------------------------------
    Sean O Malley
    AIG
    ------------------------------



  • 7.  RE: Reporting on Opportunities not 'updated' in x days

    TOP CONTRIBUTOR
    Posted 8 days ago
    Sean, if this is successful, I'd love to see a blog post how-to on how you did this. I'm not a super CRM admin, I can do workflows and that's about it. But creating these types of reports would be beneficial for us.
    I'm also going to look into Sales Insights that @Jacky Chen suggested. ​​​

    ------------------------------
    Brooke Browne
    Marketing Director
    Smartbridge
    Houston TX
    ------------------------------



  • 8.  RE: Reporting on Opportunities not 'updated' in x days

    TOP CONTRIBUTOR
    Posted 8 days ago
    Hi Sean-

    We went about it a bit differently and it works great.

    There are two rollup fields created on the Opportunity entity that are looking at the latest completed activity and the latest quote entered.

    Then we have our integration tool (SmartConnect from eOne) run a scheduled integration to look at the two dates to find the most recent of the two. It updates another custom field on the Opportunity called "Last Activity Date."

    -Joe

    ------------------------------
    Joseph C. Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 9.  RE: Reporting on Opportunities not 'updated' in x days
    Best Answer

    TOP CONTRIBUTOR
    Posted 8 days ago
    Edited by Gus Gonzalez 7 days ago
    It's long - I know - but I believe this is what you need - if it is, consider subscribing to the channel so you don't miss any Two Minute Tuesday tips like the one you see below:

    https://www.youtube.com/watch?v=LAjj5ZB0KYM

    ------------------------------
    Gus Gonzalez
    8x Microsoft MVP, CRMUG All-Star
    CEO, Elev8 Solutions
    ------------------------------



  • 10.  RE: Reporting on Opportunities not 'updated' in x days

    TOP CONTRIBUTOR
    Posted 7 days ago
    There may be another way...

    Cobalt have a free solution that allows you to do "not in" queries.

    Install this solution then you can add a line to your Advance Find that says "and include records that do not have an email or phone call attached in the last 5 days".

    The logic is easy but they explain it better than I can:

    https://www.cobalt.net/category/crm-lab/intelligent-query/

    ------------------------------
    Simon West
    Nett Sales LLP
    Aldbourne
    ------------------------------



  • 11.  RE: Reporting on Opportunities not 'updated' in x days

    Posted 7 days ago
    Thanks everyone for all the suggestions. I think the Rollup field may just give us what we need with the least effort.

    Thanks again,

    ------------------------------
    Sean O Malley
    AIG
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role