Sales Stage Age

I'd like to record the age of the current sales stage, not just the age since "Closed-Won". I was wondering if there were a way to createa process that set a date field every time the sales stage changed, then I could create a related calculated field to record the age in days since the change.  

Can anyone help with this or think of a better solution? 

  • Hi Elizabeth Buckfelder,

    You present a valuable use case for leveraging some Sugar's more powerful features, and this is possible using a combination of SugarBPM and Sugar Logic. Here is how I was able to achieve your request:

    Prerequisites:

    • Running Sugar Sell or Enterprise. For reference, I validated this test in the Fall '19 release (9.2).
    • I am running the default configuration in Opportunities with Revenue Line Items, so all changes are centered around the Sales Stage field in the RLIs module. If you have Opportunities configured to not use RLIs, you should still be able to mirror these same steps for that module.
    • A general working knowledge of SugarBPM design

    Note: For the sake of brevity, I created this proof of concept using 3 sales stages rather than the entire list of stock sales stages.

    Phase 1: Studio Modifications

    We first need to create all the necessary fields and calculations to track your sales stage durations.

    1. Go to Admin > Studio > Revenue Line Items > Fields > Add Field
    2. For each non-closed sales stage you track, create 3 fields:
      1. Start date (e.g. Prospecting Start Date, Qualification Start Date, etc.) - Field type: Date
      2. End Date (e.g. Prospecting End Date, Qualification End Date, etc.) - Field type: Date
      3. Integer (e.g. Prospecting Duration, Qualification Duration, etc.) - Field type: Integer
        1. Flag the field as calculated. The formula builds off the concept presented in the 'Calculated Field - Count of Days Between Two Dates' KB article. The formula should incorporate the field names created for the start and end date. The follow formula either subtracts the difference between the start and end date for the prospecting sales stage, or if an end date does not exist yet, outputs the number of days the RLI has been in that sales stage to date.
          ifElse(isValidDate(toString($prospecting_end_date_c)),abs(subtract(daysUntil($prospecting_end_date_c),daysUntil($prospecting_start_date_c))),abs(daysUntil($prospecting_start_date_c)))

          Your field list in Studio should look similar to the following once complete:

           {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  
    3. Optional: Add the fields to the RLI Record View layout if you want the data readily accessible by your users. If you do not add the fields to the layout, they will still function as intended. I added all the created fields to validate the expected behaviors. You may want to omit adding the date fields for production use as they can be edited by some or all users depending on role and team restrictions. The calculated fields are natively read-only. For this example, I added all the fields under a separate panel. I set this panel as a tab to keep the data segregated from the primary record view panel.
       {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Phase 2: SugarBPM

    Now that you have all the fields created in Studio, it is time to design the process to populate the fields as expected. First, create the base record for your process definition:

    1. Go to Process Definitions > Create Process Definition
    2. Give the definition a name, set the target module to 'Revenue Line Items', and click 'Save & Design'

    In the design phase, there are two scenarios you need to account for:

    1. Sales stage set on a new RLI
    2. Sales stage changing on an existing RLI

    Sales Stage Set on a New RLI

    For this use case, create a start event and configure it to start for 'New Events Only'. There is no need to enter any additional criteria unless desired for a more complex use case:

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Sales Stage Changing on an Existing RLI

    For this use case, you will first need to create a start event for each non-closed sales stage. Set each event to apply to 'Updated Records Only (All Updates - See Help Text)'. This is done to cover for the scenario where a user may revert a RLI to sales stage that was previously set and you need to recalculate the start and/or end date. In addition, each start event needs a criteria set corresponding to its sales stage. Set the criteria to 'Sales Stage changes from <sales stage>'.

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Once your start events are created, you should have a design layout similar to the following:

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    The next step is to create actions for all of the events where the sales stage is changing. The action type should be set to 'Change Field', and the settings should be configured to set the end date corresponding with the sales stage to the value of 'Run Time'. This means that the current date will populate in the end date field when the sales stage changes from that value.

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Connect the start event for each sales stage to its corresponding action and your design layout should appear like this:

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Now, you will create a gateway to set the start date for the new sales stage on the RLI. The gateway should remain as the default type as 'Exclusive' and direction as 'Diverging'. We expect 1 possible outcome to occur each time the process is triggered. Connect the new record start event and all of the sales stage actions to the gateway. Your design layout should now look like this:

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    With the gateway established, you now need to create objects for all possible outcomes. First, create an end event with the default result of 'Do Nothing'. The end event covers the scenario where a RLI moves to a closed status. Then create actions for each non-closed sales stage. Similar to before, the action type should be set to 'Field Change'. The settings should be configured to set the start date corresponding with the sales stage to the value of 'Run Time', and the end date should be set to an empty value to account for instances where the RLI is reset to a prior sales stage. 

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Once you have all the actions created, connect the gateway to the end event and all the actions. Connect all the actions to the end event as well, and your layout should look similar to the following:

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

     

    The last steps are to finish configuring your gateway. Set the default flow of the gateway to the 'End Process' event. In settings for the gateway, set criteria for each action to 'Sales Stage is equal to <corresponding sales stage>':

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    Note: This design will overwrite the start date if a RLI is moved into a sales stage it previously held. If you want to retain the original start date a RLI went into a given sales stage, you need to create some additional logic to identify whether the start date is currently populated and include that in your gateway criteria for each sales stage.

    Once those settings are saved, click the 'Save & Validate' to ensure the design has no errors. Then, enable your process definition and start tracking your sales stage ages!

     {SugarClub Administrator Edit: We're sorry, but this image is no longer available}  

    You could further enhance this feature by creating calculated fields on the Opportunities module to provide averages or sums of the various sales stage lengths from the child RLIs.

     

    One final caveat is that this solution involves creating a significant number of fields on RLI module. If you already have a large number of custom fields (200+) on the module and/or you have a large number of sales stages you want to track aging on, then a custom-coded solution may be better since it can eliminate a large number of the fields you have to create to track this data. MySQL is the most common database used with Sugar, and it is what is used in SugarCloud. MySQL has a finite amount of space it can handle in any given custom field table, so it is important to be conscientious of potential physical limitations you may encounter. If a custom solution is desired, my company, Upsert, delivers the results you desire.

  • Could I do the same thing without incorporating RLI? Just using the opportunities module?


  • This is a great example of your kind help, but any chance of getting the images restored for the screenshots as they ar all missing due to old forum migration?
    Thanks in advance

    .

    CRM Business Consultant