Salesforce Pipeline Visualization With Blockspring and Google Sheets

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):

christoph janz pipeline visualization
(From http://christophjanz.blogspot.com/)

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.

You can make your own copy of the template from our template vault on Trello – grab an invite here.

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.

salesforce pipeline by transition

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.

salesforce pipeline by stage

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:

  1. Make a copy of the spreadsheet (accessed in the template vault below)
  2. Install Blockspring if you haven’t already (comes with a 2-week trial)
  3. Connect Salesforce to Blockspring (requires Salesforce API access)
  4. Click ‘Run’ in Blockspring to populate opportunities data
  5. 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.

Submit a comment