Performance Reminders - Wordfly

Pulling performance information into an output set can be complicated. What problems are you encountering? Have you built any custom query elements yet?

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 Nick, 

We created a local_view which pulls the data we need for output into a table that can be referenced in output set up.

I can send you the SQL we used but we also have custom ticketing tables in place and a very simple performance structure in place - i.e did not solve the multiple emails if same perf in one day problem.

Let me know if you would like more info on our set up in case it helps drive some ideas for you.

Thanks

Louise

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

Hi Louise,

That would be great.  If nothing else, it may help spark some ideas.  

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.  

Redundant to replies I've previously posted, but a parameter of Venue in our key to this and then have a triggered campaign per venue (not as directly applicable when it's a rep situation, but hopefully still sparks something). We populate custom fields on the Production Season level, then pull them in dynamically via custom output set elements.

Note that this strategy is problematic if you need formatting--you can load html into fields like that, but then you can't use the same field to populate the text version of your email correctly. (I typically cheat by just referencing that more content is available via the view as web url. Definitely not as good customer service, but I have to assume people who look at text versions are used to also having to look things up again on webpages.)

Otherwise, I also +1 to Scott and Gabriela's comments about giving good consideration to whether it really pays off to have it populate dynamically. (For us it does, but this should always be evaluated.)

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