A large part of SEO is all about link building, you don’t need me to tell you that. What is important though is not just the changes in rankings which are happening due to the link building but also the amount of referral traffic which is coming through from this work.
If your links aren’t generating traffic then I would begin to question the long term success those types of links are having on your website, rankings and brand.
Below outlines a quick and simple step by step process to quickly assess how effective your link building techniques have been in generating traffic to your website.
Step 1: Download all referral traffic from Google Analytics
Go to Google Analytics –> Traffic Sources –> Sources –> Referrals
Then view the maximum amount of rows, 500 by using the filter at the bottom of the screen.
Then export all of this data into a CSV file which can easily be opened later in Excel. Click on Export –> CSV at the top of the screen as shown in the screenshot below
Step 2 – Combine Links Data with Rererral traffic
The next step is to open up the file you have just download and re-save this as a normal Excel file as we will be adding in some more tabs, data and look-ups which standard CSV files aren’t designed to handle.
Once you have done this do the following;
- Rename the first tab to ‘Google Analytics Referral Data’
- Create a second tab called ‘Links Built’
Below is a screenshot of some of the links which I have built to my site by shamelessly self promoting my content;
The links in the above screenshot are just a small sample I have scraped together for this blog post, I don’t actually keep track of this for my own blog – I have got much better things to do with my life For people building links in competitive industries and on larger websites then you will likely have lists which go into the hundreds or thousands, so this method can really save some time for you.
So now you have all of the link and referral data within one Excel file which you can then do some cool Excel magic on.
Step 3 – Do Cool Excel Magic
Now you want to find out how many visitors the link building has resulted in. As mentioned previously, if you are just building links for the SEO value and not for traffic is this really going to be a good long term SEO strategy…?
The next step here is to add an extra column next to the list of referral traffic to see if this referral traffic was from the effort you put into link building;
If you want to copy and paste the formula then it is;
=IF(COUNTIF(‘Links Built’!$A$2:$A$100,CONCATENATE(“*”, A8, “*”))>0,”Yes”,”No”)
The formula may look a little scary but all it is essentially saying is: “See if this domain in A8 is contained within the list of links which I have built”. For a full guide on what this means take a look through the blog post explaining this in detail, How to VLOOKUP Using Partial Match
The different parts of the formula are saying;
- Count the range of cells, A2:A100 if….
- The cell contains a partial match on cell A8
- If there is a match, then put “Yes” in the cell
- If not, then put “No” in the cell
Now simply drag this formula down through to all of your referral traffic to see if this was from a link which was built or not.
Note, this check is only looking at the domain name and not page specific. So if you built a link on www.example.com/page1.html which drove 0 visits and you got an organic link on www.example.com/page2.html which drove 100 visits then all of this traffic would be attributed towards the link building work which you have done – which isn’t correct in this example.
By default Google Analytics doesn’t display the full referral path, but only domain name. It is possible to set up an additional profile within Google Analytics, to get the full URL for referrals then follow the guide in the link. One thing to note is that Google Analytics profiles only show data from the date they were set up, so you cannot see historical data with this method. Although if you get it set up now then you can begin to get more accurate data in the future.
Second note, the formula described above can also be run as aVLOOKUP if you need to pull the data back into this tab by simply editing the formula as follows (although this method will only bring back the first occurrence of the domain name mentioned, unless you have the full referral path within your analytics profile);
=VLOOKUP(CONCATENATE(“*”, A8, “*”), ‘Links Built’!$A$2:$A$100, 1, FALSE)
Step 4 – Analyse the Data
Now you will have ended up with a list of Yes/No’s which will tell you if you have built the link for this traffic source. Below is an example of how this can look after you have filtered by all of the “Yes”;
As you can see from three domains listed above, these have driven over 1000 visits to my blog in a short period of time. Are these good for SEO? Well traditionally you may argue that the links are no followed so they don’t offer any value. Personally I would rather have a no followed link which drives actual traffic and real people to my website instead of a followed link which doesn’t drive any traffic at all.
I would suggest running reports like this on a regular basis to continually assess if the work you are doing is actually driving real users to your website and not just building links for the pure PageRank benefit of the link. If all of your links have driven 0 traffic to your website in the past X months then I would begin to ask yourself if what you are doing is going to be having real long term results for your website.
If you also track the type of website where you have been building links such as, guest blog post, infographics, directories etc. then you can quickly assess which type of links are or aren’t driving traffic to your website which can help gain further insights into what is working from a traffic point.
Latest posts by Michael Cropper (see all)
- How to Repeat A Cell Value Multiple Times In New Cell In Excel - August 10, 2017
- Introducing GeezerCloud, Our Latest Product Development For Restaurants - June 27, 2017
- We’ve Passed Google’s Mobile Sites Certification! - June 8, 2017