Our Blog

How to Build a Link Intersect Tool in Excel for Competitive Link Analysis

You Are Here :
Home
Blog
Our Blog

How to Build a Link Intersect Tool in Excel for Competitive Link Analysis

Competitive Link Analysis should form an important strategy in your SEO strategy since learning more about your competitor’s backlinks can help provide opportunities and improve your link profile. However, locating such links manually can be frustrating, especially if websites have many backlinks.

One way to locate backlinks is to build your own link intersect tool in Excel. This tool can help you locate domains linking to your competitors, but not linked to you. Excel offers a cost-effective solution although you may need to opt for a monthly subscription for the backlink source in case you do not have this feature.

You can begin by getting the required backlink data through any tool such as Majestic, Moz OSE, Ahrefs, etc. Almost all tools allow you to export data to Excel. For instance, you can use OSE and then select the number of competitors that you want to analyze. You can go to OSE and enter the required domain name, before clicking on search. You can use a filter to only get external links to pages that are on the root domain or sub-domain.

You can then download the data using the Download CSV option that allows for 10k of links or go into Advanced Reports where your daily credit limit will allow you to download as many as 100k of links. In case of the latter, you can opt to use the filters if you wish in order to reduce the amount of results.

You need to repeat the procedure to get your own CSV data as well as those of your competitors and you will receive an email once the process is complete. Next, you need to transfer the data into your Excel file. You can do so by opening the CSV file, selecting the required columns filled with URL and target URL data, and copy/pasting them in Excel. Alternatively, you can use Excel Text Import wizard without even opening the CSV files and import that data into an empty Excel file.

However, make sure that your Windows installation is not set up for any European country. You need to reset it to English (United States) in the Windows Regional and Language Options section or keep your European country, but change decimal symbol to . (dot) instead of , (comma) and also the list delimiter to , (comma) instead of ; (semicolon).

However, there may be some errors while using the Excel Text Import Wizard that could display data with errors and you may need a few more steps to ensure that all the imported data is error-free. Once you do get the data from the first domain then you need to copy data from the balance domains under that data without copying the header row so that you only have a single header row at the top.

You may also have to split the number of domains in batches of 10 to 20k based on your Excel version to avoid error messages due to large amount of data. A good tip is to get all your data into tables for easier handling of your data and easier implementation of formulas. Your formulas should only include the source URL and target URL, and should provide you with the source and target subdomain in two separate columns.

Your formula will change in case you do not use OSE and the result will be data the name of each URL after the .// section, but before the very first / section of any URL including links that use https. You will also need to change your formula depending on whether you have chosen the Pages to Subdomain or To Root Domain option at the time of extracting your data.

Next, you will have to list all your Top Level Domains (TLD) and Second Level Domains (SLD) while ensuring that the SLDs remain under the TLDs so that their detection is done at the last stage. Next, you need to extract the TLDs and SLDs from the Target Subdomain. From this data, you will be able to get the actual root domain that involves stripping away all data to get it.

You will also need to check if any source domain is linked to a target domain at least once so as to know how many links your competitors have been getting from that particular source. You also need to check if any link points to your primary domain or not and a simple conditional formula can help you determine just that.

Finally, you need to create a new Excel sheet or rename any default sheet in which you will need to create a pivot table to analyze your data. You need to click on the Insert Tab in Excel and select Pivot Table, and then enter the name of the table that contains your backlink data. Once your empty table is created then you need to bring in your Source Domain and Target Root Domain or Target Subdomain fields depending on your chosen strategy along with the URL fields in the Row Labels Box.

Next, you need to drag all Link to Us and Unique Domains data along with all other required metrics to the Values Box section. You need to get their sum by using the Sum Of option and then get their averages by selecting the Average option instead of Sum. You now have detailed data on your hand that you need to compress by using the Source Subdomain fields. You can do so by going to Options, then to Pivot Table, and then clicking on Collapse Entire Field. This process can be repeated for Target Root Domain too.

You now need to filter and sort the remaining data so as to only get competitor links and not your own links. By choosing the Sum of Unique Domains option in the More Sort Options section, you will get a pivot table of your links of your competitors that are sorted by the number of competitors they are linked with.

The above efforts of building a Link Intersect Tool in Excel will reward you with backlink data of your competitors. Alternatively, you can also visit vertify.ro to get a readymade template with all required formulas that will only require you to add your own backlink data.

Why Choose Anuva?

Anuva is a leading Digital Marketing Company providing results-driven Internet Marketing Services including Local SEO Services, Online Reputation Management Services, WordPress SEO, Ecommerce SEO, Professional SEO Services, SEO Consulting Services, SEO Audit Services, etc. to clients worldwide. Looking for Google Ads Services OR Facebook Ads Agency? We are a highly experienced PPC Management Company specialized in eCommerce PPC Management. Please check our Client Testimonials and SEO Rankings for you to see the outstanding results we have achieved. Contact us to generate a Huge ROI on your invested dollars from our strongest Online Marketing Services.

We Are Qualified
Seo Ranking
activedemand.com
digital marketing automation platform alberta 1
marketing automation softwares alberta 1
marketing automation webcasting 1
marketing automation webcasts 3
online agency marketing automation platform 6
google.com Ranking As Of 24-Apr-2021
ares.net
ares p2p file sharing program 1
ares p2p network 2
revolutionary p2p file sharing system 6
official ares download 4
p2p file sharing platform ares 2
google.com Ranking As Of 24-Apr-2021
biyanitechnologies.com
ace digital language lab 1
ace digital language laboratory 1
ace digital language labs 1
ace digital languages lab 1
ace digital language laboratories 1
google.co.in Ranking As Of 24-Apr-2021
csrhub.com
buy csr tools 1
search sustainability ratings 1
social responsibility and sustainability ratings 1
social responsibility ratings 4
social sustainability ratings 5
google.com Ranking As Of 24-Apr-2021
emenu-international.com
emenu international 1
interactive emenu 2
international e-menu 1
international interactive emenu 1
emenu solutions 2
google.com Ranking As Of 24-Apr-2021
eremex.com
download topor pcb 1
topor competitive advantages 1
topological router for pcb 2
pcb design time reduction 2
topological router for printed circuit boards 2
google.com Ranking As Of 24-Apr-2021
greenviewdata.com
cloud email services ann arbor 3
premium antispam service ann arbor 1
secure hosted email services ann arbor 1
hosted email services ann arbor 10
secure email hosting services ann arbor 5
google.com Ranking As Of 24-Apr-2021
lumeta.com
real-time breach detection in somerset 6
real-time breach detection in somerset nj 3
real-time breach detection somerset 3
real-time breach detection somerset nj 5
somerset real-time breach detection 4
google.com Ranking As Of 24-Apr-2021
mirekusoft.com
buy install monitor software 1
buy install monitor softwares 1
buy program installation monitor 4
buy program installation monitor tool 3
software installation monitor tool 7
google.com Ranking As Of 24-Apr-2021
promero.com
oracle predictive dialer call center solution 1
oracle preview dialing software 1
predictive dialer software oracle 1
oracle call center software service 5
oracle call center software services 5
google.com Ranking As Of 24-Apr-2021
prowagersystems.com
bingo software for website 1
bitcoin poker software for web 2
custom html5 poker software 2
bitcoin poker software for websites 3
html5 poker software 10
google.com Ranking As Of 26-Apr-2021
railcarrx.com
railcar repair management software 1
railcar repair management softwares 1
railcar repair management 2
railcar service software 1
railcar software 4
google.com Ranking As Of 26-Apr-2021
remiware.co
crystal ssrs reports scheduling tool 1
schedule crystal ssrs report 1
schedule crystal ssrs reports 1
ssrs report scheduler software 2
ssrs crystal reports scheduler tool 1
google.com Ranking As Of 26-Apr-2021
Testimonial
You have all done a wonderful job making both of my websites look more modern and professional. I would also like to say how excellent your customer support has been. Any and all concerns I had were addresses immediately with no questions asked....
- Beau Mason
We are more than satisfied with Anuva's Search Engine Optimization services. They have developed and upgraded our website and promoted the business very efficiently. Kudos! Keep it up! We have emerged as a reputed large scale organization and...
- Richard, Seattle
Why Us?
  • 70% + Cost Savings
  • IP Protection & Confidentiality
  • Expert Team with Global Delivery Experience
  • Business and Domain Expertise
  • Maintain your Competetive Edge

Call Me Back

    Success! Thanks for Your Request.
    Error! Please Try Again.