Send Statistics Integration

Overview

GreenArrow Engine’s web interface can be configured to replace ListIDs with mailing list names, identify mail injection sources (for example, GreenArrow Studio), and replace SendIDs with descriptions for individual sends by running queries on local or remote databases. This page provides example configurations for GreenArrow Studio and Interspire Email Marketer, along with information on how other injecting applications can have their data integrated into GreenArrow Engine’s send statistics.

Default Values

By default, GreenArrow Engine’s web interface statistics display the following values in the Mailing List(s), Mail Class / Category, and Description columns:

• Mailing List(s) - the ListID of the send.
• Mail Class / Category - the Mail Class that was sent to, if SimpleMH was used.
• Description - the SendID of the send.

GreenArrow Studio Integration

If you’re using GreenArrow Studio, running the following command will make stats for GreenArrow Studio campaigns more readable:

hvmail_set sendid_listid_integration greenarrow_studio

The above command causes the following updates for GreenArrow Studio campaigns. Other sends will use the default values:

• Mailing List(s) - the mailing list name.
• Mail Class / Category - the string GreenArrow Studio.
• Description - the campaign name.

Interspire Email Marketer (IEM) Integration

Interspire Email Marketer (IEM) mailing list and campaign names can be integrated into GreenArrow Engine’s statistics. The next two sections contain examples of how to configure GreenArrow Engine to connect to IEM’s MySQL or PostgreSQL database. These examples cause the following updates for IEM campaigns. Other sends will use the default values:

• Mailing List(s) - the mailing list name.
• Mail Class / Category - the string IEM.
• Description - the campaign name.

MySQL Example

cat <<'EOT' > /var/hvmail/control/integration.db_pdo
mysql:dbname=iem
username
password
EOT

echo 'SELECT listid, name FROM email_lists ORDER BY LOWER(name)' > /var/hvmail/control/integration.all_lists

echo "SELECT name FROM email_lists WHERE listid = SUBSTRING(?,2)" > /var/hvmail/control/integration.listid_to_name

echo "SELECT 'IEM', ( SELECT name FROM email_newsletters bb WHERE bb.newsletterid = aa.newsletterid ) FROM email_stats_newsletters aa WHERE statid = ?" > /var/hvmail/control/integration.sendid_to_class_and_name

PostgreSQL Example

echo 'pgsql:user=username dbname=iem password=password' > /var/hvmail/control/integration.db_pdo

echo 'SELECT listid, name FROM email_lists ORDER BY LOWER(name)' > /var/hvmail/control/integration.all_lists

echo "SELECT name FROM email_lists WHERE listid::varchar = SUBSTRING(?,2)" > /var/hvmail/control/integration.listid_to_name

echo "SELECT 'IEM', ( SELECT name FROM email_newsletters bb WHERE bb.newsletterid = aa.newsletterid ) FROM email_stats_newsletters aa WHERE statid::varchar = ?" > /var/hvmail/control/integration.sendid_to_class_and_name

Other Integration Options

Other send statistics integration options can be configured by updating the files described below. The description for each file contains an example GreenArrow Studio configuration. Additional example configurations are shown in this page’s Interspire Email Marketer (IEM) Integration section. Each file is located within the /var/hvmail/control/ directory.

integration.db_pdo

This file records the DSN that PHP’s PDO should use to connect to the database with. You can find documentation for constructing DSNs on PHP.net’s PDO Drivers page. Here’s a PostgreSQL example taken from a GreenArrow Studio installation:

pgsql:user=greenarrow dbname=greenarrow password=password

integration.sendid_to_class_and_name

This file records the SQL query to run given a SendID in a single bind variable. Returns one row with two columns. The first column is the value to populate the Mail Class / Category column with, and the second column is the value to populate the Description column in GreenArrow Engine’s statistics. Here’s the default value:

SELECT * FROM s_sendid_to_mailclass_and_description(?) AS (mail_class_name varchar, description varchar)

integration.listid_to_name

This file records the SQL query to run given the ListID in a single bind variable. Returns one row with one column. The column’s value is used to populate the Mailing List(s) column in GreenArrow Engine’s statistics. Here’s the default value:

SELECT name FROM s_mailing_lists WHERE id::varchar = SPLIT_PART(?, 'a', 2)

integration.all_lists

This file records the SQL query to run to retrieve information on all mailing lists in the system. Returns multiple rows with two columns. The first column is the ListID and the second column is the name of the mailing list. This data is used to replace ListIDs in the Mailing List drop-down menu with mailing list names in GreenArrow Engine’s statistics:

Here’s the default value:

SELECT 'a' || id, name FROM s_mailing_lists ORDER BY lower(name)