Direct Database Access

Table of Contents
Overview
SimpleMH Click and Open Tracking Tables
Bad Address Tracking Table

Overview

We do not support writing to the database because it is possible to get the data into an invalid state by using raw CREATE/INSERT/UPDATE commands against this database. Instead, use the APIs. When using the APIs, validation of the data is enforced and sometimes additional actions (such as updating dependent records) are performed. Modifying the data or schema in the database through direct access is an unauthorized modification of the software and may invalidate your support.

We do, however, realize that some on-premises customers are going to want to perform read-only access on the database. Contact GreenArrow technical support to have a read-only account set up for you.

Because there is not an API that provides access to click, open, unsubscribe, bounce, and spam complaint data on each campaign, until the time when that API exists, we will provide documentation on queries that allow directly accessing this data. It is in our plans to add this API.

For other areas: Because the database behind GreenArrow Engine is complex we are not able to provide full documentation of the schema. However, we may provide some limited and partial documentation to point those who are going to access the database directly in the right direction. The database schema itself and the presence of documentation on the schema is subject to change without notice.

SimpleMH Click and Open Tracking Tables

The clickthrough_urls and clickthrough_clicks tables are used for SimpleMH click and open tracking. A url of '' is an open:

CREATE TABLE clickthrough_urls
(
        id                      int not null primary key default nextval('clickthrough_urls_seq'),
                                -- url id

        sendid                  varchar(100) not null,
                                -- sendid of the message with this url id embedded

        listid                  varchar(100) not null,
                                -- listid of the message with this url id embedded

        url                     text not null
                                -- url that person should be sent to if they click
                                -- if this is a zero length string, then this means that this is an open tracking url
);

CREATE TABLE clickthrough_clicks
(
        id                      int not null primary key default nextval('clickthrough_clicks_seq'),
                                -- primary key
                                -- not sure if we will ever use this, we may want to get rid of it so that we don't
                                -- have the overhead of maintaining the index for this field.

        urlid                   integer not null,
                                -- referrs to id field in clickthrough_urls table
                                -- this is what we use to get the sendid and listid

        clicktime               integer not null,
                                -- time in unix seconds that click came through

        emailaddress            varchar(100) not null CHECK ( LOWER(emailaddress) = emailaddress ),
                                -- email address of the subscriber that clicked
                                -- will be the string 'unknown' if we don't know the email address of the subscriber. this happens when
                                --   the email address lookup data has been purged a set period of time after the send.
                                -- must be normalized to lower-case

        html_or_text            char(1) not null,
                                -- 'h' for html, 't' for text

        email_code              integer
                                -- user-supplied code that applies to this email. for whatever the user wants to do or track.
);

Here’s a query to get the number of clicks for each URL in a SendID:

SELECT aa.url, SUM(bb.partial_num) AS "clicks_total"
FROM
        clickthrough_urls aa,
        ( SELECT urlid, COUNT(1) AS "partial_num" FROM clickthrough_clicks
                WHERE urlid IN ( SELECT id FROM clickthrough_urls WHERE sendid = '@@SENDID@@' ) GROUP BY urlid ) bb
WHERE
        aa.id = bb.urlid
GROUP BY
        aa.url

The above has the query planner:

Look at clickthrough_urls and get a list of urlids that should be included in this data set.
Look at clickthrough_clicks to get the actual data.
Use the outer SELECT to GROUP BY the urilid and SUM the partial_sum number, because there might be multiple urlids that all have the same URL. These urlids could either have different ListIDs, or they could have been inserted at different times, when due to MVCC isolation, they could not see each other in the database.

Here’s a query to get the number of distinct subscriber clicks for each URL in a SendID:

SELECT aa.url, COUNT(1) AS 'clicks_total', COUNT(DISTINCT bb.emailaddress) AS 'clicks_unique'
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
        AND aa.sendid = '@@SENDID@@'

Here’s a query to get breakdown of clicks over time:

SELECT date_trunc('hour', to_timestamp(bb.clicktime)) AS "hour", COUNT(1) AS "clicks_total"
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
        AND aa.sendid = '@@SENDID@@'
        AND aa.url <> ''

Notes:

Valid options for the first argument of date_trunc are in PostgreSQL’s Functions and Operators documentation, section 9.9.2.
Wrap date_trunc with EXTRACT(epoch FROM ....) if you want to convert back to seconds past the Unix epoch.

Here’s a query to get breakdown of opens over time:

SELECT date_trunc('hour', FROM to_timestamp(bb.clicktime)) AS "hour", COUNT(1) AS "opens_total"
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
        AND aa.sendid = '@@SENDID@@'
        AND aa.url == ''

Here’s a query to get raw data on all clicks for a SendID:

SELECT aa.url, aa.sendid, aa.listid, bb.clicktime, bb.emailaddress, bb.html_or_text, bb.email_code
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
        AND aa.url <> ''
        AND aa.sendid = '@@SENDID@@'

Here’s a query to get data on all opens for a SendID:

SELECT aa.sendid, aa.listid, bb.clicktime, bb.emailaddress, bb.html_or_text, bb.email_code
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
        AND aa.url = ''
        AND aa.sendid = '@@SENDID@@'

Here’s a query to get all data in the database:

SELECT aa.url, aa.sendid, aa.listid, bb.clicktime, bb.emailaddress, bb.html_or_text, bb.email_code
FROM
        clickthrough_urls aa,
        clickthrough_clicks bb
WHERE
        aa.id = bb.urlid
ORDER BY clicktime

Bad Address Tracking Table

The bounce_bad_addresses table is used to track email addresses which were identified as being bad due to bounces, spam complaints and unsubscribes:

CREATE SEQUENCE bounce_bad_addresses_id_sequence;

CREATE TABLE bounce_bad_addresses(
        id              INT DEFAULT nextval('bounce_bad_addresses_id_sequence') NOT NULL,
                        -- database identifier

        listid          VARCHAR(100) NOT NULL,
                        -- listid

        sendid          VARCHAR(100) NOT NULL,
                        -- sendid

        email           VARCHAR(256) NOT NULL,
                        -- email address that is bad

        bouncetime      INT NOT NULL,
                        -- time in seconds since epoch that the bounce came in

        type            CHAR(1) CHECK ( type IN ('s','h','o','c','u') ),
                        -- type of bounce: s=soft, h=hard, o=other, c=spam complaint, u=unsubscribe

        code            SMALLINT NOT NULL,
                        -- bounce code

        is_local        SMALLINT CHECK (is_local IN (0,1)) NOT NULL,
                        -- if this was a local bounce (last bounces that triggered repeat processing counts)

        repeat_info     TEXT,
                        -- json information about the repeat bounce run that caused this.
                        -- hash with keys:
                        --  count   - number of bounces detected in run
                        --  missing - number of missing bounces in run (apart from count)
                        --  bounces - list ref of hashes containing info on bounces. same format as the
                        --            hashes in bounce_repeat_tracker.data
                        --  start   - unix timestamp of first bounces
                        --  end     - unix timestamp of last bounce

        text            TEXT,
                        -- the bounce description (QSBF information or part of the bounce message)

        click_tracking_id TEXT,
                        -- Click-Tracking-ID associated with the message/bounce

        PRIMARY KEY (id)
);

Here’s a query to get all bounces which should result in subscriber deactivation for a send:

SELECT * FROM bounce_bad_addresses
WHERE sendid = '@@SENDID@@' AND type IN ('s', 'h', 'o')
ORDER BY id;

Here’s a query to get all spam complaints for a send:

SELECT * FROM bounce_bad_addresses
WHERE sendid = '@@SENDID@@' AND type = 'c'
ORDER BY id;

Here’s a query to get all unsubscribes for a send:

SELECT * FROM bounce_bad_addresses
WHERE sendid = '@@SENDID@@' AND type = 'u'
ORDER BY id;

Here’s a query to get all bounces for a subscriber:

SELECT * FROM bounce_bad_addresses
WHERE email = '@@EMAIL@@' AND listid = '@@LISTID@@' AND type IN ('s', 'h', 'o')
ORDER BY id;

Here’s a query to get all spam complaints for a subscriber:

SELECT * FROM bounce_bad_addresses
WHERE email = '@@EMAIL@@' AND listid = '@@LISTID@@' AND type = 'c'
ORDER BY id;

Here’s a query to get all unsubscribes for a subscriber:

SELECT * FROM bounce_bad_addresses
WHERE email = '@@EMAIL@@' AND listid = '@@LISTID@@' AND type = 'u'
ORDER BY id;