This forum post will show you how to look at the time taken between each stage of your Opportunities.

The report can be useful for identifying certain areas in your sales process that may be causing a bottleneck, as well as providing you with an insight of where your sales resources should be allocated.

To start with, we will need to create an Opportunity report, and add columns for Object Reference and Opportunity Name. It is important that we use the Object Reference for this, to avoid confusion between duplicate records.

Next, add calculated columns for each of the Stages. These will look into the Audit records, to identify when new values were assigned to Opportunity data. For the first one (see below) we are only looking for Opportunity changes that relate to Stage one, so we have used the formula : LIKE "%1%" to only retrieve related info, for Stage two we would adapt this to : LIKE "%2%", and so on.

If you wish to copy and paste this formula, then you can : IF(audit_records.fields.new_value LIKE "% [enter your stage number here] %", audit_records.fields.created_at, NULL)

Once this has been completed for all of your Stages, you will see a screen similar to below

You will notice that the data is not filtered to Stages specifically, and so in order to make this report look at relevant records only, we then need to add a criteria that will narrow our view down to Audit records relating to Stages only

Now we need to add a Summary View, and the first column that needs to go here is a grouping column, which is going to group by the Object Reference of the Opportunity

To ensure that we are only looking at the most recent records for these field changes (since Opportunities can move forwards and backwards in their stages, we only want the most recent records), we need to add columns to the Summary View; telling Workbooks to only find the most recent date for each Stage, using the formula below:

Once this has been completed for each stage, you will see the dates upon which each Stage was updated in Workbooks.

Click Add Summary View and then Add Summary View from Summary to create a new Summary View.

Now in order to see the time in days between each Opportunity Stage, we simply need to use the DATEDIFF function, to work out the differences between the date values we have collected.

Once this has been completed for all of the Stages, you will be left with data similar to this

As you can see, the retrieved data is not perfect, and is affected by the following issues :

1) Opportunities may 'jump' stages, eg from Stage 1, straight to Stage 3.  In that scenario there may be missing values as shown in the screenshot.

2) Your database may be set up so that Opportunities can move backwards through stages and then forwards again rather than following a linear, Stage 1 to Stage 2 to Stage 3, etc.  If Opportunities do move backwards, you might see negative values in your report, as shown above.

It's important to consider the implications of the above points when considering the effectiveness of this sort of report within your organisation.