Upcoming LunaMetrics Seminars
Washington DC, Sep 22-26 Boston, Oct 6-10 Chicago, Oct 20-24 Seattle, Nov 3-7

Multi-Channel Attribution Modeling – the Tool To Get You Started

Last Thursday, Robbin wrote a post on attribution modeling without Google Analytics Premium. In that post she shows how you can use Excel to generate a first touch, linear, and first/last touch attribution model for your conversions or ecommerce transactions.

This is a topic that a lot of you are interested in, judging by the amount of traffic and shares that post got. So I decided build on Robbin’s post by creating a template that you can all use for your own attribution modeling needs. The Attribution Modeling Tool is a Google Doc spreadsheet that has all the formulas you need built right in. It will do the following types of modeling:

  • First touch
  • Last touch
  • First and last touch
  • Linear
Attribution Models - First Touch, Last Touch, First & Last Touch, Linear

(Click to enlarge)

Get your copy of the Attribution Modeling Tool here. Once it’s open, go to File > Make a copy so you can edit it.

The second sheet (MCF Data) is where you paste in the data that you export from the Top Conversion Paths report in GA (refer back to Robbin’s post if you need more details). Specifically, you’ll need to copy the first three columns of data, starting at row 8 (the first row of actual data below the headings Basic Channel Grouping Path, Conversions, and Conversion Value). Don’t copy the last row of data, which is just the totals.

show more rows in Google Analytics

* TIP: Before you export your Top Conversion Paths report from GA, make sure to show 500 rows, or as many rows as you need to get all of the paths and data. The Attribution Modeling Tool can accommodate up to 500 rows of data.

 

The first sheet (Attribution Models) gives you the numbers and graphs of the value of each channel using each of the above models. The remaining sheets (which are hidden) do all the heavy lifting. There are around 14,000 formulas in this spreadsheet, so it may take a few seconds or longer to fully load or update.

Insights from attribution modeling

Remember, attribution modeling let’s us divvy up the conversion value to various channels that led up to that conversion. For example, look again at the graph above (click to enlarge). This shows us that Direct visits are worth nearly $85,000 with a last touch model, with Organic Search and Paid Search being worth about $26,000. But look at how things change with the first click model. Now Direct, Organic Search and Paid Search are each worth around $44,000.

If you’re only looking at the last touch attribution, you may be tempted to put less money into your search marketing and SEO efforts. You may be foolishly thinking that the people who purchase all come to you directly, so why should you pay? But as you can see above, a lot of people who come to you directly and buy were introduced to your site from a paid or organic search. Otherwise, they may have never come back directly and purchased.

 Your Turn

Go get the Attribution Modeling Tool and play around with it. Do you find it useful or a waste of time? Anything surprise you? Bugs or other unusual quirks (Google Docs are know for those)? Let us know in the comments!

Jim Gianoglio

About Jim Gianoglio

Jim Gianoglio is our Digital Analytics Engineer. He works with implementation, analysis of Google Analytics, and spearheads the LunaMetrics Google Analytics seminars across the country. Want to see him in action? He'll be heading our Google Analytics training in Los Angeles. Before succumbing to the siren song of analytics, he led the SEO campaigns of Fortune 500 companies in the insurance, retail and CPG industries. Things you didn’t know about Jim: he runs marathons, photographs weddings and has done voiceovers for TV commercials.

http://www.lunametrics.com/blog/2012/05/17/multichannel-attribution-modeling-tool/

23 Responses to “Multi-Channel Attribution Modeling – the Tool To Get You Started”

Chris H says:

Great tool! This will help out on many levels. It would be great to ‘make a copy’ but that option is grayed out. Totally understand if you’re trying to keep your trade secrets in-house, but would love a copy of my own :)

Chris H says:

It works now – like you said must’ve been a glitch in the matrix. Thanks again!

Hey Chris -

I’ve had that happen when I go to a shared Google Doc – even when I’m already logged in, it makes me log in again (and you can’t make a copy unless you’re logged in).

But it looks like you got it figured out :)

I don’t get the figures in the attribution model in Google Docs. How can I get the attribution modeling working?

Gerard – Are you copying and pasting your MCF data in the second sheet?

Are you able to make edits to the doc? The link in the article takes you to a read-only version – you have to go to File > Make a copy before you are able to edit (and add your data in).

If you are unable to go to File > Make a copy (if it’s grayed out) it’s because you need to sign in. This happens sometimes even if you’re already signed in to a Google account, for some reason Google Docs doesn’t recognize that and you have to sign in again. Then you’ll be able to make a copy and add your data.

Does that help?

It is not working with me, Jim. I have made a copy of the Google Docs file and I am logged in with my gmail account. I’ll get the alert, ‘data columns cannot be of type string.

What am i doing wrong?

Gerard -

Thanks for the heads up! It seems like Google docs are always prone to quirks and bugs like this.

I can’t seem to reproduce the error you’re seeing. When are you getting the alert, and where is it showing up? On the charts?

I did find this thread that’s about a year old that talks about a similar error message:
http://productforums.google.com/forum/#!topic/docs/1JcX7vXnMFs

But I’m not sure why you would be getting that.

I’ll let you know if I find anything more – thanks again for pointing it out.

Hi Jim, thank you for sharing this great idea.

However, the MCF reports can accommodate more than 500 rows (this works for every report in GA).

You have to choose more rows to show (e.g. 500) and then look for “table.rowCount%3D500″ in the URL of that report. Then you change 500 in the URL for how many rows you want to show. Ta da. :-)

Jim: I’ll get the alert by the charts/graphs.

Further: When I’ll get data on the charts, only from direct traffic.

Should I translate the basic channel groupings from dutch to english to get it working?

Thanks for this report. Worked in go.
One addition to this report is to determnine the most valuable channels by the value per conversion (instead of looking at absolute conversion values).
Use segments to detrmine the total conversions for example “first interaction”, “last interaction” in google analytics and write down the conversion value per traffic source. Divide the two and you’ll see per channel (organic. refferal, direct) etc. your most valuable channel.

I’ll get my data from your Google Docs document. Thank You very much.

Can you use this only for basic channel grouping of also for custom channel groupings?

@Vojtech – Thanks for pointing that out. I decided to limit the Google doc to 500 rows just because the number and complexity of the formulas really slows things down. Even at 500 rows, it can get a little fickle. Of course, anyone can add more rows and copy the formulas down if they need to, but for most people, 500 rows should be more than enough.

@Gerard – yes, the language difference is causing it break. The best thing to do is edit the formulas in the FirstTouch, FirstLast, and Linear sheets to match the Dutch words for each channel (direct, referral, organic search etc.). Although this is more work up front, it’s easier in the long run – you won’t have to translate every time you use it. You’ll need to unhide the three sheets I mentioned. The cells that you’ll need to edit are:

FirstTouch sheet – the cells that contain the channel names (B3, B5, B7 … B19)

FirstLast sheet – all cells in Row 4, you’ll need to edit the formulas to use the dutch word for each channel. For example, in cell A4, the formula is
=if(ISBLANK(‘MCF Data’!$C2), IFERROR(1/0),(if(countif(‘MCF Data’!$D2, “*direct*”)=1, (‘MCF Data’!$C2)*.5, 0)))
Replace the “*direct*” with “*[insert dutch word for direct]*”
Once you’ve made the replacements, copy those cells down to the bottom of the sheet (to row 502).

Linear sheet – all cells in Row 3 (same as above)

@Ernst – that’s a great suggestion – thanks for the comment! I’ll see if I can add that in to this tool.

Jason says:

Great resource here. FYI the links/thumbnails after the post under “You might also like” are pointing to spammy sites.

Thanks Jason!

We’re working hard to clean up the spam links. Hackers are up to no good again :(

Sam says:

Awesome tool! Just saved me a whole bunch of time as I was going to make one in Excel.

Thanks Sam – I’m glad this helps!

Also, stay tuned for an updated tool now that Google Analytics has released a reporting API for Mulit-Chanel funnel data. This wasn’t available previously, hence the unfortunate need to download the report, copy and paste. Soon it should be possible to just open the spreadsheet and have the data in there automatically!

Jon says:

Great tool Jim – how is the version using the Multi-channel API coming along?

cheers
Jon

George says:

I copied all the data to MCF sheet. All top conversion path data is in A,B, and C column, but it doesn’t calculate. What should be the problem?

tony says:

Excellent tool Jim! Many thanks.
But why does the instruction on the first sheet ask to “copy and paste the data into cell C2 in the next sheet.”? I actually pasted my data into cell A2 and it was all fine.
Cheers

Thanks for the heads up Tony! I edited the instructions on the first sheet. You are correct, you should paste the data into cell A2.

Rajesh says:

Once I copy the data to MCF sheet, how do I need to execute it? There is not such option available. Pls help.

@Rajesh – the only thing you need to do is copy and paste your data into the second tab of the spreadsheet. The charts on the first tab automatically update based on that data.