Automatically mark bounced emails as invalid

Idea created by Daryl Acumen on Oct 29, 2015
    Active
    Score15
    • Daryl Acumen
    • Ishani Lad
    • Johannsen Quinio
    It's annoying that emails can bounce dozens of times, yet not get marked as "invalid" in SugarCRM.  Currently I have this code running in a cron script on my server.  I'm shocked that a system as mature as Sugar doesn't include an option this basic by default.

    update email_addresses d  inner join  
    (
    SELECT
    a.first_name, a.last_name, d.*, count(*) as fails
    FROM 
    campaign_log b 
    inner join leads a on
    a.id = b.target_id inner join
    email_addr_bean_rel c on
    a.id = c.bean_id inner join
    email_addresses d on
    c.email_address_id = d.id
    WHERE 
    b.deleted=0 AND 
    b.activity_type='send error' AND 
    b.archived=0 and
    d.deleted = 0
    group by 1,2,3
    order by count(*) desc
    ) e
    on d.id = e.id
    set d.invalid_email = 1
    where e.fails > 1;