Monday, May 03, 2010

How to use the API method SendMassEmail to connect to a website database

The SendMassEmail method of the JangoMail Email Marketing API is a versatile method that can launch an email campaign to recipients in a variety of ways. There are various "To" input parameters, each with its own use depending on where your email list data is stored.

The full specification of the method is located here. The "To" input parameters include:
  • ToGroups - send to an entire Email List - specify the name of an Email List (also called an Email Group) to send to. Specify multiple Email Lists separated by a comma
  • ToGroupFilter - sent to a segment of an Email List - specify the name of an Email List followed by a colon followed by a valid SQL filter (just the WHERE clause). For example, if you have an Email List called Contact List with the fields FirstName, LastName, and Company, then an acceptable input value would be "Contact List: Company = 'ABC Corporation'". An additional acceptable input value would be "Contact List: Company = 'ABC Corporation' OR FirstName = 'John'".
  • ToOther - specify a string of email addresses in any unstructured format. JangoMail will parse the data and pull out the email addresses and send to them. Optionally, pass structured data on-the-fly with field name and field value information, in order to send personalized emails to a dataset on the fly.
  • ToWebDatabase - pass in a SQL query (usually a SELECT statement) that will be executed against your web site database, in real-time, to pull down a list of email list data, to send a personalized email campaign.
This article will focus on how to use the ToWebDatabase input parameter.

In order to be able to use this feature, your JangoMail account first needs to know how to connect to your web database platform. There are two steps to this:
  1. You must create a Web Database Connection Profile. Create a Web Database Connection Profile under Lists --> Databases --> Add New Item --> Internet Web Database. If you're not familiar with creating a connection profile, see the detailed PDF guide.
  2. Then you must set this Profile as the Master Profile, so that the API knows that this connection profile is the one to use when you use ToWebDatabase. Set the Web Database Connection Profile as your Master Profile under Settings --> Integrating JangoMail with Other Systems --> Set Master Profile for Web DB. See screenshot below:

Set Master Connection Profile here.

If you attempt to call SendMassEmail and specify a value for ToWebDatabase without first having set a Master Profile, you will get this web service Exception:

MissingWebDatabaseSettings: There is no Master Web Database Connection Profile set for your account. This must be set prior to specifying a SQL query for the ToWebDatabase parameter.
Once you have set the Master Profile, you're ready to make the API call and pass in a SQL query in the ToWebDatabase input parameter. If you wanted to send to all email addresses that had been added to your database in the last 24 hours, for example, your SQL query might be:

select * from Accounts where AccountCreateDate >= dateadd(hh, -24, getdate())

And the full API call would look like the screenshot below: