The Waterfall Chart

I recently took part in a challenge organized by Storytelling with Data to create a waterfall chart.

I rarely see this type of chart in digital marketing dashboards, if ever! The challenge wasn’t limited to data related to digital marketing but I wanted to find a use case for a waterfall chart in a digital marketing environment.

The Waterfall Chart

What is a waterfall chart

Waterfall charts are used to show how an initial value has increased or decreased by a number of values in between that lead to a final value. These intermediate values can either be time based or category based.

My challenge was not only to create such a chart for a digital marketing report but to also find real data.

A couple of years ago I started working with a client on their blog and content strategy. Since we started publishing content that is relevant but also targeting high volume search terms, their visibility improved significantly.

A Waterfall Chart To Visualize Online Visibility Improvements

This type of data is a great candidate for a waterfall chart that will show how the number of keywords ranking on the first page of Google has increased over a certain period of time.

Such data can also be visualized with a bar chart or even a line chart.

The use of a waterfall chart however will emphasize on the intermediate values or in this case the number of new keywords in high positions for the intermediate time period (each month in 2017).

With the inclusion of a takeaway title this visual turned into a great data storytelling example.

Here are a few other use cases for a waterfall chart to be used in digital marketing dashboards

  • Newsletter subscribers at the start and end of year with their intermediate positive or negative numbers
  • Number of external links at the start of a link building campaign and at its end
  • Number of acquired leads for the duration of a paid media campaign

You can create a waterfall chart in Excel 2016 and other data visualization tools like Tableau. I used Excel 2013 and this tutorial.

The Pie Chart Dilemma

A pie chart is used extensively in presentations and reports. I use pie charts very often in my monthly performance dashboards. Or to be fair I used to.

The Naughty Pie Chart

In some of my recent videos I have used pie and doughnut charts without putting much thought.

If you watch my eCommerce Dashboard video you would agree it is hard to understand the pie chart I used for eCommerce conversion rate by country. The main reason being that the data contains many categories (countries) (slices) and a pie chart is totally unsuitable for the visualization of this type of data.

In fact it’s ugly!

So lately I have been very cautious about how I use pie charts. Or even doughnut charts!

Against the Pie Chart

Regardless of their popularity I have heard many data visualization experts to speak against pie charts and recommend using bar charts instead. The argument is that pie charts are hard to read.

When pie chart slices are close in values it is hard to determine which one is bigger. If the values are not close in size you can only determine which one is bigger but not by how much.

With bar or column charts however the audience will compare the end points. And because the values (size) of the bars or columns start at a common baseline it is easier to understand their relative values.

Also as discussed above pie charts are not suitable when you are comparing the values of more than a couple of categories.

In the visual below the pie chart is used to visualize traffic by marketing channel to a certain website. In this example we have seven categories included in the chart, each represented by a slice in a different color.

pie chart

Looking at this chart I have to agree it is hard to analyze each channel’s performance as compared to the other channels.

Below is the same data visualized with a bar chart. And it does look much better! It tells a good story that is very easy to understand.

pie chart vs bar chart

In Defense of Pie Charts

But pie charts do serve their purpose!

In the example above we are looking at seven different categories. But if we are visualizing only two parts of a whole a pie chart doesn’t look this bad. In fact I think I prefer it over a chart with only two bars.

Here is an example of a data visual for new vs returning visitors to a website.

pie chart two values

vs:

 

pie chart vs bar chart two values 1

Another big reason I have stuck with pie charts for a long time is that they are designed to compare parts of a whole. Or in other words they are the visual that will show percentage data labels.

All of the data visualization tools I have worked with only pie charts will calculate the percentage of each part to a whole. Including Excel.

Unless your numbers are already calculated and your tables include percentage values you will not be able to assign a percentage data label to a bar chart. (correct me if I am wrong here!).

Final Thoughts

Your lust for pretty data might lead you away from pie charts. And that’s understandable. But they can often be more effective if you are comparing two categories (two slices) within a total.

Excel Bubble Chart to Visualize and Measure Website Conversions

How to Use Excel Bubble Charts

Excel bubble charts are my favorite way to present data in Excel, yet I rarely see them in use in reports or dashboards. A typical scenario to choose a bubble chart is in a set of data with three values for a category. With bubble charts you have the ability to add another dimension of data in the size of the bubble. Another thing to have in mind when choosing a bubble chart is that these graphs use only values, both axes represent a value, unlike other charts which use the category for one of the axes. Therefore in a bubble chart the category can be added as a data label.

Visualize Website Conversions with an Excel Bubble Chart

Excel Bubble Chart

In this video I am using an eCommerce example to show you how you can represent your conversion data in an Excel bubble chart. In this example scenario I have used random numbers for several eCommerce categories on a beauty website that sells makeup and skincare. The table shows the number of sessions, the number of sales and actual revenue for each category of products. *Note, to have this type of data you’d need Google Analytics content grouping set up, it will make it possible to break all these metrics by all of the categories on the website. Here are some instructions on how to set up content groupings.* The bubble chart will present the number of sales on the vertical axis, the number of sessions on the horizontal axis, the size of the bubble is the actual revenue and the color represents the category. I have also used data labels to mark the revenue and the name of the category.

Steps to Create an Excel Bubble Chart

  • Start by selecting the three right columns (since bubble charts do not use categories for the axes, you only need to select the values in your table)
  • Then insert a bubble chart
  • Change the chart name to reflect the data you are reporting
  • To make the graph look cleaner delete the gridlines
  • Then edit the axes to start from 0
  • Then add data labels to include the actual revenue number and the category name
  • To add data label for the revenue data choose data label to be the size of the bubble
  • Then to add the category name select value from cells and select the column with the category data
  • Aligh the data labels above the bubble
  • Click on bubble and select “Vary by color” from the “Fill” options to add a different color for each category
  • Then add axes titles and align horizontally
  • Rename the axes to reflect the name of the metric (sales and sessions)
  • Finally adjust chart position to fit it within your dashboard

How to Analyze the Data

Ideally you’d want all bubbles to be as big as possible and at the most far top right corner of your chart. When looking at the data find opportunities in categories where you receive a lot of traffic, high number of sessions, but not that many conversions. Or there might be a category that is reporting good revenue and low number of sessions. This section of your website might have a big potential to increase traffic and further increase your revenue.

Have fun creating your excel bubble chart. You might find it useful for other sets of data you are reporting on.