Missing Data Can Be Meaningful

No matter how big the Big Data gets, we will never know everything about everything. Well, according to the super-duper computer called “Deep Thought” in the movie “The Hitchhiker’s Guide to the Galaxy” (don’t bother to watch it if you don’t care for the British sense of humour), the answer to “The Ultimate Question of Life, the Universe, and Everything” is “42.” Coincidentally, that is also my favorite number to bet on (I have my reasons), but I highly doubt that even that huge fictitious computer with unlimited access to “everything” provided that numeric answer with conviction after 7½ million years of computing and checking. At best, that “42” is an estimated figure of a sort, based on some fancy algorithm. And in the movie, even Deep Thought pointed out that “the answer is meaningless, because the beings who instructed it never actually knew what the Question was.” Ha! Isn’t that what I have been saying all along? For any type of analytics to be meaningful, one must properly define the question first. And what to do with the answer that comes out of an algorithm is entirely up to us humans, or in the business world, the decision-makers. (Who are probably human.)

No matter how big the Big Data gets, we will never know everything about everything. Well, according to the super-duper computer called “Deep Thought” in the movie “The Hitchhiker’s Guide to the Galaxy” (don’t bother to watch it if you don’t care for the British sense of humour), the answer to “The Ultimate Question of Life, the Universe, and Everything” is “42.” Coincidentally, that is also my favorite number to bet on (I have my reasons), but I highly doubt that even that huge fictitious computer with unlimited access to “everything” provided that numeric answer with conviction after 7½ million years of computing and checking. At best, that “42” is an estimated figure of a sort, based on some fancy algorithm. And in the movie, even Deep Thought pointed out that “the answer is meaningless, because the beings who instructed it never actually knew what the Question was.” Ha! Isn’t that what I have been saying all along? For any type of analytics to be meaningful, one must properly define the question first. And what to do with the answer that comes out of an algorithm is entirely up to us humans, or in the business world, the decision-makers. (Who are probably human.)

Analytics is about making the best of what we know. Good analysts do not wait for a perfect dataset (it will never come by, anyway). And businesspeople have no patience to wait for anything. Big Data is big because we digitize everything, and everything that is digitized is stored somewhere in forms of data. For example, even if we collect mobile device usage data from just pockets of the population with certain brands of mobile services in a particular area, the sheer size of the resultant dataset becomes really big, really fast. And most unstructured databases are designed to collect and store what is known. If you flip that around to see if you know every little behavior through mobile devices for “everyone,” you will be shocked to see how small the size of the population associated with meaningful data really is. Let’s imagine that we can describe human beings with 1,000 variables coming from all sorts of sources, out of 200 million people. How many would have even 10 percent of the 1,000 variables filled with some useful information? Not many, and definitely not 100 percent. Well, we have more data than ever in the history of mankind, but still not for every case for everyone.

In my previous columns, I pointed out that decision-making is about ranking different options, and to rank anything properly. We must employee predictive analytics (refer to “It’s All About Ranking“). And for ranking based on the scores resulting from predictive models to be effective, the datasets must be summarized to the level that is to be ranked (e.g., individuals, households, companies, emails, etc.). That is why transaction or event-level datasets must be transformed to “buyer-centric” portraits before any modeling activity begins. Again, it is not about the transaction or the products, but it is about the buyers, if you are doing all this to do business with people.

Trouble with buyer- or individual-centric databases is that such transformation of data structure creates lots of holes. Even if you have meticulously collected every transaction record that matters (and that will be the day), if someone did not buy a certain item, any variable that is created based on the purchase record of that particular item will have nothing to report for that person. Likewise, if you have a whole series of variables to differentiate online and offline channel behaviors, what would the online portion contain if the consumer in question never bought anything through the Web? Absolutely nothing. But in the business of predictive analytics, what did not happen is as important as what happened. Even a simple concept of “response” is only meaningful when compared to “non-response,” and the difference between the two groups becomes the basis for the “response” model algorithm.

Capturing the Meanings Behind Missing Data
Missing data are all around us. And there are many reasons why they are missing, too. It could be that there is nothing to report, as in aforementioned examples. Or, there could be errors in data collection—and there are lots of those, too. Maybe you don’t have access to certain pockets of data due to corporate, legal, confidentiality or privacy reasons. Or, maybe records did not match properly when you tried to merge disparate datasets or append external data. These things happen all the time. And, in fact, I have never seen any dataset without a missing value since I left school (and that was a long time ago). In school, the professors just made up fictitious datasets to emphasize certain phenomena as examples. In real life, databases have more holes than Swiss cheese. In marketing databases? Forget about it. We all make do with what we know, even in this day and age.

Then, let’s ask a philosophical question here:

  • If missing data are inevitable, what do we do about it?
  • How would we record them in databases?
  • Should we just leave them alone?
  • Or should we try to fill in the gaps?
  • If so, how?

The answer to all this is definitely not 42, but I’ll tell you this: Even missing data have meanings, and not all missing data are created equal, either.

Furthermore, missing data often contain interesting stories behind them. For example, certain demographic variables may be missing only for extremely wealthy people and very poor people, as their residency data are generally not exposed (for different reasons, of course). And that, in itself, is a story. Likewise, some data may be missing in certain geographic areas or for certain age groups. Collection of certain types of data may be illegal in some states. “Not” having any data on online shopping behavior or mobile activity may mean something interesting for your business, if we dig deeper into it without falling into the trap of predicting legal or corporate boundaries, instead of predicting consumer behaviors.

In terms of how to deal with missing data, let’s start with numeric data, such as dollars, days, counters, etc. Some numeric data simply may not be there, if there is no associated transaction to report. Now, if they are about “total dollar spending” and “number of transactions” in a certain category, for example, they can be initiated as zero and remain as zero in cases like this. The counter simply did not start clicking, and it can be reported as zero if nothing happened.

Some numbers are incalculable, though. If you are calculating “Average Amount per Online Transaction,” and if there is no online transaction for a particular customer, that is a situation for mathematical singularity—as we can’t divide anything by zero. In such cases, the average amount should be recorded as: “.”, blank, or any value that represents a pure missing value. But it should never be recorded as zero. And that is the key in dealing with missing numeric information; that zero should be reserved for real zeros, and nothing else.

I have seen too many cases where missing numeric values are filled with zeros, and I must say that such a practice is definitely frowned-upon. If you have to pick just one takeaway from this article, that’s it. Like I emphasized, not all missing values are the same, and zero is not the way you record them. Zeros should never represent lack of information.

Take the example of a popular demographic variable, “Number of Children in the Household.” This is a very predictable variable—not just for purchase behavior of children’s products, but for many other things. Now, it is a simple number, but it should never be treated as a simple variable—as, in this case, lack of information is not the evidence of non-existence. Let’s say that you are purchasing this data from a third-party data compiler (or a data broker). If you don’t see a positive number in that field, it could be because:

  1. The household in question really does not have a child;
  2. Even the data-collector doesn’t have the information; or
  3. The data collector has the information, but the household record did not match to the vendor’s record, for some reason.

If that field contains a number like 1, 2 or 3, that’s easy, as they will represent the number of children in that household. But the zero should be reserved for cases where the data collector has a positive confirmation that the household in question indeed does not have any children. If it is unknown, it should be marked as blank, “.” (Many statistical softwares, such as SAS, record missing values this way.) Or use “U” (though an alpha character should not be in a numeric field).

If it is a case of non-match to the external data source, then there should be a separate indicator for it. The fact that the record did not match to a professional data compiler’s list may mean something. And I’ve seen cases where such non-matching indicators are made to model algorithms along with other valid data, as in the case where missing indicators of income display the same directional tendency as high-income households.

Now, if the data compiler in question boldly inputs zeros for the cases of unknowns? Take a deep breath, fire the vendor, and don’t deal with the company again, as it is a sign that its representatives do not know what they are doing in the data business. I have done so in the past, and you can do it, too. (More on how to shop for external data in future articles.)

For non-numeric categorical data, similar rules apply. Some values could be truly “blank,” and those should be treated separately from “Unknown,” or “Not Available.” As a practice, let’s list all kinds of possible missing values in codes, texts or other character fields:

  • ” “—blank or “null”
  • “N/A,” “Not Available,” or “Not Applicable”
  • “Unknown”
  • “Other”—If it is originating from some type of multiple choice survey or pull-down menu
  • “Not Answered” or “Not Provided”—This indicates that the subjects were asked, but they refused to answer. Very different from “Unknown.”
  • “0”—In this case, the answer can be expressed in numbers. Again, only for known zeros.
  • “Non-match”—Not matched to other internal or external data sources
  • Etc.

It is entirely possible that all these values may be highly correlated to each other and move along the same predictive direction. However, there are many cases where they do not. And if they are combined into just one value, such as zero or blank, we will never be able to detect such nuances. In fact, I’ve seen many cases where one or more of these missing indicators move together with other “known” values in models. Again, missing data have meanings, too.

Filling in the Gaps
Nonetheless, missing data do not have to left as missing, blank or unknown all the time. With statistical modeling techniques, we can fill in the gaps with projected values. You didn’t think that all those data compilers really knew the income level of every household in the country, did you? It is not a big secret that much of those figures are modeled with other available data.

Such inferred statistics are everywhere. Popular variables, such as householder age, home owner/renter indicator, housing value, household income or—in the case of business data—the number of employees and sales volume contain modeled values. And there is nothing wrong with that, in the world where no one really knows everything about everything. If you understand the limitations of modeling techniques, it is quite alright to employ modeled values—which are much better alternatives to highly educated guesses—in decision-making processes. We just need to be a little careful, as models often fail to predict extreme values, such as household incomes over $500,000/year, or specific figures, such as incomes of $87,500. But “ranges” of household income, for example, can be predicted at a high confidence level, though it technically requires many separate algorithms and carefully constructed input variables in various phases. But such technicality is an issue that professional number crunchers should deal with, like in any other predictive businesses. Decision-makers should just be aware of the reality of real and inferred data.

Such imputation practices can be applied to any data source, not just compiled databases by professional data brokers. Statisticians often impute values when they encounter missing values, and there are many different methods of imputation. I haven’t met two statisticians who completely agree with each other when it comes to imputation methodologies, though. That is why it is important for an organization to have a unified rule for each variable regarding its imputation method (or lack thereof). When multiple analysts employ different methods, it often becomes the very source of inconsistent or erroneous results at the application stage. It is always more prudent to have the calculation done upfront, and store the inferred values in a consistent manner in the main database.

In terms of how that is done, there could be a long debate among the mathematical geeks. Will it be a simple average of non-missing values? If such a method is to be employed, what is the minimum required fill-rate of the variable in question? Surely, you do not want to project 95 percent of the population with 5 percent known values? Or will the missing values be replaced with modeled values, as in previous examples? If so, what would be the source of target data? What about potential biases that may exist because of data collection practices and their limitations? What should be the target definition? In what kind of ranges? Or should the target definition remain as a continuous figure? How would you differentiate modeled and real values in the database? Would you embed indicators for inferred values? Or would you forego such flags in the name of speed and convenience for users?

The important matter is not the rules or methodologies, but the consistency of them throughout the organization and the databases. That way, all users and analysts will have the same starting point, no matter what the analytical purposes are. There could be a long debate in terms of what methodology should be employed and deployed. But once the dust settles, all data fields should be treated by pre-determined rules during the database update processes, avoiding costly errors in the downstream. All too often, inconsistent imputation methods lead to inconsistent results.

If, by some chance, individual statisticians end up with freedom to come up with their own ways to fill in the blanks, then the model-scoring code in question must include missing value imputation algorithms without an exception, granted that such practice will elongate the model application processes and significantly increase chances for errors. It is also important that non-statistical users should be educated about the basics of missing data and associated imputation methods, so that everyone who has access to the database shares a common understanding of what they are dealing with. That list includes external data providers and partners, and it is strongly recommended that data dictionaries must include employed imputation rules wherever applicable.

Keep an Eye on the Missing Rate
Often, we get to find out that the missing rate of certain variables is going out of control because models become ineffective and campaigns start to yield disappointing results. Conversely, it can be stated that fluctuations in missing data ratios greatly affect the predictive power of models or any related statistical works. It goes without saying that a consistent influx of fresh data matters more than the construction and the quality of models and algorithms. It is a classic case of a garbage-in-garbage-out scenario, and that is why good data governance practices must include a time-series comparison of the missing rate of every critical variable in the database. If, all of a sudden, an important predictor’s fill-rate drops below a certain point, no analyst in this world can sustain the predictive power of the model algorithm, unless it is rebuilt with a whole new set of variables. The shelf life of models is definitely finite, but nothing deteriorates effectiveness of models faster than inconsistent data. And a fluctuating missing rate is a good indicator of such an inconsistency.

Likewise, if the model score distribution starts to deviate from the original model curve from the development and validation samples, it is prudent to check the missing rate of every variable used in the model. Any sudden changes in model score distribution are a good indicator that something undesirable is going on in the database (more on model quality control in future columns).

These few guidelines regarding the treatment of missing data will add more flavors to statistical models and analytics in general. In turn, proper handling of missing data will prolong the predictive power of models, as well. Missing data have hidden meanings, but they are revealed only when they are treated properly. And we need to do that until the day we get to know everything about everything. Unless you are just happy with that answer of “42.”

Cheat Sheet: Is Your Database Marketing Ready?

Many data-related projects end up as big disappointments. And, in many cases, it is because they did not have any design philosophy behind them. Because many folks are more familiar with buildings and cars than geeky databases, allow me to use them as examples here.

Many data-related projects end up as big disappointments. And, in many cases, it is because they did not have any design philosophy behind them. Because many folks are more familiar with buildings and cars than geeky databases, allow me to use them as examples here.

Imagine someone started constructing a building without a clear purpose. What is it going to be? An office building or a residence? If residential, for how many people? For a family, or for 200 college kids? Are they going to just eat and sleep in there, or are they going to engage in other activities in it? What is the budget for development and ongoing maintenance?

If someone starts building a house without answering these basic questions, well, it is safe to say that the guy who commissioned such a project is not in the right state of mind. Then again, he may be a filthy rich rock star with some crazy ideas. But let us just say that is an exceptional case. Nonetheless, surprisingly, a great many database projects start out exactly this way.

Just like a house is not just a sum of bricks, mortar and metal, a database is not just a sum of data, and there has to be design philosophy behind it. And yet, many companies think that putting all available data in one place is just good enough. Call it a movie without a director or a building without an architect; you know and I know that such a project cannot end well.

Even when a professional database designer gets involved, too often the project goes out of control—as the business requirement document ends up being a summary of
everyone’s wish lists, without any prioritization or filtering. It is a case of a movie without a director. The goal becomes something like “a database that stores all conceivable marketing, accounting and payment activities, handling both prospecting and customer relationship management through all conceivable channels, including face-to-face sales and lead management for big accounts. And it should include both domestic and international activities, and the update has to be done in real time.”

Really. Someone in that organization must have attended a database marketing conference recently to get all that listed. It might be simpler and cheaper building a 2-ton truck that flies. But before we commission something like this from the get-go, shall we discuss why the truck has to fly, too? For one, if you want real-time updates, do you have a business case for it? (As in, someone in the field must make real-time decisions with real-time data.) Or do you just fancy a large object, moving really fast?

Companies that primarily sell database tools often do not help the matter, either. Some promise that the tool sets will categorize all kinds of input data, based on some auto-generated meta-tables. (Really?) The tool will clean the data automatically. (Is it a self-cleaning oven?) The tool will establish key links (by what?), build models on its own (with what target data?), deploy campaigns (every Monday?), and conduct result analysis (with responses from all channels?).

All these capabilities sound really wonderful, but does that system set long- and short-term marketing goals for you, too? Does it understand the subtle nuances in human behaviors and intentions?

Sorry for being a skeptic here. But in such cases, I think someone watched “Star Trek” too much. I have never seen a company that does not regret spending seven figures on a tool set that was supposed to do everything. Do you wonder why? It is not because such activities cannot be automated, but because:

  1. Machines do not think for us (not quite yet); and
  2. Such a system is often very expensive, as it needs to cover all contingencies (the opposite of “goal-oriented” cheaper options).

So it becomes nearly impossible to justify the cost with incremental improvements in marketing efficiency. Even if the response rates double, all related marketing costs go down by a quarter, and revenue jumps up by 200 percent, there are not many companies that can easily justify that kind of spending.

Worse yet, imagine that you just paid 10 times more for some factory-made suit than you would have paid for a custom-made Italian suit. Since when is an automated, cookie-cutter answer more desirable than custom-tailored ones? Ever since computing and storage costs started to go down significantly, and more so in this age of Big Data that has an “everything, all the time” mentality.

But let me ask you again: Do you really have a marketing database?

Let us just say that I am a car designer. A potential customer who has been doing a lot of research on the technology front presents me with a spec for a vehicle that is as big as a tractor-trailer and as quick as a passenger car. I guess that someone really needs to move lots of stuff, really fast. Now, let us assume that it will cost about $8 million or more to build a car like that, and that estimate is without the rocket booster (ah, my heart breaks). If my business model is to take a percentage out of that budget, I would say, “Yeah sure, we can build a car like that for you. When can we start?”

But let us stop for a moment and ask why the client would “need” (not “want”) a car like that in the first place. After some user interviews and prioritization, we may collectively conclude that a fleet of full-size vans can satisfy 98 percent of the business needs, saving about $7 million. If that client absolutely and positively has to get to that extra 2 percent to satisfy every possible contingency in his business and spend that money, well, that is his prerogative, is it not? But I have to ask the business questions first before initiating that inevitable long and winding journey without a roadmap.

Knowing exactly what the database is supposed to be doing must be the starting point. Not “let’s just gather everything in one place and hope to God that some user will figure something out eventually.” Also, let’s not forget that constantly adding new goals in any phase of the project will inevitably complicate the matter and increase the cost.

Conversely, repurposing a database designed for some other goal will cause lots of troubles down the line. Yeah, sure. Is it not possible to move 100 people from A to B with a 2-seater sports car, if you are willing to make lots of quick trips and get some speeding tickets along the way? Yes, but that would not be my first recommendation. Instead, here are some real possibilities.

Databases support many different types of activities. So let us name a few:

  • Order fulfillment
  • Inventory management and accounting
  • Contact management for sales
  • Dashboard and report generation
  • Queries and selections
  • Campaign management
  • Response analysis
  • Trend analysis
  • Predictive modeling and scoring
  • Etc., etc.

The list goes on, and some of the databases may be doing fine jobs in many areas already. But can we safely call them “marketing” databases? Or are marketers simply tapping into the central data depository somehow, just making do with lots of blood, sweat and tears?

As an exercise, let me ask a few questions to see if your organization has a functioning marketing database for CRM purposes:

  • What is the average order size per year for customers with tenure of more than one year? —You may have all the transaction data, but maybe not on an individual level in order to know the average.
  • What is the number of active and dormant customers based on the last transaction date? —You will be surprised to find out that many companies do not know exactly how many customers they really have. Beep! 1 million-“ish” is not a good answer.
  • What is the average number of days between activities for each channel for each customer? —With basic transaction data summarized “properly,” this is not a difficult question to answer. But it’s very difficult if there are divisional “channel-centric” databases scattered all over.
  • What is the average number of touches through all channels that you employ before your customer reaches the projected value potential? —This is a hard one. Without all the transaction and contact history by all channels in a “closed-loop” structure, one cannot even begin to formulate an answer for this one. And the “value potential” is a result of statistical modeling, is it not?
  • What are typical gateway products, and how are they correlated to other product purchases? —This may sound like a product question, but without knowing each customer’s purchase history lined up properly with fully standardized product categories, it may take a while to figure this one out.
  • Are basic RFM data—such as dollars, transactions, dates and intervals—routinely being used in predictive models? —The answer is a firm “no,” if the statisticians are spending the majority of their time fixing the data; and “not even close,” if you are still just using RFM data for rudimentary filtering.

Now, if your answer is “Well, with some data summarization and inner/outer joins here and there—though we don’t have all transaction records from last year, and if we can get all the campaign histories from all seven vendors who managed our marketing campaigns, except for emails—maybe?”, then I am sorry to inform you that you do not have a marketing database. Even if you can eventually get to the answer if some programmer takes two weeks to draw a 7-page flow chart.

Often, I get extra comments like “But we have a relational database!” Or, “We stored every transaction for the past 10 years in Hadoop and we can retrieve any one of them in less than a second!” To these comments, I would say “Congratulations, your car has four wheels, right?”

To answer the important marketing questions, the database should be organized in a “buyer-centric” format. Going back to the database philosophy question, the fundamental design of the database changes based on its main purpose, much like the way a sports sedan and an SUV that share the same wheel base and engine end up shaped differently.

Marketing is about people. And, at the center of the marketing database, there have to be people. Every data element in the base should be “describing” those people.

Unfortunately, most relational databases are transaction-, channel- or product-centric, describing events and transactions—but not the people. Unstructured databases that are tuned primarily for massive storage and rapid retrieval may just have pieces of data all over the place, necessitating serious rearrangement to answer some of the most basic business questions.

So, the question still stands. Is your database marketing ready? Because if it is, you would have taken no time to answer my questions listed above and say: “Yeah, I got this. Anything else?”

Now, imagine the difference between marketers who get to the answers with a few clicks vs. the ones who have no clue where to begin, even when sitting on mounds of data. The difference between the two is not the size of the investment, but the design philosophy.

I just hope that you did not buy a sports car when you needed a truck.