The LP_UPDATE_EMAIL_PROMOTION stored procedure is required for updating email responses in Tessitura from WordFly email campaigns. This stored procedure specifically handles how unsubscribes and hard bounces are updated on the constituent record. Note that preference updates are handled by a separate stored procedure. Let’s review some common questions about the LP_UPDATE_EMAIL_PROMOTION stored procedure and how to test it.
What the stored procedure does
WordFly updates opens, clicks, bounces (soft and hard), and opt-outs in Tessitura by calling the WP_UPDATE_EMAIL_PROMOTION stored procedure. This procedure in turn calls the localized LP_UPDATE_EMAIL_PROMOTION stored procedure, giving you a place to perform custom processing for email responses.
You’ll need to configure the LP_UPDATE_EMAIL_PROMOTION stored procedure for use with your Tessitura database. When it is not configured, Tessitura is unable to update opt-outs and hard bounces on constituent records.
When the LP_UPDATE_EMAIL_PROMOTION stored procedure is working and processing email responses, those responses will show up in a few places in your database:
ALL email response events are written to the table T_EPROMOTION_RESPONSE_HISTORY.
The highest priority email response is written to T_PROMOTION. The priority of the email response is set within your TR_EMAIL_RESPONSE_CODE tables. By default the unsubscribe response code (@responsecode 5) has the highest priority and will overwrite other responses like open and click.
An overview of the T_PROMOTION responses is shown on the constituent record under the Connections tab > Promotions radio.
In Tessitura v14+
Tessitura introduced Contact Permissions for managing unsubscribes (@responsecode 5). Contact Permissions are found under the Contact Details tab of the constituent record as a radio button. The addition of Contact Permissions allows for either a Global Unsubscribe or a Targeted Unsubscribe. While there are both options, we recommend a Global Unsubscribe and allowing subscribers to select preferences to help manage communication expectations.
| Global Unsubscribe | Revoke email permissions for all Contact Permission Types for the customer. |
| Targeted Unsubscribe | Revoke email permissions for a specific Type for the customer. For this to work, the unsubscribe needs to be correlated with a specific Contact Permission Type using data available on the promotion. The recommended approach is to use media type, with an email-specific Media Type set up for each corresponding Contact Permission Type. For example, Contact Permission type is Marketing and the Media type is Email Marketing. |
Organizations can continue to use Contact Restrictions in v14+ Tessitura for unsubscribe management. Contact Restrictions will eventually be phased out in future upgrades and a migration path will be provided to assist with moving over to Contact Permissions.
For hard bounces (@responsecode 2), it is recommended to uncheck the Allow Marketing box (market_ind) under Contact Details > Electronic Addresses.
Configure the stored procedure
The LP_UPDATE_EMAIL_PROMOTION stored procedure is accessed by Tessitura database users that have permissions to run queries in the impresario database.
When configuring the stored procedure, note these items:
- Decide which of the sample functionality you would like to enable.
- Remove or comment out any unwanted code and verify that the sample code makes sense for your organization’s Tessitura configuration, paying special attention to @response_code
- Make a point to modify the procedure to remove hard bounces, unsubscribes, and abuse complaints (Note: abuse complaints by default are send back as @responsecode 5 which is the same as unsubscribe).
- Reach out to Tessitura Support for troubleshooting, or Tessitura Consulting for customizations.
In Tessitura, your email responses are set up in system tables TR_EMAIL_RESPONSE_CODE and reference the TR_RESPONSE table. Tessitura comes with preset values for open, click, soft bounce, hard bounce, and opt-out/abuse complaint.
Learn more about email response codes
Test updates to the stored procedure
The best way to handle testing in the database is to execute the WP_UPDATE_EMAIL_PROMOTION stored procedure directly in the database. You’ll need to find valid download number (dl_no) and customer number (customer_no) values from your T_PROMOTION table in Tessitura. Once you’ve completed testing you can make your actual updates in the live stored procedure which will start updating responses on constituent records in Tessitura after your email campaigns are sent.
WP_UPDATE_EMAIL_PROMOTION parameters for the stored procedure execution
| PARAMETER | VALID VALUE |
|
@dl_no |
Value from T_PROMOTION |
|
@customer_no |
Value from T_PROMOTION |
|
@response_code |
5 for unsubscribe, 4 for click, 3 for soft bounce, 2 for hard bounce, 1 for open |
|
@response_dt |
Any valid date string, e.g. '10/24/2021' |
|
@eaddress |
Any email address associated with the customer |
|
@url |
Any url, e.g. 'https://www.google.com'. Only used for clicks. |
Here are some test examples you can run in your database:
To simulate an unsubscribe
To run an unsubscribe for "customer 1234" in "download 678" execute:
exec impresario.dbo.[WP_UPDATE_EMAIL_PROMOTION] 678, 1234,5,'10/24/2021','test@domain.com',null
To simulate a click
To run a click event for "customer 2345" in "download 678" execute:
exec impresario.dbo.[WP_UPDATE_EMAIL_PROMOTION] 678, 2345,4,'10/24/2021','test@domain.com','http://www.google.com