This template is inspired by Christoph Janz’s recent post on sales pipeline visualization, where he proposed visualizing the transitions between pipeline stages (as opposed to static monthly totals):
I’ve built KPI dashboards with Salesforce pipeline data before, but I was intrigued by this new take on tracking a pipeline’s development.
Transition rates, since they’re normalized as percentages, give you a snapshot of an entire system in one view. They’re what mortgage modelers use to compare the risk of vast and unrelated pools of mortgages.
So I set out to build a template modeled off of Christoph’s post, that’ll let you visualize live pipeline data from Salesforce. This template uses Blockspring to pull data into Google Sheets, but you could also take this same approach with Excel.
Diving into the charts
A summary table and chart allows you to view overall transition rates by month, and spot high-level trends of how deals are moving through your pipeline.
My personal favorite is the pipeline transition chart, which opportunity volumes moving from each stage into one of four buckets: kept in the same stage, moved to next stage, lost or won.
This helps you quickly spot where deals or getting stuck, or at what stage you’re losing deals.
Right off the bat, I see the power in Christoph’s approach to pipeline analysis – you can see based on the colors in each stage if things are headed in the right direction.
I could see this being most useful for benchmarking one pipeline against another (ie one company against another, or salesperson against another, etc.) – since it provides a normalized view of sales efficiency.
How it’s made
This template pulls in historical changes to your Salesforce opportunities, meaning any change you’ve made to a deal throughout its history, for all of your deals.
Opportunities data is then aggregated up into a monthly pipeline, with transitions modeled based on changes to deals (including value updates).
This happens under the hood in a few aggregation tabs – all you have to do to get set up is:
- Make a copy of the spreadsheet (accessed in the template vault below)
- Install Blockspring if you haven’t already (comes with a 2-week trial)
- Connect Salesforce to Blockspring (requires Salesforce API access)
- Click ‘Run’ in Blockspring to populate opportunities data
- Select the order of your pipeline stages, so that charts appear in the correct order
These instructions are also contained on the ‘Setup’ tab of the template, which will walk you through the process to pull live Salesforce data.
Get your copy of the template
Interested in taking it for a spin? Get your copy of this template in our template vault on Trello below.