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.Thedistinct_id
is passed back to you through the Event
Notification System (in thestudio_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. Thedistinct_id
is also provided as a custom field so that it can used in email
campaign content.The
distinct_id
may beNULL
or an integer between1
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:
- How many seconds it took to get the data from the remote database.
- 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:
- Receive
bounce_bad_address
,studio_unsub
, andscomp
events
from the Event Notification System, and update your database to
disable the subscriber. This is the recommended method. - 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.