Funnel charts

In today’s data-driven world, visualizing complex information is crucial for making informed decisions. Among the myriad of chart types available, funnel charts stand out for their ability to represent the progressive stages of a process. Picture this: you’re diving into the analytics of a killer marketing campaign, tracking the journey of potential leads, or deciphering the intricate layers of a recruitment pipeline. Funnel charts aren’t just another pretty face in your data toolbox; they can be the Gandalf guiding you through the labyrinth of insights. From tracking the conversion rates of marketing campaigns to analyzing recruitment pipelines and sales processes, funnel charts provide a clear and intuitive way to identify bottlenecks and optimize performance. In post, I present a practical application of funnel charts in a real-world scenario.

Example Funnel chart and data: Let’s consider a fictional recruiting process. Each of the main five stages of the pipeline are associated with a bar whose length corresponds with the number of candidates that belong in that stage.

Applications: Total number of applications received.
Phone Screen: Candidates who passed the initial phone screening.
Interview: Candidates who passed the technical/formal interview.
Offered: Candidates who received job offers.
Hired: Candidates who accepted the offer and were ultimately hired.

The overall goal of the visualization is to help identify where improvements can be made to increase the number of successful hires for example.

We can create a funnel chart showing the numbers at each stage from top (Applications) to bottom (Hired) in both Excel and Python. However, I prefer to show not just the raw numbers (Candidates column values) but also the percentages of the total in each stage. So, if we started the top process at 100%, I want to see what percentage actually made it to the last process, and at any process in-between.

To do this in Excel version will require a helper column and some creativity as it doesn’t allow adding Combination chart option in Funnel chart types, and cannot show both percentages and labels on the funnel bars. It also does not allow custom values or a column for the bars’ data labels either. Even if we created a formula to combine the Candidates count with percent as “1200 (100%)”, it won’t be able to draw the funnel bars as it becomes non-numeric. The default Funnel chart may look like this:

In order to add the percentage information at each stage, we can either show it on the bars next to the counts (e.g. 1,200 (100%) etc.) or show the Stage names in the bars as data labels and show the counts along Y-axis as its labels. Both versions are shown below:

The choice is yours but either version will require creating two helper columns: Percent, and Combined Labels. The Percent is a simple percentage calculation per stage starting with 100% and the calculating the remaining percentages based off of that first stage. The Combined Labels column will then use a formula to join the raw number with the percentages calculated in Percent column and put that within parentheses for example. In Option 1 chart shown above, the combined text is shown as labels on the bars themselves by using Text Boxes and formulas for each pointing to the Combined Labels column values at each row; whereas in Option 2 chart, the combined text is set as Category Axis labels and the stage names as added as Text Boxes and using formula within each Text Box to point to the Combined Labels column value for the stage. Once they’re done, we can group all the Text Box elements added with the chart to make them act as one. That way, if the chart is moved, all the custom labels move with it in unison, when the dataset changes, all the labels update automatically since they’re using formulas and not hard-coded text inputs. This accomplishes my objective just fine albeit with some additional work.

To achieve the same effect in Python of course requires some coding but requires less manual intervention once the code is all set up. Changing the dataset should not require any change in code. The Python version is shown below.

I prefer the Python version because it draws the outlines and connects the edges of each stage of the funnel to the subsequent ones making it visually even more intuitive. Additionally, when the chart is drawn on a brower, it is interactive allowing you to hover over each stage and it’ll show additional details in a tooltip (the above is a static image for exemplification, therefore hover functionality will not show here). Of course, the font and colors can be customized in code as well. There may be various packages out there for Python Funnel charts but I used plotly. Plotly generates a SVG file optimized for the web and displays it the default browser (from a local server that it sets up automatically) which enables high fidelity resizing of the chart in the browser. The image can be manually from the browser or other tools, or programmatically saved to a local file from within the same Python code.

Let’s look at another example. Here’s a table with estimated percentages of a typical middle-class American’s budget, based on the latest data. These percentages are based on average consumer expenditures and can vary depending on individual circumstances, lifestyle choices, financial goals and location.

The funnel chart for this data looks like as below.

For example, when we total them up we see about 84% of net income is gone to the specified categories leaving about 16% for savings, emergency funds, or other financial goals. The image also shows a tooltip next to the Lodging category with additional details. This chart also shows, as labels for each stage, the percent of a stage compared to the previous stage if we specify the ‘percentPrevious’ property in the texttemplate parameter. For example, texttemplate="%{percentInitial:.1%} (%{percentPrevious:.0%})" shows the 32.9% (33%) on lodging meaning 32.9% of the net income goes to lodging expenses and it is about 33% of the previous stage Net Income.

Regardless of the versions used, we can see that about 4% of the original applicants were hired, or 50 people. The big drop-offs in-between are noticeable from Applications to Phone Screen and again from Interview to Offered. While there was still some gap between Offered and Hired, it’s only 1.25 points compared to Interview to Offered which was at 11.25 points drop. The Applications to Phone Screen gap of 53+ points may be due to quality of applications and/or delay in processing the applications before reach Phone Screen stage requiring more insights such as processing time between the stages. There’s a visualization for that too!

I hope this was useful and interesting. Happy charting!