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,
PRIMARY KEY  (`id`),
KEY `index_2` (`date`),
KEY `index_3` (`email_title`)

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 (substituting your own database, user, and password info).

@ $db = new mysqli(‘localhost’, ‘user’, ‘password’, ‘database’);

if (mysqli_connect_errno()) {
//echo ‘Error: Could not connect to the database..’;

if (!empty($_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) VALUES (null,’$ip’,’$host’,’$referrer’,’$date’,’$time’);";

header( ‘Content-type: image/gif’ );
echo chr(71).chr(73).chr(70).chr(56).chr(57).chr(97).

Finally, insert this into your email.

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

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. Now this is only the skeleton structure for your email tracking. You still need to add a mechanism for uniquely identifying what email recipient opened this web bug (maybe I’ll address that in a future post). Once you capture who opened the web bug, then you can code up something to report your impressions, open-rate, and click-thru rate.