Posts Tagged: Excel Combination Charts

Feb 10

Segmenting Data with a KPI Overlay

This week in the Web Analytics Master Certification course, we are still talking about Segmentation, but we are looking to drill down further with the data to provide more meaning, and get to actionable insights.

One way to provide a huge boost of meaning to a chart is to combine two sets of data to see if there is a relationship. With Excel 2007, it is fairly easy to combine two sets of data and make a nice little chart. If you have never done it before, Mathew McDonald, the author of Excel 2007: The Missing Manual has a video that describes how to do it here.  Combining data sources can really bring data to life, which is good good good.

For part of my assignment this week, I decided that the most important KPI for my subject website (in addition to overall number of conversions) is: Profit Margin / Sale.  Meaning, it is great if we made a ton of sales, but how much are we actually earning from those sales?

At a first glance, it is important to know where our visitors are coming from.  If they are coming from organic search, links from forums, links from social media, youtube, etc., of course it is cheaper than if they are coming from PPC (Google Adwords in this case).   Perhaps there is a relationship with our Profit Margin / Sale and our traffic sources.  Let’s take a look.

This chart displays the number of visitors from each source and compares to profit margin/sale.

KPI Profit Margin per Sale with Visitor Sources

Looking at the chart, there appears to be a relationship of some sort.  Overall traffic is heading up with Organic Search leading the way, and profit margin is heading slightly up (in general) over time.  There are some dips that don’t seem to make sense though…

We can go deeper with this by picking a few additional segments to look at.

In this case I chose:

  • Number of Conversions that were for “High Margin” products (those that are >65% margin).
  • Number of Conversions that included two orders in the same session.  (thereby reducing shipping costs — in this case the retailer is offering free shipping, so he can usually have a better margin if he can ship more than one item in a single box, unless that item is a fully assembled motorcycle or something).

This chart shows “high margin conversions” and “multiple order conversions” overlaid with profit margin.

profit by visitor type

Looking closely here, it seems the relationship for profit margin to conversion type is much stronger.  Of course we can throw the data into excel or minitab to evaluate the “correlation coefficient”, but first glance, the relationship seems evident by the chart.

Some may argue to do the statistical correlation first, to which I wouldn’t disagree… but the point here is that you can get some pretty powerful information with just some raw data and excel.  HiPPOs (as Avinash likes to call the Highest Paid Person’s Opinion)  care about statistics, but they don’t like to see them.  They would rather see a chart that tells a story.

Please feel free to share your thoughts on this and any examples of using data overlays in your analytics.  Would be great to see some other examples.