I have a mailing list of about 6,000 records. I maintain this list using a MySQL database and on occasion I use the list to update members with new information. I wrote my own email program to send emails to my list. One of the problems is that I now have hundreds of bad email addresses. I haven't taken the time to clean the list in some time and with the list dating back to 2004 you can imagine the level of bounces I get after each mailing.
The other day I received an email from DHL that said the following:
Your DHL WebShip/CorporateShip User ID XXXXXX is set to expire on 7/19/2006, due to inactivity over the past 180 days.
To keep your registration active, please log in with the User ID at https://sso.dhl-usa.com/sso/ and highlight the 'Ship' menu in the top navigation and then click on 'Prepare a Shipment' prior to this expiration date.
This message is the only notification you will receive before your User ID expires. If you do not log in prior to 7/19/2006, your User ID will no longer be valid. All shipment history and address book entries will be removed. At that point you will need to re-register with DHL WebShip/CorporateShip to process any future shipments.
If you have forgotten your password please go to www.dhl-usa.com and click on 'Forgot Password' or click the link below.
https://sso.dhl-usa.com/sso/forgot.asp
If you need technical assistance, please contact Technical Support at 1-800-527-7298 or email easy.help@dhl.com.
This is a system generated email, please do not reply.
That gave me an idea.
I wrote a script that searched through my list and selected the records where the user has not logged in for over 180 days. An email was sent out informing the user that they needed to log in by July 20 or their membership would be deleted.
This accomplished three things. It eliminated bad email addresses from my list, it trimmed my list of users who have no intention of ever coming back, and it reminded inactive members about my services.
The next step is to set up a cron job to run the script once each week and then delete those records that expired. Hands free, no hassle solution to managing a large email list.
Another cool thing I learned while building the script was a neat little snippet that can be used in a MySQL query to select records that are older than 180 days.
SQL:
-
-
DATE_ADD(lastlogin,INTERVAL 180 DAY) < NOW()
-
Where lastlogin is the column holding the timestamp of the users last visit.