Sending a mailing can be triggered in Higher Logic Thrive Marketing Professional (Thrive Marketing Professional) as a result of actions occurring within the iMIS database. This iMIS Triggered Mailing feature has many applications, such as sending out welcome mailings to new members when they're added to iMIS or sending confirmation mailings after someone registers for an event.
Because of the many possible applications, some database programming is required to enable and use the Triggered Mailing feature. The procedure below explains how to enable this feature.
NOTE: This requires extension "Remote Triggered Mailings" (62) to be enabled.
Enable Ad-hoc Remote Queries
NOTE: If you're using SQL Server 2005, ensure that the SQL Server Browser Service is running on your database server.
Ad-hoc remote queries also must be enabled. To do this on your database server, go to the SQL Server Surface Area Configuration tool and follow these steps:
- Click Surface Area Configuration for Features.
- In the new window, click Ad Hoc Remote Queries on the left.
- On the right, check the Enable OPENROWSET and OPENDATASOURCE support boxes.
- Click OK.
- Close the Surface Area Configuration tool.
Schedule a Triggered Mailing
Informz_Schedule_Triggered_Email uses the mailing_id of the mailing you want to trigger to send the mailing to an iMIS name_id. This stored procedure is called from whatever process triggers the email to go out (e.g., a form is completed on client’s web site, an order record is added to your iMIS database, a member registers for a particular event, etc.).
Syntax
EXEC informz.INFORMZ_SCHEDULE_TRIGGERED_EMAIL @mailing_id, @name_id, @date_to_send, @add_if_already_exists
Parameters
The stored procedure takes four parameters:
| Parameter | Type | Description |
|---|---|---|
| @mailing_id | Int | Identifier of the mailing you want to trigger Thrive Marketing Professional to send. If you need assistance locating this in the UI, create a case. |
| @name_id | Varchar; length=10 | Identifier (from the iMIS NAME table) of the intended recipient. |
| @date_to_send | Datetime | The exact date and time you want Thrive Marketing Professional to send the email to this person. |
| @add_if_already_exists | Bit (0 or 1) | Whether to schedule this email for this recipient if a record with the same @mailing_id and @name_id exists but hasn’t yet been scheduled in Thrive Marketing Professional. The default is zero (0), do not to schedule the same email to this person multiple times. |
- Return Code Values - 0 (success), 1 (failure). The most likely cause of a failure is that the value for the @name_id parameter doesn’t exist in the NAME table.
- Result Sets - There are no returned from this stored procedure.
Enable Ad-hoc Remote Queries
Ad-hoc remote queries must be enabled for Thrive Marketing Professional to transfer data into user-defined tables. Below is the command line option for setting the values.
NOTE: You must be connected to a SSMS query window as a sysadmin or as a user with serveradmin rights, and you must have the query window connected to your iMIS database.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1 GO
RECONFIGURE
INFORMZ.INFORMZ_TRIGGERED_EMAILS
This table is used by the Triggered Mailing feature to store the list of the Mailing IDs and Name IDs of emails:
- waiting to be scheduled in Thrive Marketing Professional,
- emails that have already been scheduled successfully in Thrive Marketing Professional, and
- emails that were not scheduled in Thrive Marketing Professional because of an error.
The TRIGGER_STATUS field in this table indicates the status of the email. The possible values for the TRIGGER_STATUS field are:
| Trigger_Status | Description | Resolution |
|---|---|---|
| 0 | New record; hasn't been scheduled in Thrive Marketing Professional yet. | No error occurred. |
| 1 | In progress. | In progress. |
| 6 | Record successfully processed and scheduled in Thrive Marketing Professional. | No error occurred. |
| 100 | Mailing ID does not exist on brand. | The mailing ID could not be found in the Thrive Marketing Professional account. Verify that the Mailing ID is valid by checking in the UI as shown by Higher Logic. |
| 101 | Mailing ID has been deleted from Thrive Marketing Professional. | The mailing has been deleted in Thrive Marketing Professional. To continue using this Mailing ID for a triggered email, undelete the mailing in Thrive Marketing Professional. |
| 102 | Mailing ID not scheduled in Thrive Marketing Professional. | The mailing was not scheduled as a triggered mailing in Thrive Marketing Professional, or the triggered mailing status was removed from the mailing. Reset the mailing's trigger status. |
| 103 | Subscriber not found in Thrive Marketing Professional. | The Name ID could not be found in the Thrive Marketing Professional database. This could be because the EMAIL field on the NAME record is empty or NULL, or the NAME.STATUS field contains a status not selected for this client's bridge install. |
| 104 | Subscriber is unsubscribed from Thrive Marketing Professional. | Thrive Marketing Professional attempted to schedule the mailing to the subscriber, but the subscriber was unsubscribed. Therefore, Thrive Marketing Professional cannot send the email to this person. |
| 105 | Subscriber not in target group at time of mailing. | The subscriber was not listed as being in the target group when the mailing was published. |
A process should be established whereby this table is checked periodically for records with a TRIGGER_STATUS >= 100. Once the error has been resolved (e.g., mailing has been scheduled correctly after receiving error 102), the TRIGGER_STATUS field of that row can be set to zero (0) to allow Thrive Marketing Professional to attempt to schedule the email again.
NOTE: If a record has TRIGGER_STATUS = 0 and the “date to send” has already passed, Thrive Marketing Professional processes the mailing immediately.