I hope you'll suffer a question from a novice :)

I'm trying to find the best way to graph (in Excel) the month-by-month share of site-wide visits and pageviews in five referral sources, grouping the remaining share as one: "other sources."

My thought was to do a clustered, stacked column chart, where each month has a cluster of two columns—visits and pageviews. Each column in a cluster would be stacked by source. The visits column would use one y-axis, and pageviews would use a secondary y-axis—pageviews have much higher values than visits. Please forgive me if any of this terminology is wrong.

Am I on the right track or is there a better way to visualize this data? If this is the way to go, any direction on how to arrange my data to coax Excel into getting all these parameters into one chart? Or does that question belong on a different StackExchange site?


  • $\begingroup$ Welcome to the site. :) How many months of data do you have? $\endgroup$ – Michelle Mar 10 '12 at 7:51

What are you trying to communicate with the data? Or, what are you trying to understand?

These should drive your visualisations.

Without any better to go on - here's what I would recommend. Borrowing heavily from Tufte - I'd recommend you read his books as they are short and high value.

  1. Split visits and pageviews onto separate charts as the scales are not meaningfully correlated. I mean to say - 1000 visits and 3000 page views just means your visits will look low, but that doesn't mean anything on its own. (Unless you are trying to show # of page views per visitor - even then there's a better way to show it).
  2. If you are showing multiple trends (eg trend for each referral source over time), use a line chart instead of stacked bar/column chart. It's easier to see both the trend for each series and the comparison between each of the series' at any point in time. If, however, you are trying to show the difference in performance between a given referral source and others, you should try more specifically to show the difference.

You can show lots of things with charts.

Separated charts are surprisingly effective as compared to overlaying the data on a single chart.

  • $\begingroup$ Jason, thanks for the quick response. Splitting the data into two charts revealed exactly what I'm trying to show: the % share of referral source A is dropping month-over-month while the % share of referral source B is rising, in both visits and pageviews. After splitting up into two charts, the trend of each source seems to be effectively communicated in the stacked bar chart, but I'll also try a line chart for comparison. And I'll also have to swing by the library this afternoon :) $\endgroup$ – moon prism power Mar 10 '12 at 17:11

Stacked column charts make it difficult to compare between columns, except for the first (nearest to the X axis) layer. When you add clusters of stacked charts you make it hopelessly difficult to find any patterns.

You should make two charts, one for visits and one for page views. These are different quantities and should be analyzed separately, especially since they have different magnitudes. You don't get any benefits for lumping them together.

For each chart I'd draw five lines, one for each referral source. That way you can make all kinds of interesting analyses, mainly:

  • comparing between referral sources;
  • and seeing how each source evolves over time.

Hope that helps.

  • $\begingroup$ Carlos, thanks for the response. Clusters indeed made it difficult to see patterns. $\endgroup$ – moon prism power Mar 10 '12 at 19:54

You should also check another great technique from Edward Tufte: Small Multiple

“Illustrations of postage-stamp size are indexed by category or a label, sequenced over time like the frames of a movie, or ordered by a quantitative variable not used in the single image itself.”

Although you have only 5 sources which can still be fitted into a single chart and still be distinguished, I think that you should already start looking on splitting the sources into different charts with this technique.

It is also fairy easy to do with excel.

Create line chart (over time) for each of the sources and each of the metrics and arrange then in a 5*2 table.

  • $\begingroup$ I would disagree that it is easy to do in Excel (it is very easy to make the graphs not-perfectly aligned, and I see this happen very frequently). But you are right in that sometimes 5 lines is too many, I cite an example in a blog post, and the Bella consults blog has several examples as well. $\endgroup$ – Andy W Mar 11 '12 at 14:42
  • $\begingroup$ Thanks, Guy. In the first section of my report, each source is examined separately as you describe. My second section explores each source as a share of the whole, where I used two stacked charts: one for visits, one for pageviews. These stacked charts seem to show more clearly than the separate line charts that two sources almost mirror each other in growth and decline over time. Andy, those little sparkline charts in the Bella consults blog are sexy. Definitely want to find a way to implement something like that in my report. $\endgroup$ – moon prism power Mar 12 '12 at 2:30
  • $\begingroup$ @rkarbowski, I give some resources for sparklines in my tables post on the blog. In particular for excel, this free ad on works very well, sparklines-excel.blogspot.com. $\endgroup$ – Andy W Mar 12 '12 at 12:25
  • $\begingroup$ @Andy, thanks! Great stuff; if only I had the privilege to up-vote comments :). $\endgroup$ – moon prism power Mar 12 '12 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.