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 customer 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.
Topics covered in this article:
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 customer records so this is an important step and you can learn more about setting it up in the next section of this post.
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:
You'll see ALL email response events written to the table T_EPROMOTION_RESPONSE_HISTORY.
Next you'll see the highest priority email response written to T_PROMOTION. The priority of the email response is set within your TR response tables. By default the unsubscribe response code (@responsecode 5) has the highest priority and will overwrite other responses like open and click.
Lastly, you'll see an overview of the T_Promotion responses on the customer record under the Connections tab > Promotions radial.
In Tessitura v12.5 and earlier
For unsubscribes (@responsecode 5), it was recommended to update Contact Restrictions by setting the E-Market Restriction to No E-Marketing.
In Tessitura v14+
For unsubscribes (@responsecode 5), Contact Restrictions are replaced by Contact Permissions which are handled under the Contact Details tab of the constituent record as a new radial button. The addition of Contact Permissions now 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.|
For hard bounces (@responsecode 2), it is recommended to uncheck the allow marketing indicator (market_ind) under Contact Details > Electronic Address.
The LP_UPDATE_EMAIL_PROMOTION stored procedure is accessed by Tessitura database users that have permissions to run queries in the impressario 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.
As a note of reference:
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. *If you are in a consortium, please review this article for notes.
Example screenshot of two tables mapped using the default for license holders with the click response mapping between the two tables...
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 customer records in Tessitura after your email campaigns are sent.
What are the parameters for the stored procedure execution?
Here are all of the WP_UPDATE_EMAIL_PROMOTION parameters and valid values for each:
- @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/2011')
- @eaddress: (any email address associated with the customer)
- @url: (any url, e.g. 'http://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/2011','email@example.com',null
To simulate a click...
To run a click event for "customer 2345" in "download 678" execute: