Link insertions and drops can be a powerful tool for boosting your SEO and driving targeted traffic. But finding the right opportunities can feel like searching for a needle in a haystack. That's where this data-driven approach comes in. I'm going to show you how to leverage data pivot tables to identify high-potential link drop opportunities on sites you're already working with, maximizing both SEO and conversion potential. Some call this Parasite or Piggyback SEO, if you were confused by the post thumbnail.
This guide was generated from my 2023 video transcript.
This version has been recut, for silence, making it faster, and the steps, below, may help you to learn this process.
What is an SEO Link Drop or Insertion?
A link drop or link insertion involves strategically placing your link on existing, high-ranking pages of established websites. The "parasite" aspect refers to leveraging the authority and traffic of those pages to boost your own website or offer. It's a win-win: the website owner gets some relevant content, and you gain valuable backlinks and exposure to a targeted audience.
Why Use Data Pivot Tables for Link Drops?
Instead of blindly reaching out to site owners, this method empowers you to identify pages with significant search volume and relevant content opportunities. By using data pivot tables, you can quickly analyze a site's ranking keywords and pinpoint the most promising targets for link drops. This allows you to:
Identify High-Traffic Pages: Target pages that are already attracting a significant number of visitors.
Assess Relevance: Ensure your link aligns with the existing content for a natural and valuable user experience.
Optimize for Conversion: Choose pages where your link can effectively drive conversions, whether it's sales, sign-ups, or brand awareness.
(as close to the top of the page, as possible, usually)
The Step-by-Step Guide to Data-Driven Link Drops
Here's a breakdown of the method, from data gathering to opportunity identification:
Step 1: Choose Your SEO Ranking Tool
You'll need a tool that can reveal the ranking keywords for a given website. Some good options include:
SE Ranking: Offers comprehensive ranking data, often showing a larger number of keywords than other tools. This is my preferred tool.
Using this link supports my content efforts. (thanks)SpyFu: Known for its SERP data and a relatively affordable option, but also a much smaller data pool. Not ideal for this use-case, but cheap.
SEO PowerSuite Rank Tracker: A viable option, though it typically shows fewer keywords than SE Ranking or SpyFu. I don’t prefer to support this company, due to the costs, and the out-of-date user experience.
Alternatives: Moz Open Site Explorer and SEMrush can also be used, but their free or trial versions may have limited data. Ahrefs may have a cheaper plan, now, too, which might be viable, but for the cost, you might-as-well go with SE Ranking, if you’re gonna spend the money.
Open PageRank (not covered in video): The truly FREE option.
Anything above a score of four has a certain value, but the higher the better. This is doesn’t show keywords ranking, just domain metrics.
It’s also the better bulk authority option, ironically, but just remember that it will NOT show you individual page rankings, only domain level.DomDetailer: These guys are basically arbitrating API credits from Moz and Majestic, which show certain metrics, but not keyword rankings.
Step 2: Filter for Top-Ranking Keywords
In your chosen tool, filter the ranking keywords, of a domain, to display only those ranking in positions 1-10 (the first page of Google search results). This ensures you're focusing on pages with significant visibility.
If you are just looking at domain level metrics, it’s important to look at things mid-range, or higher, in terms of value, and if you have eyes on more actionable datapoints, cross-reference those values.
Authority values, like Moz DA, for example, can be falsified by spam links.
Step 3: Export the Data to CSV
Export the filtered ranking data to a CSV (Comma Separated Values) file. This will allow you to work with the data in a spreadsheet program like Excel or Google Sheets.
Step 4: Prepare the Data in Your Spreadsheet
Open the CSV file in your spreadsheet program. (Excel)
Move the "URL" column to the end of the sheet (optional, but can help with organization).
Identify and select the "URL" and "Search Volume" columns.
Step 5: Create a Data Pivot Table
With the "URL" and "Search Volume" columns selected, go to "Insert" and choose "PivotTable."
Create the pivot table in a new worksheet.
Step 6: Configure the Pivot Table Fields
Drag the "URL" field to the "Rows" section. This will list each unique URL from the data set.
Drag the "Search Volume" field to the "Values" section. This will sum the search volume for all keywords associated with each URL.
Drag the "URL" field a second time to the "Values" section. This will count the number of times each URL appears in the ranking data, giving you an idea of how many different keywords it ranks for.
Step 7: Extract and Clean the Pivot Table Data
Select all the data in the pivot table (excluding the grand total row, as it can cause errors).
Copy the selected data (Ctrl+C).
Paste the data into a new worksheet. This detaches the data from the pivot table and allows you to apply your own filters.
Step 8: Format and Filter the Data
Select the entire sheet and apply filters (usually found under the "Data" or "Home" tab).
Freeze the top row for easier scrolling.
Adjust column widths for better readability.
Step 9: Sort by Search Volume
Sort the data by the "Search Volume" column, from largest to smallest. This will bring the URLs with the highest collective search volume to the top.
Step 10: Analyze and Identify Opportunities
Now, carefully examine the URLs at the top of the list. Consider factors like:
Relevance: Does the page's content align with your website or offer?
Outbound Links: How many outbound links are already on the page? A page with few outbound links is a better target.
Potential for Conversion: Can you craft a link that will naturally drive conversions from the page's existing traffic?
Beyond Link Juice: The Conversion Advantage
While backlinks are valuable, the true power of this method lies in the potential for driving targeted traffic and conversions. By placing your link on a relevant, high-traffic page, you can expose your brand to a ready-made audience.
Maximizing Conversion Potential
Consider ways to make your link even more impactful:
Video Embeds: Ask if you can embed a relevant video on the page.
X-Post Embeds: Embed a relevant tweet to add social proof and engagement.
Strategic Anchor Text: Use anchor text that is both relevant to the page and optimized for conversions.
Caveats and Considerations
Site Owner Approval: Not all site owners will be open to link drops or insertions. Be prepared to offer value in exchange for the placement.
Overpricing: Link drops and niche edits are often overpriced, especially from vendors, so don’t move-forward unless it’s top tier.
Foreign Sites: While foreign sites may not always be ideal for client projects, they can be valuable for private projects seeking exposure to impressions, and they still provide authority to link profiles.
Conclusion
This data-driven method transforms the often-random process of link building into a strategic and targeted endeavor. By using data pivot tables to identify high-potential link drop opportunities, you can maximize both the SEO and conversion benefits of your efforts. I use methods, like this, all the time, and have pulled some extraordinary placements, so I highly recommend this approach. Next, take-a-look at my media kits scraping guide, which shows you how to find opportunities, like these.
Share this post