Remote Lists

• Table of Contents
• Description
• Permissions
• Database Connections
• Delivery
• Removing Subscribers
• Limitations
• Custom Fields
• Notes

Description

Remote Mailing Lists provide a way to use GreenArrow Studio to send to recipients stored in an external MySQL, PostgreSQL or Microsoft SQL Server database.

When a campaign is sent, GreenArrow Studio connects to the external database and runs a query to retrieve the recipient list for the campaign.

Remote Lists are supported in GreenArrow Studio 4.55.0 and later.

Permissions

By default, only the System Organization can create remote lists. To enable this feature for other organizations, see the “Permissions” section of the organization’s page.

The following options are available:

• Allow users to select any remote database connection.
• Allow users to select from the following remote database connections.
• No access to remote lists.

Using these options, the system administrator can restrict which remote database connections an organization can access.

Database Connections

GreenArrow Studio’s Remote Lists will use those connections configured in GreenArrow Engine. To configure a new Database Connection to use with a Remote List, go to the Configure => Database Connections menu in GreenArrow Engine.

If there are no Database Connections configured in GreenArrow Engine, the option to create a Remote Mailing List will not appear in Studio.

Delivery

At the start of a campaign, the specified query is run against the remote database and the results are loaded into a local cache. Suppression lists are applied to the local cache, and then sending is done from the local cache. After the campaign is sent the local cache is deleted.

There are three special column names in the returned data:

• email – provides the email address to send to. This is required.

  Email addresses must meet the definition provided in the Subscriber Record documentation, except that addresses which contain International Domain Names are not currently supported for Remote Lists; however, if you convert the IDN to punycode before using it in GreenArrow Studio, you can send to those addresses.

• distinct_id – is designed as a primary key to identify the subscriber in addition to or replacement of the email address. This is typically the primary key from your database.

  The distinct_id is passed back to you through the Event Notification System (in the studio_rl_recipid column) and in the detailed click, open, bounce, unsubscribe, & spam complaint reports, so that you can associate the event back to the subscriber in your database.

  When processing Event Notification System data, we recommend keying off of the distinct_id, rather than the subscriber’s email address for two reasons:

  • Under some circumstances, the Event Notification System won’t know the subscriber’s email address. For example, if a bounce is received more than 90 days after an email is sent to a Remote List subscriber, GreenArrow would have already rotated out the subscriber’s email address, and so will not possess that information. In that situation, GreenArrow will still know the subscriber’s distinct_id, though.

    When GreenArrow does not know a subscriber’s email address, it replaces it with an @bounced-address-not-found.greenarrow-internal-error.drh.net address.

  • You could have multiple subscribers with the same email address.

  The distinct_id is also provided as a custom field so that it can be used in email campaign content.

  The distinct_id may be NULL or an integer between 1 and 2,147,483,647.

  If you provide a distinct_id, it must be consistent for the subscriber record over time. Otherwise, Engine’s system that detects email addresses that are repeatedly soft-bouncing will not work.

• obscured_email can be provided to prevent Studio from logging the recipient’s email address.

  The value provided for obscured_email must be a valid email address, localpart, or localpart followed by an @ sign as defined by Marketing Studio. If the value provided does not match one of these patterns, then the recipient is skipped.

  When obscured_email is provided, an obscured email address will be used (instead of the recipient’s real email address) in the areas listed in the Obscured Email Addresses document’s What does this protect? section.

  See the Obscured Email Addresses documentation for more information.

The data in all other columns are used as custom fields. (The custom field names don’t need to be already defined in the mailing list.)

Column names are case-insensitive. Duplicating a column name results in an error.

If there is an error connecting to the remote database or running the query, the system will automatically retry 10 additional times. Each retry has a 30-second delay between them. Error messages will be logged in the Campaign History Log.

An invalid value in an email, distinct_id, or obscured_email column will cause the row to be skipped (and the campaign will send to the remaining valid rows). The number of rows skipped due to invalid data are included in the Campaign History Log along with example data from the first five skipped rows.

Also included in the Campaign History Log are:

1. How many seconds it took to get the data from the remote database.

2. The number of email addresses suppressed due to suppression lists.

Modified Query

For Postgres databases, the query will be wrapped in a cursor when the subscriber list is downloaded for sending. This provides a significant performance boost to GreenArrow Studio. We have not observed any cases in which this causes a problem with any queries.

Removing Subscribers

It is essential that you stop sending to subscribers which bounce (indicating that the email address is bad), unsubscribe, or generate a spam complaint.

This can be accomplished two ways:

1. Receive bounce_bad_address, studio_unsub, and scomp events from the Event Notification System, and update your database to disable the subscriber. This is the recommended method.

2. Configure GreenArrow Studio to add bounces, unsubscribes, and spam complaints to a suppression list which applies to this mailing list.

   This is not the recommended method because your database gets out of date and it is harder to build a system where subscribers can re-subscribe.

Important note: Even if you don’t use GreenArrow Studio’s unsubscribe link, instead opting to insert an unsubscribe link which goes to your own system, it is essential that you handle the studio_unsub event. The studio_unsub event may be generated by unsubscribe requests which initiate through the List-Unsubscribe header in the email.

Limitations

Because no subscribers are stored in GreenArrow Studio with a Remote List, the following features are not available:

• Subscribers
• Subscriber Imports
• Subscriber Exports
• Autoresponders
• Web Forms
• Web Views (the view email in your web-browser link)

Additionally:

• The Unsub Redirection URL for a mailing list may not use custom field replacement codes.

Custom Fields

Custom Fields can be defined on a Remote List, but their use is different than in a standard list.

In a Remote List, the data returned by the query determines the custom fields used for sending, completely regardless of the custom fields defined for the mailing list.

Specifically:

• Custom field validations and data-type limitations are not applied to the data.
• A custom field does not have to be defined on the mailing list to be used in sending, as long as it is returned by the query.

Custom fields defined in the mailing list are only used for:

• The replacement code menu in the HTML editor
• Preview and Seed Custom Field Values
• Special Sending Rule previews
• Interpolation settings

If a custom field is not defined on a mailing list, the following interpolation settings will be used:

• Data will be HTML encoded when replaced into HTML content.
• Newlines will not be replaced with <BR/> tags.
• Data will be URL encoded when replaced into URLs.

It is best practice to only create “Text Multiline” custom fields on Remote Lists, as the data retrieved from the query will be treated as such.

Notes

• Except for bounces, the case of the email address will be preserved for Event Notification System events and in the list of events on the Statistics page. The case of the email address is not guaranteed to be preserved on bounces.

• Recipient email addresses, for the purpose of processing bounces and spam complaints, are kept for 60 days. Bounces or spam complaints received after this 60-day window will be recorded but will be recorded with an address at the bounced-address-not-found.greenarrow-internal-error.drh.net domain.

• When updating a remote segment, any trailing whitespace or semicolons (;) in the query are removed before saving. This is because GreenArrow will optionally wrap the query in order to obtain a COUNT of the recipient list when scheduling the campaign.