performance reminder email success stories

Beau,

Did you have any luck with this?  We are also a Mail2 client and have been struggling.

Any advice?

Are you struggling with set up or with content?

In the way of set up, I've usually opted for the custom view approach for M2 scheduled reminders.

In my former job for a music presenter, one single tour was held in up to 9 different venues so I stored values like venue address, short Google Map, restaurant and parking URLS in TR_THEATRE which has a lot of unused fields in the client.  I could then join the relevant row to Facility via Theatre_No in the custom view.

In my current job, a custom view is a better choice because:

- Patrons can potentially see 2 (sometimes 3) productions in one single day so we opt to send one email with all the day's performances at once. 

- We have a case where multiple records have the same email address but we personalise the emails with different names eg emails to a casting agency, executive assistant etc.

- We have a strict style guideline for the way dates and times are presented so a custom view allows for compliant formatting.

- You can insert additional text which depends on things like the number of tickets, the venue etc.

We're still making other improvements and learning along the way!

Oh ho.  One of the things I'm having difficulty with is creating a custom view that will work with Mail2.

Would you mind sharing your code so I can take a look to see where I have gone wrong?

Dot,

There is documentation on the mail2 support site on how to create custom views to work with mail2 that might help.  Here's the link:

http://mail2support.lynch2.com/customer/en/portal/articles/613890-custom-scheduled-campaign-setup

You can have whatever you want in your view. However, you must a column named " custom_date" and do not forget to grant "select" on your view. 

Travis

This is what I have built out for the post-show Thank you for Attending campaign using the Mail2 instructions.  The view is created with only the field name address and no data, so I have done something wrong somewhere.  What do you make of it?

USE impresario

GO

/** OBJECT: VIEW [dbo].[LV_M2_ATTENDED_PERF]    Script date: 10/21/2015 10:51 **/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[LV_M2_ATTENDED_PERF]

AS

WITH attendedCTE (customer_no,expr_dt, perf_dt, prod_season_desc)

AS

(

SELECT c.season, c.perf_dt, b.prod_season_desc, a.customer_no

FROM dbo.VS_PERF AS c WITH (NOLOCK) INNER JOIN dbo.LVS_PROD_SEASON_BULK AS b WITH (NOLOCK) ON c.prod_season_no = b.prod_season_no

INNER JOIN dbo.T_ORDER_SEAT_HIST AS a ON c.perf_no = a.perf_no

WHERE (a.user_location = 'NSCAN')AND (a.customer_no > 0) AND (a.id IN (SELECT MAX(ID) AS maxID FROM dbo.T_ORDER_SEAT_HIST GROUP BY order_no, perf_no))

GROUP BY c.season, b.prod_season_desc, c.perf_dt, a.customer_no

)

SELECT

distinct ee.address

From dbo.LTX_M2_ELISTS_EADDRESS AS ee WITH (NOLOCK) INNER JOIN

dbo.T_EADDRESS AS te on ee.address = te.address INNER JOIN

dbo.VRS_EADDRESS_TYPE AS et ON te.eaddress_type = et.id INNER JOIN

attendedCTE cte ON te.customer_no = cte.customer_no

GO

GRANT SELECT ON [dbo].[LV_M2_ATTENDED_PERF] TO [ImpUsers] AS [dbo]

GO

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 date 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 data format should be YYYY-MM-DD.

Ok.... That's part of where I'm tripping over myself.

Based on what you see, should I use the perf_dt for the custom_date or should custom_date be blank?

Another thing, I noticed you have the customer_no and season specified in a different order to that of the select statement in your CTE, i.e. The first CTE column is customer_no but the season is the first in the select statement.

I'll email you one an example so you can tinker with it.

I use the perf_dt as custom_date, but you could leave it blank.

Travis