In this article
WordFly sends back campaign results to Tessitura. Data sent back to the Tessitura database includes: opens, clicks, and unsubscribes (opt-outs, hard bounces and abuse complaints). If you're not seeing these responses in WordFly there might be something that needs to be adjusted with your integration or with your data set up.
Once you have the WordFly-Tessitura integration set up your account will be constantly sending reporting data back to your database. But, there are 4 pieces of data that are required on your list imports for data to go back to Tessitura.
The data you need in your list import is:
- email address
- customer number
- download number
- source number
Email address and customer number are added to your list via your Output set.
Download number and source number are added to your list when you promote your list in Tessitura. Lists are promoted with an appeal, media and source group, and the promotion itself is generated slightly differently in Extractions versus List Manager lists. Sources are required to track responses to an appeal in Tessitura. Extractions or List manager lists generated without a promotion cannot send data back to Tessitura.
Review these articles to make sure you are creating and importing your lists correctly:
List Manager lists
Second to the list data, Tessitura also requires the LP_UPDATE_EMAIL_PROMOTION stored procedure to be set up in your database. This must be running for unsubscribes and hard bounces to be processed in the CRM.
Learn more about setting up this stored procedure here: LP_UPDATE_EMAIL_PROMOTION stored procedure
Those are the two key pieces required for sending data back to Tessitura. Next, let’s look at where email responses show up in Tessitura.
Email responses show up in three places in Tessitura.
You’ll find the highest priority response for every subscriber emailed logged in system table T_PROMOTION. The email address is written to T_PROMOTION when WordFly sends back a response. Typically the unsubscribe is set to a higher priority than an open. This means if a subscriber both opened and unsubscribed in your email this table would only show the unsubscribe response.
You’ll also find the entire response history of every response recorded in system table T_EPROMOTION_RESPONSE_HISTORY. This table does not use priority and will show every single response a subscriber has had with your emails.
Clicks (response code 4) are given a number in the url_no column. This number is the ID in the TR_EPROMOTION_URL table. The TR_EPROMOTION_URL table is populated when a unique link in the email is clicked by the subscriber.
Lastly, you will see email responses on subscriber records in Tessitura.
On the constituent record, under the Connections tab > promotions radial you will see every single response from T_PROMOTION.
The LP_UPDATE_EMAIL_PROMOTION stored procedure controls where unsubscribes and hard bounces display on the patron record in Tessitura. This procedure will be customized based on your business rules.
All email responses collected for an email campaign sent in WordFly are sent back to Tessitura system tables. These responses include all opens, clicks, opt outs (unsubscribes and abuse complaints) as well as soft and hard bounces. With click responses the actual link that was clicked is sent back as well.
By default, Forward to a Friend events are not sent back to Tessitura as any response code. If your team is interested in creating a unique response to track Forward in the database you can do so following these basic steps. WordFly does send back clicks from the received Forward to a Friend. All the clicks will be tied back to the original subscriber, except for the unsubscribe link which does not alter the original subscriber.
A history of all email responses are saved in T_EPROMOTION_RESPONSE_HISTORY and the highest level responses are saved in T_PROMOTION. Clicked link responses are saved in TR_EPROMOTION_URL.
The numerical value assigned to the “response” column in these system tables relates to the values set up in TR_RESPONSE and TR_EMAIL_RESPONSE_CODE.
Here are the default values for email responses in TR_RESPONSE which show up in the 'response' column of T_PROMOTION:
9 – Open
11 – Hard bounce
12 – Soft bounce
10 – Click
13 – Unsubscribe / Abuse complaint
And the default values for email responses in TR_EMAIL_RESPONSE_CODE which show up in the 'response' column of T_EPROMOTION_RESPONSE_HISTORY:
1 – Open
2 – Hard bounce
3 – Soft bounce
4 – Click
5 – Unsubscribe / Abuse complaint
The responses are defined in two places and two different sets of values are used. The responses that are written by WordFly are defined in TR_EMAIL_RESPONSE_CODE, which defines a subset of values in TR_RESPONSE. The values from TR_EMAIL_RESPONSE_CODE.promo_response is written to T_PROMOTION.response. TR_EMAIL_RESPONSE_CODE.email_response is written to T_EPROMOTION_RESPONSE_HISTORY.response. T_PROMOTION can include any response from TR_RESPONSE, while T_EPROMOTION_RESPONSE_HISTORY can only contain email response from TR_EMAIL_RESPONSE_CODE.
WordFly is typically processing your event responses on a continuous basis without much delay. Generally speaking you should see responses show up in Tessitura within 1-2 hours. Server traffic could cause delays, for example if you are running an on-sale this would limit the resources available to process the responses we send back.
Here are some more details about event processing that are helpful to know:
- WordFly response processing is controlled by a software robot that processes responses until all event processing is finished and then takes a short break (90 minutes) before running again. Once responses are available and the robot is processing events it will continue to process the responses until those are done.
- The robot may never stop processing events because events for high-volume sending organizations deploying on a daily/consistent basis because events are continuously coming in.
- Small test campaigns may in theory take longer process if the robot is "on a break" and not running at the time when the response was received, generating a delay of up to 90 minutes.
- The responses are prioritized with unsubscribes and bounces first followed up with clicks and opens last.
Once you have email responses recording in Tessitura you can use these in future email Campaigns.
Data available to drive custom processing
|Response Code||Passed to local procedure|
|Customer Number||Passed to local procedure|
|Source Number||Passed to local procedure|
|Email Address||from T_PROMOTION by source_no & customer_no|
|Media Type||from T_PROMOTION by source_no & customer_no|
|Control Group||from T_APPEAL, TX_APPEAL_MEDIA_TYPE by source_no|
|Source Group||from TR_SOURCE_GROUP. TX_APPEAL_MEDIA_TYPE by source_no|
Examples of using email response data in Tessitura:
- Use unsubscribes and hard bounce events as suppressions on new List Manager list and Extraction list criteria
- Run queries in your database to gather all the click data for a URL (for example, gathering RSVP information)
- Use open and click data from previous promotions to re-target engaged subscribers
- Use non-opens and no click data to target subscribers you want to reengage
If you are encountering a situation where email responses are not showing up in Tessitura, follow these steps to troubleshoot.
1. Check your integration settings under Account > Tessitura.
Click the Test URL button. Do all of the service endpoints have a positive result after you test them?
After selecting Test URL you want to see this result...
The integration needs to be working in order for email responses to be sent back to Tessitura. If you’re seeing a different result then it might be that your integration settings have changed. We can help get this back on track, just email us to get started.
2. Check T_PROMOTION in Tessitura
Are you seeing promotions in Tessitura under T_PROMOTION?
If you're not seeing anything here then your list may not have been promoted. This step is required for tracking responses back to WordFly.
Promoting your list will add download number and source number as data fields. These need to be mapped to the correct WordFly data fields during your list import. If this data wasn't on your list then the stored procedure won't be able to send back email responses to the customer record.
Promoting your list happens slightly differently based on the type of list being used:
For Extractions, the promotion records are created when the Extract Constituents step is run in Tessitura. When you complete the Extract Constituents step you must make sure to check "generate source codes" and "create promotions". Your Extraction details will need to have the appeal, media and source group selected for the promotion to be generated.
For List Manager lists, the WordFly user must select an Appeal during step 3 of the List Manager list import process in WordFly. WordFly will tell Tessitura to create promotion records tied to the selected Appeal.
There’s one more thing! Tessitura also requires customer number on your list in order to send data back to the database. This data is always included with the output set so you shouldn't have to check for it when you import your Tessitura lists into WordFly.
3. Check your LP_UPDATE_EMAIL_PROMOTION stored local procedure.
If you are seeing responses in T_PROMOTION but you aren’t seeing responses on the customer record (for example, they aren't being marked with an unsubscribe response), then the issue is problem with the LP_UPDATE_EMAIL_PROMOTION stored local procedure.
WordFly updates Promotions 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. This needs to be configured for use with your system.
- Take a look and make sure the procedure is handling the unsubscribe events the way you want it to respond.
- The default stored procedure will still have the "RETURN" statement on line 33, this causes the procedure to stop running at that point so the sample code needs this updated in order to start working.