christianbeer/bounces-handler

Very Simple Email Bounces Processing System

Very Simple Email Bounces Processing System¶ ↑

Bounces-handler package is a simple set of scripts to automatically process email bounces. I use this to remove forum users where the email is invalid in order to not send them a newsletter again.

This is a simplified version of github.com/kovyrin/bounces-handler using a local SQLite3 database.

Attention: The used parser does not recognize multiple emails in one bounce as is generated by Postfix these days!

Package Structure¶ ↑

• Perl scripts to process incoming email:

  • bounces processor – could be used to process all your bounce emails

Database structure¶ ↑

The same database structure is used as in the upstream version:

• mailing_domains - this table stores a list of all mailing domains we’ve ever seen in our blacklists/bounces.

  • id - auto-incrementing primary key

  • name_crc32 - CRC32 hash of the domain name (used as a key for faster domains lookups)

  • name - domain name

• mailing_blacklist - major blacklisting table used for keeping track of all blacklisting events in the system and all emails related to those events.

  • id - auto-incrementing primary key

  • domain_id - foreign key to associate a record with a domain

  • user_crc32 - CRC32 hash of a username part ([email protected]) of an email

  • user - username part ([email protected]) of an email

  • source - shows where a record came from (bounce, unsubscribe request, honeypot email or something else)

  • level - one of the two possible blacklisting event levels (hard or soft)

  • reason - short string description of a reason for blacklisting.

  • created_at - creation timestamp

Bounces processing script¶ ↑

This simplified version is meant to be run on the commandline where emails are provided via stdin. Here is the workflow I use.

1. Export bounce mails in eml format¶ ↑

I usually use Thunderbird to sort the bounces and then use the “Save As…” feature to export all of them into a directory.

2. Check-out the code¶ ↑

You can get the latest version of this package from the github account:

$ git clone git://github.com/ChristianBeer/bounces-handler.git

To run the script you’ll need to install the following CPAN modules (at least): DBI, DBD::sqlite3, Mail::DeliveryStatus::BounceParser, String::CRC32.

On Debian this is done with: apt-get install sqlite3 libdbi-perl libdbd-sqlite3-perl libmail-deliverystatus-bounceparser-perl libstring-crc32-perl

For the next steps there is also a bash script supplied that conveniently executes the commands.

3. Set up your DB¶ ↑

When you have the latest version of the code, you need to set up the database for the scripts. This is done by running:

$ sqlite3 bounces.db < db/schema.sqlite3

If you choose to use another name for the database, change it in your ‘email-processor/bounce-processor.pl’ file.

4. Process Emails¶ ↑

Next step you need to do is to loop over the files exported in step 1:

$ for f in eml/*; do ./email-processor/bounce-processor.pl < $f; done

Make sure that the filenames don’t contain spaces, this can be done with this command:

$ for f in eml/*\ *; do mv "$f" "${f// /_}"; done

5. Extract information¶ ↑

Now you have all the bounces in the sqlite database and can extract the information you want. I use this query to get the email adresses that bounced for non-spam reasons:

$ sqlite3 bounces.db "select user, name from mailing_blacklist u, mailing_domains d where u.reason<>'spam' and u.reason<>'unknown' and u.domain_id = d.id;"

Database description¶ ↑

Possible level values:

• :hard - for bounces, feedback loop emails and other places where you want to be sure any of your emails won’t be send to an address.

• :soft - for unsubscribe link clicks, etc, where some emails exists, but you want to ban it from the system for some reason (unsubscribe link click, for example).

Possible source values, default one is :other :

• :bounce - used for bounce emails, could be hard (if an address does not exist), or soft (mailbox quota, etc).

• :unsubscribe - used for user unsubscribe requests

• :honeypot - used for email addresses which are known honeypots for spammers

• :other - used for other situations (you can explain them in your reason field)

Authors and credits¶ ↑

All the code in this package has been developed by Alexey Kovyrin for Scribd.com and is released under the GPLv2 license. For more details, see LICENSE file.

For the bounces processing code we’ve used Mail::DeliveryStatus::BounceParser CPAN module by Meng Weng Wong.

Simplification was done by Christian Beer.

Links¶ ↑

Author’s Blog:: blog.kovyrin.net

Scribd Site:: www.scribd.com

Bounces Parsing Module:: search.cpan.org/~freeside/Mail-DeliveryStatus-BounceParser-1.4/BounceParser.pm