Analyzing Inbound Links Using BacklinkWatch Data - Second Step: Cleaning up the Data for Analysis
(Page 4 of 4 )
After we have collected the data, we can start cleaning it. There are steps that we need to take so that the data that we are analyzing are clean and useful. To start with:
- Filter nofollow links by going into Data -> Autofilter, and then in column F (Flag) select the drop down button and choose (Blanks). This will remove any row that contains “nofollow” data.
- Then in Column E (OBL), in the drop down button, select (NonBlanks). This will remove any row that has no “number of outbound links” data.
- Delete column D (page rank colum) as it is not needed. To delete, simply click the whole column D, and then right click delete.
- Do not include rows that contains no anchor text data. To do this, under column C (anchor text), select the drop down and choose (NonBlanks).
- Select A1 until the end of the data table; in this example, it is D1001. Copy and paste to another empty Excel worksheet, starting again on cell A1.
- Correct the numbering of the back link data in column A. Start with 1 and end with the last. In this example it should be 940 (clean data after filtering).
Save the Excel spreadsheet. To make sure you have done this correctly, see filtered.xls here: http://www.php-developer.org/2009/03/backlinkwatch-excel-spreadsheet.html
Third Step: Analyzing the Data
To start, copy and paste this formula starting F2 to F941:
=MID((MID(B2,8,(LEN(B2)))),1,(FIND("/",(MID(B2,8,(LEN(B2)))),1))-1)
This will give the unique domain. After that, select column F, and then copy and paste as values. It should remove all the formulas in column F, leaving only the unique domains.
Select column F (highlight all column F data), select data -> filter -> advanced filter, and then click OK. In the list range select all data in F, check “unique records” and then OK.
Finally, select starting cell A1 all the way down to F933. Copy and paste that to an empty worksheet at starting cell A1 again. Correct the numbering of the filtered data table, starting from 1 until 117.
Statistical analysis
Number of back links pointing to the URL coming from Unique domains = (117/1000) * 2877 = 336 unique domains
To count the number of links containing “PHP developer” in anchor text:
Select column C, data -> filter -> autofilter -> select drop down button -> custom -> select “contains” in the box enter “PHP,” then AND “contains;” in the box, enter “Developer.” Counting that one, it will be 28. So, (28/1000) * 2877 = 81 links coming from unique domains using “PHP developer” in the anchor text
Also, take the average of column D (number of links in the page). It is 141 links per page on average.
Examining inbound links containing the targeted text, you will see they are around 76% relevance. For this statistic, the higher, the better.
To beat this competitor, you may need more than 336 unique domains linking to you, with less than 141 links per page and 81 links in anchor text with 76% relevancy.
Google analyzes more than 200 factors in their ranking. This analysis is just an approximation, but it provides a quantitative approach to inbound link analysis.
Check the final Excel spreadsheet “data analysis.xls”: http://www.php-developer.org/2009/03/backlinkwatch-excel-spreadsheet.html
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
