Hey
Lurked anonymously for a long time, then lurked with this account. Figured it was time to give something back to a place I’ve learned a lot from.
This is a guide on how to find virtually every affiliate a website has. You could use that to find prospective affiliates to work with for yourself, see affiliates that have success and reverse engineer their methods or whatever.
You will need:
Majestic or Ahrefs (I prefer Majestic)
Excel (MS/Libre/Open Office)
Ayima Redirect Path Chrome Extension (Optional)
Free Huge CSV Splitter (Optional)
Scrapebox (Optional)
SEMRush (Optional)
URL Profiler (Optional)
Let’s get started
First step is to obviously choose a website you want to research. For this example, I’ll use a popular betting and casino site: paddypower.com
After that, using either Majestic or Ahrefs, have a look for any “affiliate footprints” - URLs linking in that look like they are affiliate tracking links.
We have two clear examples here for paddypower.com:
and:
Probably an old system and a new system.
If you’re struggling to find footprints in the link profile, run a quick google search. “[Brand/Product] Review” usually does the trick.
Click on one of these results. This is where the Ayima extension comes in. Find your big CTA button and click on it and see where you end up:
to:
There be footprints. Make a note of all that you find.
If you wanted to verify those links, you insert the URLs themselves into majestic or Ahrefs and see where the link comes from:
All look like affiliate sites to me.
Now, you could find all the affiliates you need that way, but obviously when you’re doing this in bulk on huge websites, (45k referring domains, for example), that’s not gonna work.
So here is how we do it in bulk. First, download your Majestic report (fresh or historical index, whatever you fancy).
Solid.
Once all downloaded and ready, open up your Excel file and start...oh:
Yeah, this will happen. Excel has a line limit of about 1,000,000 lines, so when it tries to open files larger than that, it will break. You can still access the file, but you won’t get all of the data that you want.
However, never fear, the Free Huge CSV Splitter is here: hxxp://sourceforge.net/projects/splitcsv/ - as it says, it will split the file up into smaller pieces for you. I usually go for 800,000 lines just to be safe
Once ready, open up one of your files. Dig out the footprints you had before - for this example, I’ll use the “pid=” and “aff_id” footprints we found earlier.
Highlight cell A1, then press Data > Filter at the top of the Excel sheet:
(I’m sure you all know how to do Excel filters, but I’ll run through just in case)
Press the drop-down button on the A1 cell and go to text filters -> contains:
In the popup, add your footprints - make sure you select the “or” rule for best results if you have found more than one:
Apply the filter. You now have a list of websites that linked to paddypower.com with one of their affiliate tracking links. If you end up with something like this:
You’re rolling son. If not, try another one of your files. If you still have nothing, you fucked up with your footprints somewhere.
Time to make sense of these results. For me, all I’m interested in is the root domain of the affiliate website. You could find every single URL if you want to, but in this run-through I’m just going to take out the root domain of each affiliate website and later one we will qualify that list.
Highlight the entire B column. What I do now is open a new Excel tab for each step, to keep things smooth and to ensure I don’t fuck up somewhere. Paste column B into a new tab, now it’s time to use the “text to columns” function:
Select “delimited” and then “next”, on the next screen, check “other” and insert the “/” symbol. At that point you can just press “finish”
Copy and paste the C column into a new tab. Then, with all cells highlighted, do a quick find and replace (Ctrl+F):
Find all www’s and replace them with blanks. Basically, you’re trying to strip all the URLs down to root domain level. We want to do this so that it is both a) easier to remove duplicate and b) easier to prospect the domains later.
Removing the duplicates is easy, just go to:
And that’s it. You’ll now have a raw list of root domains that have linked to the source website with an affiliate link.
Rinse and repeat this for however many files you have. Make sure you de-dupe your ‘master list’ again.
You could leave at that with your big list of websites, but wouldn’t it be better to know the better websites in the list? You could run an Alexa check in Scrapebox, but that isn’t the best indicator. So what I like to do is to use the SEMRush API in the URL Profiler tool.
This will give you, among other things, the SEMRush traffic score for all the domains in your list, including their organic and paid traffic. Generally, the higher the traffic, the more active the affiliate is. By no means the ultimate test, some lower traffic sites might be gold mines, but a good starting point.
First we’ll paste all of those URLs into the tool:
Now you can check any stats you’d like the tool to run. I’m going to run Majestic stats as well as SEMRush. When selecting SEMRush, you’ll get this pop-up:
You can ask URLProfiler to check the root domain’s score in a number of different domains. Leaving it to the TLD is fine, but say, for Paddy Power, you only wanted to focus on affiliates getting UK or DE traffic, you could do that too.
Click “Run Profiler” and let it do its thing:
May take awhile depending on the size of your list. I’d highly recommend the Server or Agency plan on URL Profiler for this. It does a ton of other stuff as well - it’s an invaluable tool for me.
When done, you will have a complete list of affiliates which you can then sort and filter by SEMRush rank, Majestic TF/CF, Majestic Topical Trust (nice to single sites down by category) or whatever you like.
Hope it helps a webmaster or two.
PS - You didn’t think I’d do all this and not share with you the Paddy Power affiliates, did you?
hzzps://mega.co.nz/#!wkVBWIbA!2BkwqjX9Fia5ixoYyEWceZ1dZ9TvNdhS30qQ_IYPzMc
Lurked anonymously for a long time, then lurked with this account. Figured it was time to give something back to a place I’ve learned a lot from.
This is a guide on how to find virtually every affiliate a website has. You could use that to find prospective affiliates to work with for yourself, see affiliates that have success and reverse engineer their methods or whatever.
You will need:
Majestic or Ahrefs (I prefer Majestic)
Excel (MS/Libre/Open Office)
Ayima Redirect Path Chrome Extension (Optional)
Free Huge CSV Splitter (Optional)
Scrapebox (Optional)
SEMRush (Optional)
URL Profiler (Optional)
Let’s get started
First step is to obviously choose a website you want to research. For this example, I’ll use a popular betting and casino site: paddypower.com
After that, using either Majestic or Ahrefs, have a look for any “affiliate footprints” - URLs linking in that look like they are affiliate tracking links.
We have two clear examples here for paddypower.com:
and:
Probably an old system and a new system.
If you’re struggling to find footprints in the link profile, run a quick google search. “[Brand/Product] Review” usually does the trick.
Click on one of these results. This is where the Ayima extension comes in. Find your big CTA button and click on it and see where you end up:
to:
There be footprints. Make a note of all that you find.
If you wanted to verify those links, you insert the URLs themselves into majestic or Ahrefs and see where the link comes from:
All look like affiliate sites to me.
Now, you could find all the affiliates you need that way, but obviously when you’re doing this in bulk on huge websites, (45k referring domains, for example), that’s not gonna work.
So here is how we do it in bulk. First, download your Majestic report (fresh or historical index, whatever you fancy).
Solid.
Once all downloaded and ready, open up your Excel file and start...oh:
Yeah, this will happen. Excel has a line limit of about 1,000,000 lines, so when it tries to open files larger than that, it will break. You can still access the file, but you won’t get all of the data that you want.
However, never fear, the Free Huge CSV Splitter is here: hxxp://sourceforge.net/projects/splitcsv/ - as it says, it will split the file up into smaller pieces for you. I usually go for 800,000 lines just to be safe
Once ready, open up one of your files. Dig out the footprints you had before - for this example, I’ll use the “pid=” and “aff_id” footprints we found earlier.
Highlight cell A1, then press Data > Filter at the top of the Excel sheet:
(I’m sure you all know how to do Excel filters, but I’ll run through just in case)
Press the drop-down button on the A1 cell and go to text filters -> contains:
In the popup, add your footprints - make sure you select the “or” rule for best results if you have found more than one:
Apply the filter. You now have a list of websites that linked to paddypower.com with one of their affiliate tracking links. If you end up with something like this:
You’re rolling son. If not, try another one of your files. If you still have nothing, you fucked up with your footprints somewhere.
Time to make sense of these results. For me, all I’m interested in is the root domain of the affiliate website. You could find every single URL if you want to, but in this run-through I’m just going to take out the root domain of each affiliate website and later one we will qualify that list.
Highlight the entire B column. What I do now is open a new Excel tab for each step, to keep things smooth and to ensure I don’t fuck up somewhere. Paste column B into a new tab, now it’s time to use the “text to columns” function:
Select “delimited” and then “next”, on the next screen, check “other” and insert the “/” symbol. At that point you can just press “finish”
Copy and paste the C column into a new tab. Then, with all cells highlighted, do a quick find and replace (Ctrl+F):
Find all www’s and replace them with blanks. Basically, you’re trying to strip all the URLs down to root domain level. We want to do this so that it is both a) easier to remove duplicate and b) easier to prospect the domains later.
Removing the duplicates is easy, just go to:
And that’s it. You’ll now have a raw list of root domains that have linked to the source website with an affiliate link.
Rinse and repeat this for however many files you have. Make sure you de-dupe your ‘master list’ again.
You could leave at that with your big list of websites, but wouldn’t it be better to know the better websites in the list? You could run an Alexa check in Scrapebox, but that isn’t the best indicator. So what I like to do is to use the SEMRush API in the URL Profiler tool.
This will give you, among other things, the SEMRush traffic score for all the domains in your list, including their organic and paid traffic. Generally, the higher the traffic, the more active the affiliate is. By no means the ultimate test, some lower traffic sites might be gold mines, but a good starting point.
First we’ll paste all of those URLs into the tool:
Now you can check any stats you’d like the tool to run. I’m going to run Majestic stats as well as SEMRush. When selecting SEMRush, you’ll get this pop-up:
You can ask URLProfiler to check the root domain’s score in a number of different domains. Leaving it to the TLD is fine, but say, for Paddy Power, you only wanted to focus on affiliates getting UK or DE traffic, you could do that too.
Click “Run Profiler” and let it do its thing:
May take awhile depending on the size of your list. I’d highly recommend the Server or Agency plan on URL Profiler for this. It does a ton of other stuff as well - it’s an invaluable tool for me.
When done, you will have a complete list of affiliates which you can then sort and filter by SEMRush rank, Majestic TF/CF, Majestic Topical Trust (nice to single sites down by category) or whatever you like.
Hope it helps a webmaster or two.
PS - You didn’t think I’d do all this and not share with you the Paddy Power affiliates, did you?
hzzps://mega.co.nz/#!wkVBWIbA!2BkwqjX9Fia5ixoYyEWceZ1dZ9TvNdhS30qQ_IYPzMc