Contributors
@elise-dopson
Business Benefits
Gain better insights and plan more appealing content by improving your research process.
Identify your competitor websites by searching on industry keywords and recording websites that frequently rank in search results.
Create a spreadsheet with columns for URL, Title, Referring Domains, Publish Date, and Organic Traffic.
Crawl competitor websites with Screaming Frog, using the Ahrefs API to pull the URL, title, number of referring domains, publish date, and organic traffic for each page, then add this information to your spreadsheet.
Add a column named LAR and calculate this metric for each post by dividing the number of referring domains by the average number of referring domains per post for that site.
LAR stands for Links Above Replacement. It’s a metric that allows you to control for site size when you’re looking for high-performing content. If you don’t use this, you risk having large sites dominate your top performers list, missing great content with high performance on smaller sites.
Use the =AVERAGEIF function to calculate the average number of referring domains per post on the fly, with the domain name as the criterion.
Add a column named bLAR and calculate this metric for each post by dividing the number of links to the post by the total number of links to the site that hosts it.
bLAR is a similar metric that stands for back-of-the-napkin LAR, and it helps you to find content that is pulling in an above average percentage of its site’s traffic. If a post represents 10% of a site’s total referring domains, it’s probably worthy of more of your attention than one that represents 0.001%.
There are a couple of ways to improve the (rather rough) bLAR metric:
- Instead of a domain-wide link count, pull links to the content subdomain or subdirectory (e.g., blog.example.com or example.com/blog/). You have to find those patterns manually, so this scales for up to 30 sites but probably not beyond that.
- Exclude links to sites’ homepages. Use Screaming Frog and the Ahrefs API to pull the link metrics for the homepage of each URL in your list. Then, subtract homepage links from the total link count. That populates the denominator with only non-homepage links, a more representative (albeit still imperfect) sample of links earned by content.
Copy URLs, publish dates, and number of referring domains to a new tab on your spreadsheet and sort by website then publish date.
We’re going to create moving averages to find content that did well at the time it was published, compared to other content on the website. For example, a page that brought in a few thousand visitors five years ago might have helped make the site popular and indirectly contributed to the site’s current tens of thousands of daily visitors.
Odds are, the content that helped create a powerful site is far different than what sustains it (or, worse, feeds off it). The former is also likely to be more relevant to you—you want to find the stuff that will build and expand your audience, not maintain the status quo.
Add a column called Moving Average and index each page’s performance: divide referring domains by the median of referring domains for all preceding pages on that site.
If B1 is the number of referring domains for the first post, B2 for the second post, and so on, your formula looks like this:
- = B1
- = B2/MEDIAN(B$1)
- = B3/MEDIAN(B$1:B2)
- = B4/MEDIAN(B$1:B3)
- = B5/MEDIAN(B$1:B4)…
Remember to restart this formula wherever the site changes, or copy each site’s page data to a different tab to make automation easier.
*You can use the median or the average. Often, the median does a better job controlling for outliers. Every site has that one post that earned exponentially more links, shares, comments, etc. If you use the average, you’ll falsely inflate the results for a “typical” post on the site.
Go back to your original tab, add a column named RDs Per Organic Visit, and calculate this metric for each page: divide Referring Domains by Organic Traffic.
If the Referring Domains metric is in column G and Organic Traffic in column H, your formula would look like:
=G3/G4
Say you want to know what Amazon-related content people just had to link to. If you look only at the typical export of most-linked content, you’ll see plenty of stuff that earned those passive links.
But with the added bit of context from organic traffic, you unearth content that piqued others’ interest—not just the stuff that’s been sitting at the top of a high-volume SERP for years.
Sort your spreadsheet tabs by LAR, bLAR, Moving Average, and RDs Per Organic Visit and record the top ten or twenty pages for each calculated metric.
If you’re looking at more or larger sites, you might want to record the top 50 or 100 instead.
Look for common themes and topics in the recorded pages.
Last edited by @hesh_fekry 2023-11-14T16:01:19Z