Skip to content

Update to PPC Click Tracking Script for Yahoo and MSN…

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;

2 Comments

  1. c0py c0py

    How is the conversions table used in this script?

    Thanks.

  2. It’s setup to work with Directtrack. As I mentioned I ripped this directly out of my system so there’s some stuff in it that doesn’t function with this script. What it’s there for is I have a script that I dump the advanced stats from directtrack and then it imports them into the table. THen I have another table to match up the clicks with the conversions so I can see what words are converting.

Comments are closed.