Create a conversion report from your favorite analytics tool
First off you need to pull out a conversion report from your analytics tool, and add a couple of new dimensions and segments. For this example we use an atypical eCommerce conversion - transactions and well earned cold hard cash in the form of revenue.
The first thing we want to do is to filter the results based on three different things. In Google Analytics this is done through “segments”.
All users (not sessions) that have completed a transaction at least once and where one of the traffic sources the user visited us from (if more than one) was Google Organic.
Important; even though you are now seeing more sources of traffic than Google Organic, this is entirely normal. Since we are looking to find which users that had Google Organic as part of their customer journey, there will be other channels that have impacted their journeys as well.
You should now be left with a number of transactions and some revenue from the journeys that included at least one visit from an organic search result.
Number of transactions: 122
Revenue: $ 1 496
Note: The above process only fits into a more data-driven attribution model, where you want to see the contribution of Organic Search no matter if it was, first-click, last-click or anywhere in between. Make sure that you choose the right attribution model for the job, and as we are geeks when it comes to data driven attribution models that would be the absolute best. Unsure which rules-based attribution model you should choose? Check out our guide to attribution modeling.
If you instead use a rules-based attribution model just note down how much revenue Google Organic is attributed with.
Split the report to show organic conversions per day, with a secondary split on “Landing Page”. You will now see how much organic traffic has generated in transactions and revenue per landing page.
Import these numbers into a spreadsheet.
Protip: Instead of trying to pull out these numbers by yourself, try the extension from Supermetrics.com in your favorite spreadsheet-tool. It is simply awesome. If you´re a Fjord user already you can use our export-function, but on the other hand, if you´re a user - why would you? The app does all of the steps below for you.
Export clicks on “Page” level from Google Search Console
This report should contain the columns “Date”, “Page” and “Clicks” . You are now left with what pages generated traffic, and on which date. An easy, short step - and we will leave it at that. Make sure that the date format between both reports are the same.
Connecting the reports
To make things go a little faster we suggest that you remove all rows that do not contain at least one conversion from the Google Analytics-report.
Do a vertical lookup-function between the “Page”-column in your Google Search Console report and the “Landing Page”-column in your Google Analytics report. You only need to find out if the Google Analytics “Landing Pages” are found within your Google Search Console “Pages”.
Just to be sure - if you have any converting “Pages” from the Google Analytics-report left that did not find a match in the Google Search Console one, you can do a little manual digging to connect the last few dots. GSC shows which URL was clicked on in the search result, but Google Analytics will report on the final URL - meaning if there are some URLs that are redirecting to another location there could be a discrepancy. If you know that your site has lots of redirects going on, we would suggest tagging the URLs in the report accordingly.
But; if all goes well you are now left with a list that tells you all URLs that were landed on before the conversion took place, and how many clicks there were on that URL in total on any given day.
The tricky part - adding keywords to your pages
This is the part we are most happy about having automated. You now need to query Search Console on each of these pages to find out which keywords actually generated traffic on any given day.
This can be a time consuming process, but the end result could be worth it. You now want to add one keyword per row together with what landing page this keyword is associated with. Repeat the lookup-process from earlier to tie all of this together. In addition we would like to suggest an optional categorizing of your keywords and terms to make the end result much better. One example of a categorization would be “Branded Search Terms” vs. “Generic Search Terms”. We are covering the whys and hows of this soon, in addition to some hot tips.
Add categories and weights to your keywords
All of us would probably agree that a branded search term carries a more clear buyer's intent than more general terms. “Orange soda” vs. “Famous brands Orange soda” probably should weigh differently. The same story applies to “Orange soda” vs. “Buy orange soda”, where the statistical probability of which term contributed more to the results should weigh heavier towards the latter.
First you would like to start by categorizing all search terms that are branded. Just filter away all keywords and search terms that do not contain your brand-name ( and variations of it) and you should be left with a list that is only branded search terms. In the category column fill out “Branded Search Terms” or the like.
Secondly we want to find keywords that contain words that could point towards high buying intent - “buy” , “order” , “delivery” etc. Now, what could probably happen is that some of the keywords under the “Branded Search Terms” category are visible here as well, and this is actually a good thing.
A search term that contains both high buyer's intent and a strong brand preference could weigh even heavier than “branded” terms alone. You could mark these rows with “Branded High Intent Search Terms” instead. What you are left with should now be terms that do not contain brand, but only contain words of high intent within the search term. Mark these as “High Intent Search Terms”. Next up you choose everything that does not already have a category, tag these as “Generic Search Terms”.
Keep in mind that you don't have to follow our categories - you can tag keywords to your heart's content, but we usually find it easier to use a few strong categories, over many weaker ones.
Now to the real reason we are doing this; when it is time to attribute what a landing page contributed back to a keyword, we really should use different values on different categories.
Now, you could do a small guesstimation here and look at the conversion rates on organic traffic to more “brand heavy” pages such as the home page, and compare it to a huge set of other pages where branded terms are not as prevalent. If the conversion rate on the latter is half of the “brand heavy” one, it means that the “Generic Search Terms” category should only weigh half.
The best way to do this , if you have access to it, is to compare it to Paid Search. We would pull out a report of all keywords that were active around the same dates, clean them up and do a lookup between the organic terms and paid ones and copy the conversion rates from the paid search-terms over to our organic ones. One thing you will probably experience is that this only works for a very small selection of your terms, and you will have many keywords without any data in this column. Our suggestion is to do the same categorizing of keywords in the Paid-Search sheet (as you´ve already done in the organic one) and now look at averages within these categories.
Example
What does this tell us? It tells us that every time a High Intent Branded Search Term pops up on a landing page, it is probably around 6 times more likely to convert compared to the Generic Search Terms, and 50% more likely to convert when compared to Branded Search Terms.
Now why in the world do you need to know this, and what could you do with this information? Earlier we mentioned that in order to give the statistical probability some more umpfh we needed some weights. What we have just created is these weights.
Now, onto the actual attribution.
CoolStore Inc. sold one case of “Orange Soda” the 28th of February this year through Google Organic.
The Analytics / on-page data tells us that on the day of the sale, the person that bought it landed right on the product page. The Google Search Console data tells us that on the day of the sale there were no more than five clicks to this product page.
The search terms used, and their amount of clicks was.
“Orange Soda” - 2 clicks
“Famous Brand Orange Soda” - 1 click
“Best price on Orange Soda” - 1 click
“Best price on Famous Brands Orange Soda” - 1 click
If we had to bet money on which of these 4 search terms closed the sale right now it would be harder for us to know exactly which of these terms we should use. There are still too many unknowns, and 40% of the clicks came out of one the terms, the more generic one.
If we use our ratios / weights on the above data, this happens.
The following formula could look scary, but it really is not. First - add all of the ratios on top of one another. Remember that even though the ratio on the first category is 1:1 for every singular click, there still was two clicks on this choice, bringing it up to the same probability as the the 2:1 ratio category, that only had one click.
2+2+4+6= 14
Secondly, divide your 1 conversion by the number you got -
1.00 / 14 = 0.071 or 7.1%
Then we need to multiply the available ratios with the number above
2 * 7.1% = 14.2%
2 * 7.1% = 14.2%
4 * 7.7% = 28.4%
6 * 7.7% = 42.6%
This will bring the number up to close to 100%, but not entirely due to the rounding made in this article. When you do it yourself, keep the decimals.
And here we have our probabilities for the actual contribution from the keywords!
So right now, if this was a betting game, you would have 300% bigger chance of winning the grand prize if you placed your price on the “High Intent Branded Search Terms” category when compared to the first two ones - even though the first search term had twice the amount of clicks.
But; this is not a betting game. Even though the possibility of it exists, we just do not know for sure. That is why we would now hand out what we call “Attribution Points” to the keywords. If you want to do this with revenue instead of one singular conversion it is entirely possible.
So - now it is finally time to attribute the sale of the Orange Soda to the keywords - for the sake of the article we also added attributed revenue beside it - let's say 113$ worth of revenue.
Now; when you multiply this times ten, or times a hundred, or maybe even times a thousand, or more, the statistical probability that your SEO team will make the right choice when it comes to which content to create or which pages to optimize increases a lot.
Would we say that this is the absolute best way of doing it?
No, we would still say that letting a data-driven attribution model do this is the best, since it will also take into account both the aspect of time, number of interactions before- or after the organic click, where in the customer journey the click happened and what other contributing channels were in the mix at the time would give the most correct answer available when it comes to organic search keyword attribution. Not to mention the time that your team might use to actually set up this analysis once a month or maybe more.
