A Data-Driven Approach to Link Building in SEO
I built a Tableau report that analyzes the fundamental business economics of link building in search engine optimization (SEO).
This is an example Data Analyst Portfolio Project write up created as an example for the course Create a Winning Data Portfolio.
Project Summary
Here’s a video of me walking through the entire project in less than 6 minutes:
Project Links
Business Problem & Key Questions
The website scribewriting.com is considering investing in link building but doesn’t know how many backlinks they need to build to rank their web pages and how much ranking is going to cost.
I’ve been tasked with putting together a link analysis for the following keywords:
book cover design
book writing software
how to overcome writers block
how to publish a book
how to write a book
writing tips
For each keyword, I need to answer the following questions:
How many links do I need to build for each keyword?
How much is link building going to cost for each keyword?
Data Preparation
To obtain the data, I used an SEO tool called Ahrefs.
For each website, there were two CSV files I needed to download from Ahrefs:
Backlinks
Referring domains
Each keyword contains up to 10 websites, so I ended up with close to 60 CSVs.
To make the downloading of CSVs a repeatable process, I used a Python script with Selenium.
After concatenating the CSVs together, I uploaded them into BigQuery.
I used SQL to prepare my data for the analysis.
Approach
Here’s the approach I used to determine how many links scribewriting.com would need for each keyword:
Pulled backlink data for each website ranking on the first page of Google for the desired keyword.
Created a “Quality Flag” that filtered out low-quality links that likely weren’t helping in ranking.
Domain Rating >= 20; Organic traffic to the domain >= 1000; Link is NOT nofollow
Calculated the total number of unique backlinks each site had that met the Quality Flag.
Calculated the average (Serp Avg) and median (SERP Median) number of links for all the websites ranking on the first page for each keyword.
Took the max (SERP Max) value of the SERP Avg and SERP Median so that we’re conservative in the estimation (underestimating is what we want to avoid).
If scribewriting.com already has a page ranking for this keyword with links that meet the quality flag, I subtract those links from the SERP Max. This field is called Link Gap From Max.
The Link Gap From Max is the number of backlinks scribewriting.com would need to build today, but this number will increase over time as the ranking competing websites continue building links. To account for this, I calculate a rolling 3-month average and median link velocity (i.e. the number of links the ranking websites build each month).
The last thing to calculate is the time period scribewriting.com is planning on building these links. If the time period is 6 months and the link velocity is 2, that means in addition to the total Link Gap From Max, they will need to build 12 (6*2=12) additional links.
If the keyword is “book cover design” and the Link Gap From Max is 22 with a link velocity of 2 over 6 months, the total links needed would be 34 (22 + 6 * 2 = 34).
Here’s the approach I took to determine how much link-building would cost:
Now all I had to do was estimate the cost per link. Using online research I found the average cost per link is around $150 if the organization builds an in-house link-building team and $300 if they outsource their link-building. In scribewriting.com’s case, they were building a team internally.
To calculate the total cost of link building to rank for “book cover design”, I multiplied 34 * $150 = $5,100. I repeated this process for the other 5 keywords.
Results & Key Takeaways
The website scribewriting.com should expect to build around 150 links with a total cost of $22,500 to rank for their 6 desired keywords.
Here’s the breakdown by keyword:
