"Best Practices" for Soft Bounces

Hi all,

We have it set up so that if a customer's email hard bounces, it shows up as a strike-through in the constituent header (which is customised for every department here slightly differently).

B

Hi Brooke,

I'm interested in giving that a go here, too.  Would you mind sharing the code that you use?  I guessing it is in TR_FLEX_HEADER_ELEMENT?

Thanks!

Jenny Graf, UMS

Hi Jenny

We use a custom header rather than the flex header.

So we first grab the primary email address

select @eaddress=address
from T_EADDRESS
where customer_no = @customer_no
and primary_ind = 'Y'

select hardbounce=case
                                          when exists (select 1
                                                                  from T_PROMOTION (NOLOCK)
                                                                  where response=9
                                                                  and customer_no=@customer_no
                                                                  and eaddress=@eaddress
                                                                  having COUNT(*)>2
                                                                  )
                                          then 'Y'
                                          else 'N'
                                          end 

So we check for 3 or more hard bounces recorded against  the primary email address for the customer and return a Y if this is the case, then use logic in Infomaker to format the primary email address in the header with strikethrough.

Here is the code a a single statement  with @customer_no

select hardbounce=case
                       when exists (select 1
                                    from T_PROMOTION p (NOLOCK)
                                    join T_EADDRESS e (NOLOCK) on p.customer_no=e.customer_no and p.eaddress=e.address and e.primary_ind = 'Y'
                                   where p.response=9
                                    and p.customer_no=@customer_no
                                    having COUNT(*)>2
                                   )
                        then 'Y'
                        else 'N'
                   end

Hope that helps

Mark

Hi Jenny

We use a custom header rather than the flex header.

So we first grab the primary email address

select @eaddress=address
from T_EADDRESS
where customer_no = @customer_no
and primary_ind = 'Y'

select hardbounce=case
                                          when exists (select 1
                                                                  from T_PROMOTION (NOLOCK)
                                                                  where response=9
                                                                  and customer_no=@customer_no
                                                                  and eaddress=@eaddress
                                                                  having COUNT(*)>2
                                                                  )
                                          then 'Y'
                                          else 'N'
                                          end 

So we check for 3 or more hard bounces recorded against  the primary email address for the customer and return a Y if this is the case, then use logic in Infomaker to format the primary email address in the header with strikethrough.

Here is the code a a single statement  with @customer_no

select hardbounce=case
                       when exists (select 1
                                    from T_PROMOTION p (NOLOCK)
                                    join T_EADDRESS e (NOLOCK) on p.customer_no=e.customer_no and p.eaddress=e.address and e.primary_ind = 'Y'
                                   where p.response=9
                                    and p.customer_no=@customer_no
                                    having COUNT(*)>2
                                   )
                        then 'Y'
                        else 'N'
                   end

Hope that helps

Mark