- Table of Contents
- Database Connections
- Removing Subscribers
- Custom Fields
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.
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.
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
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.
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 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.
distinct_idis passed back to you through the Event Notification System (in the
studio_rl_recipidcolumn) 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
When GreenArrow does not know a subscriber’s email address, it replaces it with an
You could have multiple subscribers with the same email address.
distinct_idis also provided as a custom field so that it can be used in email campaign content.
NULLor an integer between
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_emailcan be provided to prevent Studio from logging the recipient’s email address.
The value provided for
obscured_emailmust 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.
obscured_emailis 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 two more times. The
first retry is after a 1-minute delay, and the second retry is
after a 5-minute delay. Error messages will be logged in the
Campaign History Log
An invalid value in an
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:
How many seconds it took to get the data from the remote database.
The number of email addresses suppressed due to suppression lists.
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.
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:
scompevents from the Event Notification System, and update your database to disable the subscriber. This is the recommended method.
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 may be generated by unsubscribe
requests which initiate through the
List-Unsubscribe header in
Because no subscribers are stored in GreenArrow Studio with a Remote List, the following features are not available:
- Subscriber Imports
- Subscriber Exports
- Web Forms
- Web Views (the
view email in your web-browserlink)
Unsub Redirection URLfor a mailing list may not use custom field replacement codes.
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.
- 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
- 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.
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
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
COUNTof the recipient list when scheduling the campaign.