Database Object Overview

Knowledge Level: 
Time: NA
Suggested Skills: Tessitura, SQL
Objective: All of the database objects that we include follow typical Tessitura conventions and include "M2" in the title to designate them as mail2 objects. The integration includes the following in the Tessitura database.

TABLES

LTR_M2_ELISTS

A table that contains the public and private lists used in Mail2. Internal and Test lists are not tracked in Tessitura, since subscription/opt-out information is largely irrelevant with these lists types.

LTX_M2_ELISTS_EADDRESS

A table that tracks the connection between email addresses, list subscriptions, opt-outs, and organization names.

LT_M2_GLOBAL_UNSUBSCRIBE

A table that tracks global opt-outs per organization. In consortium environments, global opt-outs are tracked on an individual organization basis and uses the Parent Table names from Tessitura to identify each organization. When a patron is globally unsubscribed, their subscription preferences are preserved in the LTX_M2_ELISTS_EADDRESS table so that if and when they reactivate, their settings are remembered.

LT_M2_PROMOTION_UPDATE

When Asynchronous Promotion Processing is enabled, actions such as clicks/opens/unsubscribes are stored in this table for processing against the actual promotion records on constituent records in Tessitura at a later time. After action rows in this table are processed they are moved to LT_M2_PROMOTION_LOG for archiving.

LT_M2_PROMOTION_LOG

This table is a mirror of LT_M2_Promotion_UPDATE, and is used as part of the Asynchronous Promotion Processing workflow. After promotions from LT_M2_Promotion_UPDATE are processed they are moved to this table for archiving for up to 3 months. Promotion rows older than 3 months are automatically deleted by LP_M2_UPDATE_PROMOTIONS_IN_BULK each time that it is run.

VIEWS

LVS_M2_SUBSCRIBERS

This view lists email addresses and their current, opt-in list preferences to currently active lists. It does not show any globally unsubscribed email addresses, or any data for lists that are inactive. It will show one row for each address/list subscription pair per customer number. As a result, there may be duplicate email addresses shown in this view since an e-address can show up on multiple constituent records. This view is sensitive to control grouping and organization.

PROCEDURES

LWP_M2_GET_ELISTS

This procedure retrieves all of the lists in the LT_M2_ELISTS table for a particular organization and all of the List Manager lists that have the mail2 box checked in two datasets. The count of addresses or constituents on the lists is returned as with the data set.

LWP_HANDLE_LISTS

This procedure handles List Create and List Delete webhooks. Whenever a new list is created or an old list is deleted, this procedure handles the information. Deleted lists are just marked as inactive, so that list subscription information is preserved for future retrieval if necessary.

LWP_M2_GET_ELIST_DATA

This procedure returns the actual email addresses that are included on a specified mail2 list or List Manager list. Globally unsubscribed addresses are never returned. If both a List Manager list and a Mail2 list are specified, only addresses that are a part of both lists will be returned (the intersection of the two lists). This ensures that anyone you identify through list manager also has a current active subscription for the list to which you are trying to apply them and ensures spam compliance.

LWP_M2_GET_ESUBSCRIPTIONS

This procedure returns data for the Tessitura custom screen. It retrieves all of the email addresses for a particular patron and their subscription preferences for those addresses. Only addresses that are accessible to the currently logged in Tessitura user will be displayed.

LWP_M2_HANDLE_WEBHOOKS

This procedure receives all of the information from webhooks that are sent from the mail2 system. Webhooks post information to this procedure which then routes the information appropriately depending on the type of webhook received. This procedure also handles checking for existing e-addresses prior to passing info along and creates new addresses if no address exists. If an address has both a record in the anonymous mail2 user account and a real constituent record, this procedure inactivates the anonymous eaddress_no.

LWP_M2_HANDLE_SUBSCRIPTION

This procedures handles all webhooks that carry subscription, opt-out, and global opt-out information. Based on the webhook, the procedure handles the data appropriately and updates the LTX_M2_ELISTS_EADDRESS table appropriately. It also updates the LT_M2_GLOBAL_UNSUBSCRIBE table if necessary.

LWP_M2_HANDLE_CONTACT

This procedure fires when a source number is passed back to Tessitura with a webhook and executes a version of LP_UPDATE_EPROMOTION. It translates the webhook code into a response code and deposits a row on the promotions tab of the customer record.

LWP_M2_LOCAL_HANDLE_CONTACT

This procedure is a local version of the LWP_M2_HANDLE_CONTACT procedure. It is called from LWP_M2_HANDLE_CONTACT and is meant to be customized to carry out any additional actions that you would like to take when a user opens, clicks, subscribes or unsubscribes from one a campaign or list.

LWP_M2_UPDATE_EADDRESS

This procedure is a wrapper procedure for WP_MAINTAIN_EADDRESS. It executes WP_MAINTAIN_EADDRESS in 'ad hoc' mode. This procedure is responsible for creating e-addresses when an e-address is being subscribed but doesn't yet exist in Tessitura.

LWP_M2_INSERT_SUBSCRIPTION

This procedure can be set as a trigger in LP_UPDATE_CUST_RANK or run as a nightly procedure. The purpose of this procedure is to add customer email addresses automatically when a particular locally defined condition exists. This condition is defined locally by your organization and must be set prior to implementing this procedure.

Still need help? Contact Us Contact Us