I love my SEO spreadsheet. It’s my go-to option for evaluating marketing efforts, and I check it every day. So let’s talk about SEO spreadsheet templates.
In this article, I show you how I built mine. I also discuss how I use my SEO spreadsheet to track Domain Authority, Domain Ranking, and everything else I need when optimizing pages for search traffic.
SEO Spreadsheet Challenges for Marketers
So why use an SEO spreadsheet? Like all the best spreadsheet templates for business, it solves a need.
Specifically, there is no quick, easy, and consistent way to filter high-quality opportunities from less-than-stellar ones. You can’t just blindly trust metrics like Domain Authority (DA) and Domain Rating (DR) to give you an accurate reading of the quality of a domain.
Looking for a deeper dive into this type of marketing? Check out this UCDavis course on Google SEO.
Basically, without a way to organize and optimize your efforts, you’d have to manually evaluate each link opportunity. That takes time when you factor in things like organic traffic trends and anchor text profiles.
That’s why I build my SEO spreadsheet template.
The Best Spreadsheet Template for SEO
There’s a way to evaluate each link opportunity quickly, easily, and consistently. That’s my SEO spreadsheet template.
I also call it my Link Opportunity Evaluation Sheet (LOES). I use it to help determine the value of a backlink. This helps me determine potential partners, who to reach out to, and how much of an opportunity exists for my marketing efforts. In this article, I’ll show you how you can use this template to evaluate link opportunities faster and more consistently than you would manually
Note: You also need at least one Search Engine Optimization (SEO) tool like Ahrefs or Majestic in order to use this template. It’s also what I use for my clients at SEOExpert.ph, with my own rating system. You can adjust the template based on your own subjective biases. I’ll show you how to do that below.
Make a Copy of the SEO Spreadsheet Template
First, you’ll want to make a copy of my SEO spreadsheet. Here’s how to do that. I included screenshots to show each step, including how to use it once you make your own copy.
Here’s the link to my template.
Once you’re there, click FILE > Make a copy.
How to Use the SEO Spreadsheet Template
I created three parts for this template:
- Evaluation Sheet
- “Paste Anchor Texts Here” Sheet
- Scoring Sheet
Let’s talk about how to use each of them. Note that the “Evaluation Sheet” in my SEO spreadsheet is shortened to “Eval Sheet” in the tab.
Evaluation Sheet
The evaluation sheet looks like this:
Total Possible Score
The first thing to note on the Eval tab of this SEO spreadsheet that there’s a maximum score each link opportunity can get. The default score is 900 (meaning each metric’s full score is 100), but you can adjust this in the “Scoring” tab. I’ll talk more about scoring later.
Metrics Column
The metrics column lists all the variables that we take into consideration when evaluating a link opportunity. Currently, the sheet lists the following metrics:
- Domain Rating (DR) – Ahrefs metric to determine domain quality. The higher the DR, the higher the score.
- Organic Traffic – How much organic traffic does the website receive every month? The more organic traffic, the higher the score.
- # of Referring Domains – How many referring domains does the website have? The more referring domains, the higher the score.
Note: The number of referring domains is not a good indicator of domain quality by itself. After all, a website can have 2,000 spammy referring domains! It’s good to use this metric in combination with other metrics like Trust Flow or Trust Ratio.
- Organic Traffic Trend – Is it trending upwards, downwards, or just stable over a period of six months? Higher scores are given to upward and stable trends. While a decreasing trend is given zero.
A declining traffic trend may be indicative of an algorithmic devaluation or an outright manual penalty, so I tend to give this metric some importance.
- % Share of Page w/ Most Traffic – What percentage of total traffic does the top-performing page receive? Ideally, all pages should receive some traffic, but if only one or two pages receive the majority of the traffic, I give them a low score.
You can find this value by going to Organic Search > Top Pages.
- Anchor Text Selection – What’s the anchor text profile of the website? We want to avoid links from certain types of posts unless you’re in those niches. I usually reject web pages with target keywords that match terms I don’t find valuable.
We’ll talk more about anchor texts later.
- Trust Flow (TF) – A Majestic metric to determine domain quality. The higher the TF, the higher the score.
- Citation Flow (CF) – Another Majestic metric to determine domain quality. This is not assigned a score but is used to determine the Trust Ratio. You can learn more about TF and CF here.
- Trust Ratio (TR) – Computed by dividing the TF over CF. The quotient is a value from zero to one and is used to determine if the site is linking to trustworthy sources.
- Spam Score – A Moz metric that determines how spammy a domain is. he Spam Score is the “SS” value in the table.
Total Score
Total Score is the sum of each metric’s individual score. This value will be computed against the Total Possible Score.
Grade
A letter-based score is derived from the Total Score. I made this so it’s intuitive for those more familiar with a letter grading system.
Recommendation
The sheet will give you a recommendation based on the domain’s grade.
- A – Excellent
- B – Very Good
- C – Fair
- D – Manual Evaluation
- E & F – Reject
I will usually pursue link opportunities with Excellent, Very Good, and Fair scores.
A website that receives a “D” needs to be manually evaluated. This usually happens if the website has a fairly low traffic, stable trend, and has poor anchor texts. I tend to avoid those. Remember, we’re looking for trustworthy sites we want to partner with! They should offer valid, real-world expertise like I found at SpreadsheetPoint.
“Paste Anchor Text Here” Sheet in The SEO Template
In this part of the SEO spreadsheet, you add anchor texts for the website you’re considering. You don’t have to do this manually. In fact, you shouldn’t. Use Ahrefs to do a bulk export into the SEO spreadsheet.
To get there in Ahrefs, visit Backlink Profile > Anchors and click the Export button.
You don’t need to go wild with this. I only export around 200 anchors. That’s just the most frequent ones.
Then, copy and paste the list to the sheet.
Troubleshooting tip: Make sure to paste without formatting (CTRL + SHIFT + V). That removes any extra formatting you’ve copied and helps avoid issues with formulas in Google Sheets.
Once you’ve done that, the SEO spreadsheet will run this formula to search for unwanted keywords. I prepopulate this with some that I don’t like to work with. You can edit this to make your own, too.
Of course, you can always change what keywords to watch out for. It all depends on your niche.
In this example, my sheet wants to watch out for eight words. In general, I steer away from these anchor texts because they often lead to sites I’m not interested in working with.
“Scoring Sheet” In The SEO Template
In the scoring sheet, you can assign scores per metric.
Just assign minimum and maximum values to a certain score. For example, I gave 30 points for websites with 20 to 25 DR.
Depending on your preference, you can assign just ten points to the same DR range – it all depends on you!
My recommendation: Stick to the default scores if you’re not too sure what metrics to prioritize.
Enter your blog’s metrics in the SEO Template
Now, it’s time to try the spreadsheet template!
Once you’ve copied the sheet, edited the factors to your niche, and pulled in some of your own information, you’ll see something like this.
It shows an increasing traffic trend (nice!) and a total score out of a possible 900. The right side of the SEO spreadsheet also shows my letter grade and recommendation. Based on my experience, this site looks like a strong potential partner. I reached out to them, showed them my work, and they invited me to contribute my ideas.
Conclusion
I hope you enjoyed this deep dive into my favorite SEO spreadsheet. The article included screenshots and a step-by-step breakdown on how to use it. Most importantly, I also included a link to the template itself, so you can copy it and use it on your own marketing efforts.
Do you have any questions on SEO marketing spreadsheets? Please let me know in the comments.
Frequently Asked Questions
Can I add more keywords to the filter?
Yes, this SEO spreadsheet allows for additional keyword filters. You can add as many as you want. Just change the formula in the Anchor Text Selection score by adding your preferred banned keyword.
Can I also use this for internal linking?
This sheet is made exclusively for evaluating external opportunities for your landing pages. Other potential SEO spreadsheets could evaluate internal links, potential keyword clusters, and the popularity of your contributing authors.
How does this template improve your SEO?
I use this sheet to evaluate potential partners. It removes guesswork and inconsistencies I had before. While the time I am able to save is just a couple of minutes per website, I evaluate dozens of domains daily so the saved time does stack up.
