8
2013
Awesome Email open tracking with PHP and MySQL
An article by admin
Sometime last year, I wrote about super awesome email open tracking using PHP and MySQL, and I’ve since ALSO updated that one to be EVEN MORE SUPER AWESOME!
Tracking email open rates is both very valuable, and very useful, particularly if you run a service where statistics are important, or in scenarios where more detailed information on users (namely their participation with site content outside the website is crucial).
That being said, a couple things you should know about email open tracking:
- It does not work in plain text emails, so I always advise presenting data associated with email open rates with that handy little *Data only reflects open rates for HTML email clients, and email clients where images are loaded* notation
- It won’t always work (even as HTML). Gmail for example often will not load images from unknown senders by default, and this will prevent the email from being tracked unless the user selects to view images.
Now that we’ve gotten all the “I told you so-s” out of the way…
The concept of email tracking
This part is dubiously simple: wherever you send a message that needs to be tracked, we’ll just tack on an image that ACTUALLY loads a PHP file on our server.
This PHP file (in this example “record.php”) that is requested in the <img tag, takes $_GET parameters, logs them into the database, and then serves up an actual image (so that we don’t make the email client go “WTF MAN”).
So onto the example which will probably make this conceptual thing actually click…
<p>Hi!</p>
<p>I am a message, just going about my business.</p>
<p>Don't mind me!</p><p>Hi!</p>
<p>I am a message, just going about my business.</p>
<p>Don't mind me!</p>
<img alt="" src="http://yourwebsite.com/[email protected]&message=19" width="1" height="1" border="0" />The implementation
Before we get started, there’s a few things we need to take care of:
- We’ll need the URI and the path to our blank.gif file (for filesize and readfile functions) to output the image when the tracking script is requested
- A transparent 1px X 1px gif image called “blank.gif”
- A database table. In this case, “email_log” (sql provided in the download and below)
Once those are good to go, we’ll implement in the location where the message actually gets sent in our message sending file (example files use “send.php”):
$message = $_POST['message'];
$subject = $_POST['subject'];
$to = $_POST['recipient'];
$from = '[email protected]';
//Since the tracking URL is a bit long, I usually put it in a variable of it's own
$tracker = 'http://yourwebsite.com/record.php?log=true&subject=' . urlencode( $subject ) . '&user=' . urlencode( $to );
//Add the tracker to the message.
$message .= '<img alt="" src="'.$tracker.'" width="1" height="1" border="0" />';
$headers = "From: $from <".$from.">\r\n";
$headers.= "Return-Path: " . $from . "\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=UTF-8\r\n";
$send = mail( $to, $subject, $message, $headers );Now that we’ve gotten our message sent, and it will request record.php with some query parameters, we’ll move onto the tracking!
The Tracking Script (record.php)
The most important part of record.php is making sure that all of the necessary $_GET parameters exist before anything happens…
if( !empty( $_GET['log'] ) && $_GET['log'] == 'true' && !empty( $_GET['user'] ) && !empty( $_GET['subject'] ) )Once we are quite sure that we have something TO track, let’s include the database connection script and initiate it.
For this example, I’m using my own mysqli database class, however, I certainly respect sticking with what knows and what works. That being the case, to use another method of interacting with the database, simply replace anything prefixed with $database->[function here] with any other database interactivity desired.
//Include the configuration file with the db creds and file locations
require( 'config.inc.php' );
//Include the database class
require( 'class.db.php' );
//Initiate the database class
$database = new DB();The next step is to tell the email client that the script is going to provide a content-type of image/gif:
//Begin the header output
header( 'Content-Type: image/gif' );As always, sanitize any data interacting with the database. Here, we’ll also assign the $_GET parameters to $variables since they’ll be used more than one time.
$user = $database->filter( $_GET['user'] );
$subject = $database->filter( $_GET['subject'] );Note: $database->filter( $_GET[‘stuff’] ) can be exchanged with any other sanitizing function you prefer, but at minimum if changed should use mysql_real_escape_string( $_GET[‘stuff’] ) to prevent SQL injection.
Since each time the email is opened or viewed by the recipient, it will make a call to our record.php file, it is important to check if they have already been recorded into the database (unless of course you’re interested in exactly how many times, in which case you should use UPDATE and add a column to indicate open counts).
$exist_count = $database->num_rows( "SELECT user FROM email_log WHERE user = '$user' AND subject = '$subject'" );As long as the $exist_count variable returns a nice solid “0”, we can go ahead and record that they’ve opened the message using the data passed to the script from the $_GET params:
if( $exist_count == 0 )
{
//Make an array of columns => data
$insert_record = array(
'user' => $user,
'subject' => $subject
);
//Insert the information into the email_log table
$database->insert( 'email_log', $insert_record );
}Again, this article uses the class.db.php located, and documented here, so check it out for the $database->insert( ‘table_name’, array( ‘column’ => ‘value’ ) format.
And last but certainly not least, since we told the user’s email client to expect a graphic, we should ‘probably’ stick to what we said and output the graphic (blank.gif), even if it is a 1px X 1px transparent gif:
//Full URI to the image
$graphic_http = 'http://yourwebsite.com/blank.gif';
//Get the filesize of the image for headers
$filesize = filesize( 'blank.gif' );
//Now actually output the image requested (intentionally disregarding if the database was affected)
header( 'Pragma: public' );
header( 'Expires: 0' );
header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
header( 'Cache-Control: private',false );
header( 'Content-Disposition: attachment; filename="blank.gif"' );
header( 'Content-Transfer-Encoding: binary' );
header( 'Content-Length: '.$filesize );
readfile( $graphic_http );And (now) last, GET OUT! This script is done.
exit;The code above assumes that “blank.gif” is located in the same directory as record.php, and that the URI you’re using is yourwebsite.com, so be sure to adjust accordingly!
The Database Schema used
This is also provided in the download package, but here it is anyway!
CREATE TABLE `email_log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(220) DEFAULT NULL,
`subject` varchar(220) DEFAULT NULL,
`opened` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Adjustments and areas of improvement
Since this example uses the message subject as the parameter associated with each message, it is important to note that in normal usage, both the user, and the subject recorded here would be int values associated with a specific user, and a specific message rather than a full email address and varchar subject.
The output within record.php could also be adjusted to begin with the header content-type specified, and close with the image data output outside the check for $_GET parameters , and should be if you plan on including a tracking code within all the emails sent by a system, but not necessarily passing any data to the <img src, or leaving the values empty.
Conclusion
Email open tracking is simple (even if all the stuff above this makes you go “HUUUUUHHHH?!”), easy to implement, and in my experience- a frequently requested part of more advanced web projects and applications whenever you’re working with a large number of system sent emails.
When implemented properly (as in the provided download and examples above), it is an effective way of providing data to clients (or yourself) on the efficiency and effectiveness of their email marketing campaigns, or if their girlfriend/boyfriend DID read that message (Disclaimer: I take no responsibility for arguments caused by the aforementioned!)
[dm]12[/dm]
Related Posts
44 Comments + Add Comment
Leave a comment
Recent Snippets
- htaccess : Only allow access to specific wordpress upload types if logged in
- MySQL : Query WordPress Database for Invalid Media Filenames
- PHP : Get Actual IP Address with PHP
- JavaScript : Allow Tab in Textarea
- PHP : Clean sanitized database contents on output
- htaccess : Force www. prefix in URIs
- PHP : Force File Download with Correct Content Type
- Wordpress : Disable upgrade notification
Thank you very much.
I was just searching for a good email tracking script for a long time and you have done it.
Hi i used your script, but for each email client, gmail, yahoo etc. a got ‘Disarmed’ and of course since that happend email tracking didn’t work since there is no record in db.
Greetings
Did you double check the URI that the script was requesting the image from to ensure that it was not a localhost script?
This was immensely helpful, thanks.
I managed to get it set up and the tracking is working, but I can’t get the picture to load in the email signature, I am using a custom one not the gif. Also I would like to receive an email when someone has read my mail, any help? I appreciate all the work that you have put into this, Thank you very much.
—
Chris
Hi Chris,
Regarding the picture loading, keep in mind that it must be both a full HTTP and an absolute path to the same image, you can try something such as…
$graphic_http = 'http://yourwebsite.com/img/blank.gif';$filesize = filesize( dirname( __FILE__ ) .'/img/blank.gif' );
Assuming that the blank.gif graphic is in a folder named “img” within the same directory as the script loading the file.
Regarding the script sending you an email when someone has opened your message, you can add the “mail()” function anywhere above the line that says “//All done, get out!” and the closing “exit;”. See the send.php file, or this article on CSS Tricks about sending pretty HTML emails for the actual mail sending to you
I have tried this code its working but record.php is not loaded and running ,will u help me how can i load record.php so that log can be inserted in my database
Maybe dumb question but still let me ask this:
Is it possible to find out from which email account the actual email message was read/opened in case of relay email was used to send to? e.g. craigslist relay emails?
Very good question actually, I haven’t ever looked into that, but I don’t believe mail clients generally load in recipient information (aka which email address you opened a message with)- if anyone else knows otherwise I’d be interested to know how reliable it is for sure!
Additionally, in the event that a mail client did provide the actual email account from which a message was read, I’m thinking it still would not work since you’d need to do a dynamic img src replacement within an email body; something like:
<img src="record.php?log=true&user={REPLACEMEWHENOPENED}" />Which unfortunately would require some overwriting somewhere along the lines that wouldn’t be available due to email restrictions
Hey mate, this code works great! There is only one more thing I need… How do you also include a “text-version” of the email? Do you have to modify the headers? Thanks in advance.
Indeed- it gets a bit too lengthy for me personally to handle both HTML + Plain text version as PHP’s mail functions are dry reading at best. I recommend using the PHPMailer class (https://github.com/PHPMailer/PHPMailer) which makes it A LOT easier to handle more advanced email sending use cases 🙂
Hi,
This is great and just what I need. I am understanding most of it but can’t figure out where to see my results. That is, see the list of emails that have been opened – or not.
Would appreciate a little help with this.
Thanks ever so.
You can either build an interface, or just go directly into phpmyadmin. If you haven’t modified the scripts, you would look in a database named “yourdatabasename” and inside that, the table named “email_log”
This is excellent! Do you have any tips on how to implement the part that takes an incoming mail sent to [email protected] and splits ‘[email protected]’ from that to then process the change to the message with the 1×1 gif and relay it?
Also, what if you wanted to also track clicking of all links within a given message? For example, aWeber has an option that replaces each of your links in an email with a custom link that lets you track when each link was clicked.
Thanks!
-David
Absolutely. For inbound mail parsing (which is what it would be with the splitting out joeblow@xyz), there are 2 routes you can go:
1) Use a PHP POP3 or Imap plugin such as fMailbox to scrape the contents of a mail inbox, then use some custom functions to extract elements from each part of the message (http://flourishlib.com/docs/fMailbox). I have done this on projects reliably, but it does get sort of nasty on the code-front.
2) Use mailgun, which has built in mail parsing which will post messages directly to a PHP script (same as a form would). I’ve been going this route lately due to it’s simplicity (I am in no way affiliated with mailgun)
To track links for a message, there are also 2 pretty straightforward ways:
1) Use bit.ly (disadvantage here is that every link would have to have a generated bit.ly link, and you’d have to use those links rather than a normal link)
2) As messages are going out, have the message bodies run through a function such as the one here (http://stackoverflow.com/a/18779124), but instead of appending (as is the case in the provided link), prepend a link to YOUR OWN site, which has a page that simply logs the URL, then does a header( ‘Location: requested URI’ ); redirect.
So as per #2 above, your links would be overwritten to read something similar to:
http://google.com -> http://yoursite.com/track.php?link=http://google.com
And as such, track.php would do something similar to:
if( isset( $_GET[‘link’] ) )
{
$link = trim( $_GET[‘link’] );
//SANITIZE THE LINK PRIOR TO INSERTION IN THE DB
//insert the request into the database here
header( ‘Location: ‘. $link );
exit;
}
Hello, and thank you very mutch for you script !
I have 1 question : my database is in PostgreSQL, is it possible to use your script ? Where am I suppose to change the code ?
Thanks !
Good question! My example uses my database class, so you’d need to replace anything you see such as “$database->num_rows”, “$database->insert” with PostgreSQL specific requests or a PHP wrapper for Postgres, beyond that, you shouldn’t need to change anything else
I have uploaded the files into a folder called trackmail. I ran the send.php file, the email gets sent, but no database entries are made in the database.What could be wrong?
@conrad did you change the configuration in config.inc.php? If so, and it still is not working, double check the URI for $tracker in send.php on line 33 to ensure it’s being pointed to the record.php file to load the 1px gif.
You can also try using a more prominent image as the request source, or view the source of the message from the inbox it was sent to to ensure that the blank.gif file is being loaded and/or is available.
I just want the time stamp to update a multicolumn table. How do i do that. for eg.
The table you have created is email_log with columns id, user, subject and timestamp
I just want the timestamp to be updated in my table useractions, that consists of multiple columns.
Can you tell me how to manage that?
Thankyou for the response before,the thing that i was doing wrong was that i had put it in a subfolder and hadnt updated the uris for the tracker.php and the http url
Hi, I have created the db in localhost and used appserv to run php in my computer. I got message ” Message sent with tracking!” after pressing SEND button. Unfortunately, I still can’t receive any email in my mail account. Can you kindly help me solve this issue?
Annoyingly, some ISPs have odd proxy settings that may require you to use SMTP or POP to send your message from localhost. For example, in my office, normal PHPmail messages do not send- but when I open my laptop at a different location and send (any) message using phpmail, all of the queued messages suddenly hit my inbox.
Try the PHPMailer script with SMTP (will work with gmail as well) from https://www.phpdevtips.com/2014/07/using-phpmailer-spectacular-php-emails/
Hi
This is good work, but i think that need to track and record ip address too it’s important about tracking
so can you plz lead me how to add ip tracking lines in your codes? 🙂
In record.php, you’d need to add a function to retrieve the user’s IP (neither the ISP nor the email client will provide this information), and then just assign it to a variable and a database column and you’ll be good to go.
See https://www.phpdevtips.com/snippets/get-actual-ip-address-php/ for the function I use to grab IP addresses, and be advised, that there are privacy issues with implementing this. See http://stackoverflow.com/a/18633028 for a better list than I’d write, and always be careful with userdata, and how/why you collect it.
Hi, I used fixed IP, not domain name(websitename.com), to load the pic in my web server. However, i can’t download the pic in the gmail account and no any log in db.
i have tried this code and image send but record.php is not working ,how i can send image url so that record.php works and logs will upadated in my database.please help
Excellent – I did not use the send file but instead, added the lines needed to my system emailer and tested it – worked on the first try – Then added it to my lead capture pages which sends an email to the person that filled out the form… Thanks
Great! Glad you were able to get some good use out of it!
Hi:
this script is excellence.
But I need help more…
IF I use another isp mail smtp service: Gmail. how can i set up the smtp in your script ?
and if the smtp need tls setting, how to do it ?
thanks !
great blog. please provide some suggestions to add tracking of button click action with mail open tracking
Thank you very much.
dear
this is a awesome tutorial. some days ago i develop a panel for sending 250 mail at a time, but i don’t know how many mail reached client. when i read this and implement this … 🙂 i know
thanks
saifullah
Has anyone figured out how to make this work with Gmail. Allthough I have it setup as in the example, the gif will not render in gmail but leave a broken image icon….The php script and the image have 775 rights. Don’t know whats the matter…
If you’re testing from localhost, Gmail (the website) won’t have access to your image, however, a local mail client would, so just be sure the image is web accessible on a server or use a local email client and you should be good to go!
Nice trick! And what about CC and BCC ? How to know which recipient has opened the email?
Thanks,
Unfortunately for CC and BCC, you’d end up with the same tracking code as it’s one-code-per-message, so for that use case you’d want to send those messages individually.
I am getting emails. I open them which mean the image should load and I should have an entry but the table is empty… any suggestions…
I’ve created beacon, getting entries in database. How can I generate a web notification when someone opens the email? How can I track database update fot notification?
Have you considered adding additional code to track IP but also browser type, mobile/agent type, deviceID or other data that can be retrieved from email headers and web traffic (email client http traffic to web server downloading the image files)?
It would be nice to see similar information that I can see with various analytics tools on my webserver – such as operating system, mobile_agent (iphone, android etc), browser and version, etc… I think most of this can be picked up when the request is sent for the image and it is served back, no?
Would be interesting to add IP address and resolve goelolcation longitude and latitude as reported by the ISP owning the IP … not so useful for mobile since most route over towers and private lan to NOCs in major cities – but certainly helpful for those businesses and households with static IP addresses they are using their wifi on or pc on the network to access the emails sent out…
I have caught many fraudsters using services like this – and STREAK for gmail (which does not show us the IP unfortunately – so I have to also add a link in the email to see if they will click it and hit my server where I can get their real time IP address – or at least the proxy they are using… sometimes I can get through all that and reverse trace route right to their pc even through VPN proxies.
Just getting tired of all the scammers out there – so a little fighting back by tracking is, in my mind, worth the time to do.. I have turned over evidence of international mail and wire fraud (ID theft) to authorities several times – I think one was acted on because I had to write a letter on how I tracked them…
Anyhow – would love to see this project grow a little and willing to help in any way…
Absolutely, and there are various implementation techniques that can be used here. For the purposes of this article, I kept it quite simple.
Thank you, thank you, thank you! Easy to implement.
Thank you! Easy to implement.
Thank your for this. Ive implemented this but the email get sent to spam always at gmail. But when I tried to remove the tracking image, then the emails get sent naturally and lands at gmails main inbox.
How to get around gmail spam?
Regards,
Colin
You may need to use a mail delivery service such as sendgrid.com or mailgun.com. Email providers are getting fairly aggressive about blocking unknown content without DKIM and other security settings (which both sendgrid and mailgun provide)