How to build a sales funnel in Tableau: Workout Wednesday – Week 14 Solution
We’re in the middle of a lockdown and my audio driver is broken on my laptop so this week’s video solution is just a blog post. Before we start, be sure to use the correct data source located here.
It’s important to know this custom dataset is designed for challenges in building a marketing or sales pipeline. There is only one row per “opportunity”. And an opportunity will cross many stages. Most organizations use each stage to allocate appropriate resources in order to close a deal.
The challenge in working with this data is we often want to have the data accounted in multiple stages–not just the current stage, but all previous stages, too. This is the challenge provided.
Step #1: Set the framework
Add [Last Stage] to rows and [value] to columns. Make sure bar chart is selected.
Step #2: Create a sort calculation and sort rows.
We’ll want to sort our stages. So create the following calculation called [sort].
//sort CASE [Last Stage] WHEN "Prospect" THEN 1 WHEN "Lead" THEN 2 WHEN "Qualified" THEN 3 WHEN "Opportunity" THEN 4 WHEN "Negotiations" THEN 5 WHEN "Closed" THEN 6 END
Now sort [Last Stage] ascending by the sort calculation:.
Step #3: Create the sales funnel calculation.
SUM([Values]) to columns, again. Then make it a
RUNNING_SUM(). For the second calculation you need to create a running sum of the [value].
The only problem is that the running sum shouldn’t be in the down direction, rather it should be running upward. This is not an out-of-the-box functionality with Tableau, but there is a way it can be done by customizing the Table Calculation.
Edit the table calculation and select Specific Dimensions. Make sure [Last Stage] is selected. Below, on Sort Order, create a custom sort using the [sort] field, select minimum aggregation and choose the sort order as descending.
Click-, hold-, and-drag this table calculation over to measures on the data pane. Name the calculation [sales funnel].
Step #4: Create the percent of stage calculation.
For the last calculation we are going to calculate what percent of closed sales wen through each stage using another Table Calculation. Write the following calculation:
// percent of stage LOOKUP(SUM([value]), LAST()) / [sales funnel]
Add this calculation to columns. Then to the right of all three calculations on columns: double-click and type MIN(1.0). Make a dual axis between MIN(1.0) and [percent of stage]. Synchronize the axes. Make sure all mark types are bars. Change the opacity of the bar color to 35%.
Step #5: Format!