Setting up a Custom View for mail2
|Time: 20+ minutes|
|Suggested Skills: Tessitura, SQL|
|Objective: This article is meant to provide information on how to set up a scheduled campaign of "Custom" type. Examples of custom-type campaigns include but are not limited to Membership reminder emails, abandon cart emails, ticket discount alerts, anything else you can make up!|
Create the database view that will drive the data. Required criteria:
- The view must be named beginning with: lv_m2
This is because we retrieve a list of all views from the database that begin with that string for selection via the website. This should conform with typical Tessitura view naming conventions.
- The view is required to contain a column with the name: address
This represents the email address for the patron to which the custom reminder will be deployed. The field name must be lower case.
- The view is also required to contain a column named: custom_date
This date is the date that is used by the Tessitura Date Section window in the scheduled job. For example, if you wanted to create a Membership reminder, the custom_date column would contain the DATETIME value on which the membership expires. For an abandon cart reminder, the column would contain the date on which the cart was abandon. The field name must be lower case and DATETIME format should be YYYY-MM-DD. When you send tests using the scheduled campaign testing tool, or use the "Send Now" feature with mail2 custom views, the start and end date you enter should correlate to this field in the view. Rows with a custom_date greater than or equal to the start date and less than the end date assigned in the test or send now process will be selected.
- Always make sure that the Tessitura customer_no 0 and the T_eaddress record with the eaddress_no 0 are suppressed from your view.
- If a record in the view is returned with any null values for optional fields, it can interfere with the accuracy of Data Review. To account for this, be sure to insert an empty string for any optional fields that may return a null value. In your SELECT statement, it would look like:
ISNULL(field_name,'') as field_name
The view may contain any number of other columns, and all of these columns may be inserted in to the custom campaign at send time using tokens. For example, if you create a view named: LV_M2_Membership_Reminder, it would have the following required columns:
And may contain additional columns such as:
Any of these columns can be merged in to your campaign dynamically at send time by using the token representation of these columns:
As you can see, we simply pre-pend the column name with "order__" (that's a double-underscore).This makes all of the columns you script in to your view accessible for custom merging in to the campaign that is deployed at send time.The rest of the setup for custom scheduled campaigns is the same as typical performance-based scheduled campaigns.
CUSTOM VIEW EXAMPLE:
The following is code for a custom view that has the required fields, and also show an example of using a Common Table Expression (CTE) in defining the view:
USE [dbname] GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE VIEW [dbo].[LV_M2_MEMBER_SUBSCRIBERS] AS
WITH memberCTE (customer_no,expr_dt)
Define the CTE query.
( SELECT customer_no,expr_dt FROM TX_CUST_MEMBERSHIP WHERE memb_level = 5 and expr_dt > GETDATE() )
SELECT distinct ee.address, cte.expr_dt AS custom_date, ee.status, te.customer_no, te.primary_ind, et.description AS eaddress_type, ee.last_update_dt FROM dbo.LTX_M2_ELISTS_EADDRESS ee WITH (NOLOCK) JOIN dbo.T_EADDRESS te ON ee.address = te.address JOIN dbo.VRS_EADDRESS_TYPE et ON te.eaddress_type = et.id JOIN memberCTE cte on te.customer_no = cte.customer_no GO GRANT SELECT ON [dbo].[LV_M2_MEMBER_SUBSCRIBERS] TO [ImpUsers] AS [dbo] GO