In this post I’ll share some of the best practices for presenting your data for quick analysis.
Conditional Formatting and Rules
Looking at data by itself can be a little overwhelming:
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:
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:
- Excel – “Home” Tab -> Conditional Formatting
- Open Office Calc – Format -> Conditional Formatting
- Google Sheets – Format -> Conditional Formatting -> Colour Scale
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:
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:
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:
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:
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:
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:
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:
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:
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.
— Jonathan Ellins (@Jonathan_Ellins) July 7, 2016