Getting your data ready for AI: where the time goes

The model is the part of an AI project people talk about. The data is where the time goes. On our projects, getting the data into shape has often taken 80% or more of the effort. Surveys of data scientists put it lower but still large: in Anaconda’s 2020 State of Data Science survey, loading and cleaning data took about 45% of their time.

I wrote about this several times between 2019 and 2021. This guide pulls those posts together, because the problem hasn’t gone away. Today’s AI models are far better than the ones we built then, and they still give bad answers when they’re fed bad data.

Why the data takes so long

Building a model is the easy part now. The large cloud companies have automated much of it. The hard part is getting clean, reliable data to the model, every day, in a form it can use.

Every dataset is messy in its own way. Customer activity data needs different cleaning than customer profiles bought from a third party. We’ve seen zip codes stored as numeric values, which drops the leading zero from every address in New England. We’ve seen a column that should hold names contain the value “20” a few percent of the time, for no reason anyone could remember, because the person who set up the database had left the company years before.

Each of those problems has to be found, understood and fixed before a model can be trusted. Most of them need someone from your side who knows what the data is supposed to mean.

The three tables most projects need

Most of what we build for clients, especially in e-commerce, starts from three tables:

• A customer table with one row per customer.
• A product table with one row per product.
• An activity table that records each time a customer did something with a product: viewed it, bought it, returned it.

From those three, we can build recommendations, group customers by what they buy, find similar products when something is out of stock, and estimate how much each customer is worth over time and how likely they are to leave.

The trouble is that most companies don’t store data this way. A customer who moved shows up twice with two addresses. Multiply that by ten million customers and every analysis turns into a cleanup job. When we’ve helped clients restructure their data into these three tables, the time to get a model into production has dropped from months to days.

More data sources can make it worse

One client gave its data team more than 20 different data stores. On paper, the team had everything it could need. In practice, everyday questions took queries hundreds of lines long, nobody agreed on the right way to pull basic things like recent orders, and every result turned into an argument about whether the data was right.

Simplifying that setup took months. When it was done, the team got about 75% more work done. It was a big win, and it would have been much cheaper to set it up that way from the start.

The same principle works across a whole company. For seven brands at a Fortune 250 consumer goods company, we built one shared definition for every metric, so the same metric means the same thing in every brand and the portfolio report that took an analyst about a week now runs in minutes.

Please don’t use a spreadsheet as a database

In October 2020, the Associated Press reported that nearly 16,000 coronavirus cases had gone unreported in Britain, a problem widely thought to be connected to file size limits on the Excel spreadsheets used to track them. I’ve had a long love-hate relationship with Excel, going back to my years at Abbott Labs. It’s a fine tool at the end of a pipeline, for charting clean data. As a place to store and clean data, it fails: anyone can edit it, nobody can see what changed, and the cleanup has to be redone by hand every time.

If your important data lives in spreadsheets, moving it to a proper database is usually the first job. Cloud databases like Google BigQuery and Amazon Redshift aren’t much harder to learn.

How we start on a new dataset

When we start with a client’s data, we do two things over and over.

First, we inspect it. We look at how each field is distributed, find the outliers and the strange values, and write down what we learn so nobody has to repeat it.

Then we explore it. We look for patterns and relationships between fields, test our assumptions, and work out which approach is most likely to improve the metric the client cares about.

Depending on how well a company looks after its data, those two steps take anywhere from two weeks to a little over a month. That work is a large part of our fixed-fee Discovery, which measures where your metric stands today and tells you plainly whether your data can support the result. You can read how it fits in how we work with clients.

Start with the data you ask for

Data you collect on purpose is often the cleanest you’ll have. When a new customer signs up, a few good questions, like how much they usually spend or what they’re shopping for, fix the problem of knowing nothing about them. I’ve seen cases where customers who answered more of those questions spent more over time, because they expected a better experience in return.

What to do before you hire anyone

• Ask each team that uses data what format would give them most of what they need, then build that.
• Get one clean customer table, one product table and one activity table, if your business works that way.
• Move important data out of spreadsheets.
• Find out whether the metric you want to improve can be calculated from your data today.

You don’t need to move everything into a new system first. We connect Bennett ONE to your data where it already lives, so nothing has to move.

That last question is one of the first I’d ask any firm about, and I explain why in how to choose an AI consulting firm. Bad data is also one of the five reasons AI projects fail to pay off.

Talk to us

If you’re not sure your data is ready, the first call with one of our senior engineers is free and takes 30 minutes. We’ll tell you plainly what we see.