You’re wanting to look at ROI by product over time against your goal, and you want to do more than show a data table. You want to come up with a way to visualize the data that’s meaningful and easy to understand. Naturally, you start by plotting a line graph of ROI over time with a different colored line for each product. You might even throw in a dotted line representing your ROI goal.

Unfortunately, there’s two major flaws in this plot. The obvious one is the amount of clutter. You can’t really see anything here because all of these lines are overlapping each other. (Imagine how bad it would be if you had to look at 10 products.) The other problem I have with the above plot is that it seems to indicate that each product carries the same weight, which we know this isn’t the case. If one product has a high ROI but relatively little spend, we know it won’t have much of an effect on the overall ROI.

Let’s solve these two problems, starting with the ROI weight. (Transform your data first, then create a plot to match it, not the other way around.) We are going to use a slight variation of centering and scaling your data. Start off centering your data by subtracting the ROI goal from the ROI for each product/date combination. To scale the data, we are going to calculate the percentage of each day’s spend by product and then multiply that by the difference.

(Product Day ROI – Goal ROI) * (Product Day Spend / Total Day Spend)

This tells us how each product affected overall ROI for the given day. Let’s call this metric ROI Goal Impact. We can now recreate the line graph with our new metric.

The scaling has helped us a little here. We can easily see Product 5 is one of the biggest drivers of ROI and that Product 4 has little effect on overall ROI. We’ll see the benefit of centering the data round the goal in a second. This is a good start, but we are still seeing a lot of clutter from the overlapping lines.

Converting our plot from a multiple lines chart to a stacked bar chart might clear the clutter. The trick here is that we have positive and negative components. To account for this, I split the data into two new sets, positive ROI Goal Impacts and negative ROI Goal Impacts. I can then use ggplot2 to plot both datasets as stacked bar charts to get the results below.

We can now easily see which products have the largest/smallest effect on our overall ROI as well as when each product came in above or below goal for each given day.

There’s one really cool added bonus to the chart thanks to the centering we did. If you take the positive bar and the negative bar for a given day and add them together, you actually get the overall ROI difference from goal for the day, which, at the end of the day, is what we are concerned with. Let’s add points representing the overall ROI difference from goal for each day. (You could also add a trend line, but I like points here as it keeps a little more focus on the bars.)

And there we have it! A plot that shows us how each product affects overall ROI, both in magnitude and direction relative to goal, as well as the overall ROI itself in relation to goal. To me, this provides a lot more value than our original line graph. All it took was a simple metric and format change.