Remote Lists

Description

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

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

Remote Lists are supported in SMTPProvider Application 4.55.0 and later.

Database Connections

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

If there are no Database Connections configured in SMTPProvider Application, 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 two 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 SMTPProvider Application, 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. This is especially useful if you can have
    multiple subscribers with the same email address. The distinct_id
    is also provided as a custom field so that it can 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.)

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 one minute delay, and the second retry is
after a five minute delay. Error messages will be logged in the
Campaign History Log

An invalid value in an email or distinct_id 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.

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 SMTPProvider Application 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 SMTPProvider Application’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

Remote Lists may only be created on the System Organization. (This
is because they have access to Database Connections.)

Because no subscribers are stored in SMTPProvider Application 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.SMTPProvider-internal-error.drh.net domain.