Stage Duration Analysis allows you to track how long deals are spending in each stage of the sales pipeline and which stages are causing bottlenecks in your sales process.
The challenge is while the data exists in SalesForce, in the back-end datasource we only have the dates the stages were created on. Therefore some data modelling is required in order to calculate time spent in the stages as well as handle situations where a stage might repeat itself in an opportunity.
By knowing how long each opportunity spends in the different stages you can create the following reports to provide additional insights into your sales process:
The Stage Duration By Owner shows you how long it takes team members to close deals. In addition to identifying who is closing a deal the fastest and who needs additional coaching, you can also deduct the optimal sales cycle that reps should run.
Stage Duration by opportunities Win\Lost can provide insight into successful vs unsuccessful sales cycles.
Step 1 - Import Relevant Tables
Connect to your SalesForce account and import the following tables:
Opportunity, OpportunityHistory, OpportunityFieldHistory, OpportunityStage
You can bring in other tables such as the user table to provide analysis by rep or other dimensions
Step 2 - Create Data Model
Refer to the attached ElastiCube in order to calculate the stage duration. In particular, the following 2 custom tables:
Stage Duration Int – This identifies all of the opportunities stages, ranks them by order and gets the stage create and modified dates
Stage Duration – This table aggregates all stages from the intermediary table and calculates total time spent in different stages.
Step 3 - Create Dashboards
Create a relationship between the Stage Duration table and you can go ahead and create dashboards off that table.