SimpleMH Click and Open Tracking

• Table of Contents
• Overview
  • clickthrough_clicks Table
  • clickthrough_urls Table
• Position of Open Tracking Image
• Dynamic Data
• Example Query
• Using HTTPS
• Event Tracking Metadata Storage
  • Local
  • Stateless
  • External
    • Pruning External Metadata
    • Troubleshooting External Metadata Database Connections
• Setting a User-Defined Link ID
• Skipping Click Tracking
• Universal Link Tracking
  • Overview
  • Flagging Links for Universal Link Handling
  • App Requirements
  • Well-Known Files
    • Configuring Well-Known Files
    • Apple Example
    • Android Example
    • Unknown or Unconfigured Domains
  • Reverse Proxy Considerations

Overview

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:

clickthrough_clicks Table

clickthrough_urls Table

Position of Open Tracking Image

When SimpleMH open tracking is enabled, a tracking image will be inserted into the HTML part.

To control the position of the open tracking image, add <opentag/> to your HTML part where you’d like it positioned. This must be done before the closing body tag (</body>). The tracking image is inserted into the first of the following within the HTML part:

1. In place of the first <opentag> or <opentag/> tag. If more than one of these tags exist, the others are kept in the HTML unaltered.
2. Immediately before the first </body> tag.
3. Appended to the end of the HTML.

Dynamic Data

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 encoded in the re-written URL. For example, the following URL:

http://server.example.com?query=string&params=included

Would be stored in SimpleMH’s database as:

http://server.example.com

The re-written URL would look like:

http://greenarrow.example.com/click/e72/HZGVmYXVsdDEwMDAxLHQxLGh0dHA6Ly93d3cuZHJoLm5ldA/qP3F1ZXJ5PXN0cmluZyZwYXJhbXM9aW5jbHVkZWQ/scd6c91ef45

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 clickthrough_urls table.

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.

Example Query

Here’s an example query that displays opens for SendID mm100525:

SELECT * FROM clickthrough_clicks WHERE id = (SELECT id FROM clickthrough_urls WHERE sendid = 'mm100525' AND url = '');

The SendID is constructed in accordance to the Mail Class’s configured Statistic Report Grouping.

Using HTTPS

GreenArow’s Apache instance listens on TCP ports 80 (HTTP) and 443 (HTTPS) by default.

We recommend using HTTPS on port 443 for click and open tracking. TLS Certificate Configuration shows how to configure HTTPS.

If GreenArrow’s Apache instance is on the same server as another Apache instance that is bound to port 443, 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.

Event Tracking Metadata Storage

By default, SimpleMH uses an internal database for tracking recipient email addresses and link URLs. When a click, open, unsubscribe, bounce or spam complaint is received, the data is retrieved from that internal database. For most usages of GreenArrow, this default behavior is acceptable and fast.

When using GreenArrow in a clustered configuration (such as Processing Events on Dedicated Servers), however, this behavior is not desireable – because events triggered due to messages delivered by one GreenArrow node might be processed on another GreenArrow node.

This is why we offer multiple options for SimpleMH Event Tracking Metadata Storage.

The system default Event Tracking Metadata Storage is set using default_event_tracking_metadata_storage. However, some systems may have a legacy configuration file /var/hvmail/control/opt.simplemh_stateless_event_handling set to 1. In that case, the default is stateless. The default_event_tracking_metadata_storage directive takes precedence over the legacy configuration file.

Local

This is the default mode for GreenArrow. With Local Metadata Event Tracking, event metadata (such as recipient email address and Click-Tracking-ID) is stored on disk on the GreenArrow node that delivered the email.

These events (clicks, opens, etc) must be processed by the same node that delivered the message.

This results in short click tracking links that minimally inflate your message size.

Stateless

With Stateless Metadata Event Tracking, SimpleMH can be configured to embed the email address, link URL, and other message metadata in the message itself instead of recording it in its database.

Stateless Metadata Event Tracking has two advantages:

1. It allows you to offload event processing to another GreenArrow server.
2. It reduces disk space requirements.

The downside of Stateless Metadata Event Tracking is that it causes the average email size to increase since the message itself is used to store this extra information. For clicks, opens, and unsubscribes the information is embedded into the link. Configuring simplemh_compress_links can somewhat help with the length of link URLs. For bounces, the information is inserted into the email as an X-Mailer-Info-Extra header.

Regardless of whether or not Stateless Metadata Event Tracking is configured, repeat bounce counting only takes into account the bounces for the particular server on which they are processed.

Stateless Metadata Event Tracking can be configured in the following ways:

• On the individual message, provide the following header:

  X-GreenArrow-EventTrackingMetadataStorage: stateless
  

• On the Mail Class, set Event Tracking Metadata Storage to Stateless Metadata Event Tracking.

• For all mail classes / messages that don’t otherwise set Event Tracking Metadata Storage, set default_event_tracking_metadata_storage.

External

External Metadata Event Tracking is an option that lets you provide an external Postgres database to which GreenArrow will connect for its Event Tracking Metadata. This external database is used for the metadata needed to process engine_click and engine_unsub events. For other events (such as engine_open or scomp), this mode is the same as Stateless Metadata Event Tracking.

This mode offers the following advantages over the other options:

• The tracking metadata is centralized, so emails sent from one GreenArrow node can have its events processed on any other GreenArrow node that is configured to use the same External Metadata Event Tracking Metadata database connection.

• Click tracking links are as short as possible – approximately 70 bytes plus the length of your domain name. This is regardless of the length of the destination URL. The links in this mode look like this:

  https://example.com/click?Pz1HRlKdRPkW1b2Uk8tUfxIz1HRlKdRPkW1b2Uk8tUfxI903b7e002a
  

The downsides of External Metadata Event Tracking include:

• You’re responsible for managing the external Postgres server, configuring replication for high availability, backing it up, and pruning its dataset.

• If there’s a service disruption with your external Postgres server, no External Metadata Event Tracking clicks, opens, and unsubscribes will be processed.

To configure External Metadata Event Tracking:

1. Create a Postgres Database Connection in GreenArrow using either the UI, API, or greenarrow.conf.
2. Set external_metadata_event_tracking_database to that Postgres Database Connection.
3. Add the following schema to your external Postgres server:

   create table ga_tracking_data (
     id uuid not null primary key,   -- will be a random uuid
     date date not null,             -- UTC date this entry was created
     data jsonb not null             -- json blob containing the tracking data
   );
   
   -- create an index for pruning
   create index ga_tracking_data__created_at_idx on ga_tracking_data (date);
   

External Metadata Event Tracking can be applied to messages in the following ways:

• On the individual message, provide the following header:

  X-GreenArrow-EventTrackingMetadataStorage: external
  

• On the Mail Class, set Event Tracking Metadata Storage to External Metadata Event Tracking.

If external_metadata_event_tracking_database is not configured, or if it is not reachable during pre-delivery message processing, then messages that would otherwise use External Metadata Event Tracking will instead use Stateless Metadata Event Tracking.

The total number of possible connections to the external_metadata_event_tracking_database can be up to the sum of apache_max_clients + simplemh_max_servers + /var/hvmail/control/opt.simplemh.redis_num_workers.

The external_metadata_event_tracking_database_read_failover directive allows you to specify a read failover. If the external_metadata_event_tracking_database cannot be reached (or if the requested data cannot be found) while processing a click, then external_metadata_event_tracking_database_read_failover will be checked.

The external_metadata_event_tracking_link_rows directive controls whether or not every row created in the external Postgres database will include the SendID and/or ListID values with which the link is associated.

Pruning External Metadata

GreenArrow does not automatically prune data from the metadata tracking table.

To prune, ensure this index exists:

CREATE INDEX IF NOT EXISTS ga_tracking_data__created_at_idx ON ga_tracking_data (date);

Then, you can prune old data using whatever time line you’d like:

DELETE FROM ga_tracking_data
  WHERE date < ((NOW() AT TIME ZONE 'UTC') - '90 days'::interval);
VACUUM VERBOSE ga_tracking_data;

Note that VACUUM will not free disk space – it will instead mark the deleted rows available for re-use.

Troubleshooting External Metadata Database Connections

Errors during delivery

When sending messages, if there is a problem with your External Metadata Database Connection, then GreenArrow will fall-back and generate the message using Stateless Metadata Event Tracking.

To find out why GreenArrow is falling-back to Stateless, and you use SMTP injection, you can run the following command to review the last 10 minutes of failures:

logdir_select_time --last '10 minutes' --dir /var/hvmail/log/simplemh \
  | tai64nlocal \
  | grep 'ERROR EXTERNAL METADATA'

If you inject using the HTTP Submission API (as opposed to SMTP), then you’ll want to run this command instead:

logdir_select_time --last '10 minutes' --dir /var/hvmail/log/simplemh2 \
  | tai64nlocal \
  | egrep 'ERROR EXTERNAL METADATA|PHP Warning'

Errors during event processing

If GreenArrow cannot connect to the External Metadata Database while processing a click, the end-user will receive an error message. Additional details will be logged to /var/hvmail/apache/logs/error_log.

Run the following command to view only the most recent 50 errors communicating with your External Metadata Database:

cat /var/hvmail/apache/logs/error_log \
  | grep 'ERROR EXTERNAL METADATA' \
  | tail -n 50

These errors will look something like this:

[Tue Jun 25 20:02:17.947168 2024] [php:warn] [pid 40635] [client 127.0.0.1:37599] PHP Warning:  pg_query_params(): Query failed: ERROR:  syntax error at or near "f"\nLINE 1: SELECT data, id x f b FROM ga_test_table WHERE id IN! ($1, $...\n                          ^ in /var/hvmail/webapp/click/click.php on line 157
[Tue Jun 25 20:02:17.947186 2024] [php:notice] [pid 40635] [client 127.0.0.1:37599] error: ERROR EXTERNAL METADATA: original_url=http://test.localhost/click?Pu02-MavNR-G1CLkUQMi8dzvfQOOWJ9Td6BnAPAfsd5c2a01b original_domain=test.localhost request_domain=127.0.0.1: cannot query the external event tracking metadata database

The log message includes:

• original_url – This is our best attempt to determine what the original URL was that was clicked on. If you have a proxy in front of GreenArrow that modifies the request, the actual URL might have been different than this.
• original_domain – The domain name that was used in the link that was clicked on.
• request_domain – The domain that was issued as a request to GreenArrow directly. This may be different than original_domain if you have a proxy in front of GreenArrow.

Setting a User-Defined Link ID

If you have a specific instance of a link that you’d like to track separately from other instances of that same URL, you can add the HTML attribute data-ga-linkid="linkid" after the href= attribute.

The Link ID may have a maximum length of 100 UTF-8 characters. Link IDs longer than this maximum length are ignored. Lead and trailing whitespace is trimmed.

Links that have a User-Defined Link ID are tracked separately in the statstics screen and API.

For example:

<a href="https://example.com" data-ga-linkid="link123">Our Great Link</a>

If the link ID is not quoted, it will end at the first space or > after the =, for example:

<a href="https://example.com" data-ga-linkid=link123 data-something-else="foo">A</a>
<a href="https://example.com" data-ga-linkid=link123>B</a>

This data attribute is not removed from the HTML before delivery.

Skipping Click Tracking

If you have a link for which you’d like to skip click tracking (so the URL will not be rewritten), you can add the HTML attribute data-ga-notrack after the href= attribute.

For example:

<a href="https://example.com" data-ga-notrack>Our Great Link</a>
<a href="https://example.com" data-ga-linkid="link-123" data-ga-notrack>Our Great Link</a>

This data attribute is not removed from the HTML before delivery.

Universal Link Tracking

Overview

Universal links (Apple) and app links (Android) allow mobile devices to open URLs directly in a native app instead of a web browser. When a recipient taps a link in an email on their mobile device, the operating system checks whether a native app has registered to handle that URL. If so, the link opens in the app; otherwise, it opens in the browser as usual.

This feature works through configuration files hosted at well-known paths on a domain. Apple devices look for an apple-app-site-association file, and Android devices look for an assetlinks.json file. These files tell the operating system which app is authorized to handle URLs on that domain.

Because GreenArrow rewrites links for click tracking, the original URL is no longer visible to the operating system. To solve this, GreenArrow supports a /uni/ prefix for click tracking links. When a link is marked for universal link handling, GreenArrow generates a click tracking URL with /uni/ at the beginning of the path (e.g., /uni/click... instead of /click...). The app registers to handle paths beginning with /uni/, so the operating system routes those links to the app. The app then calls back to GreenArrow to resolve the final destination URL and record the click.

Flagging Links for Universal Link Handling

To enable universal link handling for a specific link, add either universal="true" or deeplink="true" to the <a> tag. This attribute must appear after the href= attribute.

When GreenArrow encounters one of these attributes while rewriting links, it generates the tracking URL with a /uni/click prefix instead of the standard /click prefix.

Examples:

<a href="https://example.com/product/123" universal="true">View Product</a>
<a href="https://example.com/account" deeplink="true">My Account</a>

Recipients who do not have the app installed will still be redirected normally through the browser when clicking a /uni/click link. Using these attributes does not break click tracking for non-app users.

Links without these attributes continue to use the standard /click prefix and are not affected.

App Requirements

It is the responsibility of the app developer to implement the following behaviors:

Both platforms:

• When the app is opened via a link with the /uni/ prefix, the app must make an HTTPS GET request back to GreenArrow using that same /uni/click... URL. GreenArrow will respond with a redirect to the final destination URL. This step is required for the click to be recorded in GreenArrow’s statistics. The app should then navigate the user to the resolved URL or the appropriate in-app content.

iOS (Apple Universal Links):

• The app must include the URL domain in its Associated Domains capability, using the format applinks:links.example.com (where links.example.com is your URL domain).

Android (App Links):

• The app must define an intent filter that responds to the /uni/* path on the URL domain. This is configured in the app’s manifest file. For example:

  <intent-filter android:autoVerify="true">
      <action android:name="android.intent.action.VIEW" />
      <category android:name="android.intent.category.DEFAULT" />
      <category android:name="android.intent.category.BROWSABLE" />
      <data android:scheme="https"
            android:host="links.example.com"
            android:pathPrefix="/uni/" />
  </intent-filter>
  

Well-Known Files

GreenArrow can serve the well-known configuration files that Apple and Android devices use to verify universal link / app link associations. These files are configured separately for each URL domain.

To use this feature, the URL domain must have TLS enabled. The well-known paths are only valid over HTTPS.

Configuring Well-Known Files

Each URL domain has two optional JSON fields:

• Apple App Site Association JSON – The complete JSON document to serve for Apple’s apple-app-site-association file.
• Android Asset Links JSON – The complete JSON document to serve for Android’s assetlinks.json file.

These fields can be configured independently. For example, you can configure only the Apple file if you only have an iOS app.

GreenArrow validates that the content you enter is valid JSON, but does not validate that it is semantically correct for either Apple or Android. You are responsible for providing the correct JSON structure for your app.

These fields can be set through the URL Domains UI, API, or greenarrow.conf.

Apple Example

Apple devices request the following paths:

• https://links.example.com/.well-known/apple-app-site-association
• https://links.example.com/apple-app-site-association (legacy path for older devices)

GreenArrow serves both paths. An example JSON document:

{
  "applinks": {
    "details": [
      {
        "appIDs": [
          "ABCDE12345.com.example.myapp"
        ],
        "components": [
          {
            "/": "/uni/*",
            "comment": "Matches any URL whose path starts with /uni/"
          }
        ]
      }
    ]
  }
}

Android Example

Android devices request the following path:

• https://links.example.com/.well-known/assetlinks.json

An example JSON document:

[
  {
    "relation": ["delegate_permission/common.handle_all_urls"],
    "target": {
      "namespace": "android_app",
      "package_name": "com.example.myapp",
      "sha256_cert_fingerprints": [
        "AB:CD:EF:12:34:56:78:90:AB:CD:EF:12:34:56:78:90:AB:CD:EF:12:34:56:78:90:AB:CD:EF:12:34:56:78:90"
      ]
    }
  }
]

Unlike Apple, the path patterns that the app handles are defined in the app’s Android manifest via intent filters, not in the assetlinks.json file. Your intent filter must include the /uni/ path prefix.

Unknown or Unconfigured Domains

If a request is made for a well-known path on a domain that is not configured as a URL domain in GreenArrow, or on a URL domain that does not have the corresponding app JSON configured, GreenArrow responds with a 404 error.

Reverse Proxy Considerations

If you have a reverse proxy in front of GreenArrow, you may choose to serve the well-known files yourself instead of proxying them through to GreenArrow. If you proxy the well-known file requests through to GreenArrow, the request must include a Host header set to the URL domain being resolved, so that GreenArrow can serve the correct well-known file.