Performance Reminders - Wordfly

I wonder if your best solution here is to have a separate Wordfly template for each performance. The benefit there is that you can use Wordfly's editor to better format your performance synopsis, although then you'd need either a separate triggered campaign for each performance or some serious dynamic text logic in your template. Guess it depends on how many shows you're running at once.

I wonder if your best solution here is to have a separate Wordfly template for each performance. The benefit there is that you can use Wordfly's editor to better format your performance synopsis, although then you'd need either a separate triggered campaign for each performance or some serious dynamic text logic in your template. Guess it depends on how many shows you're running at once.

Agreed! I know a lot of places have very cool dynamic content that will pull in during an entire season. However, I've found for our purposes (as a theater with usually only 2 shows running at a time in different venues), it's easier to build a new WordFly campaign per production. That way I can customize all the content on an individual production basis and not have to mess with lots of dynamic tags. This has also proven useful for our fundraising team that wants specific numbers for our sponsors on how many emails their logos were listed in. Performance reminders can get pretty complicated so for organizations just starting with triggered campaigns, I'd encourage building them on the production/performance level as well. 

Thanks all. Our theatre runs in rotating rep which complicates the reminders a little bit as our main stage could be running up to 3 shows during a given period of time. 

i guess we could do individual reminders based on production. I just wanted to see if someone was using an alternative resolution.

Hi Brian

I have setup a custom view for our pre/post performance emails.
We rarely have multiple production running in the same week (less so on the same day) so we send the emails daily and the view returns a single line per email address with one of their order numbers (if they have more) a flag to see if they have tickets to collect or to  remind them to bring their tickets. We use inventory content items to add in the URL, image running times etc for the performance as awell as having a Warning field which can be used for the normal Flashing lights, loud bangs in a performance but we also use it for other events - for instance next week we have a public transport strike so that is being added for the performance on those days to remind customers to leave earlier etc.

In the past when I have worked at venue with multiple productions running on the same day, and also when we wanted to send Members an email a week rather than one per performance I have written Table functions or stored procedures and local table to populate multiple performance information into a single email.

The only thing I always do though is make sure the List and the output set are pointing at the same view/table just to ensure that everyone you pull through has the relevant data.

Mark

Thanks Mark.  Are you able to share the setup?  Our resident SQL coder is no longer with the organization, but maybe this could help point us in the right direction.  

Here is the SQL I am using for my current view.

CREATE view [dbo].[lv_pre-post_emails]
AS
select
     theatre=case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' else 'Other' end,
     email_type=case
                              when convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) then 'TODAY'
                              when p.perf_dt<GETDATE() AND school.key_value is not null then 'SCHOOL-POST-SHOW'
                              when p.perf_dt<GETDATE() then 'POST-SHOW'
                              when school.key_value is not null then 'SCHOOL-REMINDER'
                              else 'REMINDER'
     end,
     o.customer_no,
     weekday=DATENAME(weekday,p.perf_dt) ,
     date=convert(varchar(11),p.perf_dt,113),
     start_time=replace(right(convert(varchar(20),min(p.perf_dt),100),8),':00',''),
     production_name=min(i.description),
     running_time=min(ISNULL(rt.value,'Please check the website closer to the performance.')),
     drinks_allowed=min(ISNULL(da.value,'Yes')),
     warning=min(ISNULL(wa.value,'NA')), -- Adding NA rather than blank/null as guarantee the response write code to say if warning not equal to 'NA'
     header_image=min(case
                                        when img.value IS null and f.th_no=2 then 'theatre-royal.jpg'
                                        when img.value IS null and f.th_no=8 then 'city-hall.jpg'
                                        else img.value
                              end ),
     perf_url=min(ISNULL(url.value,'')),
     order_no=min(o.order_no),
     email=MIN(ea.address),
     tickets_to_collect=max(case when sli.sli_status in (3) then 'Y' else 'N' end),
     tickets_to_bring=max(case when sli.sli_status in (12) then 'Y' else 'N' end)
from t_facility f(NOLOCK)
join T_PERF p (NOLOCK) on p.facility_no=f.facil_no
join T_SUB_LINEITEM sli with (NOLOCK) on sli.perf_no=p.perf_no and sli.sli_status in (3,12)
join T_ORDER o with (NOLOCK) on o.order_no=sli.order_no and o.customer_no>0
join T_EADDRESS ea with (NOLOCK) on o.customer_no=ea.customer_no and isnull(ea.primary_ind,'N')='Y' and isnull(ea.inactive,'N')='N'
join t_inventory i with (NOLOCK) on p.prod_season_no=i.inv_no
join TX_INV_TKW ik with (NOLOCK) on i.inv_no=ik.inv_no or p.perf_no=ik.inv_no
join TR_TKW k with (NOLOCK) on ik.tkw=k.id and k.category=8 --TNEW performance category
left join TX_CUST_KEYWORD school with (NOLOCK) on o.customer_no=school.customer_no and school.keyword_no=304

--join LT_TRN_PRIVACY_ACT dp with (NOLOCK) on o.customer_no=dp.customer_no and dp.allow_email in (1,2)
left join TX_INV_CONTENT rt with (NOLOCK) on (i.inv_no=rt.inv_no or p.perf_no=rt.inv_no) and rt.content_type=37 --running times
left join TX_INV_CONTENT da with (NOLOCK) on (i.inv_no=da.inv_no or p.perf_no=da.inv_no) and da.content_type=38 --drinks allowed
left join TX_INV_CONTENT wa with (NOLOCK) on (i.inv_no=wa.inv_no or p.perf_no=wa.inv_no) and wa.content_type=39 --warnings
left join TX_INV_CONTENT img with (NOLOCK) on (i.inv_no=img.inv_no or p.perf_no=img.inv_no) and img.content_type=40 --header image file
left join TX_INV_CONTENT url with (NOLOCK) on (i.inv_no=url.inv_no or p.perf_no=url.inv_no) and url.content_type=41 --url bit after www.theatreroyal.co.uk/.../ ie young-frankenstein

join tx_inv_tkw ps with (NOLOCK) on p.perf_no=ps.inv_no and ((ps.tkw in (59,91) and p.perf_dt<GETDATE()) or (ps.tkw in (60,90) and p.perf_dt>GETDATE())) --postshow keywords for earlier perfs and preshow for dates ahead

where f.th_no in (2,8)
and
(
     (f.th_no = 2 and school.key_value is not null and convert(varchar(10),dateadd(day,14,GETDATE()),102)=convert(varchar(10),p.perf_dt,102) and p.perf_no not in (18632) ) --7 days before for TR schools
or
     (f.th_no = 2 and school.key_value is null AND convert(varchar(10),dateadd(day,4,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --4 days before for TR
or
     (f.th_no = 8 and convert(varchar(10),dateadd(day,1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --Day before for CH
or
     convert(varchar(10),dateadd(day,-1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102) -- Yesterday for post show
or
     convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) -- Today
)

group by case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' else 'Other' end,
     case
          when convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) then 'TODAY
          when p.perf_dt<GETDATE() AND school.key_value is not null then 'SCHOOL-POST-SHOW'
          when p.perf_dt<GETDATE() then 'POST-SHOW'
          when school.key_value is not null then 'SCHOOL-REMINDER'
          else 'REMINDER'
     end,
     convert(varchar(11),p.perf_dt,113),
     DATENAME(weekday,p.perf_dt),
     o.customer_no

Hope that helps

Mark