Sometimes tracking email is useful to know if the recipient received an email and whether they opened the same. There are some free and paid solution is already available in the market. In this post, I am writing to describe the strategy of tracking email using popular technology PHP and MySQL database.
Use Cases
Email tracking can be useful for many reasons. It is useful for marketing purpose to get some insight like if the promotional emails are getting hit or not. Another insight might be useful to know the customers who are the active recipient. Beside email opening information we also get user’s information like IP address, device information, location etc. This post is just for educational purpose only and for personal and legit use only. DO NOT use this trick to exploit anyone.
The Strategy
Most of the email provider has a feature named “Request read receipt”. If you mark this feature on, you will get notified if the recipient opens the email. But if the recipient does not wish to send the receipt, you will not get it. This is why this option is not very reliable. So, below is the strategy to get the receipt alternately. The same method also applied by various free/paid mail tracker as well.
The strategy is to insert an image pixel inside the HTML body of the email. The pixel can be transparent so that the user will not be able to see that. Or you can put a visible pixel as well. Almost all the email provider display image by default hence whenever a user will open the email, he/she will make an HTTP GET request to get that pixel from your own server. And below setup will keep track of the request. Alright !! Enough of the boring paragraph, let’s jump into the implementation part.
Implementation / Coding
We have following part to implement the solution:
- A transparent image pixel with a dimension of 1×1 px.
- A PHP server to serve the pixel image.
- PHP Scripts to serve the pixel and track the same.
- MySQL database tables to store records of the tracking and email.
Let’s start with the MySQL database first. We need the below tables for this task:
CREATE TABLE IF NOT EXISTS `mt_all_mails` (
`id` INT NOT NULL AUTO_INCREMENT,
`mail_to` VARCHAR(1000) NOT NULL,
`mail_cc` VARCHAR(1000) NULL,
`mail_subject` VARCHAR(1000) NOT NULL,
`mail_body` VARCHAR(10000) NULL,
`sent_time` DATETIME NULL,
`tracklink` VARCHAR(1000) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mt_mail_open_history` (
`id` INT NOT NULL AUTO_INCREMENT,
`mail_id` INT NULL,
`open_time` DATETIME NULL,
`opener_ip` VARCHAR(20) NULL,
`opener_user_agent` VARCHAR(500) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
mt_all_mails is the table to keep information about each email. A PHP script (explained later) will generate mail_id and tracklink for an email. mail_to and mail_subject fields are required to identify the email later. mt_mail_open_history is the table that will contain all the opening information for each email. mail_id in this table is the foreign key to all emails table’s id column.
Now the php scripts to generate an email item along with the tracklink:
$mailto = filter_input(INPUT_POST,'mailto',FILTER_SANITIZE_EMAIL);
$mailcc = filter_input(INPUT_POST,'mailcc', FILTER_SANITIZE_EMAIL);
$mailsubject = filter_input(INPUT_POST,'mailsubject', FILTER_SANITIZE_STRING);
$mailbody = filter_input(INPUT_POST,'mailbody', FILTER_SANITIZE_STRING);
if($mailto == "" || $mailsubject == "") {
echo "Please enter valid values.";
exit;
}
$tracklink = "https://<your server path>/getpixel.php?mailid=";
$all_mail_data = array();
$all_mail_data['user_id'] = $_SESSION['user_id'];
$all_mail_data['mail_to'] = $mailto;
$all_mail_data['mail_cc'] = $mailcc;
$all_mail_data['mail_subject'] = $mailsubject;
$all_mail_data['mail_body'] = $mailbody;
$all_mail_data['sent_time'] = date("Y-m-d H:i:s", time());
$all_mail_data['tracklink'] = $tracklink;
TableInsert('mt_all_mails', $all_mail_data);
$mailid = GetMaxRecordID('mt_all_mails','id',array('mail_to'=>$mailto,'mail_subject'=>$mailsubject));
$tracklink .= $mailid;
TableUpdate('mt_all_mails', array('tracklink'=>$tracklink), array('id'=>$mailid));
echo $tracklink;
The above script is capable of accepting few html form fields by post method and create an email item and insert into mt_all_mails table. I have few reusable functions to insert/get mail id/update records in the tables.
function TableInsert($tableName,$dataArr) {
require 'dbconnect.php';
$sql_insert_stmt = "INSERT INTO $tableName";
$col_list = "(";
$value_list = " values(";
foreach($dataArr as $col => $val) {
$col_list .= $col . ',';
$value_list .= '\'' . $val . '\',';
}
$col_list = substr($col_list, 0, -1);
$value_list = substr($value_list, 0, -1);
$col_list .= ")";
$value_list .= ")";
$sql_insert_stmt .= $col_list . $value_list;
if ($conn->query($sql_insert_stmt) === TRUE) {
$conn->close();
return true;
}
else {
$conn->close();
return false;
}
}
function GetMaxRecordID($tableName,$idcolName,$dataArr) {
require 'dbconnect.php';
$sql_get_id = "SELECT max($idcolName) as $idcolName from $tableName WHERE ";
$whereclause = "";
foreach($dataArr as $col => $val) {
$whereclause .= $col . " = '" . $val . "' AND ";
}
$sql_get_id .= substr($whereclause, 0, -5);
$result_get_id = $conn->query($sql_get_id);
$conn->close();
while($row = $result_get_id->fetch_assoc()) {
$result_id = $row[$idcolName];
}
return $result_id;
}
function TableUpdate($tableName,$colNameArr,$whereArr) {
require 'dbconnect.php';
$sql_update_tbl = "UPDATE $tableName SET ";
foreach($colNameArr as $col=>$val) {
$sql_update_tbl .= $col . " = '" . $val . "',";
}
$sql_update_tbl = substr($sql_update_tbl, 0, -1);
$sql_update_tbl .= " WHERE ";
foreach($whereArr as $col=>$val) {
$sql_update_tbl .= $col . " = '" . $val . "' AND ";
}
$sql_update_tbl = substr($sql_update_tbl,0,-5);
if ($conn->query($sql_update_tbl) === TRUE) {
$conn->close();
return true;
}
else {
$conn->close();
return false;
}
}
Prepare the dbconnect.php with your own server credentials.
Once you have the tracklink from the above script, go to the compose email section and insert an image from URL and provide the tracklink in the URL field:
Send the email once the pixel has been attached. Now every time the recipient open the email, he/she will make a request to the tracklink, where we will keep track the opening details. Below is the script for the same [getpixel.php]:
<?php
require_once 'commonFunc.php';
$mailid = filter_input(INPUT_GET,'mailid',FILTER_SANITIZE_NUMBER_INT);
if($mailid == "") {
echo "Invalid request";
exit;
}
$sql_get_mail_info = "select * from mt_all_mails where id = " . $mailid;
$result_get_allmails = GetAllTableDataFromQuery($sql_get_mail_info);
if($result_get_allmails->num_rows == 0) {
echo "Mail ID not found";
exit;
}
$mail_row = $result_get_allmails->fetch_rows();
$mail_open_hist_record = array();
$mail_open_hist_record['mail_id'] = $mailid;
$mail_open_hist_record['open_time'] = date("Y-m-d H:i:s");
$mail_open_hist_record['opener_ip'] = getIPAddress();
$mail_open_hist_record['opener_user_agent'] = $_SERVER['HTTP_USER_AGENT'];
TableInsert('mt_mail_open_history', $mail_open_hist_record);
header('Content-type: image/png');
header('Content-Disposition: inline; filename="inv_pixel.png"');
readfile('inv_pixel.png');
?>
Result
When the recipient will open the email, the above script will make an entry in the open history table with timing information along with other user information. You can see the result by querying the table:
select * from mt_mail_open_history;
If you get the count group by mail_id to check the open count for a particular email.
Too much coding and having problem to create the tracker. Comment below your issue and will try to solve the same.
If this is too much work for you, one readymade solution I have created with the above method. You can register and use the same. Here is the link:
https://subhadip.ca/mailtrack/
Happy Coding !!