PHP code for a WebBug (or Clear GIF) Email Tracking Solution

I had to create an email Open-Rate tracking system for a client the other day, so I thought I’d share it. This code hides a 1×1 pixel, also called a Web Bug or Clear GIF, in the email, so that if the person views the email, it “phones home” and adds the incident to a MySQL table.

The thing to keep in mind is that the person must view the graphics in the email, so for instance, I use Thunderbird, and by default graphic are turned off and I have to click a button that say “View Images” (or something like that) to see any images (including the Web Bug). So, this may skew your statistics just slightly, but from my testing, it seems a lot of people view images in email.

Final note… this method does not require any graphics libraries (like GD) to be loaded. I found several solutions that required GD, and I didn’t want to go that route, so that’s why I picked this solution.

So, create yourself a table.

DROP TABLE IF EXISTS `database`.`emailstats`;
CREATE TABLE  `database`.`emailstats` (
`id` int(10) unsigned NOT NULL auto_increment,
`ip` varchar(45) default NULL,
`host` varchar(255) default NULL,
`referrer` varchar(255) default NULL,
`date` date default NULL,
`time` time default NULL,
`email_title` varchar(255) default NULL,
PRIMARY KEY  (`id`),
KEY `index_2` (`date`),
KEY `index_3` (`email_title`)
) ENGINE=MyISAM AUTO_INCREMENT=5359 DEFAULT CHARSET=latin1;

Next, create a index.php in a path something like /images/logo.gif NOTE: logo.gif is a FOLDER, not a file. This will be explained later… for now, just create a FOLDER named /images/logo.gif and add an index.php with the following code.

<?php
$email_title = 'Ad-Hoc Email '.date("m-d-Y");

@ $db = new mysqli('localhost', 'user', 'pass', 'database');

if (mysqli_connect_errno()) {
	//echo 'Error: Could not connect to the database..';
	//exit;
}

if (!empty($_SERVER['REMOTE_ADDR'])) {
	$ip = $_SERVER['REMOTE_ADDR'];
	$host = gethostbyaddr($_SERVER['REMOTE_ADDR']);
	$referrer = $_SERVER['HTTP_REFERER'];
	$date = date("Y-m-d");
	$time = date("H:i:s");

	$sql = "INSERT INTO database.emailstats (id,ip,host,referrer,date,time,email_title) VALUES (null,'$ip','$host','$referrer','$date','$time','$email_title');";
	$db->query($sql);
}
header( 'Content-type: image/gif' );
echo chr(71).chr(73).chr(70).chr(56).chr(57).chr(97).
      chr(1).chr(0).chr(1).chr(0).chr(128).chr(0).
      chr(0).chr(0).chr(0).chr(0).chr(0).chr(0).chr(0).
      chr(33).chr(249).chr(4).chr(1).chr(0).chr(0).
      chr(0).chr(0).chr(44).chr(0).chr(0).chr(0).chr(0).
      chr(1).chr(0).chr(1).chr(0).chr(0).chr(2).chr(2).
      chr(68).chr(1).chr(0).chr(59);
?>

Finally, insert this into your email.

<img src="http://www.domain.com/images/logo.gif" border="0" alt="" />

Does that make sense now? It looks like its an image (which is really is, but just in a sneaky way), but it logs the server call in the database. From there, you can code up something to report your impressions, open-rate, and click-thru rate.

Feel free to comment. I’m always open to other ways of doing things.

No related posts.