Adam Murray Nowadays, sales is both science and art. Best practice blends the application of advanced data models with the experience, intuition, and knowledge of sales management, to deeply understand the sales pipeline. This process helps sales managers manage and invest in their team and anticipate opportunities that lead to exceeding revenue goals.
Why sales and analysts should work together
Analytics and sales should partner to forecast new business revenue and manage pipeline, because sales teams that have an analyst dedicated to their data and trends, drive insights that optimize workflows and decision making.
For this partnership to work, it requires sales leaders who really care about data and are open to analysts’ advice about how to use the Salesforce data they generate. And it requires analysts to have a closer understanding of the sales process so that they can focus their data models and tailor their analysis to help sales get the most out of their data.
In this blog, we share some ideas of how to best use data to manage sales pipelines and have access to the fundamental data models that enable this process.
The value of modeling Salesforce data for sales leaders and analysts
Sales data can get messy. There are lots of nuances, legacies from previous data and a rapidly growing pool of data. So, partnering with analysts to model Salesforce data will give sales teams more confidence to predict the revenue that teams are going to close at the end of any given period, and identify behaviors and strategies that will be most effective. Data gives sales a real-time understanding of where changes and opportunities are occurring in pipelines, so appropriate action can be taken to improve performance.
Analysts can use SQL as a more powerful tool than Salesforce to model messy sales data. By applying complex logic, you can more seamlessly build data models and gain fast, more advanced analysis. Plus, it unifies Salesforce metrics and definitions into one data model that becomes a single source of truth for your company, meaning there’s no question about the accuracy of data and no conflict between teams about what’s accurate.
This is not to say that data modeling should be focused specifically on sales. BI and analytics is used across multiple functions: marketing, sales, customer success, product, distribution, operations and logistics to name a few. There are multiple cases of leveraging data to drive better relationships between all these departments, better execution across all departments and best-in-class customer experience as a result of all departments working hand-in-hand with data at the center of all decisions.
Key ways to optimize insights for sales
For sales leaders, what’s hugely empowering is the ability to slice and dice data on the fly, understand what team and individual reps should be achieving, and easily measure the team from a data driven standpoint. It gives them the power to see insights, understand root causes and take immediate action.
To achieve this, first requires getting the data into a form that delivers insights. Salesforce data is extracted, transformed and loaded into a data warehouse using an ETL tool connected to the data warehouse. Then, use a data model to model the data into a single unified source of truth. From there you can create charts, and you can start applying filters to dictate exactly what information you want to show and what findings you want to extract.
What are these data models?
Generally speaking, there are two main resources to generate these charts and insights:
1. Daily snapshot of opportunities that’s derived from a table of opportunities’ histories. This will show you what stage any opportunities are in, who is the owner, what’s the value and what’s the close date
2. Sales pipeline movement by opportunity month. This is built out of the daily snapshot of opportunities and describes the end state of a pipeline set to close in a given month. It takes the daily snapshot and turns it into a pipeline movement chart. It’s like an event log of how an opportunity moves through a pipeline for a given period.
Daily snapshot of opportunities – a summary
First, collect all the data that lives in the opportunity’s histories so that you can know exactly on any given day what the state of any given opportunity is.
After creating the daily snapshot, then calculate the metadata such as: how many times is that opportunity pushed? How many times has the amount changed? How many times has the stage name changed? How many days has an opportunity spent in a given stage? How old is the opportunity?
Then, join additional data onto that table, such as activities data, which gives you an idea of how active an account executive is when they’re working with an opportunity. From this, you can form expectations for how you think that opportunity is going to progress over the month. It’s also a good management tool that allows you to quickly see exactly all the activities that are going on.
The stages needed to get there are:
1. Find all opportunity field value changes
2. Construct and define start and end dates for each field value
3. Create a daily snapshot of opportunities
4. Calculate opportunity metadata
5. Join additional data onto table
Sales pipeline movement by opportunity month
This is the opportunity that you expect to close in any given period.
It’s important for sales leaders to understand how opportunities enter, change and exit a month because this information can drive management and direct sales enablement, specifically when addressing anomalous events. For example, you can really see how and why that pipeline has changed. Was it pushed? Was it lost? Was there a big decrease? Or maybe it’s positive. Maybe there’s a huge increase in a month when a new feature was launched. These anomalous events are best detected when doing this kind of analysis.
The sales pipeline is determined by two factors: the close date of opportunities and the amount / value of those opportunities. And we categorize these changes into a set of movements: start; create a month; pull; increase; decrease; pushed; close lost; close won; and open.
Amounts and values change a lot, as do close dates. This data model aims to clean up this situation to make the data as actionable as possible. So, every opportunity is limited to one entry activity and one change activity in a given month. Let’s see how to categorize each of these movements and how to consider them when evaluating your sales pipeline.
Start
Evaluating the start is pretty easy. This is all the opportunities that are open on the last day of the previous month that have a close date set for this month. This starting number on your pipeline isn’t going to change throughout the month.
Depending on your sales cycle (fast vs slow), the percentage of your total pipeline made up by your starting pipeline will vary.
Created in month
These are the opportunities created in the month of the pipeline that you’re creating, whose close date is also in the same month. Again, the percentage of your pipeline will vary, dependent on your sales cycle but it will be a pretty sizeable part of your pipeline.
Pulled
This is a little more complicated. Let’s say you have an opportunity that’s set to close the next month but then you drive some sort of activity that pulls that close date into the current month. That is pulled pipeline and it’s counted if the first close day of this opportunity is after the month and the last close date is in this month. But we don’t count opportunities that get pulled again.
Increases / Decreases
There are two types of opportunities that we capture in this increase and decrease. For the opportunities that start in month, any changes that occur to that opportunity before its exit. So, if you look at the top one, the close date stays in this month and it increases. The next one shows an opportunity that pushes the amount increased and then pulls in. We need to include that. The third one shows an increase and then a push. And then the final one a pull and then an increase. What we do not include is when the increases and decreases occur outside of the month and the opportunity does not pull back in.
Why do we exclude these certain amount changes? In order for the pipeline to work, the entry amount, the amount change, and the exit amount must net out at zero.
Pushed
Pushed pipeline was meant to close this month, but for some reason it gets pushed out. We look at the value of the final push when summing up the value of each opportunity, to see what percentage of your pipeline is pushed.
Pushed pipeline will probably be a large consistent portion of opportunities exiting each month. If you look at pipeline by segment, you might notice that smaller segments may have less pushed pipeline, while larger segments generally have more.
Closed lost
This is when the last closed date is in month but the opportunity amount at the closed date is lost. Ideally this stays small and consistent over time and hopefully decreases as your reps get better and better at working the pipeline.
Closed won
These are the scenarios to celebrate. It’s when the last close date is in the month and the opportunity is successfully closed, so you can factor in the opportunity amount at the close date. Ideally, this is always increasing.
Open
Depending on when you’re looking at this pipeline movement chart – if it’s the beginning of the month then most of your pipeline will still be open. If it’s at the end of the month, most of your pipeline will be closed. This will never be completely zero because reps are always working their pipeline up until the last second and may not move out the close date.
This data model is dependent on the daily snapshot of opportunities. Here’s one example to show how we categorize these messy changes and simplify them. This opportunity that starts at $1,000, gets pushed the next month, decreases, gets pulled, gets pushed again, and increases again after it gets pushed.
So what we care about here is the sum of the decrease and the increase, and then the final push, as well as the start. So we have a start of $1,000. The sum of the increases and decreases equals $1,000 so that is the increase. And then it gets pushed after increases, so we count the value of the push as a value of the opportunity when it left this month.
We do not count the final increase. Why? Because we need every single opp to net out to zero by the end of the month. This opp increased by $1,000 and it finally got pushed at the end of the month.
There’s a lot of discussion here about what’s included and what’s omitted in this type of analysis. One of the values for sales leaders of working in partnership with data analysts, is that it allows better management of the sales team and how they manage their pipeline. Furthermore, the data analysts can identify changes that are occurring and behavior that might now not be beneficial. With this information, sales managers can engage with their reps and the customer engagement team to mitigate some of that behavior. So even just as a side benefit, this provides huge value from the analysis.
And what’s great is that it’s possible to continually add information that’s found to be valuable. If your data analyst has a hypothesis, you can dig into the data, model it out and then see how it plays out in the real world. So the flexibility and the ability to iterate on this information makes it a more and more powerful tool.