When the WordFly-Tessitura integration is set up, WordFly will continually send back reporting data to your database. Email campaign data includes opens, clicks, and unsubscribes (opt-outs, hard bounces and abuse complaints). If you’re not seeing these responses, your integration or data may need to be adjusted.
Requirements for sending data back to Tessitura
List data requirements
|REQUIRED DATA||ADDED TO YOUR LIST...|
|Email address||Via your output set|
|Customer number||Via your output set|
|Download number||When you promote your list in Tessitura|
|Source number||When you promote your list in Tessitura|
Lists are promoted with an appeal, media, and source group. 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.
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.
- List Manager lists: Creating | Importing
- Extractions: Creating | Importing
- Set up LP_UPDATE_EMAIL_PROMOTION stored procedure
Where responses show up in Tessitura
Next, let’s look at where email responses show up in Tessitura.
- The highest level responses are saved in T_PROMOTION
- A history of all email responses is saved in T_EPROMOTION_RESPONSE_HISTORY
- 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
The highest priority response for every subscriber emailed is 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 opened an email then unsubscribed from it, this table would only show the unsubscribe response.
The entire response history of every response is 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.
On the constituent record, under the Connections tab > Promotions 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 constituent record in Tessitura. This procedure will be customized based on your business rules.
Response types that show up in Tessitura
All email responses collected from a WordFly campaign 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 link URL that was clicked is sent back as well.
By default, Forward to a Friend events do not have a response code and are not sent back to Tessitura. To track Forwards in your database, you will need to create a custom email response code. However, when a forwarded email is clicked, WordFly sends back the clicks from that email. All clicks are tied back to the original subscriber, except for the unsubscribe link which does not alter the original subscriber.
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 value 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
Default values for email responses
|Shown in the 'response' column of T_EPROMOTION_ RESPONSE_HISTORY||Shown in the 'response' column of T_PROMOTION|
How responses are processed
WordFly typically processes 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.
- WordFly response processing is controlled by a software robot that processes responses until all event processing is finished. It 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 they are done.
- Responses are prioritized. Unsubscribes and bounces are processed first; clicks and opens last.
- For high-volume organizations who send on a daily/consistent basis, the robot may never stop processing because events are continuously coming in.
- Small test campaigns may in theory take longer to 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.
Use response data sent back to Tessitura
Responses recorded in Tessitura can be used 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
Testing + Troubleshooting
Use this checklist to verify that email responses will show up as expected in Tessitura.
Is your integration working?
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, it’s possible that your integration settings have changed. We can help get this back on track. Email us to get started.
Did you promote your list from Tessitura and map the fields?
Check T_PROMOTION in Tessitura
Are you seeing promotions in Tessitura under T_PROMOTION?
If you’re not seeing anything here, 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, the stored procedure won’t be able to send back email responses to the constituent 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.
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.
Is your stored procedure set up correctly?
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 constituent record (for example, they aren’t being marked with an unsubscribe response), there is a 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.
- 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. The sample code needs this updated in order to start working.