Set up Tessitura preferences

Article author
Miguel Panayotty
  • Updated

Allow your subscribers to choose which communication preferences suit them best. Create your communication preferences in Tessitura and set up the preferences integration to send updates back to Tessitura from your WordFly subscribe and preferences pages. 



How the integration works

Preferences are collected in WordFly from the subscribe page and preferences page. Any subscribers with a Tessitura customer number on record can have preference updates go back to Tessitura.

Preference updates are collected in WordFly and sent back to Tessitura to update new local tables in the impresario database. The preference type and opt-in value are sent back to Tessitura when preferences are updated: 1 for opt-in, and 0 for opt-out. A new stored procedure can be configured to update preferences on the constituent record in any way your organization wants to have it reflected. Use preferences in your Tessitura list criteria to target the right subscribers for every email campaign.

Preferences can be interests, attributes, contact point purposes, or contact permissions. These values are created in Tessitura in system tables.



Set up as keywords



The Attribute should be set up as:

Data Type String
Edit Mask Alphanumeric
Category Constituent

Contact Point Purposes


Make sure your Contact Purpose category is set up appropriately in the TX_CONTACT_POINT_CATEGORY_PURPOSE table in order for your purpose to display as an email purpose.

Contact Permissions TR_CONTACT_PERM_TYP


Updates on the constituent record in Tessitura

Preferences will show up on the constituent record in Tessitura in the following places based on the preference type:


Interests will display under the Attributes tab > Interests


Attributes will display under the Attributes tab > Attributes


Contact Purposes will display under the Contact Details tab > Contact Point Purposes



Contact Permissions will display under the Contact Details tab > Contact Permissions



Installation steps


To start

Go to Account > Tessitura. In the Send preferences updates to Tessitura section, download the installation scripts. If you don’t have access to this section of WordFly, please email us.

Identify which Tessitura preference type you plan to use for preferences: Interests, attributes, contact point purposes, or contact permissions. Use one type or a combination of all those types.


Steps to complete

  1. Run the wf_preferences1_0_4.sql install script in the Tessitura database.*
    Instructions are included inline as comments in the scripts.
  2. Install the wp_preferences_test.sql script in the impresario database to assist with testing.
    This script can easily be configured with the desired customer and preference data and then executed to emulate the database calls made by WordFly during preference updates.
  3. Review, modify, and test the new LP_WF_PREFERENCE_UPDATE_CUSTOMER stored procedure.
    Ensure that it meets the needs of your organization and that preference updates are being processed correctly. It is disabled by default, but you can enable custom processing by removing the “RETURN;” statement from line 28.
  4. Create new or update existing output sets to include your preferences.
    The data will have to be in the format of 1 (opt in) and 0 (opt out). Ideally you should update all of your output sets to include preferences to ensure that any updates that have come in since your last email campaign will get updated and reflected in a new list import into WordFly.
  5. Enable the “Send preferences updates to Tessitura” option in WordFly


* The wf_preferences1_0_4.sql install script will create the following database objects in impresario:


LT_WF_PREFERENCE_HISTORY Table that stores an audit history of all Preference updates
LTR_WF_PREFERENCE_TYPE Table that stores the available reference preference_types.

Preference types and IDs:
Purposes (ID 1)
Interests (ID 2)
Attributes (ID 3)
Contact Permissions (ID 4)


LP_WF_PREFERENCE_UPDATE_HISTORY Stored procedure called by WordFly with each Preference update. This procedure handles standard processing of recording all preference updates in the LT_WF_PREFERENCE_HISTORY table and then calls the LP_WF_PREFERENCE_UPDATE_CUSTOMER procedure for custom processing.
LP_WF_PREFERENCE_UPDATE_CUSTOMER Disabled by default. The RETURN statement on line 28 must be removed for the procedure to run. The procedure has default sample code to update Purposes, Interests, Attributes, and Contact Permissions, and works for all organizations as is. The procedure can be customized for additional data processing.


Sample preferences data that would populate in the LT_WF_PREFERENCE_HISTORY in the impresario database. This data would then update on constituent records through the LP_WF_PREFERENCE_UPDATE_CUSTOMER stored procedure.





Update output set elements to include preferences

Preferences in WordFly are treated as data fields because the data is used for personalizing emails and segmenting campaigns, just like other data fields. Your output sets in Tessitura define personalization data for all subscribers and allow Tessitura list imports to be used to communicate any preference updates from Tessitura to WordFly.

WordFly will stay updated with preference updates on the Tessitura constituent record through data provided by your Tessitura output sets. Each time you import a List Manager list or Extraction, you will use an output set to provide data for your subscribers.


  • We recommend configuring all your output sets to include subscriber preference information for every preference you plan to use for WordFly emails.
  • Your output sets will need to include a data column for each preference you are using in Tessitura. A value of 1 (opted in) or a value of 0 (opted out) should be returned for each subscriber.
  • Verify any updates made to your output sets with internal test accounts before using with all your subscribers.



Below we provide guidance for adding each type of preference to your output sets. Please keep in mind that our examples may not work for every organization. We encourage you to talk to the experts at Tessitura if you have questions.

Adding Interests

For the specified interest, this output element returns 1 if the Selected value on the interest row is Y. It returns 0 if the Selected value is N or if the interest does not exist for a constituent.

An output element group and output element pair should be added for each WordFly preference interest set up in your system, updating the Data_Where value in the example (tkw=) with the ID number of the interests from your database (found in TR_TKW).




Adding Attributes

For the specified attribute, this output element returns 1 if the attribute value is Yes. It returns 0 if the attribute value is No or if the attribute does not exist for a constituent. To use attributes with values other than Yes and No, the case statement in the Data_From column would need to be changed to translate those values to either 1 or 0.

An output element group and output element pair should be added for each WordFly preference attribute set up in your system, updating the Data_Where value in the example (keyword_no=) with the ID number of the attributes from your database (found in T_KEYWORD).




Adding Contact Point Purposes

For the specified contact point purpose, this output element uses a custom view to return 1 if the purpose exists on the primary email address for a constituent. It returns 0 if the purpose does not exist on the primary email address.

Run the CREATE_LV_WF_CONTACT_PURPOSE.sql install script in the impresario database before making the contact purpose output set elements.

An output element group and output element pair should be added for each WordFly preference contact point purpose set up in your system, updating the Data_Where value in the example (purpose_id=) with the ID number of the contact point purposes from your database (found in TR_CONTACT_POINT_PURPOSE).




Adding Contact Permissions

For the specified contact permission type, this output element returns 1 if the answer value on the permission row is Y. It returns 0 if the permission value is N or if an answer does not exist for a constituent.

An output element group and output element pair should be added for each WordFly contact permission type set up in your system, updating the Data_Where value in the example (type_id=) with the ID number of the permission types from your database (found in TR_CONTACT_PERM_TYPE).





Set up preferences in WordFly

There are a couple account setup steps in WordFly to complete before using the preferences integration.


Step 1: Set up your Tessitura preferences IDs in WordFly

Go to Subscribers > Preferences. Click Create a Preference to start adding your preferences. If the button is greyed out, make sure you have the Manage Preferences user permission enabled. You can add as many preferences as you need for your organization. When added, preferences in WordFly are stored as subscriber data fields.

  • Type in the name of your preference. Do not use special characters.
  • Select the Tessitura preference type
  • Enter the preference ID from Tessitura system tables
  • Click Save
  • To remove a preference, click Delete. However, be aware that deleting a preference will delete any stored subscriber data for that preference.
  • Subscribers going to the preferences page will see boxes checked for any preferences they are signed up to receive in the past.



Step 2: Create and publish your preferences page in WordFly

Go to Pages > Preferences 

  • The preferences you set up in step one can be added to your preferences block.
  • You can modify the text, upload a header image, and make updates to match your branding.
  • You must select Publish for the page to be live.


Test preference updates in Tessitura

WordFly provides a testing script (wp_preferences_test.sql) to test your preference updates in Tessitura directly. The script mimics the data WordFly will send back to your database when preference information is sent back to Tessitura. This testing step can be completed after running our install script and configuring your LP_WF_PREFERENCE_UPDATE_CUSTOMER stored procedure.


To use the test script

1. Open Microsoft SQL Server Management Studio

2. Select the ‘impresario’ database

3. Open the wp_preferences_test.sql script in the application

4. Configure Step 1 values

This step covers all the data WordFly will be sending back to your Tessitura database when a preference is updated. There are three parts to configure:


    This is where you will use a valid customer for your test. You could use your own personal Tessitura record or another test customer. Update the customer number and email address to reflect this subscriber. Also be sure to update the TR_Org ID to your organization’s unique Tessitura Organization ID (feel free to ask Tessitura or WordFly what your ID is).

    Tip: Use ‘Valid Values’ to find correct/available values in your database for each.
    Highlight the value and click ‘Execute!’ to query available data in your database.

    This section specifies the opt in or opt out response WordFly will send back for the preference. For the ‘preference_ind’ use a value of 1 to test opt-in updates for a preference and a value of 0 to test opt-out updates. You can leave ‘preference_dt’ as is unless you want to test sending back a date for the preference update.

    This section allows you to test each type of preference you will be using. Only uncomment one preference at a time for testing. Update the preference_id value to a valid interest value from your database.

    Example of an Interest test
    Uncomment the Interest test, then update the preference_id with the appropriate interest id you would like to test.

5. Click Execute! to run the script and verify the results.
The first set of results will show you what WordFly is sending to your database. The second set of results will show you the update in your database. You can even have your Tessitura client open to the specific constituent record you are testing where you can verify there as well that the preference updated successfully.


6. Test all your preferences for both opt-in (@preference_ind = 1) and opt-out (@preference_ind = 0) to ensure everything is updating as you expect it to.



Test preference updates from WordFly

After finishing all your preference setup tasks, it’s time to test preference updates from WordFly to Tessitura. Create a campaign in WordFly. In your email footer, add a link to your Preferences page. Send the test campaign to some internal testers and ask them to click the preferences link to update preferences. Verify preference updates in Tessitura within 1-2 hours.


Verify your preferences updates in Tessitura

After your subscribers click Update on the preferences page, WordFly sends all the information back to Tessitura and updates the constituent record. The subscriber’s customer number must be logged in WordFly to send preference updates back to Tessitura.





How long does it take for updates to appear in Tessitura?

There is a new stored procedure LP_WF_PREFERENCE_UPDATE_CUSTOMER handling the preference updates and it will be running all the time. Expect to see preference updates on constituent records in 1-2 hours from the time the preference was updated in WordFly.

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


What data is required in order for preferences to update in Tessitura?

The new stored procedure LP_WF_PREFERENCE_UPDATE_CUSTOMER requires email address and customer number for sending preference data back to Tessitura successfully.


What if a customer resubscribes to our mailing list?

If a customer wants to start receiving emails again, they would simply modify their preferences again on your website (in a My Account section). Once the user resubscribes (or updates their mailing preferences) to your email program, the next time you import your list into WordFly, the preferences will be updated for that email address based on the data provided by your output sets. If there is an unsubscribe in WordFly for the subscriber, that unsubscribe will need to be overridden on the List Summary page after the list with their updated preferences has been imported.


Are these preferences linked to a specific email address? What if we have several different emails for 1 customer or under a household?

The preferences are linked to an email address + customer number. Tessitura allows preferences for multiple addresses on a record. The user would be modifying preferences for the email address and customer number the email was sent to.


Can I use @preferenceHistoryId in the preferences stored procedure?

The @preferenceHistoryId parameter is not used by the default implementation of LP_WF_PREFERENCE_UPDATE_CUSTOMER. It is passed to the stored procedure to provide a link back to the LT_WF_PREFERENCE_HISTORY for cases where additional custom processing is added to LP_WF_PREFERENCE_UPDATE_CUSTOMER. We attempted to implement LP_WF_PREFERENCE_UPDATE_CUSTOMER so that it works as is for everyone, but it is designed to allow an entry point for customization.