Hello,
I was wondering if anyone had solutions for dealing with patrons who have recently opt-ed back in to receive email communications but have a previous global unsubscribe record. Currently, opting back in does not automatically overwrite the unsubscribe, so these patrons are still suppressed from emails, unless we manually go in and remove the global unsubscribe.
Is there a way to make it so that when a previously unsubscribed patrons, re-opts in to receive email, this is automatically updated?
Many thanks,
Jamie jean
Hi,
I know this is an old thread, but it's a topic I've been looking at recently.
I'm setting up the Unsubsribe Override as per following WordFly article:https://support.wordfly.com/hc/en-us/articles/205771906-Unsubscribe-override-CRMSubscribeDate
Has anyone else done this and do you have any advice?
I'm planning to use the Contact Permission > Last Update Date from Tessituraas the date to map to the Subscriber date in WordFly.
many thanks,Dara
This is how I do it. Feel free to reach out to me if this needs explaining. I never learned how to comment my code.
This is a view that I reference in other views when generating data sets to access in output sets to use in WordFly campaigns..
with signup_date as
(
SELECT
row_number() over(partition by customer_no order by customer_no, date desc) as sort,
customer_no,
date,
old_value,
new_value
FROM
TA_AUDIT_TRAIL
where
table_name = 'T_CUSTOMER'
and column_updated = 'EMarket_Ind'
--and old_value <> '(none)'
--and new_value = '(none)'
)
SELECT TOP (100) PERCENT
cust.customer_no,
--max(case when isnull(cust.emarket_ind,3) = 2 then '' else isnull(cast(convert(varchar,cast([date] as date),101) as varchar),'') end) as 'eList_no_emarketing',
max(isnull(cast(convert(varchar,cast([date] as date),101) as varchar),'01/01/1900')) as 'eList_no_emarketing',
MAX(CASE WHEN a.keyword_no = 589 THEN CASE WHEN key_value = 'Yes' THEN '1' WHEN key_value = 'No' THEN '0' ELSE '' END ELSE '' END) AS 'eList_Friends_and_Family',
MAX(CASE WHEN a.keyword_no = 590 THEN CASE WHEN key_value = 'Yes' THEN '1' WHEN key_value = 'No' THEN '0' ELSE '' END ELSE '' END) AS 'eList_30_Under_30',
MAX(CASE WHEN a.keyword_no = 591 THEN CASE WHEN key_value = 'Yes' THEN '1' WHEN key_value = 'No' THEN '0' ELSE '' END ELSE '' END) AS 'eList_MTC_Education',
MAX(CASE WHEN a.keyword_no = 592 THEN CASE WHEN key_value = 'Yes' THEN '1' WHEN key_value = 'No' THEN '0' ELSE '' END ELSE '' END) AS 'eList_Reading_Series'
T_CUSTOMER cust
left outer join dbo.TX_CUST_KEYWORD AS a on cust.customer_no = a.customer_no and a.keyword_no IN (589, 590, 591, 592)
--left outer join TA_AUDIT_TRAIL audit on cust.customer_no = audit.customer_no and column_updated = 'EMarket_Ind' and old_value = 'No E-Marketing' and isnull(new_value,'') <> 'No E-Marketing'
left outer join signup_date b on cust.customer_no = b.customer_no and b.sort = 1 and b.new_value = '(none)'
--INNER JOIN dbo.T_KEYWORD AS b ON a.keyword_no = b.keyword_no
GROUP BY cust.customer_no
ORDER BY cust.customer_no