Ideas From an Affiliate, Network Owner and Advertiser

Sorry for the long time between posts. Just things have been very busy lately. Here’s an update to the click tracking code for tracking your conversions to include yahoo and msn pay per click. I just pulled this direct out of my system. This also includes a way to exclude bots by IP. Using the list from iplists.com I in a file called ip_list.txt in the same directory as you tracking file. So that your tracking doesn’t record bot visits. Below have included the updated sql schema for click tracking, it’s a little messy and could be better but just haven’t had time to improve it. If someone has a better click tracking schema around I’d be interested in seeing it.

So our urls to use to track

Yahoo Search Marketing:

http://www.yourdomain.com/index.php?s=ysm&n=1

To make this work properly you need to enable tracking URL’s in your account. This will pass all the appropriate variable automatically.

Google Adwords:

http://www.yourdomain.com/index.php?s=ggl&qs={keyword}&k={keyword}&n=1

This could be improved to scrape the query out of the referer and use that for the Query String in the table, it’s on the list.

MSN Adcenter:

http://www.yourdomain.com/index.php?k={Keyword}&s=msn&qs={QueryString}&mt={MatchType}&oii={OrderItemId}&ai={AdId}&n=1


<?
$keyword = addslashes(trim($_GET['k']));
session_start();
DEFINE('DB_HOST','host');
DEFINE('DB_USER','username');
DEFINE('DB_PASS','password');
DEFINE('DB_PRIMARY','tracking');
@mysql_connect(DB_HOST,DB_USER,DB_PASS);
@mysql_select_db(DB_PRIMARY);
if(mysql_ping() == true)
{
if(!isset($_SESSION['id']))
{
// Server variables
$ip = $_SERVER['REMOTE_ADDR'];
$referer = $_SERVER['HTTP_REFERER'];
$useragent = $_SERVER['HTTP_USER_AGENT'];
$source = addslashes(trim($_GET['s']));
$site = addslashes(trim($site));
$niche = trim($_GET['n']);
if($niche == ''){ $niche = 1; }
$loc = trim($_GET['loc']);
if($loc == ""){ $loc = "us"; }
if($source != "ysm")
{
$keyword = str_ireplace("_"," ",$keyword);
$query = addslashes(trim($_GET['qs']));
$matchtype = addslashes(trim($_GET['mt']));
$item_id = addslashes(trim($_GET['oii']));
$ad_id = addslashes(trim($_GET['ai']));
$clickid = addslashes(trim($_GET['gclid']));
}else{
$query = addslashes(trim($_GET['OVRAW']));
$keyword = addslashes(trim($_GET['OVKEY']));
$matchtype = addslashes(trim($_GET['OVMTC']));
$ad_id = addslashes(trim($_GET['OVADID']));
}
if(!CheckBotIp($ip))
{
$sql = "INSERT INTO `clicks` (`keyword`,`source`,`ip`,`referer`,`useragent`,`time`,`site`,`querystring`,`matchtype`,`orderitemid`,`adid`,`clickid`,`niche`,`country`) VALUES ('$keyword','$source','$ip','$referer','$useragent',NOW(),'$site','$query','$matchtype','$item_id','$ad_id','$clickid','$niche','$loc')";
//echo $sql;
mysql_query($sql);// or die(mysql_error());
$id = mysql_insert_id();
$_SESSION['id'] = $id;
$_SESSION['keyword'] = $keyword;
$_SESSION['referer'] = $referer;
}
}else{
$id = $_SESSION['id'];
$keyword = $_SESSION['keyword'];
$referer = $_SESSION['referer'];
}
}
/**
* Functions
*/
function CheckBotIp($ip)
{
$flag = 1;
$ips = file("/home/ip_list.txt");
$ips = array_map("trim",$ips);
foreach ($ips as $listip)
{
if($ip == $listip)
{
return 1;
}
}
}
?>

Here’s the updated schema to record for all systems. As I mentioned before it’s crude and needs some work so if anyone wants to contribute I’d be happy to post it and give you credit.

– phpMyAdmin SQL Dump
– version 2.11.0-dev
– http://www.phpmyadmin.net
–
– Host: localhost
– Generation Time: Dec 14, 2007 at 02:34 PM
– Server version: 5.0.22
– PHP Version: 5.1.6

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

–
– Database: `tracking`
–

– ——————————————————–

–
– Table structure for table `clicks`
–

CREATE TABLE IF NOT EXISTS `clicks` (
`id` double NOT NULL auto_increment,
`keyword` varchar(255) NOT NULL default ”,
`source` varchar(255) NOT NULL default ”,
`ip` varchar(15) NOT NULL default ”,
`useragent` varchar(255) NOT NULL default ”,
`referer` varchar(255) NOT NULL default ”,
`time` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`site` varchar(100) NOT NULL default ”,
`querystring` varchar(255) NOT NULL default ”,
`matchtype` char(2) NOT NULL default ”,
`orderitemid` varchar(255) NOT NULL default ”,
`adid` varchar(255) NOT NULL default ”,
`medium` varchar(50) NOT NULL,
`network` varchar(10) NOT NULL,
`clickid` varchar(50) NOT NULL,
`niche` int(11) NOT NULL,
`country` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `medium` (`medium`),
KEY `source` (`source`),
KEY `time` (`time`),
KEY `useragent` (`useragent`),
KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

– ——————————————————–

–
– Table structure for table `conversions`
–

CREATE TABLE IF NOT EXISTS `conversions` (
`id` double NOT NULL auto_increment,
`click_id` double NOT NULL,
`source` varchar(50) NOT NULL,
`date` date NOT NULL,
`campaign` varchar(255) NOT NULL,
`campiagnID` varchar(20) NOT NULL,
`transactionID` varchar(50) NOT NULL,
`lineitemID` varchar(20) NOT NULL,
`commission` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `transactionID` (`transactionID`),
KEY `click_id` (`click_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

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!

Do it yourself keyword conversion tracking

I started to write this on http://www.wickedfire.com then figured I needed some new content this week.

Have you ever wanted to find out which of your keywords are converting on your campaigns you’re running?

This is actually a relatively simple thing to do if you pass the keyword as your subid. However because we never know who’s looking at our keywords we’re going to want to rewrite the keyword as a number that we have reference to. If you’re traffic to a content site or something other then a straight redirect bounce page, you can track them with a cookie or session or both. So a very simple solution for an affiliate redirect page that tracks your conversion by keyword might be.

<?
include(“dbconnect.php”);

//
$keyword = trim($_GET['keyword']);
$keyword = mysql_real_escape_string($keyword);

// Finding the keyword in the database if it’s there
$sql = “SELECT * FROM keywords WHERE keyword = ‘$keyword’”;
$sql_out = mysql_query($sql);

if($sql_out){

$row = mysql_fetch_assoc($sql_out);
$id = $row['id']; //gets the id associated with the word
$sql = “UPDATE keywords SET count = count + 1″; // sql to increment the counter for that word
mysql_query($sql);

}else{

// This chunk puts our new word in the database and grabs it’s ID
$sql = “INSERT INTO keywords (keyword) VALUES (‘$keyword’)”; // sql to insert the new keyword we don’t have cataloged
mysql_query($sql);
$id = mysql_insert_id(); // gets the id of the record we just inserted.
}

// Here we’re redirecting to the end location with our replaced keyword with the new ID
$location = “Location: http://location.com/subid=$id”;
header($location);

?>

SQL for this is just a table is very simple. Just a 3 fields.

1. id set to primary key and auto-increment
2. keyword set to varchar 200 and indexed
3. count set to double no indexing

And that should do the trick….

This will take the incoming visitor and log the keyword, then redirect to the offer based with the subid for the keyword. This is a straight redirect bounce page with replacement keyword tracking.

Now if you want to track based on them coming to a site do two thing make your page based on php. and at the top put that same code, just remove the header and location lines. Then when you generate your page replace your links with with the $id so for example that php page would look like this.

<?
// previous code here minus the header redirect
?>

<h1> here is some text</h1>
<p> here more text and a <a href=”http://destination.com/subid=<? echo $id; ?>”>link</a><p>

now you’ll see we’re calling the php inline to echo the number we’ve retrieved from the keyword above. Then when you run that page in your browser you’ll see the id’s replaced pretty slick huh.

A couple ideas for this that could be added are geoip tracking and redirection based on the location of the visiting guest. Or how about finding out where your visitors are coming from? Do you know if you’re getting natural traffic? And if you do know, do you know where’s it coming from and which of those locations are converting for you? If you found a link to your site that was bringing a lot of nice traffic that was converting it might make sense to run a banner ad or something like that there wouldn’t it?

Tracking is Simple, Powerful and fun using a little PHP ingenuity.

another freebie from http://www.OOOFF.com