RapidMailer Amazon SES Email Cleanup
Not an unexpected outcome, but I am getting literally hundreds of failures from the leads I imported into RapidMailer. I now have RapidMailer connected to Amazon SES, a commercial email service. As email messages are attempted, SES receives delivery failure messages.
I figured out quickly that Webmail, RoundCube, Squirrel Mail, are not the best tools for the job. My goal was to parse the body of the email, and capture only the pertinent information that I could filter and isolate, that could then be used to delete the errant email addresses from the RapidMailer database.
RapidMailer is a very sophisticated WordPress Plug-in, with so many more options that shown in the videos. This adds a layer of complexity, but once it is set up, it runs pretty smoothly. It uses the WordPress user database as its storage of email addresses.
WordPress uses MySQL as a database, and phpMyAdmin through CPANEL on your hosting account as the user interface.
I have literally hundreds of thousands of leads that I had purchased from many different sources. The problem with these leads is that you have no idea how or when they were acquired, and how accurate information is about each lead. My first upload of 540 leads yielded 260 errors – rejections as unknown email addresses, or failures due to non-existent or blocked domains.
As I examined the error messages, I noticed many patterns, including complete failure to send emails to certain countries. I began to build a filter list that I would use as my basis for scrubbing the emails in the RapidMailer database.
I didn’t want to incur any more costs, so I looked around for tools and techniques I could use in the interrim to build my new RapidMailer list. I found that Outlook on the web actually gave me the best interface, albeit still manual, but I could quickly download just the failure messages that Outlook captured as an attachment. It took me about an hour to download 260 of these failure messages to text files.
A simple Windows Command Line changes them all to .TXT filenames
ren delivery* delivery1*.txt
I then scoured the internet looking for some utilities to consolidate these text files into an Excel worksheet. I found success with my trusted source, the Microsoft MVPs. They not only had an article explaining all about the techniques, but they also provided VBA code (Visual Basic for Excel – the macro language) that required only slight modification.
Once I had the raw data in a spreadsheet, I could perform lots of magic.
- Sort – eliminates blank lines and groups messages so they can be deleted as a block.
- Text to Columns – parse blocks of data based on delimiters – built-in Excel function
- Find/Replace – find patterns and replacement
- Remove Links – another macro gem from the MVPs
- Trim – a simple macro I wrote that removes extraneous spaces before and after.
- Remove Duplicates – built-in Excel function
I save this to a CSV, then import it into phpMyAdmin to a table called wp_users_failed
Once my imported data is appended to that table, I run a SQL query to eliminate duplicates in the consolidated table:
DELETE e1 FROM wp_users_failed e1, wp_users_failed e2 WHERE e1.user_email = e2.user_email AND e1.id > e2.id;
I run a SQL query that removes emails based on wildcards I have found in my pattern searches:
Check out Today’s Free eBook or Video
Check Out Today’s Featured Home Business System
I will Fill Your Downline with PAID Members
(Click Link Again for More Choices)