From NewHaven Software Wiki

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 each customer 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,) 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))

Breaking up the list

If you list is large you may wish to break it up for multiple employees to work through. This can easily be done with the use of compound filters. Just decide how you want to break up the list (e.g. states) and create a filter for each 'territory' you want to create. Then use compound filters to combine your territory filter with the 'Bad Emails' filter so each employee can run their own list.

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, in the QuickCount window you may want to click Un-Apply and then Apply again to get a refreshed list that will now exclude those records you have corrected.

Mail List Report

If you'd prefer to print a report of all the customers with bad email addresses, just go to Mail List Reports where you can choose your filter (Bad Emails) and which fields you'd like printed on the report. We recommend you include at least the customer number on the report as an efficient means of looking up the customer.

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.

To export the bad emails list, go to the Export From Mail List section in CMS to define which fields you want exported and which filter to use (your new 'Bad Emails' filter.) Be sure to include the customer number in your export as it is the fastest way to look up a customer and also can facilitate re-importing your changes.

Ideally the changes you make to the email addresses would be done right in CMS. However, if you find it easier to correct them in the spreadsheet, it may be possible to import your changes right from the spreadsheet back into CMS. Your Platinum Account Manager can provide you with a quote for this service.

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.