LP_UPDATE_EMAIL_PROMOTION Stored Procedure

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 the unsubscribe 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:

What does the LP_UPDATE_EMAIL_PROMOTION stored procedure do?

How to configure LP_UPDATE_EMAIL_PROMOTION stored procedure

Testing updates to the LP_UPDATE_EMAIL_PROMOTION stored procedure

 

 

What does the LP_UPDATE_EMAIL_PROMOTION stored procedure do?

 

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 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.

 

 

On the customer's record under the General tab, the LP stored procedure can (and is recommended to) be configured to update both unsubscribes and hard bounces. *To apply WordFly's recommended procedure updates, use the 'alter' script attached to this support article.

 

For an unsubscribe, we recommend...

 

The E-Market Restriction to 'No E-Marketing' when an unsubscribe (@responsecode 5) is sent back from a WordFly email campaign.

 

 

For a hard bounce, we recommend...

 

It is recommended to flip the market_ind on the email address (not the customer's record) to do not email when a hard bounce (@responsecode 2) is sent back from a WordFly email Campaign. 

 

 

 

How to configure the LP_UPDATE_EMAIL_PROMOTION stored procedure

 

The LP_UPDATE_EMAIL_PROMOTION stored procedure is accessed by Tessitura database users that have permissions to run queries in the database.

 

When configuring the stored procedure, note these items:

 

  • Decide which of the sample functionality you would like to enable (find a text document with samples attached to this post)
  • 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
  • Use the 'alter' script attached to this post to set the procedure to WordFly recommended defaults. This script keeps all of the Tessitura sample code in the lp_update_email_promotion stored procedure, but comments it out. Above that code it adds handling for unsubscribes/complaints and hard bounces. Unsubscribes/Complaints flip the emarket_ind on the customer; hardbounces flip the market_ind on the email address. 

 

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...

 

 

TR_EMAIL_RESPONSE_CODE table...

 

 

 

Testing updates to the LP_UPDATE_EMAIL_PROMOTION stored procedure

 

The best way to handle this is to just execute the WP_UPDATE_EMAIL_PROMOTION stored procedure directly in the database. You'll just 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 the subscriber's Tessitura record after your email campaigns are sent out.

 

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','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/2011','test@domain.com','http://www.google.com

 

Need some more examples or have any questions? Email us for other examples!

 

 

 

 

Have more questions? Submit a request
Powered by Zendesk