Skip to content

How to Track Converting Keywords for your Affiliate Marketing advertising using PHP.

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

29 Comments

  1. dealasite dealasite

    smaxor, your tutorials are awesome, thanks for sharing.

    Can you write a blog post elaborating more on the article you wrote on your main site, Affiliate Marketing vs. Adsense, specifically where you talk about building up your link networks in layers. I’ve been building my own network of sites using Eli’s squirt tools. I just want to get the framework right before I get too far in the process.

    How do you automate the creation of 10k parasite sites? Are these like free blogger accounts/pages linking to your 2nd layer or are they 10k actual sites w/ domains? I’ve heard of some BH SEO networks w/ thousands of domains/sites/hosts, that’s why I’m asking.

    You mentioned your 2nd layer sites were 301 redirects of websites you had purchased. Could your 2nd layer also be relevant sites that you build up yourself, linking to the WH site? Some guys on the squirt forum were talking about taking 1000’s of irrelevant links (3rd layer links), running them through a intermediate site that’s highly relevant to the WH site (2nd layer link), and finally 1 strong, heavy link to the WH money site. What are you thoughts on that? Maybe it’s smarter just to buy up aged sites and passing the weight like you were mentioning…

    I know this can get very complicated, but I bet I’m not the only one that would be interested in a post on this topic.

    Thx!

  2. sure I’ll see if I can write something in more detail in the next few weeks.

  3. Rick Rick

    Hi. Thank you for the code. I’m attempting to use the script but it seems the $ID is not being set in the database. I’m using your code exactly and can echo $keyword with no problem so it is getting the keyword from the URL, but not setting the $id. DB/table is set up, no db connection problems and I can manually run the sql code on phpmyadmin to insert keywords that increment the ID, but not sure why it’s not working from the script.

    Any advice? Happy to share my code, but it looks exactly like yours above. 🙂

  4. Hey Rick have you checked that the field is auto-incrementing? Also is it set to a double? Export your sql structure and post it here and I’ll see how it looks, if you want. Also are you sure you have that Field set as the primary key? If you have it set as the primary key, auto-incrementing and named `id` it should work. Let me know.

  5. Rick Rick

    Thanks. Here you go:


    — Table structure for table `keywords`

    CREATE TABLE `keywords` (
    `id` int(11) NOT NULL auto_increment,
    `keyword` varchar(200) NOT NULL default ”,
    `count` double NOT NULL default ‘1’,
    PRIMARY KEY (`id`),
    KEY `keyword` (`keyword`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

    I can manually add a keyword and can echo back the $id and have it increment. It seems to fail on adding a new keyword, at or post else statement. Thanks again, and for clarity, my code (with some test echo statements).

    //
    $keyword = trim($_GET[‘kw’]);
    $keyword = mysql_real_escape_string($keyword);
    echo “keyword grab: $keyword “;

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

    if($sql_out){

    $row = mysql_fetch_assoc($sql_out);
    $id = $row[‘id’]; //gets the id associated with the word
    echo “id1: $id “;
    $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.
    echo “id2: $id “;
    }

  6. Rick Rick

    re-reading… ID field to be DOUBLE as well? I had it as an INT. Just changed to DOUBLE to test but still not working. New keywords are not being added. Also, it seems that the one keyword I manuall added is being incremented regardless of what the $keyword is. Echos back $keyword as “test2” for example, but the keyword “test” in the db (only enry) gets incremented anyway. Hmmm. Thanks in advance.

  7. something that might help you in your debugging. Add on your mysql calls a

    mysql_query($sql) or die(mysql_error());

    what this will do is kill your process when sql chokes which seems to be the issue by what you’re sharing.

    Add “or die(mysql_error());” to your mysql calls and if you still haven’t found what isn’t working then post the output your getting on the screen for this here.

  8. Rick Rick

    If the keyword is already in the db, it completes successfully.

    For all other keywords it seems to fail when it does not find the keyword in the db or when trying to add a new keyword:

    keyword grab: test2 test2
    SELECT * FROM keywords WHERE keyword = ‘test2 test2’ //my echo of first select statement

    I added the ‘or die’ code throughout. For a new keyword it dies after the first select if the keyword is not found. Maybe it makes it through to the else and fails when trying to INSERT a new keyword?

    Should the id field be DOUBLE or an INT?

  9. Rick Rick

    Smaxor- I gave your script legs on the DP forum and linked back here, hope you don’t mind: http://forums.digitalpoint.com/showthread.php?p=4700287

    A reply helped fix my problem…

    Change: if($sql_out){

    To: $sql_count = mysql_num_rows($sql_out);
    if($sql_count > 0){

    However, I’m still having the problem where every keyword count increments on a pageload, not just the identified keyword. Thanks again.

  10. Rick Rick

    And my increment problem is fixed as well via hemlata @ Digital Point, again. Was not incrementing on the found $id:

    $sql = “UPDATE keywords SET count = count + 1 WHERE id = ‘”.$id.”‘” or die(mysql_error()); // sql to increment the counter for that word

    Thank you once again for the script. I’m using on two sites now and the subid ID is successfully being passed through to the affiliate.

    Now I just need more of those ID’s associated with actual conversions!
    -R

  11. Marc Marc

    Haha Man… 😀 I was looking for something like this for AGES!!!!! Thank you so much for posting this, I suck at php, I mean I’m sure I can impement this and create the db etc, but writing it on my own… no way 😀 Thanks again, I’m sure I will read your blog more often, it’s def. bookmarked

  12. Lp Lp

    Hey Smaxor,

    I’m trying to get your script (which is EXACTLY what i’ve been looking for) to work for me.. but I’m a complete PHP newb.

    I’ve posted a thread about it here at DP if you can take a look and see what I’m doing wrong:
    http://forums.digitalpoint.com/showthread.php?t=624163

    Thanks!

  13. Lp Lp

    ^^ Nevermind. Someone posted a reply to the thread and it works now 🙂

    Awesome script man!

  14. al al

    what if you don’t capture it as a sub-id? I’ve been using adwords to track conversions and awstats to find out all terms to create negatives. Somehow awstats knows what keywords are being entered without having to tag all of my keywords…. 🙁

  15. al al

    correction on that last statement — even if i do capture it w/ sid will i get the EXACT term entered even if my keyword matching is “broad”? Or will I just see that a broad term converted?

  16. AZ AZ

    Question about this ….

    I have it working. But say someone comes in on the keyword “test” – test is added to the db with an id of 0. Next time someone comes in on the keyword “test” is it still equal to the ID 0, or is it given another integer?

    If I understood PHP I would obviously be able to answer this myself =)

  17. I found your blog on google and read a few of your other posts. I just added you to my Google News Reader. Keep up the good work. Look forward to reading more from you in the future. Thank you

  18. I have two years of trying to integrate the business of affiliate program do not reach, I would appreciate if you could guide me a lot

  19. Anybody know how to do this for a WordPress blog? There are apparently a couple of WordPress plugins supposed to allow you to execute PHP in a blog post/page, but can’t seem to get them to work.

Comments are closed.

Subscribe if you're interested in...

  • Learning about high level Affiliate Business Strategies
  • Converting more of your users to sales
  • Increasing the value you earn from you customers

The more subscibers there is the more I'll be inspired to write.