Wordfly

Great information. Thank you for sharing this.

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:

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'

Great information. Thank you for sharing this.

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:

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'