SimpleMH Click and Open Tracking
- Table of Contents
- Dynamic Data
- Example Query
- Using Port 80
- Statefulness of Event Tracking
GreenArrow Engine offers click and open tracking when the SimpleMH method is used to inject mail. SimpleMH’s click and open tracking facility can be turned on or off on a per-Mail Class basis.
Click tracking rewrites links into a URL that GreenArrow Engine’s HTTP server listens on.
Open tracking inserts tracking images into HTML emails. If images are loaded by the recipient, an open gets registered.
If you’d like to receive notifications about click and open events, the Event Notification System can do this for you.
Click and open data is stored in two tables in GreenArrow Engine’s PostgreSQL database. The data in these tables should be treated as read-only. Here are the table structures:
||integer||Primary key for this table.|
||integer||Primary key of the clickthrough_urls entry this record corresponds to.|
||integer||Time in seconds past the Unix epoch that the click occured.|
||character varying||Email address of the subscriber who clicked.|
||integer||Value contained in the
||character varying||Value contained in the
||integer||Primary key for this table.|
||character varying(100)||SendID of the message that was clicked or opened.|
||character varying(100)||ListID of the message that was clicked or opened.|
||text||The original URL for links, or an empty string for opens.|
When dynamic data is being used, and you have control over how the URL is structured, it’s possible to reduce database bloat by putting dynamic data after a question mark. Database entries for URLs containing query strings are truncated at the question mark. The question mark, and query string following it are then appended onto the re-written URL. For example, the following URL:
Would be stored in SimpleMH’s database as:
The re-written URL would look like:
As a result, if a URL that’s inserted into a campaign is distinct for each subscriber, and contains subscriber-identification data following a question mark, SimpleMH is able to process this efficiently, and create only a single row in the
If a URL that’s inserted into a campaign is distinct for each subscriber, and contains subscriber-identification data that does not follow a question mark, then SimpleMH will insert a new row in the
clickthrough_urls table for each user. This can lead to database bloat.
Here’s an example query that displays opens for SendID
SELECT * FROM clickthrough_clicks WHERE id = (SELECT id FROM clickthrough_urls WHERE sendid = 'a100525' AND url = '');
The SendID is constructed by concatenating the InstanceID to the Mail Class. For example, if the following headers are present in a message:
X-GreenArrow-MailClass: a X-GreenArrow-InstanceID: 100525
Then the resulting SendID would be
Using Port 80
It’s recommended that click and open tracking take place on port
80. If GreenArrow Engine’s Apache instance is on the same server as another Apache instance that is bound to port
80, one solution is to bind each Apache instance to a specific IP address. Instructions for doing with GreenArrow Engine’s HTTP server are in the HTTP Server Configuration Document.
Another solution is to use a PHP include on the non-GreenArrow Apache instance. To do this, create a PHP file with the following contents:
<?php include '/var/hvmail/webapp/click/click.php';
After the PHP file has been created, update the
$CLICKTHROUGH_URL variable in
/var/hvmail/control/simplemh-config to point to the new PHP file’s URL:
$CLICKTHROUGH_URL = "http://greenarrow.example.com/click";
Send a test message after updating
/var/hvmail/control/simplemh-config to verify that click and open tracking is still functioning.
If making this edit on a cPanel server, there may be additional security/permissions issues that make the PHP include an impractical solution. Another alternative to the PHP Include is to use a
.htaccess redirect. Create or add to an existing
.htaccess file in the docroot of the domain configured in
/var/hvmail/control/simplemh-config with the following contents:
<Files .htaccess> order allow,deny deny from all </Files> Alias "/clicks" "/var/hvmail/webapp/click/click.php" Alias "/opens" "/var/hvmail/webapp/click/click.php"
The configuration shown above requires that
/var/hvmail/control/simplemh-config ends in
/click.php rather than simply
If you encounter error after implementing the above, please do the following:
Verify that PHP’s PostgreSQL database module has been installed. For example, if you’re using CentOS 6’s PHP 5.3 package, this module can be installed by installing the
yum install php53-pgsql
After making the above change, you may need to restart your web server.
Statefulness of Event Tracking
By default, SimpleMH will use an internal database for tracking recipient email addresses and link URLs. When a click, open, or bounce is received, the data is retrieved from that internal database. For most usages of GreenArrow, this is acceptable and fast.
SimpleMH can be set to instead embed that information. For clicks and opens,
that information is embedded into the link. For bounces, it is embedded into
the email as a
To turn on this stateless even tracking:
echo 1 > /var/hvmail/control/opt.simplemh_stateless_event_handling
To turn it back off:
echo 0 > /var/hvmail/control/opt.simplemh_stateless_event_handling