By: Jeremy Kadlec | Updated: 2024-01-02 | Comments | Related: > Data Quality Services
Problem
Email is mission critical in our organization. Our organization uses email as a means to communicate with current and prospective customers due to the cost, speed, simplicity and interactivity. We need to ensure our email addresses are correct when they are originally collected and accurate over the lifetime of our customers. How can we perform email validation when data is collected on our web site? How can we validate our email data in bulk for particular projects to cleanup emails, prepare emails for marketing programs, etc.? How can we be sure we are meeting the GDPR requirements?
Solution
Email has been the communication medium of choice for organizations due to the cost, delivery speed, response speed and overall client interactivity. Unfortunately, email addresses can be entered incorrectly and email address data goes stale just like any other contact data. Most organizations have a need to:
- Validate email addresses during registration on web applications
- Cleanup emails for regular communication with customers
- Improve the email deliverability for email-based marketing programs
Email based validation is more than just a simple RFC check, however it is much less complicated than validating full contact information (name, address, phone number, employment, marital status, household information, etc.) for a customer. This is where Melissa’s Global Email Verification solution can address real time email validation with a web-based API and batch-based email validation with SQL Server Integration Services (SSIS).
Melissa’s Global Email Overview
The value of the Melissa Global Email Verification solution centers around the artificial intelligence to build a numerical Delivery Confidence Score between 0 and 100 as well as public Result Codes in the final output. At a high level, the confidence score is based on artificial intelligence (Accept, Caution or Reject) surmised by domain validation (spam trap, disposable, accept all domains, mobile, black list IP), correct email format (syntax validation), mailbox validation (invalid mailbox, mail server not found), removal of illegal characters, validation from secondary data sources, compromised email checks and more. The final output is based on the best possible result and the associated data score.
Melissa’s Global Email solution includes modes for real time verification:
- Premium Mode – Email address verification including an SMTP ping and additional proprietary validation logic for the most accurate data possible.
- Express Mode - The email validation is performed against Melissa’s blazing fast cache to integrate directly into your applications for a truly real time solution. Melissa has more than 750,000,000 email addresses (both valid and illegitimate) in their cache. Further, 500,000 to 4,000,000 email addresses are added to the cache every 7 to 10 days to continuously improve their data set.
Both email validation modes from Melissa are GDPR compliant including encrypted cache for European users.
Melissa Global Email APIs
The Melissa Global Email API (REST, JSON, JSONP and XML) is used for both real time email validation and small batches. A simple Request (Learn more) and Response (Learn more) paradigm is used to process the data. The Request consists of the header as well as the record ID and email address. The Response includes the header, passes back the record ID as well as 23 fields denoting the record status. Let’s take a look at a sample JSON Request and Response:
Sample Request
{
"TransmissionReference": "string",
"CustomerID": "string",,
"Options": "string",
"Records": [
{
"RecordID": "string"
"Email": "string",
},
{
"RecordID": "string",
"Email": "string",
},
...,
]
}Learn more about the JSON Request.
Sample Response
Below is a sample response screen shot showing the Deliverability Confidence Score and the associated data:
{
"Version": "string",
"TransmissionReference": "string",
"TransmissionResults": "string",
"TotalRecords": "string",
"Records": [
{
"RecordID": "string",
"DeliverabilityConfidenceScore": "string",
"Results": "string",
"EmailAddress": "string",
"MailboxName": "string",
"DomainName": "string",
"TopLevelDomain": "string",
"TopLevelDomainName": "string",
"DateChecked": "string",
"DomainAgeEstimated": "string",
"DomainExpirationDate": "string",
"DomainCreatedDate": "string",
"DomainUpdatedDate": "string",
"DomainEmail": "string",
"DomainOrganization": "string",
"DomainAddress1": "string",
"DomainLocality": "string",
"DomainAdministrativeArea": "string",
"DomainPostalCode": "string",
"DomainCountry": "string",
"DomainCountryCode": "string",
"DomainAvailability": "string",
"DomainPrivateProxy": "string",
"PrivacyFlag": "string",
},
{
"RecordID": "string",
"DeliverabilityConfidenceScore": "string",
"Results": "string",
"EmailAddress": "string",
"MailboxName": "string",
"DomainName": "string",
"TopLevelDomain": "string",
"TopLevelDomainName": "string",
"DateChecked": "string",
"DomainAgeEstimated": "string",
"DomainExpirationDate": "string",
"DomainCreatedDate": "string",
"DomainUpdatedDate": "string",
"DomainEmail": "string",
"DomainOrganization": "string",
"DomainAddress1": "string",
"DomainLocality": "string",
"DomainAdministrativeArea": "string",
"DomainPostalCode": "string",
"DomainCountry": "string",
"DomainCountryCode": "string",
"DomainAvailability": "string",
"DomainPrivateProxy": "string",
"PrivacyFlag": "string",
"MXServer": "string",
},
...,
]
}
Learn more about the JSON Response.
Melissa Global Email with SQL Server Integration Services
Although some of the APIs enable batch processes, the record count is limited to 10. To address large scale data sets commonly found in databases, Melissa Global Email Verification includes direct integration with SSIS. The email data can be extracted from a SQL Server database table or flat file. The corresponding source widget can be dragged and dropped into the SSIS workspace as well as the Global Verify component from Melissa, then the output can be saved to a database table or flat file as shown below.
When you double click on the Global Verify widget in SSIS, the following interface displays the Email tab with the singular email address input field and 23 output fields of data which are used to validate the email addresses.
To continue the process, the email field is passed through the process as shown below.
Finally, the output is configured with the associated Result Codes.
If you are already proficient with SSIS, getting the email validation setup is fast and can be completed in as few as 5 minutes to begin benefiting from Melissa Global Email Verification.
Next Steps
- How do I get started with Melissa’s Email Verification?
- Outline your email verification needs
- Real time email validation
- Batch email validation
- GDPR compliance
- Improved email delivery
- Learn more about Melissa’s Solution
- Get Technical Questions Answered
- Global Email Web API
- Wiki – Melissa’s Global Email
- Quick Start Guide for Developers- Melissa’s Global Email
- Data Sheet – Learn more
- Contact Sales
- Call 1-800-MELISSA
- Email - [email protected]
- Outline your email verification needs
- Learn more about the Melissa solutions
MSSQLTips.com Product Spotlight sponsored by Melissa, makers of Melissa's Global Email Verification.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-01-02