Wordfly

Hey there!  

A little update from the FTC crew.

We're fully live with Wordfly now!  woohoo.  I did a few crafty customizations to get us stable;

1:  for better or worse, we've always run an opt-out list.  I wasn't a fan of how TNEW handled subscriptions, so i just used regular keywords and made 4 categories for our types of emails:

Weekly
Monthly
Targeted
Concierge

Then I updated LP_CUSTOMER_RANK with the following:

 

 declare @eprefs int

 

select @eprefs = 0

 

select @eprefs = case 

when @customer_no in (

select customer_no

from TX_CUST_TKW

where customer_no = @customer_no AND tkw = 107)

THEN 0

ELSE 1 END

 

IF @eprefs = 1 

BEGIN -- insert preferences

insert into TX_CUST_TKW

SELECT @customer_no, '107', 'Y', '1', 'FTC', 'PROCESS', GETDATE(), 'dbo', GETDATE()

 

insert into TX_CUST_TKW 

SELECT @customer_no, '108', 'N', '1', 'FTC', 'PROCESS', GETDATE(), 'dbo', GETDATE()

 

insert into TX_CUST_TKW 

SELECT @customer_no, '109', 'Y', '1', 'FTC', 'PROCESS', GETDATE(), 'dbo', GETDATE()

 

insert into TX_CUST_TKW 

SELECT @customer_no, '110', 'Y', '1', 'FTC', 'PROCESS', GETDATE(), 'dbo', GETDATE()

END

 

 

This checks whether they have a keyword set, and if not, they're new, so it opts them in.

 

2:  I had to do the basic import of our old system, and update unsubscribes, and past hard bounces.

3:  I solved a tricky tracking issue;  FYI, you can change the variable name of the source number in wordfly, under account settings;  TNEW looks for ?promo=###, so i did that, but our content site wasn't set up for tracking it, only TNEW, so i wrote a little jquery thing to pass that variable through to TNEW:

<script type="text/javascript">
	$('a:not([rel^=gb_page_center])').click(function(){
	var thisHref = $(this).attr('href');
	if (thisHref.match(/\?/)) {
	window.location = $(this).attr('href',thisHref + '&promo=144 ');
	} else {
	window.location = $(this).attr('href',thisHref + '?promo=144');
	}
	});
	</script>

Try it out here:  http://fairfieldtheatre.org/?promo=144

it will keep that variable in the url till you get to TNEW, at which point TNEW sets it in the API session

4:  next to do:  we discovered today;  someone unsubscribed AND managed their preferences; but we've been using the E-Market restriction for unsubs.  i'm gonna change this to a keyword, as it will allow for full web management of preferences.

I'm probably missing somethings but thats the jist of it...

One other more tessitura thing we did, that affects targeted emails;

I made a LTX_TKT_KWD table for shows NOT in tessitura (pre-implementation)

 LTX_TKT_KWD

-------------------------------------------------

Perf name | Interest ID

one row per ID, per show

then i updated my Interest Procedure to UNION both ticket history interests and orders interests to get a full interest history.  The hard part was going back and classifying all our past shows (got an intern to do it)

here it is, use at your own risk :/  but it seems to be going well:

--create a temp table with the counts

select customer_no, tkw, sum(weight) weight, tkw_update

into #tkw

from

(select distinct o.customer_no, it.tkw, 'N' as tkw_update, count(s.sli_no) weight --one count for each seat

from t_order o 

join t_sub_lineitem s on s.order_no = o.order_no

join t_perf p on p.perf_no = s.perf_no

join t_prod_season ps on ps.prod_season_no = p.prod_season_no

join t_production pn on pn.prod_no = ps.prod_no

join tx_inv_tkw it on (it.inv_no = p.perf_no) or (it.inv_no = ps.prod_season_no) or (it.inv_no = pn.prod_no) or (it.inv_no = pn.title_no)

where s.sli_status in (3,12,13) --seated paid, ticketed, donated

and s.due_amt > 0 --not comps

group by o.customer_no, it.tkw

UNION ALL

SELECT a.customer_no, (SELECT DISTINCT b.interest) as tkw, 'N' as tkw_update, SUM(a.num_seats) weight

FROM  LT_TKT_HIST a JOIN

              LTX_TKT_KWD b ON b.event = a.perf_name

GROUP BY a.customer_no, b.interest, a.num_seats) as w2

group by customer_no, tkw, tkw_update

 

--update the table to note the rows which are already in the final table

update #tkw

set tkw_update = 'Y' 

from #tkw t

join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw

 

--update all weights to 0

update tx_cust_tkw

set weight = 0

 

--update rows already in the table

update tx_cust_tkw

set weight = t.weight

from #tkw t

join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw

where t.tkw_update = 'Y'

 

--insert new rows for any which are not in the table

insert into tx_cust_tkw

select t.customer_no, t.tkw, 'N', t.weight, substring(@@servername,1,16),'PROCESS', getdate(), null, null

from #tkw t

where tkw_update = 'N'

Like most IT folks, my colleague James here definitely has a left-side of the brain dominancy!! 

 

Question for all you TNEW & Wordfly users out there:

How are you using lists, E-Market restriction, interests, promotions, promotion responses, suppression lists and subscriptions within your email marketing? We've only been using Tessitura for.. what is it 8 months now James? But as you all know it is possible for something to be easy to learn, but difficult to master without establishing some best practices thru good 'ol trial and error.

Who out there is interested in sharing ideas, strategies and all the fun things that could help us collectively advance our Wordfly practices???

Cheers, 

From the right-brain dominant Marketing Department of FTC

Like most IT folks, my colleague James here definitely has a left-side of the brain dominancy!! 

 

Question for all you TNEW & Wordfly users out there:

How are you using lists, E-Market restriction, interests, promotions, promotion responses, suppression lists and subscriptions within your email marketing? We've only been using Tessitura for.. what is it 8 months now James? But as you all know it is possible for something to be easy to learn, but difficult to master without establishing some best practices thru good 'ol trial and error.

Who out there is interested in sharing ideas, strategies and all the fun things that could help us collectively advance our Wordfly practices???

Cheers, 

From the right-brain dominant Marketing Department of FTC