WordFly Triggered Emails

Hi

I implemented both performance remidner and thank you emails as well as Welcome emails on WordFly gfor the Royal Opera House.

You can use outputsets but you will need help from your DBA.

The way I did it was to use local table functions, essentially this is a function that returns a table but you can program the SQL such that it brings only one row back per customer and alows you to fully customise what it returns.

I ended up using more than one table function. As well as my main function that brought back the first performance attended by the customer that week (we wanted to ensure customer only got reminders once a week) I setup a secondary function that would then show everything else they were attending that week. Also hap a second function tha brought back act and interval times and returned two fields one for HTML (which used br ) and one for text which seperated by /'s as couldn't get carriage returns to work at the time (not sure if this is still an issue).

Due to initial problems with WordFly loading lists that took a while to generate, and also for the sake of efficiency and a s a backup,  I set up the main function to check a local table for data. If data in local table was less than 2 hours old then it would use that instead of re-calculating, this meant I could run a report to populate the local table with data pulled by the function an hour or so before WordFly would request it, which meant the list pull by WordFly dropped from 4-5 minutes to under 500ms.

Essentially it was the use of local table functions that made this possible.

Mark

Hi -

We've done similar to the National in creating some views that we then link to output sets. Ours are quite as sophisticated (still working on multiple shows per day) but they've been working so far. I've saved our view to my profile LVS_TKT_HIST_PERF - it includes a lot of formatted fields that we also use in WordFly such as date/performance day/etc.

Then in our TR_QUERY_ELEMENT table we use top to get the various things that we want.. We have two facilities and we send out a different email depending on what show they want - so at any time we have two sets of triggered emails - one for each theatre.

For next upcoming performance we use top(1) shortdate with a where clause of performance_dt BETWEEN <<p1>> and <<p2>> and facility_no in (X) with p1 & p2 being start and end date I'm looking for (we send out weekly pre-show, daily post show).

Once those query elements are set up, then we make an output set that includes the right dynamic date parameters (one week, one day, etc) and a list that also uses dynamic dates and set all that up as a triggered email. 

It's a little complex, but it seems to be working for us. We've been doing them since January and have only run into a couple of times to tweak.

Hope that helps - feel free to contact me with further questions!

Heather
Seattle Rep 

Hey!  Well, this thread prompted me to finally dig in to the topic, and with some good success.  Here is what i did;

1:  i created a View
2:  Made some custom TR_QUERY_ELEMENTs
3:  Set up an Output Set with those elements - say using "Report Run Date +1 day"
4:  Made a List of people where Tickets - Performance Date is between Report Run Date +1 day and Report Run Date +1 day
5:  run the list on the extraction or extraction on the list, however you think it...

I'm curious to know if there is anything wrong with this method, or if it is just as well...The only problem i really anticipate, which is rare for us, is if 1 person had tickets to 2 events in 1 day.

I started with figuring out what data i needed in my view, and modified the  LVS_TKT_HIST just as heather did, and made LVS_TKT_HIST_PERF to include the data i needed.  You can see, i LEFT OUTER JOIN additional content types for event data that wasn't available or as i wanted it;

USE [impresario]

GO

/****** Object:  View [dbo].[LVS_TKT_HIST_PERF]    Script Date: 04/12/2011 21:03:07 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE View [dbo].[LVS_TKT_HIST_PERF]

AS

/*************************************************************************************************

Modified CWR 2/16/2004 to get rid of outer joins

*************************************************************************************************/

SELECT 

               a.customer_no, a.perf_no, CONVERT(VARCHAR, a.perf_dt, 101) as perf_dt, a.perf_name, a.mos_category, a.order_dt, a.num_seats, a.price_type, a.location, right(T_PERF.doors_open, 7) as doors_open, 

               right(T_PERF.doors_close, 7) as doors_close, TR_THEATER.description, TR_THEATER.street, TR_THEATER.city, TR_THEATER.state, TR_THEATER.postal_code, 

               TR_THEATER.driving_dir, a.tck_amt, b.value as opener, c.value as opener_time, d.value as headline_time, e.value as headline_name

FROM  LT_TKT_HIST AS a INNER JOIN

               T_PERF ON a.perf_no = T_PERF.perf_no INNER JOIN

               T_FACILITY ON T_PERF.facility_no = T_FACILITY.facil_no INNER JOIN

               TR_THEATER ON T_FACILITY.th_no = TR_THEATER.id LEFT OUTER JOIN

               TX_INV_CONTENT AS b ON T_PERF.perf_no = b.inv_no AND b.content_type = '13' LEFT OUTER JOIN

TX_INV_CONTENT AS c ON T_PERF.perf_no = c.inv_no AND c.content_type = '14' LEFT OUTER JOIN

TX_INV_CONTENT AS d ON T_PERF.perf_no = d.inv_no AND d.content_type = '15' LEFT OUTER JOIN

TX_INV_CONTENT AS e ON T_PERF.perf_no = e.inv_no AND e.content_type = '16'

WHERE (a.season IN

                   (SELECT id

                    FROM   VRS_SEASON)) OR (b.content_type = '13') OR (c.content_type = '14') OR (d.content_type = '15') OR  (e.content_type = '16') OR

               (ISNULL(a.season, 0) = 0)

We are trying to implement this and have run into a problem where we have Parking set up as a Performance also. Is there a way to use a second performance name in the mail to indicate parking is purchased? Has anyone tried to send an email using two Performance Name fields?

Hi Jencey

When I implemented the performance reminders I used local table functions and that enabled me to include 2 fields (one for text and one html) that listed all the other performances the person was attending that week, and 2 fields (one for text and one html) to show the performance timings.

I will be presenting on this solution at the conference this year, which includes why I chose this solution.

Mark

Hi Jencey

When I implemented the performance reminders I used local table functions and that enabled me to include 2 fields (one for text and one html) that listed all the other performances the person was attending that week, and 2 fields (one for text and one html) to show the performance timings.

I will be presenting on this solution at the conference this year, which includes why I chose this solution.

Mark

I forgot to mention that we ended up using Content Types to add additional information, such as Opener Name and Opener Start Time