Archive for December, 2010

Automating the Back End

December 8, 2010

When I first got into affiliate marketing a couple of years ago, my plan was to have thousands of sites, each earning a little and adding up to a lot.

About a year ago I changed tack and went for a few main sites with a lot of satellite sites that leached off the main site’s database and would feed links to it and provide link-love. This has worked out reasonably well but having established myself in a limited number of irritatingly seasonal niches, I am now back thinking about going for a broader approach and this led me to dig out my earlier plans and notes on how I wanted to develop this business.

If you are going to have a lot of sites then a key aspect of this is automation of the business processes operating behind the scenes. Having spent the last year copying and pasting sales data from affiliate program reports and occasionally taking the trouble to tie up the clicks with my server logs, I decided to do something about making this easier and I’ve just finished 2 weeks of development work integrating my web server log file database with my sales record database and adding what Commission Junction call an sId field (custom tracking option) so all my affiliate links now carry a 15 digit base-64 encoded value that carries the client IP, server date/time of the click and a code indicating the site the click came from. This turned out to be crucial as many programs (such as Commission Junction) don’t provide much in the way of useful data. In fact if you want a click date you cannot even get this through the CJ API and have to download an online report as a CSV file.

Another aspect of this was using as many APIs as I could so my system can now automatically retrieve transaction (sales) data from Paid On Results, Affiliate Window etc. which are then linked to the log file records giving a rich record describing not only the sale, but the page the click came from, the path through my site the purchaser took and the original referring url including any search phrase they may have used.

Was this worthwhile? I wasn’t sure before I started but having completed the exercise I have to say it was. I now have an integrated system where I can generate plots like the one above showing visitors to one of my sites (purple) alongside active visitors (orange: those clicking affiliate links) across a 12 month period, along with thicker and darker weekly and monthly trend lines. More importantly, I can now drill down and see which referring sites and search terms are bringing me actual purchasers rather than just visitors, and I can now reconsider doing some PPC as I can trace the origin of every sale and see whether my advertising is profitable or not.

If you are full-time into affiliate marketing and equipped to do something like this then I would say that this is definately worth setting up. It is going to save me in the order of 4-6 hours a week and gives me better intelligence on how my sites are performing than I’ve ever had before.

The only down side is getting the bl**dy APIs working. To help you along the way, here’s some comments on the best and worst APIs that I have implemented over the last couple of weeks.

Top of the heap for both the data and the ease of setting up is PaidOnResults. Their API is actually a system for retrieving a CSV file containing report data but it’s easy to parse and contains all the information I need. No problems with parsing or interpretation and you can fetch records based on the last time there was a change (so a search for sales yesterday will include repeats of records from 2 weeks ago that yesterday went from pending to confirmed).

Affiliate Window also gets a special mention for being both good and bad. The good side of their system is the quality and usefulness of the data they give you. The bad side is that their system does not properly support UTC encoded dates like their WSDL file suggests it will. To get this working in .NET (which supports and sends UTC dates) I had to replace the date objects called for by the WSDL with string objects, and then change the properties on the data carrying objects so these string could be populated with the non-standard date/time values required by the Affiliate Window web service.

I’ve implemented a couple of others but will only mention one more and that’s Commission Junction. Bottom marks here as it not only gives incomplete data (no click date/time) but you can’t query based on most recent update date (so you need to manually check old events to see when they go confimed), and you can only query single dates at a time rather than a date range.

Despite apparently having the best infrastructure to support API developers (extensive online documentation and even a dedicated API developers forum) their documentation has a number of silly errors and omissions that really throw you such as typos in attribute names and there is a maximum of 25 queries per second, as I eventually realised. You would think the forum would be a plus but in fact there’s no CJ input to it and it’s full of people who are just as lost as you are!

Finally I will mention the reason for this post. Given the scarcity of information on these APIs I am wondering about doing a CodeProject article describing the APIs as implemented in .NET. If anyone out there would realistically find this useful then let me know as this would probably be enough to prompt me into writing the article.