More Advanced Click & Keyword to DirectTrack System Conversion Tracking

If you’re in a highly competitive niche where pay-per-click, clicks cost 2-10$ per click you need to do everything you can to improve the conversion ratio’s of those highly expensive clicks. As I’m rather new to Pay-Per-Click advertising I’m learning as I go probably just like a lot of you.

In checking my stats and watching my logs (making myself crazy) I found that certain days of the week never seemed to produce conversions. So I decided to stop advertising on those days and what do you know my conversion ratio’s and return went up quite a bit. I don’t know why but certain days of the week people like to click ads but not take action for a couple of my niches. With that said I was wondering if there were certain times of the day that leads didn’t convert. Now I’ve run a lot of BlackHat leads to offers in the past and being as it didn’t matter when what converted I had never looked into tracking at this level, sure wish I would have. Needless to say, times of the day made quite a difference and increased my return on investment even more.

If you’ve been following my blog you’ve probably read through my blog you probably saw the last script I put up on tracking which keywords convert for you. In wanting to check if things converted at certain times of the day the old tracking method I was using wasn’t going to cut it anymore. Because DirectTrack, which is the affiliate system Copeac, CPAEmpire and various other smaller companies I work with run on, doesn’t give you a time only a date. So we need to reinvent how we’re going to do our tracking. Rather then by keyword like in the last tracking script we’re going to track the specifics of every click to the database.

The general premise is that rather than having a keyword ID in the database with a count attached to it we’re going to track each and every click that comes through the site. And rather then having the keyword ID passed for the subid to match those up we’re going to pass the click ID. Then when we go to pull and analyze what converted we can see where the click originated, where the click came from, what keyword triggered it, what time it was, etc.

With this layout there’s going to be a tracking.php file that will be included in the landingpage.php file that will record the click and then return the id for the recorded click. Then that id will be replaced in anything that references the affiliate link.

Layout:

First we’ll have our file somelandingpage.php this is the actual page the user is going to be going to. Very simple for this example we’re going to just make it pretty much blank. We’ll just add a couple things so we can test things properly. Of course you can just add whatever you want to the page later or move the code to a real landing page.

<?
$site = “mydomain.com”;
include(“tracking.php”);
?>
<h1>My landing page</h1>
<a href=”http://www.afffiliatelink.com/affid/offerid/<? echo $id; ?>”>link</a>

That’s it! Let’s talk about what’s going on with somelandingpage.php.

First we’re setting the variable $site which will be used in tracking.php which you’ll find out about very shortly. This is going to be the value to track which site or page the click that’s being recorded came through. In our example case we’re going to use a fictitious domain, mydomain.com. So in the database the click will be recorded as coming through mydomain.com. This will allow us to track a lot of domains or pages in the same db. Something else you might do is if you’re split testing landing pages you can just setup two different ones then rotate between them and give them different site names. Then the clicks will be tracked for each page in the db.

Next we’re including the file tracking.php file which will be what we discuss next. If you don’t know, whenever we include a file that means execute the code in the file just like it was written in the somelandingpage.php file itself. It executes it inline just like it was in it. This allows us to make one tracking file and then include it in a lot of pages.

Then we close out the PHP code and put a little HTML just so we can see on the screen that that page actually loaded. The last line of the file is going to give us the link to an offer with the click ID for the click data we inserted. You’ll see how this works shortly. Just understand that we’re going to passing the click ID for the subid in the affiliate link. Then we can go back later and match up the subid on the conversions and match it up with the click ID to see everything about the originating click.

Now lets talk about the tracking.php file.

So tracking.php is going to be the file that’s included on the landing page. Now keep in mind if you have 100 pages on a site and you want to track each one all you have to do is do is set the $site=”site.com/thispage.html”; and include(“tracking.php”); then it’ll start tracking clicks on that page as well.

The gist of the script is

1. we connect to the db
2. we get some environment variables that are set by the server (ip address, referrer, useragent)
3. get data being passed in the url. I’m tracking keyword and source of the click. Now we could get the source through the referrer but setting it when we run our ads is a lot more reliable.
4. we insert the data we gathered into the database.
5. get the id of the record we just inserted.
6. remember the affiliate link in the previous file? Well now we just echo out the id into it where the subid goes and we have the click tracked in the subid. So if that click converts we can go back and see where the click came from, what time it was, what keyword it was. All important stuff wouldn’t you say?

<?
//connecting to the database we setup
DEFINE(‘DB_HOST’,’localhost’);
DEFINE(‘DB_USER’,’login’);
DEFINE(‘DB_PASS’,’password’);

DEFINE(‘DB_PRIMARY’,’tracking’);

mysql_connect(DB_HOST,DB_USER,DB_PASS);
mysql_select_db(DB_PRIMARY);

// Server variables
$ip = $_SERVER[‘REMOTE_ADDR’];
$referer = $_SERVER[‘HTTP_REFERER’];
$useragent = $_SERVER[‘HTTP_USER_AGENT’];

// capturing data we passed in the url
//ie. http://domain.com/page.php?k=my+keyword&s=yahoo
// I’ve added the engine the click is coming from as
// I’m starting to branch out to other engines now
$keyword = trim($_GET[‘k’]);
$source = trim($_GET[‘s’]);

$sql = “INSERT INTO `clicks` (`keyword`,`source`,`ip`,`referer`,`useragent`,`time`,`site`) VALUES (‘$keyword’,’$source’,’$ip’,’$referer’,’$useragent’,NOW(),’$site’)”;
mysql_query($sql);

$id = mysql_insert_id();

?>

Lastly here’s the Database structure you’re going to need. I created a new database called “tracking” and added the table “clicks” with fields for each data piece we want to hold. Here’s the SQL for the click tracking table:

CREATE TABLE IF NOT EXISTS `clicks` (
`id` double NOT NULL auto_increment,
`keyword` varchar(255) NOT NULL,
`source` varchar(255) NOT NULL,
`ip` varchar(15) NOT NULL,
`useragent` varchar(255) NOT NULL,
`referer` varchar(255) NOT NULL,
`time` datetime NOT NULL,
`site` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

Now all you have to do is go and pull your advanced report from your directtrack based affiliate system. Or any affiliate system that supports SubId’s. And match up the subid’s on your conversions to your click ID’s. Then you know exactly what’s converting for you when.

Next post I’ll cover Building a table for the conversion records and how to automate pulling the data from the directtrack system to populate it. Also I might show you how to make a little system to see what Conversion ratio you’re getting for what words. The last thing which I haven’t implemented myself if finding what bids on words are costing, which could be referenced from reports from the ppc engines.

Stay Tuned!