From NewHaven Software Wiki

Revision as of 18:11, 22 February 2010

Introduction

Chances are you already know that your customer list is one of your most valuable assets. To make the most of that list, be it for marketing or customer service, you'll want to ensure the email address you have for your customers is a valid one. While there is little you can do to validate an email address is deliverable (like our Address Validation Module can do for physical addresses) or that it matches the recipient (like NCOA processing does for physical addresses) but you can search for common mistakes in the entry of the email address so they can be corrected. This article will explain how to identify those bad email addresses and recommend some techniques for fixing them.

Identifying bad email addresses

The following SQL can be used in a custom mail list filter to identify customers who have an email address but there is something wrong with its formatting. This could be the lack of an @ sign, a missing .com at the end, a space somewhere in the middle or non-email type data.

Create a new mail list filter and click on the box for 'Write own SQL'. Paste in the SQL below, name the filter (e.g. Bad Emails) and then save.

SELECT C.*
FROM CUST C
WHERE EMAIL <> '' AND NOT
(CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND  LEFT(LTRIM([EMAIL]),1) <> '@'
AND  RIGHT(RTRIM([EMAIL]),1) <> '.'
AND  datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND  (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0))

Techniques for repairing

Now that you have your list of customers with bad email addresses, you can use one of the following methods to follow-up and correct them:

• QuickCount
• Export/Excel
  • Fix in CMS
  • Professional Services to import your changes
• Mail List Report

Future

There are services today that offer an 'email append service' to find the proper email for a customer. Presently CMS does not support an integration with any particular vendor that offers this service. It could, however, still be done via professional services. Please contact your Platinum Account Manager if you have an email append service company you'd like us to work with. We can coordinate with them to make sure they have the data they need and that we're able to update your customer records with the resulting email addresses.