» Analytics » Multi-Chanel Attribution: discover insights using pivot charts

Multi-Chanel Attribution: discover insights using pivot charts

What is the holy grail of marketing channel analysis? It is getting all possible channels, looking at how they interact to find which ones indirectly support and which ones directly lead to a conversion i.e. a sale, a direct lead, a transaction or an inbound lead etc. We would then take all of this data and find out our precise ROI, tracking everything down to the smallest financial unit. In an ideal world, we would have this data, and it would be invaluable.

But it’s not an ideal world, so what I’m going to is look at one specific channel and then attempt to visualise attribution for ADC (see below) in an Excel pivot chart via customised attribution data. Exciting stuff!

Now, to put it bluntly, it’s damn hard to track all possible channel data. In fact, it’s near impossible, and Avinash Kaushik clearly states why in his brilliant blog post Multi-Channel Attribution: Definitions, Models and a Reality Check (I highly recommend reading this!). He goes into subtle detail and highlights three core areas for attribution modelling:

  • Online to Store (O2S)
  • Across Multiple Screens (AMS)
  • Across Digital Channels (ADC)

I would suggest a fourth area of multi-channel attribution, and that would be Online to Phone. So let’s create this:

  • Online to Phone (O2P)

Take O2S as an example. How do you know that someone has gone from your website to find out the price of a product, only to go directly to your store to buy the product? This is difficult to track. In most cases, and depending on your business type, these three areas all intersect. If you’re purely online, then you’re looking at AMS, ADC and O2P as core online channels. O2P is relatively straightforward, and it is possible to generate a unique phone number depending on how a visitor arrived at the website (via PPC, SEO, or social media).

This aside, last year Google introduced a wonderful feature in Analytics called Multi-Channel Funnels. With this, it is possible to focus on the ADC channel. ADC would include channels such as organic and paid search, direct traffic, referral traffic, email and social interactions – in essence, anything that can be tracked in Google Analytics. We can take all of these channels and see which paths people take, often over several days, before they convert. Is it common for people to receive e-shots and then go to Google to find the website, rather than clicking through from the email? If this is the case, the conversion can ‘attributed’ to the email, not the organic search.

The image below is taken from GA and highlights the ‘anatomy’ of the conversion path.

This can be viewed under the Top Conversion Paths section in Google Analytics. Go to the bottom of the menu, click Conversions > Multi-Channel Funnels > Top Conversion Paths. You should see the paths under the default Basic Channel Grouping Path.

*A quick note: you must be tracking goals or you will not see any data in Multi-Channel Funnels.

Under Top Conversion Paths, Google defaults to its Basic Channel Groupings Path, using generic labels like Organic Search, Paid Search, Email, Direct and Referral. Often, these don’t provide a deep enough level of analysis and we want to break down these sections further. Paid search, for example, could be broken down into ‘brand paid search’ and ‘generic paid search’; emails can be broken down into their relevant categories, providing attribution insights by email campaign.

This blog post by Tim Leighton provides some great advice on how to break down channel groups using the Custom Channel Groupings section. In essence, you can create a custom channel group by clicking on Channel Groupings > Create a Custom Channel Grouping, here:

Getting your path data into Excel and visualising Multi-Channel Attribution

Once you’ve created all your custom channels, you’ll be able to export this data into Excel. It’s important to use both Assisted and Last Interaction data. I’ve removed conversion values for both of these channels. Click Assisted Conversions > Your Custom Channel Groupings. Export this into Excel and you should see something like the following:

You should end up with a nice Excel sheet with the above columns. It’s important to keep the data on Assisted/Last Interaction Conversions because this will tell you which channels are more likely to close a lead and which are more likely to assist a lead.

 

  • Anything between 0 – 1.5 will have a high probability of closing
  • Anything higher than 1.5 will most likely assist a close

 

Visualising this data in an Excel Pivot Chart

Select all the data in your excel sheet, click on Insert > Pivot Table > Pivot Chart. Make sure you organise the fields so that they are like the following:

  • Axis Fields contain
    • Assisted / Last Interaction
    • Digital Channels
  • Values Contain
    • Sum of Assisted Conversions
    • Sum of Last Interaction Conversions
  • Legend Fields
    • Values

 

 

 

 

 

 

 

 

 

Once organised in this way, make sure you select the following chart type:

 

You should end up with a nicely visualised chart of Assisted and Last Interaction conversion data.

 

Click to view a bigger version of the chart

 

This combined data shows you the digital channel, the number of conversions and the probability of each channel as a ‘closer’ or ‘assister’. The beauty of Excel pivot tables is that you can select specific types of digital channel data, so if you want to focus just on search data and Direct, you would select the data to get a chart like so:

 

Click to view a bigger version of the chart

 

If you want to look at everything apart from organic/paid search and Direct traffic, you’ll get the following chart.

Click to view a bigger version of the chart

 

Hopefully, this ‘guide’ has provided one method of presenting Last Interaction and Assist data. Like I said earlier, the point of multi-channel attribution is to consider all means of conversion analysis, but this should serve the purpose of taking you some way to understanding the process of MTA as a whole. If you have any thoughts, let me know.

By Paul Gardner

A search marketer (SEO/PPC AdWords) with in-depth experience of large organisation (including FTSE 100) online marketing. Exceptionally target-driven and organised, I'm a true stickler for measurable data that produces effective actionable insights. By Paul Gardner.

Leave a comment

Your email address will not be published. Required fields are marked *


7 × four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>