WordFly perf reminder recipe?

emailed you our set up

Heath, mind if I get a copy as well?  We're just getting started with Wordfly.  pfitz@cnu.edu

Heath,
Could I get this as well? mfitzgerald@marbleskidsmuseum.org

We’re still trying to set ours up too. Would you mind sharing with me too? RGreenwald@UCDavis.edu 



Update from Tessitura Network Reuben Greenwald We’re still trying to set ours up too. Would you mind sharing with me too? RGreenwald@UCDavis.edu  View online   You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here. Flag this post as spam/abuse.

I'm putting Heath's emails below for everyone:

Hey Margaret,

So this is from 2019 but we use the same procedure

• List Suppression: is because Philanthropy want to control some people
• Constituent Type is because of schools and corporations that it gets lost at and we’ll likewise cater for them specifically
• Tkt Hist Prod Season: is obvious
• Tkt Hist Role: in our case we want it to go to the order owners only but if you are ok with Initiator Recipients (and it’s a good idea if you do that) then select as needed
• Tkt Hist Run Date:  OK this is interesting – it’s fine to do >= +3 and then ,<= + 3 for your needs
  • BUT because we have check the scheduled email Send to a subscriber ONCE A WEEK in Wordfly I left this as the below.
• Tkt Hist Performance: We wanted to leave some out (like schools and the dress rehearsal) that appear in the Prod Season
• Eaddress: like %@% because we have some website addresses to bad entries occasionally crop up

And then regarding the Output set:

Ahh … I did that myself.  I set up a local view for the Next Performance (attached).

Output is Customer_no, Perf_name and perf_dt

Otherwise you might be able to set up a TR_QUERY_ELEMENT_GROUP from https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_ticketing-0/10587/output-set---next-performance

H

USE [impresario]
GO

/****** Object: View [dbo].[LV_NEXT_PERF] Script Date: 11/10/2021 11:57:25 AM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE View [dbo].[LV_NEXT_PERF]
AS
Select distinct
pn.customer_no,
pn.perf_name,
pn.perf_dt
from VS_TICKET_HISTORY pn
JOIN (select th.customer_no,
perf_dt = min(th2.perf_dt)
from VS_TICKET_HISTORY th
Join (select customer_no, perf_dt
from VS_TICKET_HISTORY
where
perf_dt >= GETDATE() ) th2 on th.customer_no = th2.customer_no
group by th.customer_no ) thd ON
Cast(thd.customer_no as varchar(20))+Cast(thd.perf_dt as varchar(20))
= Cast(pn.customer_no as varchar(20))+ Cast(pn.perf_dt as varchar(20))

GO

Heath, Thank you so much for this!  Clearly is was really needed by this community and you are an angel!

Heath Wilder    Thank you SOO much for this! Like everyone else in this thread, I have been struggling this and really appreciate having this. I have two questions for the group:

1. We would love to include the delivery method of the ticket order in the show reminder, I know I can get the Order number in the ticket history view, but I'm not sql smart enough (yet) to join on that to get the delivery method as output, so if anyone has done that, I'd love to steal your work and give you my undying gratitude. 

2. Looking at the Wordfly documentation, they say to use a suppression list based on the promotion appeal. We run in rep for part of our season, so it is very possible that a single patron could be coming to different shows on consecutive days. I think if I create a reminder list and an appeal for each prod season, they would still get picked up and sent the reminder for each prod season, but I just wanted to run this past the hive mind and see if anyone else had thoughts. 

Thank you all in advance!

1. I can adjust the above to add in delivery method if you like

2. You are right.  Separate scheduled campaign for each prod season will only suppress that prod season.  But then again if some one has booked twice (by accident perhaps) then in my experience getting an email for each booking can pre-empt them missing sorting that issue out ahead of time.

If someone is in a scenario where they need near real time or plans on using something like this to send post visit e-mails and is in a situation where tickets can be upgraded to a membership I would highly advise against using any of the ticket history tables for this kind of thing as they will miss customers.

In a related scenario regarding building the criteria we had a situation where WF was crashing after about 8 months of operation due to the volume of duplicate customers being sent up each day based on the Criteria logic. While this is interest and not tickets the same logic could easily be applied to tickets as well.

IF OBJECT_ID (N'dbo.LFT_MBA_NewInterestsByLists', N'IF') IS NOT NULL  
    DROP FUNCTION dbo.LFT_MBA_NewInterestsByLists;  
GO  
CREATE FUNCTION dbo.LFT_MBA_NewInterestsByLists (
    @KeywordNo        INT
    ,@ListNo        INT
)
    /*Docu
        Date Created: - 06/03/2021
        Created by:  Ronald Radford/Jon Shaffer
        Purpose: Pull list of customers based on interest who have not previously received notification
        
        @KeywordNo        INT -interest to check for
        ,@ListNo        INT - List number bing used in

        Object Notes:
        This is a stop gap solution, at some point when the other fires are taken care of we will need
        to circle back and build a a logging table for tracking the customers as there is the potential for
        some to be missed if WF experience an error on last run.


        */
RETURNS TABLE  
AS  
RETURN   
(  
SELECT * FROM
(
SELECT customer_no , MIN(create_dt)as CreateDate, count(*) as Hits FROM TX_CUST_TKW
where tkw = @KeywordNo
AND selected = 'Y'
GROUP BY customer_no
) as K
WHERE CreateDate >= ISNULL((
SELECT MAX(DateLastUpdated) FROM LT_WF_EXPORT
WHERE ListNumber = @ListNo), DATEADD(day,-1, GETDATE()))
)

To get the list no for a new list you have to save the list first and then update the function with the list number. Here is how it is used in the list: