From NewHaven Software Wiki

Revision as of 18:23, 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 - Use the QuickCount feature to apply your 'Bad Emails' filter. Here you'll see just how many you have that are bad and can double-click on any one of the records to pull up that customer record. There you can review, correct and/or contact the customer. Once done, move on to the next in the list. Tip: After correcting a number of records, you may want to click Un-Apply and then Apply again to get a refreshed list that will now exclude those you have corrected.
• Export/Excel - You can also use your 'Bad Emails' filter to export a list of customers out to a file. This file would be suitable for opening with Excel. This technique has the benefit of allowing you to do sorts by the various fields. Examples of how you might use sorting includes:
  • If you wanted to break up the list to have multiple employees work through them. You could sort by state, zip or other fields and break the list up into pieces.
  • Sorting by email address may expose some commonality in the errors like spaces at the beginning of the address or phone number that were typed into the email address field.

Once you've settled on

• • 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 our 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.