Big Data Email Open Tracking in PHP
As a part of your 360° Customer View strategy, you want to know how effective your email marketing campaign is.
How many/which users open your email
How many/which users click through to your website
What demographics respond to which campaign
This can all be done using Email, a Database, and a Web server with a server side scripting language.
If a user can open an HTML email in their email client, an image can be loaded from your server in their email. That image can be a PHP script that records data about your client while displaying an image.
This is known as a tracking image, and in this tutorial we will implement one in MySQL and PHP.
You already learned how to Send HTML Emails in PHP, which will allow you to use the img tag to include a remote image url in your email, as well as beautifully format the email.
As a security measure, most email clients disallow image loading by default, meaning that even if a user opens your email you may not be able to know if they have opened it unless they explicitly tell their email client to enable image loading for that email.
Even though this example uses a static HTML email, the type of email tracking I'm describing is best used on individually sent, programatically tailored emails with the ability to identify both email campaigns and subscribers by ID. Mass messages are not targeted enough to provide you with any data about specific subscribers. Mass emails are also a great way to put your email server on a black list (learn how to remove your email server from black lists).
We will be working with the following file structure
Let's use an example company logo for images/image.jpg:
Your database will need to be able to store the email campaign ID and the subscriber's ID in order use big data analysis techniques on your email campaigns.
The SQL code to generate this table looks like this:
-- Email Campaign table CREATE TABLE EmailTracker ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, campaignID int(10), subscriptionID int(10) timestamp TIMESTAMP DEFAULT NOW(), PRIMARY KEY (id) );
Your php script needs to tell the browser what type of image to expect, then pump the image data to the browser in order to emulate what the browser is expecting from the web server.
For your benefit, the script can count that an email has been opened, by incrementing a flag in the database
<?php // trackerimage.jpg.php // You are collecting some info about your users // in this case it's the email campaign they've opened... $campaignID = intval($_GET['campaignID']); // and the subscription ID - you saved that from your // double opt-in registration, right? $subscriptionID = intval($_GET['subscriptionID']); // load the Database abstractor require_once('settings.php'); require_once('classes/EmailTracker.class.php'); // someone accessed this script by opening an email // and displaying images. Let's store that knowledge $EmailCampaign = new EmailCampaign($campaignID, $settings['mysql]'); $EmailCampaign.emailOpenedBy($subscriptionID, time()); // load the image $image = 'images/images.jpg'; // getimagesize will return the mimetype for this image $imageinfo = getimagesize($image); $image_mimetype = $imageinfo['mime']; // tell the browser to expect an image header('Content-type: '.$image_mimetype); // send it an image echo file_get_contents($image); ?>
You of course need to connect to the database and all that jazz in order to store the data.
<?php // settings.php $settings['mysql']['server'] = 'localhost'; $settings['mysql']['username'] = 'mysqluser'; $settings['mysql']['password'] = 'mysqlpassword'; $settings['mysql']['schema'] = 'tracker_example'; ?>
Your Email Tracker will do all the dirty work of connecting to the database and modifying data. In a larger application, you would of course have a separate database management class to handle connection and SQL generation.
<?php // classes/EmailTracker.php class EmailTracker { private $mysql; // mysql resource connection private $campaignID; // the email campaign ID /** * Initialize the EmailCampaign. * In this class, we are also connecting to the * MySQL server. In a larger project, connections would * be managed by a separate Database abstraction * class */ public function __construct($campaignID, $settings) { // connect to mysql server. $this->mysql = mysqli( $settings['server'], $settings['username'], $settings['password'], $settings['schema']); if ($this->mysql->connect_errno) { throw new Exception("MySQL connection error: ".$this->mysql->connect_errno); } $this->campaignID = $campaignID; } public function __destruct() { mysql_close($this->mysql);. } /** * Record that a client has opened the email in the Database */ public openedBy($subscriptionID, $timestamp) { $sql = "insert into `".get_class($this)."` "; $sql .= "(campaignID, subscriptionID, timestamp) "; $sql .= " values "; $sql .= "('".$campaignID."','".$subscriptionID."','".$timestamp."')"; $result = $this->mysqli->query($sql); if (!$result) { throw new Exception("MySQL Query Error: ".mysql->error()); } } } ?>
Your email needs to be formatted in HTML and contain a link to the trackerimage.jpg.php on your server.
You can incentivize your subscribers to download images from your server by beautifully formatting and branding the email, enticing them to want to see it. As a best practice though, do not use graphic text in your email so that users can't read the email without downloading the graphics. It prevents the user from knowing what they are reading and it's a great to create animosity in your subscribers
A simple email can be written:
<html>
<body>
<a href="http://example.com?src=email&subscriberID=1&campaignID=1"><img src="http://example.com/trackerimage.jpg.php?campaignID=1&subscriberID=1 width="200" height="100" border="0" /></a>
<h1>Your Email</h1>
<p>This email contains a tracker image. </p>
<p>See how the width and height create a visual cue for the subscriber that an image is there even when they have not yet downloaded images from their email client?</p>
</body> </html>
The resulting email will look like this prior to the user downloading the images into the email client:
And will look like this if the user chooses to download the images:
You are done. If the user chooses to download images, the tracker image will load on your server, updating you on which subscribers responded to which email campaigns.
By combining web analytics and URL query parameters in your email, you can even find out which subscribers and which email campaigns provided the best click-through ratio, which is the whole point of email marketing in the first place.
To me, the most interesting thing is that you can start to use Big Data analytics techniques to find niche markets based on unexpected customer interest, or discover relationships between customer sub-groups.
The code for this tutorial is available on GitHub:
https://github.com/backupbrain/php-email-open-tracking.git
Sending HTML Emails Using PHP
http://tonygaitatzis.tumblr.com/post/62426747491/send-html-emails-using-php
Learn to use PHP to mime encode emails to send formatted HTML content.
PHP MySQLi
http://php.net/manual/en/book.mysqli.php
The PHP MySQLi extension documentation
Email Tracking
http://en.wikipedia.org/wiki/Email_tracking
Wikipedia article explaining how Email tracking works as well as privacy and legal concerns.
Output An Image in PHP
http://stackoverflow.com/questions/1851849/output-an-image-in-php/1851873
Examples and best practices for outputting an image to the browser with PHP
What Kind of Big Data Problem Do you Have
http://www.sas.com/news/sascom/2q13/business-analytics.html
An overview of what Big Data is, what major types of problems are typical of Big Data analytics
HTML Email Boilerplate
http://htmlemailboilerplate.com/
An HTML Email boilerplate to more easily create beautiful HTML emails that work on a variety of email clients.