Starting Clean: Parsing signup data for streamlined uploading and clean(er)* data

Quality data operations is a lot like painting -- it all starts with the prep. Unfortunately, that message isn't often communicated to frontend development teams (looking at you, Product Owners) so the email address intake is typically only checking for proper format, not correct addresses. The issues tend to be in the domain names and TLDs. For example, ".con" vs. ".com". This leads to upload or send failures when moving the addresses to your ESP. Whether you're dealing with customer lists, employee directories, or any other type of list data, ensuring that the data is clean, accurate, and well-structured is crucial.

In this article, I aim to guide you through the process of cleaning and parsing list data, specifically focusing on name and email fields, in a programmatic and very fast way that has enabled us to process hundreds of thousands of records in effectively no time (testing the single-threaded quick version of the code I wrote for this article benchmarked at 7.2 seconds for 100,000 records from file load to the write of the new file). So for those of you used to regex expressions and spreadsheets, read on for some ways to make your life a little bit easier.

The Challenge of Dirty List Data

List data often comes from multiple sources and can contain various inconsistencies and errors. These can range from misspelled names and email domains to improperly formatted entries. Dirty data can lead to a host of problems, including:

• Inability to message your customers
• Inaccurate profiles
• Increased operational costs

The Need to Parse and Clean Data

Cleaning and parsing your list data are essential steps to ensure that your marketing operations run smoothly. Here are some compelling reasons why you should really put the effort into your data prep:

1. Enhanced Data Quality: Clean data is easier to work with and leads to more accurate insights, personalization, and measurement.

2. Improved Decision-Making: Accurate data supports better decision-making at all levels of an organization.

3. Increased Efficiency: Clean and well-structured data can be processed more quickly and easily.

4. Better Compliance: Ensuring that your data is clean and accurate can also help in meeting regulatory compliance standards.

Use Case: How to Clean and Parse List Data

Parsing Names

Parsing names involves breaking down full names into their parts, such as first name and last name. This can be particularly challenging when dealing with middle initials or compound last names. Even if you are collecting the names in firstname and lastname fields, you still have to ensure the correct type of data is provided. Often, a single "name" field consisting of a full name is provided. Python's nameparser library can be a great help in this regard.

Here's a code snippet that demonstrates how to parse names:

from nameparser import HumanName
    def parse_name(full_name):
        name = HumanName(full_name)
        first_name = name.first.title()
        last_name = name.last.title()

    return first_name, last_name        

That code will look at the name and separate it into variables for first and last name (ignoring middle initial if present).

Correcting Email Addresses

Email addresses often contain typos or incorrect domain names. Correcting these can be a bit tricky but is essential for effective communication. One approach is to use known common mistakes and correct them programmatically.

Here's a code snippet for email correction:

def correct_email(email):
    common_mistakes = {"con": "com", "cmo": "com", "ogr": "org", "nt": "net"}
    username, domain = email.split("@")
    tld = domain.split(".")[-1] 
    if tld in common_mistakes:
        corrected_tld = common_mistakes[tld]
        corrected_domain = domain.replace(tld, corrected_tld)
        corrected_email = f"{username}@{corrected_domain}"  
        return corrected_email
    else:
        return email        

Within this code, I'm looking for certain common mistakes. Right now, this is an abridged list. My actual code contains many more entries and is loaded from a data frame, but this should get you started.

Once the names are formatted and the emails are corrected, you just need to push them to your database, ESP, etc. This can be as simple as writing out a dataframe to CSV file and uploading or more complex such as how we do it via API directly into data storage locations.

In Conclusion

Data is often said to be the new oil, but like crude oil, it needs to be refined to be valuable. Cleaning and parsing list data may seem tedious, but the benefits far outweigh the effort and this should make your life a little easier. With clean and well-structured data, you're well on your way to making more informed decisions and running more efficient operations.

Feel free to share your thoughts and experiences on data cleaning and parsing in the comments below. Thank you for reading!

What's with the clean(er)*?

So if you got this far, you may be wondering why I said clean(er). Well, the truth is that the data is only ever going to be as good as what someone enters. That, coupled with poor front-end collection and validation processes may mean your data has to go through multiple cleaning steps. It's essential that you have a good process and strategy for data cleansing. We recommend working through this as a business problem vs. a technology problem to make sure it's understood what data is necessary. A good portion of data cleansing is getting rid of the data you don't need. Don't pollute the lake.

For those of you interested, we like the janitor package in R. The python implementation of it is pyjanitor. Check those out.

About The Asman Group

The Asman Group focuses on marTech, advanced analytics, and digital commerce strategy.? We blend data science with the?power of human experience to help our clients evolve their customer connections.

The heart of our mission is our commitment to help our clients find their ideal audience by leveraging cutting-edge marketing technology and advanced analytics to create a data-driven approach to audience marketing.?

www.asmangroup.com