The Moz Blog

Is Your Linkbuilding Working? How to Easily Check Using Webmaster Tools and a Pivot Chart

This post was originally in YouMoz, and was promoted to the main blog because it provides great value and interest to our community.The author’s views are entirely his or her own and may not
reflect the views of Moz.

Is all that time spent doing linkbuilding helping you acquire links faster than you would acquire them without linkbuilding? This guide will help you determine if the rate with which your site is acquiring links is any different than the rate your site acquires links naturally.

Pulling the Data

To start this process you will need to have already verified your site in webmaster tools. Once verified, navigate to the Traffic>>Links To Your Site section of webmaster tools.

From here you will see three sections: who links the most, your most linked content, and how your data is linked. Click "More>>" under the who links the most section.

Once you have clicked for more, you should see a list of domains that are linking to you, the number of links, and the number of linked pages. What you will also now see is the ability to download the latest links.

While I love Google docs, for this example I recommend downloading a CSV and using Excel.

Using the Data to Identify if You are Picking up Links Any Faster than Before You Started Linkbuilding

You should now have a CSV file open in excel with two columns, one with the linking domain and the other with the date the link was acquired (or spotted by Google). The first thing I recommend is formatting the data as a table.

What you are going to do now is split up the list of links into pieces based on the date you or your SEO company started doing linkbuilding. If you stared linkbuilding six months ago, select the last six months of links and cut and paste them in a new column.

This New Column will be the links after you started linkbuilding (or hired someone to do linkbuilding) The next step is to create another new column that has a date range equal to your date range for your "linkbuilding" column. We can call this column "Just before Linkbuilding". In this column you will cut and past the links from the original "links" making sure that you do not get out of position vertically within the list.

You now have three columns of links all in alignment with their corresponding dates. It's time to turn this data into a Pivot Chart.

Creating a Pivot Chart

With you data all setup you can now insert a pivot chart.

You should now see a blank pivot table and chart. In the lower right corner of the screen you will see an area that has four sections ( Report Filter, Column Labels, Row Labels, Values) Drag the items from the Pivot table Field list into each of these boxes as you see here.

As you do this you will notice your pivot table and chart come to life. The next step is to group the dates by month to make the pivot chart a bit easier to read. To do this, within your pivot table, right click on any date and select group.

Now Select Years and Months and hit Ok.

You should now have a nice easy to read chart with your links broken into three sections and organized by year and month. Mine looks like this:

The next step is to add trendlines to each of the three sections (data series), to really identify any changes in link acquisition rate. I find the easiest way to do this is to right click on one of the columns in the chart and select "add trendline".

You will be presented with a set of trendline options. There are two options I suggest changing, the first option is I suggest selecting set Intercept, this will result in all of your trendlines intercepting the x axis at 0, the second recommendation is setting the trendline color to match the series you are creating this trendline for, it just makes it easier to look at.

The Results

You now have a chart that clearly shows the liner trend with which your site is acquiring links broken down into segments based on your time linkbuilding (or hiring someone to do linkbuilding), just before linkbuilding, and historically before that. If you don't see a change in slope between the trendline for before linkbuilding and during it, something is probably not working with your linkbuilding campaign.

Note: for greater accuracy, you would NOT set intercept to zero and you would compare the slopes of the lines, but for a quick view and more organized graph, this gets the job done.

About electricmice —
I am a data addict with a passion for guiding people into making data driven decisions. I love to make data presentable, easier to read, and ultimately easier to make decisions with. I currently work at Internet Marketing Inc as a hybrid Account Manager, Data Analyst, and internal trainer in all things analytics and excel.

One thing for anyone using this to keep in mind is that the rate of acquisition and the rate of discovery (or reporting) may be different. This should provide a good 50,000 or even 20,000 foot view, but deeper exploration may be needed before making major decisions about stopping or radically modifying a link building campaign.

Something to add to this approach would be to start monitoring lag time between acquisition date and reported date. In other words, if you know you got 10 links on August 1st, and of course you know the domain the link is on, use Google's reporting to determine when those links are reported, and thus, how long it took to for those links to show up in Google's reporting.

Over time, you should develop an idea on the average lag time between acquisition and reporting (which I'd assume is even a little longer after actual discovery). Then you can hopefully begin to predict what the charts should look like going forward and how far above or below that you are. This also means that you have to make sure your measurement period is greater than the average time for links to be reported, otherwise you might determine that link building isn't working when it is, but the links just haven't shown up yet.

Couple things to keep in mind of course is that links also erode over time, and that erosion also varies for every site and every link, and of course links also form naturally as well. So understanding metrics even before undergoing specific link building campaigns should also help to better understand what the natural acquisition and erosion patterns look like.

Link building is a challenging area and one where we have little control over most of the factors, so any tool that helps to better understand performance is great, but also take everything with a grain of salt.

Never thought about using a pivot table! You rock this is a great article. Now I'm thinking of all the other great ways I can use pivot tables. Haven't used them since college, that is soon going to change :)

This will really helpful for us to analysis our link building campaign an how to make improvement for improving search engine presence. Webmaster data also will helpful for us to find unnatural links and which links are beneficial for our site. So we can arrange our traffic.

Very awesome post Justin! So easy with the steps that you've laid out in this post. I tried it out with some data from one of my clients and it showed a more positive linear trend in the "Linkbuilding" section of the chart compared to "Just Before Linkbuilding."

Awesome post, I like the way you present the data and specially with the liner graphs. People have to addict with this kind of measurement with their projects. Generally with the webmasters tools you get the links data not 100% but majorly in good amount. It is just depend on us how to filter them and make them use correctly. You have just make things out clear and simple that whats i like it....:)

Great use of pivot chart and perfect use of WMT data. #just wondering can you used the same ones for the Google analytics as well?

Pivot charts can be used with all sorts of data, When working with Google analytics out of the box, the limitation is only in how you export the data. However if you are comfortable with the analytics API or some of the tools many others have mentioned on moz, then you can easily use pivot tables and charts to organize and visualize your analytics data.

Sorry, but you had me until you added the trend lines. As a mathematician with a long background in business analytics who is now working in web analytics, I can tell you that the trend lines would get you laughed out of any serious analysis. They have so many built in assumptions that they are practically worthless. All it does is misrepresent data to try prove your point. While you do say that using the slope without the intercept is better, there are still lots of problems with that approach using the data you have.

At the very least, this post should have a discussion on exactly what the lines represent and their limitations. It's poor analysis like this that gives statistics a bad name.

I understand the R-value of the trendlines is pretty terrible here, however the concept of getting an idea of how well your inbound marketing in generating links in comparison to a period prior to focusing on garnering back links is still relevant. A whole separate blog post could be written on identifying the best type of trendline and using excel to calculate the instantaneous rate of change in determining a more accurate value for the change in velocity. However this approach is a quick and dirty, and in general paints a pretty easy to read picture.

Benj25, in general you will want to select the trendline with the R value closest to 1. In excel if you format the trendline, there is an option to display the R-Squared Value, you can use this to assist in picking a trendline. When it comes to the best approach for using those trendlines, I will leave that discussion to the statisticians =)

Hi Justin your post is a advantage and a reason why someone should
join and read Youmoz.It is really a superb pic of tips which can boost
the representation value of the link building report. I had think
many times how we can represent the links which has not been built but
generated by the sharing and recommendations as a effect of our link
building and our web promotion. But now with the help of your post i got
the great idea of how to create a report which also covers the links
which has not been built by us but are the effect of our promotions as well as
which will also improve my team image as a professionals.

That is the ultimate goal of this report. It is not so much about tracking the specific links built, but more about identifying the impact of an inbound marketing program. I am glad that I could help you show your teams and clients the value of your work.

Awesome - Even i managed to wing it :DIve noticed a veritable delay in the links also posting to webmaster tools, i have started a site approximately 13 days ago and still have not got 1 return link via the tool.

Im hoping it's functioning correctly, as all the other stats are working. Can anyone tell me whats the time-line between indexing and webmaster tools picking it up ??

New to this site but think your thoughts are valid and straight to the point. I have been having a little trouble with wbt recently, with data updating taking a little longer than normal. Anyway thanks again.

Awesome Post! I think it is very necessary to getting feedback for every one who is doing seo work because by this activity we are able to know that we are going on a right track or wrong. and as you described above in a technical way really great. sometimes what happens we know the tool but we don't know how to use it in a organized way or proper use. But this guide will help everyone for getting deep and accurate analysis and how to use it in excel with pivot table.

I want more technical post from you.

Thanks a lot for such a useful post and hope you will give more technical stuffs.

This happens when there is a non-date type entry in the date column, I have seen the webmaster tools export have issues with the last two rows, so i would check those and ensure the date cell is in fact a date. if it is not, delete the rows or set a date to them and that should solve the problem.

I don't mind checking at all. 1 reason why I don't check is it will only consume my time doing so. As long as I get the results in my linkbuilding campaign it is working, if the results are low then I'll just have to dig in deeper and run a few experiments for a better linkbuilding campaign.

pivot charts for a few our site. Very interesting and revealing. I had
to re-comment because by grouping this data by months and years I am not
only able to see which months link building strategies have the highest
propensity for links, but also visually demonstrate this to my team.
Bravo, Once Again!

Just finished my first set of pivot charts for a few our site. Very interesting and revealing. I had to re-comment because by grouping this data by months and years I am not only able to see which months link building strategies have the highest propensity for links, but also visually demonstrate this to my team. Bravo, Once Again!

Mostly SEO Experts are wondering to know that which link is harmful and which is beneficial. This is an effective presentation and will be helpful to findout the culprits. After the several recent Google updates, the importance of this post is really high.

I always check my website progress through webmaster tool but never try of creating the pivot chart. I am amazed to see that it is so simple and i can try it by myself to check the progress of backlinks of my website. Thanks for the wonderful sharing.

This is one of the best posts I've seen. The only problem I see is that my webmasters tools sucks at showing links to my site, but I guess over time it will suck at the same rate so even though it may not be a exact figure it still works for comparative purposes :)

There are many ways you can analyize your link data, you could pivot by linking domain, you could pull mozrank, pagerank, and or social triggers for your links and pivot by any of those metrics. Using pivot tables and charts in coordination with the vast amount of link data available out there today, there is immense oppurtunity to do link analysis. What about identifying your most linked to content? or breaking out links by the pages they are linked to, then look at links over time? Doing this, you can identify how often you need to re-share the content on your site. Be creative!

I'm sorry, but I fear I did not quite understand the final chart. If I do it according to your tutorial my first column is empty. So in the chart my green column is empty. What did I do wrong? I have problems in understanding the following: "This New Column will be the links after you started linkbuilding (or
hired someone to do linkbuilding) The next step is to create another new
column that has a date range equal to your date range for your
"linkbuilding" column. We can call this column "Just before
Linkbuilding". In this column you will cut and past the links from the
original "links" making sure that you do not get out of position
vertically within the list." Doesn't this mean, that I simply cut and paste the numbers from the first column ("Links") in the third column ("Just before Linkbuilding").

Ah, I hope I got it now. We are actually looking at all links that have ever been set. But we concentrate on the links that have been set half a year ago. And within this half year we started linkbuilding (let's say f.e. two months ago). Did I get this right?

You are going to want to set the two columns to contain data for equal lengths, so if you have been doing linkbuilding for two months, you want the just before column to also have two months of data, and then the third column will be everything else.

Good tip, the only thing is how often does google update there index, id prefer using Majestic SEO as theres is updated on a daily basis, Open site is terrible they can barely keep up, there index is updated every 1 to 3 months they just have a better UI and API thats all.

I think this is a very useful post and is certainly a good way of quickly seeing the impacts of a link bait campaign for example, or even comparing an agencies link aquisition rate to another. the one thing I feel is missing is a paragraph around quality of link vs quantity. It's good that you can see the link aquisition rate increasing month on month, but if I had one link in August vs 10 in July, but that link was of more value than that 10... i'd still be pretty happy. That's when you match up your rank improvements to your sites traffic to really see if those links are adding value.

Thanks for this insightful post. I was just wondering if Google Webmaster tool does not limit the amount of backlink additions it shows each iteration. This is something i've always felt it was doing, not sure though. Cheers.

Yeah i really appreciate this article, because i use this method in the webmaster and it is working fine, and according to Google, by linking the two accounts you will be able to view your Google analytic referring pages report directly from the links to your site page and this tool can access Google analytic dashboard directly from the analytic link in the top left bar when you care on the site related page.

Great Article. I like the method how you represent Webmaster Tool. One can check all SEO in one go by webmaster tool. Your method of Pivot Cart is never used by me. I will give it a try. And also I want a help from you, Is there any tool or date by which I can check how many pages are crawled and indexed by Google in last month , I want monthly details of it.

Interesting and bloody actionable... It’s great to keep a
check on active links in order to have a clear idea of how the link building is
working for you and at the same this is a good resource for links to know how
the website is performing in terms of off-page SEO.

Being a student of on-page SEO i learn a lot of things from your post. To be honest i never think about this very comprehensive and useful analysis that what i am getting or loosing from link-building. Since three months i am working on an anchor text business logo design and now i will check it out how many links i got against this particular keyword from which sites.

It was directed at Justin but glad you responded. I feel the same way and wanted to get some other thoughts. I think I'm only tracking one client where GW is reporting fewer (over a hundred fewer) domains linking to the site compared to OSE. It's frustrating to say the least.

Thanks Electricmice, nice input from this post. I like measurable actions like this, and post that helps people to analyze data with Excel are always welcome, plus it is a good way to present data in a very clean way like. Good post!

A great use of pivot charts to show important information. Great job on the simple walk through for everyone. It was very easy to follow and should provide some great actionable information to us all. :-)

Great post! A caveman can do it with these step by step directions! Now I can see that our site in the past two years gained lots of links. Although I really stopped building links in the past couple of months, our links are still growing great! Thanks

I think its important to note, with this chart you can see the quantity of links, but as other have mentioned, it does not indicate the strength of the link. We could come up with some clever modifiers to this chart that help indicate strength by pulling something like mozscore and using it as a multiplier...Be creative and see what you come up with.