Integrating mail2 with Tessitura v12 Contact Point Purposes
|Time: 20 minutes|
|Suggested Skills: Tessitura, SQL|
| Objective: In addition to being compatible with Tessitura v12, mail2 is also capable of taking advantage of the new to v12 Contact Point Purposes which would allow you to pull a Tessitura List Manager list based on mail2 list criteria. By creating a contact point purpose for each of your mail2 lists and assigning a CPP for each in the LTR_M2_ELISTS table (under system tables), the corresponding contact point purposes can be assigned using an included SQL script. Before proceeding with the CPP integration, we recommend updating to the latest version of the mail2 procedures by running the script attached to the article here. To update a mail2 integration to work with contact point purposes, follow the steps outlined below.
Download the update package attached to the bottom of this article.
Execute script 1-Alter_LTR_M2_ELISTS to add the Contact Point Purpose to the system table.
Execute script 4-Set Defaults and Reference Tables to create the necessary mail2 defaults and associations.
If you're configuring CPPs and you already have existing mail2 lists with opt-ins, you can choose to uncomment the code at the bottom of this script to automatically create the CPPs in TR_CONTACT_POINT_PURPOSES and the associations in TX_CONTACT_POINT_CATEGORY_PURPOSE, or you can do it manually as outlined in the following steps.
In Tessitura System Tables, open LTR_M2_ELISTS and assign a contact point purpose to each mail2 list that should be assigned a CPP.
Note: there can only be one mail2 list per contact point purpose. If the LTR_M2_ELISTS table does not appear in your System Tables listing, you can add it easily by adding a row in TR_REFERENCE_TABLE with a Table Name and Description of 'LTR_M2_ELISTS'. New CPPs are created in the TR_CONTACT_POINT_PURPOSE system table in Tessitura.
Before you run script 5, you'll need to create a row in TX_CONTACT_POINT_CATEGORY_PURPOSE for each new CPP you just created for step 4.
Execute script 5-Insert Contact Purpose per each entry in LTX_M2_Elists_Eaddress to backfill CPPs to each entry in LTX_M2_ELIST_EADDRESS using the mapping configured in step 4.
Execute script 6-LRP_M2_Update_Purposes
This will create a procedure that can be set up as a SQL scheduled job to periodically scoop up newly added email addresses and assign appropriate CPP for mail2 list subscription. The @range parameter of this proc is a time in minutes and the value should be equal to the number of minutes between your scheduled interval (eg. if schedule is every two hours, the @range value should be '120'). This procedure relies on the T_DEFAULT setting 'M2UpdateContactPurpose'--Make sure to create that entry in T_DEFAULTS with a value of 'YES' to allow this procedure to complete successfully.