One of the first things I often set up in a new Salesforce org is timestamping. Timestamping, or marking the Date/Time a record enters each step of a business process, is a high priority for me because it enables me to track how effectively and quickly records move through a given funnel. If you’re not convinced that this is important, here is a whole host of insights and possibilities for forecasting that this opens up:

  1. Conversion rates between steps in each funnel: This helps me identify where I have problems that need solving
  2. Velocity in funnel: This helps shine a light on where can I improve efficiency and create more capacity for my teammates
  3. Setting Baselines: How can I identify deals that are moving exceptionally quickly or slowly, and figure out how to replicate those characteristics in a deal?

That’s just scratching the surface, but hopefully, that was enough to convince you.

Now that we’re on the same page, I am going to outline below some of the considerations I make before setting up this automation, and how I set it up natively in Salesforce vs how I do it nowadays (using Sweep!).

Consideration #1: Date or Date/Time?

I want to measure the funnel’s efficiency and use the correct unit of measurement (minutes, hours, days, weeks, months?!), but to know that I must first have some expectation of how records will move through the funnel. In a one-size fits all scenario, I use Date/Time because it enables me to have the most granular measurement. If I need to convert it to days, I can do that using a simple row-level formula in a report.

Consideration #2: What else to measure?

I typically stamp the Owner and the Owner Role, and their IDs at each step of the process as well. The reason I do this is to ensure that future reporting is flexible should the names of users or user’s roles change over time. This helps smoothen out historical reporting should any of the following happen:

  • A salesperson changes from the Mid-Market team to the Enterprise Team
  • I changed my user’s display name from Ben Zeitz to Benjamin Zeitz
  • The Mid-Market team name was changed to the Commercial team

This helps me keep report filters straightforward and protect historical data.

Consideration #3: Do we allow stages to move backward?

As all RevOps or System Admins understand, most sales processes are not typically linear. Yet, in the name of good data, many opt to allow opportunities to only move forward in the process in Salesforce. Others do not even allow the skipping of steps. These are helpful when it comes to clean data for analysis, but they make the end-user experience a bit worse. I typically allow skipping and moving backward and make some data assumptions in the background to simplify analysis. To help accomplish this, I stamped both the first time and the most recent time we entered a stage.

Now that I have covered some considerations and strategies that I use for timestamping, it's time to outline this build step-by-step in flow!

  1. Create all of the required time stamping fields: For each step in the process (in this example we will use opportunity stages, titled “Stage 1”, “Stage 2”, “Stage 3”, and “Stage 4”), I create each of the following fields:
    1. [Stage Name] First Date/Time
    2. [Stage Name] Date/Time (this is the most recent date time)
    3. Stage Name Duration - a formula field with the criteria listed below. Since I am using a formula field, I acknowledge that this data will not be 100% correct for opportunities that can jump backwards and forwards across steps of the process. For this calculation, I make the assumption that if a step has moved backwards, it’s as if it was never in the future step.
      1. If opp is in a stage currently, its duration is “today’s date - date record entered the current stage”
      2. If opp is past a stage, its duration is “date record entered the next stage - date record entered the prior stage”
      3. If opp is not yet up to a stage, it should be blank
    4. [Stage Name] Owner (name)
    5. [Stage Name] Owner ID
    6. [Stage Name] Role
    7. [Stage Name] Role ID
  2. Create a flow, and add a decision node for record creation or stage change, thus limiting how often the flow runs through the decision tree.
  3. Create another decision to find what is the current stage
  4. For each stage, check if the first date/time is blank, and assign it a value of NOW when entering a stage and it is blank. If it is not blank, do nothing to the field.
  5. For each stage, assign the most recent Date/Time, Owner, Owner ID , Role, and Role ID, fields. Since this is the most recent, they will get updated each time you enter the stage.
  6. Now the fun part, if we enable skipping stages or moving stages backwards, we must:
    1. Create automation to check what the prior stage was, if it is the immediately previous stage in the process, there are no additional data to update
    2. If the prior stage is more than one stage before the current stage, you must update the Date/Times, Owner, Owner ID, Role and Role ID of each of the stages skipped
      1. When calculating duration, steps that were skipped will show a duration of 0 and enable you to calculate conversion rates appropriately - even if it didn’t spend time in a step, you need to count the record as having “achieved” that step from a conversion standpoint. This way, you have a cascading amount of records in your denominator from each stage to the next, instead of nonsensical amounts. This calculation gets more complex with branching, which we will discuss in a future post
    3. If the prior stage is a stage further along in the funnel than the current stage, we must replace the values of the most recent Date/Time, Owner, Owner ID, Role, and Role ID with a null value, thus making the assumption that if we moved backwards, it is as if we truly achieve the next stage.
  7. If using fast field updates, you’re all set - but if you use actions and related records in your flow (which may make sense if you plan on layering other items into this flow in the future), then you need an update records node for each of the timestamping fields.

Now, imagine, what happens if I add a new stage into the process, or rename a stage. I need to add 7 new fields for each new stage, create a “grouping” of automation to accommodate for the new stage, and advancing into that stage from various stages of the process, update my duration formulas for the prior and next stage, and update each of the automation groupings to accommodate for skipping or moving backward around that stage or from that stage. It's a headache! And don’t even get me started on reporting…

How do I do this today in Sweep?

To accomplish this In Sweep:

  1. I open up any funnel
  2. Click the gear in the top left corner and click “BI Settings”
  3. I toggle on the options for Date/Time, Owners, and Roles
  4. And click deploy

This will give me the following:

  1. Fields:
    1. [Stage Name] First Date/Time
    2. [Stage Name] Date/Time (this is the most recent)
    3. Stage Name Duration
    4. [Stage Name] Owner (name)
    5. [Stage Name] Owner ID
    6. [Stage Name] Role
    7. [Stage Name] Role ID
  2. Automation:
    1. Entering each stage will automatically mark the first Date/Time and the most recent Date/Time that you entered a stage, as well as the Owner, Owner ID, Role, and Role ID. Skipping steps and moving backwards will also populate the fields accordingly
    2. Duration - Sweep will calculate a TRUE duration (in hours) of how long you spent in the step. If you move backwards and forwards, you will still get an accurate representation of the time spent in each step
  3. Edits:
    1. If you make a change to the funnel (change a stage name, add a stage, etc) the automation will automatically update accordingly on deploy

Same result, in a fraction of the time! And, in a future post, we’ll show you the reporting that Sweep automatically creates so you can analyze each funnel.

To see what this looks like in Sweep compared to Salesforce, watch the short video below