Data mining for affiliates: How to discover their networks

To recap the background to this piece, I’m founder of 90 Digital, the igaming media agency (SEO/online PR / technology), and back in November 2015 I launched Oshi casino in conjunction with SoftSwiss. We began as bitcoin-only and then started to accept euros early this year. One of the greatest successes for Oshi has been affiliate marketing. If you’re not an affiliate, here is a quick explainer.

Affiliate marketing works because affiliates effectively take on the risk of finding new customers. In other words they set up the websites, do the SEO, get the traffic and if it converts, they get a revenue share. It’s been extremely important for Oshi because it’s meant we can grow without pre-financing new customer acquisition i.e. we pay when we get revenue from a customer. And in many ways, affiliate marketing is a critical pillar within the igaming ecosystem.

At the end of this article is a link to my agency website where there is much more granular detail on the process, assuming you want to actually do this yourself and access all the affiliate data talked about in this article.

Problems
Competition. In my experience most affiliate managers know a lot about striking a deal, building relationships and being good on their numbers. And that’s about it. As a result, these affiliate managers focus on a few winners and tend to ignore everybody else. It’s not their fault. It’s just the system they are in. But what it does mean is they are very inefficient at making use of those affiliates outside the top 20. If you are smart enough to go to any of the affiliate conferences, you’ll see how top affiliates are treated like pop stars.

The diva effect
Because of the competition, you end up with every operator courting a small number of successful affiliates. In turn, because of the lack of supply and the massive demand, operators will then strike really good deals with those few affiliates.

That’s great if you’re the one getting a massive revenue share or a really good CPA, but the economics for an operator makes dealing with successful affiliates quite difficult. Essentially if you offer a revenue share above 50%, you’re getting into negative return territory when you account for overheads and so on.

The long tail
There’s a wonderful book called The Long Tail, and it says: “Products in low demand or with low sales volume can collectively make up a market share that rivals or exceeds the relatively few current bestsellers and blockbusters.” Or when you get past the small group of winners who appear to ‘take all’, there’s a long list of ‘others’, who, when added up, will be greater than the few ‘winners’.

For instance, a typical bookmaker will have 15,000 affiliates on its list. Twenty affiliates will make a ton of cash and it tails off very quickly after that. That leaves you with roughly 14,700 affiliates who don’t produce a great deal, but in aggregate are massively important. If you can tap into the long tail of anything, it could be massively rewarding.

Funnily enough, ‘long tail’ is the big idea behind my casino; Oshi, i.e. to access the long tail of casino games. If you make it easy to find these games, people will start playing them and therefore get more engaged and in turn generate more revenue. Obvious, but no one else does it for many reasons. Applying this long tail principle with affiliates, if you can efficiently target those affiliates who aren’t on the radar, in aggregate they’ll bring in lots of business and I’ll get brand awareness across more of the Internet (see Figure 1).

The affiliate side of long tail
Since the main negotiation lever is revenue share, if you’re not one of the top affiliates then it’s very difficult to get a decent deal. In turn it’s harder to get enough revenue to reinvest properly and so you end up in a cycle where maybe you will never break through.

Talent
Statistically speaking if you’re an affiliate you’re most likely to not be successful. Sorry to say that, but it’s true. However, you might be on the edge of getting it right. I’ve always believed there’s a large number of affiliates who could break through, but it’s a case of being able to identify you and then support you. It’s a huge subject and one I might write about in the future.

The main point is: have systems and processes to make it easy to find what you want. In this case I want to know who the affiliates are within bitcoin casino and which casinos they’re working with. As an affiliate I would like to know who is successful on search, who has done what deals with which operators and who has site networks (disclaimer: I’m also an affiliate and own a share of a fairly big sports news website). From this I can take ideas from sites that are doing well, target certain operators and see if there are any interesting link profiles or clues to why they rank.

A typical bookmaker will have 15,000 affiliates on its list. 20 affiliates will make a ton of cash and it tails off very quickly after that. That leaves you with roughly 14,700 affiliates who don’t produce a great deal, but in aggregate are massively important. 

Tricks of the trade
Having been in online marketing since 1999 (yes, the dawn of Internet marketing) and having focused mostly on SEO, I’ve learnt a few things about data mining that have become extremely useful. One of those is mining for affiliates.

Data mining
Warning! Having written the data mining process flow up, I’ve realised it’s easy for me, but actually fairly complex if you’re not into data. Saying that I’ll cover everything I think you can do yourself, but if that’s not going to happen, reach out to me. If you’re not technical or not interested in the process, just read to the end where I have a web address you can go to where you can download the spreadsheet that I talk about here. I also go into much more detail on the data mining process.

Before you start
Just like baking a cake, you’re going to need certain ingredients in this case; tools.

• Link analysis tools. Either a single tool or a combination of them is ok. But generally it’s better to use a single tool, because you have a single frame of reference. My favourite is Majestic.com.
• Search results tool. I use SEMrush.com It tells you what keywords a domain is ranking for across 30 different countries. It’s enormously powerful, has great databases and has great multi country coverage. The other huge thing is it’s cheap access to its API. The API is critical for what you’re about to do.
• Datagrabber. This is a tool that we built within my agency 90 Digital, that uses Excel to query SEMrush API. It then returns data which we can then process. Datagrabber only works on Excel 2013, so if you can’t get Datagrabber working, then it’ll be a case of manually extracting the data from SEMrush (quite a big process).

FIRST STEP: IDENTIFY THE OPERATORS YOU’RE INTERESTED IN
When I did this analysis for Oshi, I focused on a segment: bitcoin casinos. Presumably you will have a market focus, maybe it’s UK casinos or Curaçao casinos… It’s important to pick a niche that isn’t too big when you’re learning this process. Otherwise you end up swimming in data and that’s not fun. Go and search around online, look at big affiliate sites and see which casinos seem to be prominent. Pro tip: I would look for operator brands which are at the top of affiliate lists because generally they are usually the ones that convert best… but who do not do so well in organic search.
SEO is where you add value.

Find the affiliate URLs
Once you’ve got a list of casinos you’re interested in, then find an affiliate site which links to those different operators.

Important! Operators usually use redirection links, so once you find the common pattern used by an operator for all affiliate links, it’s easy to filter and identify the referring affiliate sites. For instance, with Oshi, an affiliate would link to perhaps – http://www.askgamblers.com/visit/oshi-casino-casino-review

Take that URL and run it through a redirection checker. I like this one:
http://www.redirect-checker.org. It generates the following:

Result
http://www.askgamblers.com/visit/oshicasino-casino-review
302 Found
http://ads.askgamblers.com/www/delivery/ck.php?zoneid=12&bannerid=7351
302 Moved Temporarily
http://ads.askgamblers.com/www/delivery/ck.php?ct=1&zoneid=12&bannerid=7351
302 Found
https://www.oshi.io/refer/XYZ
302 Found
https://www.oshi.io/
301 Moved Permanently
https://oshi.io/
200 OK

Obviously, the affiliate link is the one with the unique ReferrerID: https://www.oshi.io/refer/XYZ

The referrer ID generally has to be unique, otherwise it makes tracking really difficult. So with Oshi, you know any affiliate link has ‘/refer/’. Every operator will have similar footprints, because as you know they have to have tracking links.

The pivot table aggregates and organises SEMrush data around domains and makes it easy to see the aggregated ranking data across multiple countries for any domain

Majestic
Now you will need to use majestic.com. Their subscriptions start from £45 for one month. Use the fresh index if you want the latest live links going to the operator or use the historic index if you want all links going back to 2010. Then do a raw export of the backlinks. 

Important! If it’s a large operator you’re looking at, there are several ways of only extracting the data you need from Majestic. For instance, you might just want links going into a sub domain like affiliate.operator.com, because obviously that’s where the affiliate links are all going.

Excel
You now have a big ‘blob’ of data which needs to be sorted out. First is filtering the raw data. If you know about Excel filters, you know they are very powerful and easy to work with. By using these filters you can isolate the affiliate domains.

Processing the data
You now have a big list of affiliate sites that are linking out to the operators you have got data for. You could stop here and just work of the Majestic metrics, which will give you some indication of the strength of these affiliate sites but… there’s such a loose correlation between Majestic Trust Flow and actual rankings. That’s why tools like SEMrush are so critical for understanding actual rankings.

To process domains in SEMrush it’s very helpful to clean the data you’re going to use.

NEXT STEP: CLEANING URLS
There are a number of ways to do it, but one of the most handy is a formula that I found here: https://www.ablebits.com/office-addins-blog/2013/11/08/extractdomain-names-from-url-excel/

The formula takes a long address and turns it into a short address i.e. from https://www.ablebits.com/office-addinsblog/2013/11/08/extract-domain-namesfrom-url-excel/ to ablebits.com. I now have a list of affiliate domains which I can get ranking data from with SEMrush.

Before you use SEMrush, you will need to download: DataGrabber if you have Excel 2013: https://s3-uswest-2.amazonaws.com/90digital/Datagrabber_90_Digital_Latest.xlsm (search for 90 Digital Datagrabber). Otherwise, you should be able to do the same thing with SEO tools for Excel:
http://seotoolsforexcel.com/semrush/ (search for SEO tools for Excel).

With these tools, your aim is to get as much high-level information about these domains as possible. You are using these Excel tools to query hundreds or thousands of websites via SEMrush API, and from that you can get standardised data which makes it very easy to see who does well in search rankings..

SEMrush
Obviously, you’re going to need an API key and fortunately SEMrush API keys start from $15. https://www.semrush.com/apiuse/.

You set this up with Datagrabber and you then run a query.

The magic: pivot tables
If you’ve never done a pivot table, maybe now is a really good time to learn. They allow you to manipulate data make it easy to extract the information you need. In this case, the pivot table aggregates and organises SEMrush data around domains and makes it easy to see the aggregated ranking data across multiple countries for any domain, and you can also see which countries that domain is strongest in.

Final thoughts – For operators
Recapping on some points made earlier, when you combine the SEMrush data with Majestic data you can then see which affiliate IDs are being used across multiple websites and whether those sites rank or not. Essentially you can discover affiliate networks really easily. You can also identify which affiliates have relationships with which operators. You might find an affiliate which ranks really well and only has a couple of operator relationships with casinos in your niche.

Final thoughts – For affiliates
As I mentioned before, as an affiliate you will probably put the most profitable operators at the top of your ranking lists. By having this data and seeing which other affiliates have relationships with operators who convert well for you, you can also get an idea of who you are going to target for deals.

In conclusion
When data is used well, it answers questions. For operators it’s which affiliates they should reach out to, for affiliates, it’s who does well and why. The spreadsheet with all the bitcoin affiliate data and a more detailed ‘how to’ is here: http://90digital.com/bitcoinaffiliates.

Enjoy!