Skip to content

More Advanced Click & Keyword to DirectTrack System Conversion Tracking

keyword tracking statistics

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!

17 Comments

  1. Rick Rick

    Do you know or is there a source that gives the subid restrictions for all the major networks? Just want to make sure I’m passing acceptable data.

    Things like maximum length (I think I saw 50 for copeac)… can the subid include non alpha-numeric characters like dashes or underscores (i.e. if your short term for an adword group was ab-12)?

    Thanks again. I’ll be modifying my tracking (thanks to your original script) to something like this soon.

  2. Marc Marc

    Yes…even better than the other script I just discovered here . You rock man =) I would love to see that conversion system here too.

    Btw. HOW can I best match up the click id with the subids from my affiliate report? What is the best way to do this…? Manually? (lol)

  3. Per Per

    Awesome posts…now i cant wait for your next post on how to match the click id:s with the SID:S i an automated way. Please show us soon.. =)

  4. spazdr8cr spazdr8cr

    this is a huge help, thanks!

    i think there is a mistake though. in the mysql insert code there should not be quotes around the table name or the field names, only around the variable names in the second half of the statement. right? at least that’s what works for me.

    also i couldn’t figure out what these are:

    $keyword = trim($_GET[’k’]);
    $source = trim($_GET[’s’]);

    i get an undefined index error. but that doesn’t matter, it’s all in the referer anyway isn’t it?

  5. That code could use some mysql_real_escape_string

    if someone knew u were using that code they could easily mess up ur data.

  6. Definately agree’d Wes. This code is all not secure as it should be and should have some code input cleaning added to it. Use with caution 😀

  7. Al Al

    Smacks, this is butter but how do you (if you do) split-test Landing Pages?

  8. Niz Niz

    Instead of putting $site=”site.com/thispage.html”; in every landing page you create, why not just add the following in tracking.php

    $site=$_SERVER[‘HTTP_HOST’] . $_SERVER[‘REQUEST_URI’];

    Will save you a few seconds in every landing page you create.

  9. Careful with this, the _GET variables aren’t cleaned and are ripe for SQL injections… you’ll want something like

    $keyword = mysql_real_escape_string($keyword);
    $source = mysql_real_escape_string($source);

    Before your SQL call

  10. Agree’d 100% Dankind, I think there’s a disclaimer on this site somewhere to that effect, but I should probably make it mo pronounced.

  11. I will appreciate if you provide more details on this. Thanks.

  12. Nicky Zakrzewski Nicky Zakrzewski

    Hi, I cant understand how to add your site in my rss reader. Can you Help me, please 🙂

  13. davey davey

    Hello is anyone still here? I have an important question.
    I’m using direct linking from yahoo to merchant. I understand I need to have a landing page. how do you set up this script so the landing page redirects to the merchant while still harvesting all the data.
    How do I connect the data collected on the landing page with the sale on clickbank. Is there an tid that is passed on the clickbank in the transactions report that will associate with data collected on the landing page ergo knowing what click generated that sale?
    Is all this data in this script cloaked?

  14. Do you accept guest posts? I like the choice how you wrote More Advanced Click & Keyword to DirectTrack System Conversion Tracking Ideas From an Affiliate, Network Owner and Advertiser, I am in this topic for ages and I would love to write few stories right here if you ever agree.

  15. When you’re trying to sell software through PPC campaigns, your goal is to obtain qualified leads to your software selling business, not to get as much and as untargeted traffic as possible. And that’s when negative keywords come in. Or out, actually 🙂

Comments are closed.