Extra Suppression Clause

• Table of Contents
• Intro
• Configuration
• Testing

Intro

This feature allows you to prevent sends to a configurable set of email addresses by specifying an SQL condition. The condition should evaluate to a PostgreSQL boolean value: TRUE if the subscriber should be not emailed and FALSE if the subscriber should be emailed.

This SQL condition will be included in the WHERE clause of a larger SQL query.

This feature is available to on-premise customers only. Both on-premise and cloud customers have the option of using Suppression Lists, which provide an alternative method of suppressing sends to email addresses. Suppression Lists may be configured via GreenArrow’s web interface or API.

Configuration

Th Extra Suppression Clause is a server-wide setting which is controlled by updating the /var/hvmail/control/opt.studio.extra_suppression_sql file. Note that this file does not exist by default. It should be created when enabling this feature and may be deleted when you no longer wish to use this feature.

The following strings will be replaced if found in the configuration file:

Here is an example condition:

EXISTS (
  SELECT 1
  FROM zz_suppressed_inactive_emails
  WHERE organization_id = %%ORGANIZATION_ID%%
  AND email = LOWER(%%SUBSCRIBERS_TABLE%%.email)
)

Here’s the SQL to create the example table being queried above. The table would be created and populated while logged into GreenArrow’s PostgreSQL database:

CREATE TABLE zz_suppressed_inactive_emails (
    organization_id     int,
    email               text
);

In the above example, the zz_suppressed_inactive_emails table contains two columns: organization_id and email. If the organization has an entry for that email address, it is suppressed.

Testing

To determine the number of subscribers that both were suppressed using this system and would not have otherwise been suppressed by another mechanism, run the /var/hvmail/studio/script/check-special-suppression-counts script. Enter dispatch ID(s) that you wish to check, one per line and the program will return the number of suppressed subscribers in that dispatch ID’s campaign.

A campaign’s dispatch ID is the final number that appears in a URL when you’re viewing a camaign’s stats. For example, the dispatch ID for https://example.com/ga/statistics/64/ is 64.

In the example below, data is collected for dispatch IDs 1 and 64:

# /var/hvmail/studio/script/check-special-suppression-counts
Ready to accept dispatch IDs.
1
291
64
99182

When you’re finished, press Ctrl-C to exit.