Multi‑Channel Attribution Modeling ‑ The Tool To Get You Started

Tags:

May 17, 2012

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

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!