We recently had a unique client request to identify potential partners for their new affiliate program. Apart from using affiliate networks, we wanted to create a method for selecting potential partners using competitive backlink data, as we would when identifying prospects for outreach.
Learn how we used data to inform what sites we approached for partnerships in our step-by-step process below using Ahrefs and Power BI.
Start with identifying competing sites that also have affiliate programs with audiences similar to yours.
For all intents and purposes, we’ll use the discount code and coupons industry as an example throughout this article. Say that you’re on the marketing team for RetailMeNot, which helps users save on purchases by providing coupons, promo codes, sales, and cashback offers. You know that two of your biggest competitors are Honey and Ibotta, which both have robust affiliate programs.
Now that you have those “north star” competitors in mind, it’s time to do a little digging.
In order to hone in on your competitors’ affiliate networks, you should identify the types of URL parameters that their partners use to drive users back to their sites.
Using Ahrefs, throw your competitors into the tool and go to the Backlink report:
Peruse the list of backlinks generated in the report. For Ibotta, we’re seeing backlinks from blogs like Money Saving Mom, Totally Target and Busy Budgeter. When looking at the backlinks, we immediately start to notice a few hints.
We’re seeing some backlinks marked as “Content” which is an indicator of a paid content partnership. We’re also seeing the use of the /?friend= parameter in some links, which is likely one of their affiliate campaign parameters.
As we conducted our own research for our client, we found other common parameters like:
With this information, we can filter down to backlinks with these parameters for all competitors and export them. This is manual, but if you save all your exports in one folder, you’ll be able to easily load and combine them in Power BI.
If your business already has an affiliate program, we’d suggest also exporting all affiliate backlinks using your own parameters as well, so that you’re easily able to see what sites you’re already partnering with and which you are not.
Open up Power BI and load your data. Again, you’ll want to load the folder where all of your Ahrefs exports are saved, and select “Combine and Load”:
In the query editor, remove the columns you won’t need. Select Add Column in the top menu and select Conditional Column:
Create a Conditional Column for the data you imported from the folder. If you didn’t change the Ahrefs file names for your exports, then you’ll have a column titled, Source.Name. We used this column to create our “Domain” column. The conditions were added were as follows:
This way, you can filter by specific competitors to see which sites they’ve enlisted as partners.
Using your new conditional column, create a Treemap to show which Domains (the “Group”) have the highest amount of unique referring URLs (the “Values”).
The below example is a treemap:
Then, create a table using Referral Page URLs, distinct count of ranking keywords, the average of domain rating, and the first Link URL (which is the backlink to the respective domains you identified in Step 1).
Filter by domain rating to see the high-authority sites with affiliate links to competitor sites. It should look like this:
Lastly, create filters. We used a Text Filter for the Link URL column to filter by type of URL parameter being used by the affiliates (the parameters we identified in Step 2). We also used a Slicer to filter by the various Domains or competitors. And finally, we used a Stacked Bar Chart to depict what competitors had the most unique backlinks:
Your dashboard should look like this:
Now you can drill down and look at potential affiliate partners at scale!
If you do this for your business or client, let us know how you made out in the comments!