How Visualising Data Avoids Costly Mistakes

Posted on 07/07/2016 by Jonathan Ellins

Unless you're very attentive, looking at uninteresting tables of data can make you miss out obvious issues within marketing reports. But visualising data can kickstart your brain's natural instincts to identify differences.

In this post I’ll share some of the best practices for presenting your data for quick analysis.

Conditional Formatting and Rules

Most marketing data is two-dimensional, so it’s ideal for use in spreadsheet software such as Microsoft Excel, OpenOffice or Google Sheets.

Looking at data by itself can be a little overwhelming:

Campaign data with no data highlighted
Campaign data with no data highlighted

The data highlighter automatically colour codes the data in any way you wish to set it. The most useful way I find is to have a traffic light system that shows positive results in green and negative results in red based on the averages:

Campaign data with a traffic light system (green is positive, red is negative, yellow in-between)

With the data now highlighted, it’s very easy to spot the good and bad performers at a glance. “Shopping Campaign #2” for example is doing very well in terms of bounce rate, conversion rate and revenue. Meanwhile, “Display Campaign #2” is very much the opposite, as it’s performing badly in all three areas.

To enable data highlighting, it’s usually a case of selecting a column or row and finding the conditional formatting options:

Rules can also be set to create a more accurate version of data highlighting. For example, you may wish to highlight all positive and negative figures differently, to highlight data containing certain keywords, or any data which has reached defined values.

You can find rules within the same area as conditional formatting. You will first need to define a range – highlighting all the cells is the quickest way to do this – before applying a rule to clearly change the font formatting when your conditions apply:

Google Sheets example of rule conditional formatting
Google Sheets example of rule conditional formatting with a red highlight on cells which are zero or less

Missing out an important statistics within tabular data can mean that a greatly performing area could be stiffened, or even worse, a poor performing are could be ignored and cause a lot of wastage.

Visualising Data on Graphs

Looking at data within tables means that every figure shown is either a sum or average of figures over a selected time frame. This can hide more granular issues or smooth out the statistics that hide underlying issues.

Let’s say we were looking at data on a weekly basis. All the statistics from Monday to Sunday will be combined in to each week’s total statistics. So any differences between the individual days will be lost. If we only looked at the data on a week-by-week basis then it can be harmful to skip out the best and worst days of the week whilst planning marketing campaigns:

Weekly paid advertising statistics on AdWords in graph and tabular format (click highlighted icon to reveal the graph)

The example above shows both the graph and tabular statistics for a paid search campaign. Both clicks and costs are visualised daily on the graph, revealing that the ads aren’t showing on weekends. This fact is totally hidden in the tabular data below it.

One of the most important examples of this is within paid advertising where conversion data may be collected along with cost data. Unless paid advertising platforms are setup correctly on day one, then at some point in history data wasn’t collected on conversions but was collected on costs:

Revenue stats for this account were only collected after August 2013 for cost stats go back beyond June 2011
Revenue data for this live account was only collected after August 2013 (in green) but cost stats go back beyond June 2011. Using historic data before August 2013 will skew cost vs. revenue reports

When there’s a lot of data to take in, graphs and pie charts can be used to quickly see what’s going on. For example, you could generate a useful graph through simply looking at traffic levels and conversion rates over different hours of the day to identify trends:

Visitors and conversion rate levels at different hours of the day
Visitor and conversion rate levels at different hours of the day

Visualising Data as a Comparison

It can be useful to look at the best/worst performers, or to review overall trends. But the most striking way to visualise data is to compare data in terms of percentage differences:

Google Analytics has pie charts, graphs and comparison chart views
Google Analytics has pie charts, graphs and comparison charts within it’s reports (highlighted)

Pie charts quickly show the division of a whole entity. Two useful pie charts to create for most websites is to to show different channel data for sessions vs. conversions. This quickly shows which channels bring in the traffic, and more importantly, which channels contribute most to the overall success of the website:

Traffic vs. Goal Completions report
Traffic vs. Goal Completions reports from Google Analytics

The pie charts above clearly show that there’s a lot of organic traffic that doesn’t convert as well as other channels, contributing less to the overall goal completions. They also show that referral traffic is valuable, as there are twice as many goal completions compared to traffic in terms of percentages.

The most powerful way to convey differences is to visualise the differences of each metric as a percentage difference away from the average.

The chart below shows the goal conversion rate of an ecommerce website which takes many orders over the phone:

Goal Conversion Rate comparison on different days of the week on Google Analytics
Goal Conversion Rate comparison on different days of the week on Google Analytics

Looking at the data above, paid advertising could be increased between Sunday, Tuesday and Wednesday to capitalise on the average customer who is more likely to make a purchase. Meanwhile, the opposite could happen on Thursday, Friday and Saturday to save wasted costs.

Also in the example above, special offers could be launched on the lower converting days to make up for the shortfall. This would increase the likelihood of a sale during these worse periods, similar to how restaurants have mid-week deals or how bars have happy hours at quiet times.

Looking at cost and revenue relationships from paid advertising can quickly show which campaigns, product groups, ad groups, keywords, etc. are doing better than others, and whether they are performing above or below average:

Calculating return on ad spend differences as a percentage using a spreadsheet and conditional formatting
Calculating return on ad spend differences as a percentage using a spreadsheet and conditional formatting

Any ecommerce business owner can now easily see that campaigns such as the “Local Campaign” and “Facebook Campaign” are underperforming in terms of return-on-ad-spend, and might be spending more in advertising costs than generating in actual revenue depending on overheads.


It’s human nature to spot patterns, oddities or trends within imagery. So by visualising all of your data you can quickly draw important conclusions and never miss out any vital data which could be sat right in front of you.

Which ever set of data you look I recommend checking:

  • The high performers and low performers
  • Historical data trends over different time periods
  • Comparisons between different dates, campaigns, channels, landing pages, etc.

If you need help with your don't hesitate to contact us.

Enjoy this article?

Subscribe for weekly insights

How Visualising Data Avoids Costly Mistakes

Let’s thrive together

Get in touch to take the first step.

Contact us