TRACKING IF AN EMAIL WAS OPENED BY THE RECIPIENT - A Tutorial

In my case, I was sending acceptance emails to the entrants of an art exhibition, and it was important to be able to track if they had been opened, so that I could make sure that all entrants had been notified.

Here’s the recipe from my CMSB Cookbook http://www.thecmsbcookbook.com that works for 90+% of the recipients.

The code, which is pretty standard in the email industry, relies on a hidden <image> tag and so it requires that they have image downloads enabled in their email client:

BTW: Since <img> tags won’t work on an email client if images are not enabled, I’m still looking for other workable options as alternatives.

While researching those alternatives, I came across some interesting resources (although some of them are a bit old by cyber standards) which I’ve listed below.

TRACK IF AN EMAIL WAS OPENED BY RECIPIENT

If you're sending emails to a list from a series of records in a multi-record editor using CMSB's built in $mailArray function, it’s easier then you think to track which emails were opened and which were not. (Because of the multiplicity of email clients in cyberspace, it’s not a perfect solution but it can be pretty accurate.)

First, in the editor that contains your email addresses, you’ll need to create a check box field called ‘opened’. It’s also handy to insert that field in the ListPage Fields so you can get a quick overview of the field’s status.

Now you’ll need to create a simple viewer ( I called mine email_open.php) that will update the ‘opened’ field to a value of ‘1' when accessed. It also updates the updateDate to the date and time that the email was opened to facilitate reporting. (I used the record number from the record to make sure that the right record gets updated.)

<?php
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/path/to/your/viewer_functions.php/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
?>
<?php
$numAsInt = intval( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}your_table_name SET opened = '1', updatedDate = NOW() WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
exit;
?>

NOTE: Dave Edis suggested, “You need to always escape or sanitize user input to prevent hackers from tricking the script into running their own MySQL. Instead of calling mysql_real_escape_string() or a related function I call intval() which forces the value to be a number and accomplishes the same thing."

Then somewhere in the foreach loop that loops through the records that contain your email address, you’ll need to define the variable $recnum.
<?php $recnum = $record['num'] ?>

Then in the email message body or header, you’ll need to insert an img tag that instead accesses your email_open.php file :

<img src='http://www.your_domain.com/email_open.php?num=$recnum' height='1' width='1' alt='' />


Here are links to the resources that I mentioned:

A guide to CSS support in email clients by Campaign Monitor

css - Best practices for styling HTML emails - Stack Overflow

Acid Test - Email Standards Project

HTML Emails, Outlook And Background Images Kevin Skwerl Cogill

Best,

Jerry Kornbluth