Landing Page and Referral Tracking For PHP/mysql osDate Site

returning adwords visitorsI know from google analytics that about 25% of my paid clicks are from return visitors.  Drilling deeper, I learned that one visitor from a certain city in California (I know the exact city) has been searching google for one of my most expensive keywords and clicking on my adwords ad, as his way to return to my site.  OK, once or twice, that's great.  But this man visited fifty times in one week. I guess he likes my filipina dating site, Christian Filipina.  Hey Google, please give me some control to limit the number of repeat visits!  (Well, I'd like to give this man a nice gentle letter, because he probably doesn't even know he's costing me money.)  Unfortunately, my site doesn't yet have good enough visibility into the entry points and referring sites. (See the end of the article for the full-size screenshot.)

So this morning I wrote a few lines of code.  This should work almost out of the box if you are using osDate, or else you might need to make a few obvious changes depending on your setup if you are not running osDate.

First, one DB table: 

create table cf__entry (
id int(11) not null auto_increment,
userid int(11) not null default 0,
username varchar(25) NOT NULL default '',
date int(11),
ref_url text,
landing_page text,
ip varchar(25),
signup int(1) default 0,
primary key (id)
) ;

A few words about this table.  The username column also appears in the user table, so here it is duplication of data.  Well, I do this sometimes because as much as I like doing joins and saving disk space, I don't want to clutter my mind when I am trying to do some crazy query, with making it even more crazy than necessary.  Second, sometimes URLs are incredibly long - not usually my own, but sometimes that's the way that, say, adwords, sends them to me.  And there's some very important data in those URLs.  So use "text" format to keep it all.  Efficiency does not matter here.

OK, so here is where you need to change the code.  osDate has a file init.php that sets up the session, db, etc, so this is the place to start:


if ( ! $_SESSION['original_referring_url'] ) {
if (!$ref)
$_SESSION['original_referring_url'] = 'none';
else {
$_SESSION['original_referring_url'] = $ref;

if ( ! $_SESSION['original_landing_page'] || ! strlen($_SESSION['original_landing_page']) ) {
$ref2 = $_SERVER['REQUEST_URI'];
if (!$ref2)
$_SESSION['original_landing_page'] = 'none';
else {
$_SESSION['original_landing_page'] = $ref2;
if ( (!isset($_SESSION['entryid'] )) || !$_SESSION['entryid'] ) {
$osDB->query('insert into cf__entry (
ref_url, landing_page, ip, date ) values ( ?, ?, ?, ? )',
getenv("REMOTE_ADDR"), time() ) );
$_SESSION['entryid'] = / mysql_insert_id();

That's the only insert.  Easy enough.  You could do it with less code.  I use the quoted code because I use some of that stored information elsewhere as well.  I also want to set the username/id and an indication that this was a "new signup" in some cases - so this happens in savesignup.php right after the insert into the user table:

$osDB->query('update cf__entry set signup = 1, username = ?,
userid = ? where id = ?', array( $username, $lastid ,
$_SESSION['entryid']) );

Then you have something almost identical for the basic login.  Just before the session is destroyed and created, do:

$entryid = $_SESSION['entryid'];

Then after the session_start, do:

$osDB->query('update cf__entry set username = ?, userid = ? where id = ?', array( $row['username'],$row['id'],  $_SESSION['entryid'] ) );

If you don't like your session length, by the way, here's a quick way to set it (in osDate this is in init.php just after session_start():


Good luck!

full size returning adwords visitors screenshot