Entrepreneurship
Poor man’s affiliate click tracking
A week or so ago, my friend Eric Giguere wrote about affiliate tracking for managing clickbank sales. I coincidentally happened to be working on some tracking for my own affiliate efforts with shareasale.
If you’re going “WTF?” at this point, click tracking is necessary for a popular strategy known as “adsense arbitrage”, which is the practice of using a Pay-Per-Click advertising network like Google Adwords to drive traffic to a site that promotes affiliate products, i.e., products that, when sold, pay the referrer a “bounty” or commission for driving the sale.
The idea is simple: you pay $5 per day to Google (or Overture, Looksmart, MSN, or one of many smaller players) and generate $2000 in sales for a bunch of products that pay out a commission of, say 10%. Over the course of a month, you pay out $150 to Google for the traffic and earn $200 in sales commissions. Rinse, Lather, and Repeat a couple of dozen times, and you’ve got yourself a nice little side income.
The problem with arbitrage is that tracking is difficult. You need to watch those numbers like a hawk (and have good tools for doing so), otherwise you end up spending $150 and making twenty. Or worse. That’s where tracking comes in.
So, I’m working on an affiliate program for iPods — specifically, I want to drive traffic to BeyondThePod, which buys used iPods. They run an affiliate program through shareasale that pays out $2 for each customer I drive to their site that subsequently agrees to sell them their iPod. It’s a program that hold some potential, because the payout occurs upon submission of a form, not some huge action like the customer applying for a loan (I’m pretty sure lendingtree has an affiliate program like that if you think you can:-)) or actually sending in the iPod (although I’m sure they’ve done the math on that to make it worth their while).
At any rate, I decided to place some ads on the google content network to see what I could come up with. Now, google adwords requires one to enter the keywords combinations that are appropriate for the ad, and each keyword combination has its own unique pricing. So, “ipod” has a relatively high ad rate of around a dollar, but “broken ipod help” is only a nickel. Obviously, the number of impressions shown with the former are higher, but it’s a lot harder to make a buck if my click-through costs me $1 and I only make $2 when the customer does what I’m hoping they do, namely, send BeyondThePod their old iPod.
But how do I know where the money is? It could be that I get 200 click throughs on “broken ipod” that cost me $10, but I only earn $8 from the affiliate commissions. That’s a bad deal. On the other hand, $12 in earnings is definitely worth it, right? So I need a way to track where the click-throughs are coming from, the search terms used, etc.
So I wrote a little mini-app that does this using PHP and Mysql. I decided I needed two pages and a single database table to store/track the results.
The first page harvests the pertinent data, like the HTTP Referer (the URL the user just navigated from), the user’s IP address, and some tracking code(s) that I create/control. I decided to not place any limitations (other than a 255 character limit) on my tracking code and just create them on the fly when I create the ads. This is bad for database scalability and efficiency (if you don’t see why, you need to study the computer science concept of database normalization. Or leave me a comment and I’ll do my best to extrapolate a brief explanation.), but great for getting the whole app written in about an hour. It’s a hack job, but it works.
This page then dumps the harvested data into a single database table.
The 2nd page just reads data out of this table and provides some basic reporting. I’ll probably update this as I move forward to provide additional data. If my initial results turn out to be profitable, of course.
Here’s the source code, containing the SQL needed to create the table in question and the two PHP files as described above.
If you have any feedback on this tool, please leave a comment or contact me directly. If 25 or more people respond saying they want a more full featured version and are willing to offer a few bucks to see this developed into a fully functional application, I’ll actually write the scalable version that works across multiple affiliate networks (clickbank, anyone?), arbitrage campaigns, and users. I’ll even host the whole damn thing so it’s turn-key.
Even if you’re not interested in supporting the effort with your dollars, drop me a note if this interests you, because I’ll be much more inclined to update the source code with my ongoing efforts if I know there is interest.
Happy tracking!
Discussion
4 comments for “Poor man’s affiliate click tracking”
Post a comment
I think google analytics would do what you are trying to do, and it’s for free.. right?
Google Analytics allows you to track data for anywhere that you can place the Analytics JS Script, i.e., your own site(s).
Affiliate arbitrage calls for driving traffic from elsewhere, e.g., the AdSense content network, which displays ads on non-google sites, including Really Smart Guy, and sending that traffic to affiliates.
So the traffic (or, more accurately, users/customers) never touches your site at all unless you employ a tool like the one I’m using as described in this post.
Google Analytics will therefore never gather any relevant data, because the tracking script doesn’t ever run.
Make sense?
Thank you for the write up and script. We are looking at adding an affiliate program to our site. We want to give mothering and parenting bloggers an incentive to promote us.
I love the simplicity of your script. Our team has been looking at commercial options such as cj.com and performics. Am I missing something or doesn’t your script provide all the major functionality that they do? I imagine they have fancier reporting, but that can be done later.