Index

Do you always struggle with the type of Excel charts to use in your analytics reports?

The type of excel chart you select for your analysis and reporting depends upon what you are going to analyse and report.

We create charts to display qualitative and quantitative data in a graphical format and to make it easy to understand either one, any or all of the following:

1. Type of data (Qualitative and Quantitative)
2. Relationship among data
3. Comparison of data
4. Composition of data
5. Distribution of data
6. Overlapping of data

Type of data (Qualitative and Quantitative)

Qualitative data is the data that can be classified/categorized but it can’t be measured.  For example: colours, satisfaction, rankings etc.

Quantitative data (also known as interval/ratio data) is the data that can be measured. For example 5 customers, 12 steps, conversion rate , height etc

Types of qualitative data – Nominal data and ordinal data

Nominal data – qualitative data that can’t be put into a meaningful order (i.e. ranked). For example: {Blue, Yellow, Green, Red, Black}

Ordinal data – qualitative data that can be put into a meaningful order (i.e. ranked). For example: {Very Satisfied, Satisfied, Unsatisfied, very unsatisfied} or {Strong dislike, dislike, neutral, like, strong like}

Types of quantitative data – discrete data and Continuous Data

Discrete Data – quantitative data with distinct values / observations. For example: 5 customers, 17 points, 12 steps etc.

Continuous Data – quantitative data with any value / observation within a finite or infinite interval. For example: conversion rate, visits, pageviews, bounce rate, height, weight etc.

How you can summarize Qualitative data?

Through frequency table:

Through Pie Chart:

Through Column chart

Quick announcement about my new book:

Read first few chapters of this book for free here.

How you can’t summarize Qualitative data?

You can’t summarize qualitative data by calculating mean (or average).

For example;

1. Very Satisfied: 40%
2. Satisfied: 30%
3. Unsatisfied: 20%
4. very unsatisfied: 10%

If you calculate the average here than average satisfaction is 25% which doesn’t make any sense.

Look at this calculation this way, when you are calculating the average of a qualitative data, you are actually trying to compute this:

Average satisfaction = (Very Satisfied + Satisfied + Unsatisfied + Very Unsatisfied)/4

Similarly, you can’t summarize qualitative data through line charts and histogram.

How you can summarize Quantitative data?

You can summarize quantitative data through mean, median, mode, standard deviation etc. You can summarize quantitative data through column chart, bar chart, line chart, Histogram etc.

Relationship among data

To understand relationship, you need to understand connection/correlation between two or more data points.

1. A data point/category is a mark on a graph/chart which corresponds to a piece of data.
2. The data that the mark represents is also called a data point.
3. The value associated with a data point is called data value.
4. A variable can store one or more data values which we can represent graphically via a chart.
5. A set of related data points is known as data series.

For example following chart has got 15 data points and two data series:

Here we are trying to understand the relationship between two variables named ‘average order value’ and ‘sales’.

One of the best excel chart to represent this relationship is Scatter Chart.

We can see from the chart that the relationship between the two variables is linear which means as the value of AOV increases there is a corresponding increase in the value of sales.

Consider using Scatter Chart when:

1. Analysing and reporting relationship/correlation between two variables.

2. When you want to show ‘why’. For example: why revenue is correlated with average order value or why conversion rate is correlated with number of transactions.

3. When there are more than 10 data points on the horizontal axis. More the data points the better it is for a scatter chart. Conversely few data points (like 5 or 6 data points) are not good enough for creating a scatter chart.

4. There are two variables that depend on each other.

Comparison of Data

For comparing two or more variables the best charts are column charts, bar charts, line chart and combination chart.

Column Charts

Column charts are one of the most widely used charts and are a greatly used to compare variables.

In column charts categories are plotted along the horizontal axis and values are plotted along the vertical axis.

For example following column chart compares the performance of number of branded and non-branded keywords which generated traffic on the website between July and November.

Here the categories (number of branded keywords, number of non-branded keywords) are plotted on horizontal axis and their values are plotted on the vertical axis.

Following are the examples of some columns charts that you should avoid creating:

Consider using Column charts when:

1. You are comparing two or more variables which have same unit of measurement and are of comparable sizes. So that the values of one variable does not dwarf the values of the other variables.

2. When you want to show ‘how much’. For example how much organic visits have changed over three months. So column charts can be used to show data changes over a period of time.

3. The number of categories to plot is less than 5

4. You want to show maximum and minimum values.

Bar Charts

Bar charts are similar to column charts except that in the bar charts values are plotted horizontally and categories are plotted vertically.

For example following bar chart shows the social media performance of various insurance websites:

As you can see here the categories (website names) are plotted on the vertical axis and values are plotted on the horizontal axis.

Consider using Bar charts when:

1. The axis labels are too long to fit in a column chart

2. The number of categories to plot is between 5 and 8.

3. You are comparing two or more variables which have same unit of measurement and are of comparable sizes.

4. When you want to show ‘how much’.

5. You want to show maximum and minimum values.

Line Charts

Line charts are best to show data trends esp. over a long period of time. For example following line chart shows the performance of organic and PPC traffic between July and March.

Another Example:

Here I am visualizing the link growth/velocity of BBC and CNN website in the last one year through Majestic Back Links History Tool. I have used the line chart because they are too many data points to plot and because I want to show data trend over a long period of time.

Another example:

Line charts are best to show data trends: uptrend, downtrend, short term trend, sideways trend and long term trend.

Consider using Line charts when:

1. You want to show data trends over a long period of time.

2. The number of categories to plot is more than 8.

3. You have too many data points to plot and the column or bar chart clutters the data.

4. When you want to show ‘how much’ has changed over a period of time.

Combination Charts

A combination chart is a combination of two or more charts.

For example combination of column chart with line chart.

Consider using combination chart when you are comparing two or more variables that have different unit of measurement and/or are of different sizes. For example:

Here both of the combination charts are presenting the same data.

The only difference is that,in the first chart I combined the column chart with a line chart. In the second chart I just plotted the ‘bounce rate’ on ‘secondary axes’.

I use combination charts a lot in my report and you must know how to create them as they are very useful.

Following is a short video on creating a combination chart in excel:

Note: By using combination chart you can ensure that the value of one variable does not dwarf the value of other variable(s).

Composition of Data

If you want to show the breakdown of data into its constituents then consider using Pie Chart, Stacked Column Chart or Stacked Area Chart.

Pie Chart

Pie charts are most useful when you have only one data series, less than 5 categories/data points to plot and you want to show composition of data.

For example following pie chart shows the breakdown of my website traffic sources in the last one month:

Here I have got only 4 categories (search traffic, referral traffic, direct traffic and campaigns) to plot. So pie chart is ideal to show the breakdown.

If there were more than 4 categories to plot like 8 or 10 categories then pie chart becomes cluttered and hard to read.

Another example:

Here we have got only two categories (new visitors, returning visitors) to plot. So the use of Pie chart is ideal here to show data composition.

Following are some pie charts that you should avoid creating:

Consider using Pie charts when:

1. You want to show the breakdown of data into its constituents.

2. You have only one data series.

3. You have less than 5 data points to plot.

4. The data points represent the parts of the whole pie.

5. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

Stacked Column Chart

Stacked Column charts are most useful when you have 5 to 8 categories/data points to plot and you want to show composition of data.

For example following stacked column chart shows the breakdown of website traffic in terms of new and returning visits in the last one month:

Consider using Stacked Column charts when:

1. You want to show the breakdown of data into its constituents.
2. You have between 5 to 8 data points to plot.
3. The data points represent the parts of the whole composition.
4. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

Stacked Area Charts

Stacked Area charts are most useful when you have more than 8 categories/data points to plot and you want to show the trend of composition.

For example following stacked area chart shows the breakdown of website traffic:

Consider using Stacked Area charts when:

1. You want to show the trend of composition.
2. You want to emphasize the magnitude of change over time.
3. You have more than 8 data points to plot.
4. The data points represent the parts of the whole composition.
5. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

Distribution of Data

If you want to show distribution of data then consider using column chart, bar chart, scatter chart or Histogram.

We need to determine the distribution of data points (i.e. how narrow or wide spread the distribution is) in order to trust the ‘average’ metrics.

If the distribution is wide spread then the average value is not a true representative of the typical value in a data set and hence we can’t trust the average metrics.

I have explained the impact of data distribution on average metrics in great detail in the post: How to Analyze and Report above AVERAGE

One of the best charts to show distribution of data is Histogram:

Note: You need to install ‘Analysis ToolPack’ in order to create Histogram in Excel.

Overlapping of Data

If you want to show ‘overlapping of data’ then consider using Venn diagrams.

The multi-channel conversion visualizer chart used in Google Analytics to visualize multi-channel attribution is actually a Venn diagram:

We can use Venn diagram to determine whether or not a website has got attribution problem.

If there is little to no overlap between two or more marketing channels then the website doesn’t have attribution issues.

If there is a good amount of overlap then the website has got attribution issues and you should seriously consider taking multi-channel attribution into account while analyzing and interpreting the performance of marketing campaigns.

Another great use of Venn diagrams is in visualizing the back links overlaps between websites:

The tool that I have used to create this Venn diagram is known as Venny.

You can learn more about visualizing the back links overlaps between websites through this post: Ultimate Data Visualization Guide for SEO

Note: You can create a Venn diagram in Excel. Check out this tutorial on Microsoft Office website: Create a Venn diagram

Charts to Avoid for Reporting Purpose

Throughout this post I have talked about the charts that should be used. But there are some charts which should be avoided at all cost and are worth mentioning to you:

The reason you should be avoiding reporting data via these charts to your clients is simple. Majority of people have no idea what you are trying to communicate via these charts.

• The speedometer chart looks so unprofessional, yet many marketers use them in reports.
• The tree map looks like an aerial view of an open field with marked territories. This is the opinion of one of my clients.
• Waterfall chart looks like there has been some misprinting of the column chart. This is yet another feedback from one of my clients.
• Nobody understands Radar chart or Bubble Chart.
• I have yet to find any good use of Bubble chart (aka Motion Charts) in Google Analytics.

Use these charts only when your target audience is as data savvy as you.

// Bonus Content

Anatomy of an Excel Chart

You can get more data reporting tips from this post: How to become Champion in Data Reporting