Or, Data Normalization Using Indices for Fun and Profit
One time a big outdoor sports retailer asked me to analyze their online and catalog sales data in one region of the country, and then use that analysis to try to estimate sales in future brick-and-mortar locations in the same region.
So I did the analysis. Based on my results, I confidently figured they’d probably sell more stuff in Atlanta than they would in Siler City, North Carolina. Wow, what a blinding glimpse of the obvious. The right thing to do would be to compare data between the two places on more of an even footing. There are many ways to do that, but one of my favorites is to use indices. Indexing is meaningful, easy to understand, easy to calculate, and fun. So naturally, I charge a fortune for it (just kidding).
To demonstrate, I took two of my favorite things, 1) anything to do with Ohio and 2) cheeseburgers. Then I calculated indices on based on some real retail sales data from last year.
Fact 1: People in Ohio Love Cheeseburgers. Or Big Macs, or Frosties, or Whoppers. Ohioans consumed 68% more of these types of fast foods, on average, than US consumers in general.
Fact 2: People in Warren have a higher Munch-a-Burger Index than do people in Youngstown. Folks in both cities spend more than the US average on this food, but less than others in Ohio (note the Bonus Facts below).
Fact 3: 26% of people nationally spent some amount > $0 at one of these restaurants in the past 6 months. But that’s true of 34% of Ohioans! It’s 30% in Youngstown, and 29% in Warren. Now there’s an interesting little data point. About the same percentage of the population frequent one of those restaurants between Youngstown and Warren, yet those hungry Warrenites spend oh so much more: $102.28 in the past 6 months versus $74.31 among Youngstowners.
Bonus Fact: Among largish Ohio cities, here are the indices of the Top 5 towns at these restaurants relative to others in the state:
5. West Chester 149
4. Hamilton 163
3. Loveland 187
2. Hilliard 198
1. Grove City 204 (!!!)
So people in Grove City spend twice as much as other Ohioans at these restaurants.
Bonus Fact #2: Here are the cheapest—er, lowest spending—cities in the state on this food:
5. Mentor 79
4. Massillon 76
3. Youngstown 73
2. Canton 64
1. Mansfield 49
People in Mansfield spend about half as much as other Ohioans.
What it is
An Exploratory Data Analysis, or EDA, is an exhaustive look at existing data from current and historical surveys conducted by a company.
In addition, the appropriate variables from your company’s customer database—such as information about rate plans, usage, account management, and others—are typically included in the analysis.
The intent of the EDA is to determine whether a predictive model is a viable analytical tool for a particular business problem, and if so, which type of modeling is most appropriate.
The deliverable is a low-risk, low-cost comprehensive report of findings of the univariate data and recommendations about how the company should use additional modeling.
At the very least, the EDA may reveal aspects of your company’s performance that others may not have seen.
Why do it
An EDA is a thorough examination meant to uncover the underlying structure of a data set and is important for a company because it exposes trends, patterns, and relationships that are not readily apparent.
You can’t draw reliable conclusions from a massive quantity of data by just gleaning over it—instead, you have to look at it carefully and methodically through an analytical lens.
Getting a “feel” for this critical information can help you detect mistakes, debunk assumptions, and understand the relationships between different key variables. Such insights may eventually lead to the selection of an appropriate predictive model.
What else you can do
If additional predictive modeling is deemed appropriate, a number of approaches may then be utilized.
Approach 1: A logistic model (LOGIT)
You may elect to segregate a company’s business customers into 2 separate and distinct classes: those who place a high value on or express high satisfaction with company services, and those who don’t. This type of analysis is sometimes referred to as a response model.
LOGIT could offer some insight into the factors that drive this customer rating, especially when some of those factors are opinion oriented (from existing surveys, a survey designed expressly for this purpose, or both). This model would also utilize appropriate customer data from the company’s various strategic surveys, demographic variables, etc. One of the outcomes of LOGIT modeling is a probability, or “score,” that can be appended to each person in the larger database from whence the analysis came.
Approach 2: Recursive partitioning
Recursive partitioning is a technique that uses the same database and survey information but in a different way.
This modeling approach does a good job of taking categorical variables into account as well as ordinal and continuous value variables. Categorical variables tend to be characteristics, such as type of rate plan, type of business, or location, for example. Continuous variables are numbers, like number of employees or annual revenue. Ratings scales from surveys fall into this latter type of data.
Recursive partitioning provides a “tree” output, and customers branch toward one classification or another based on how they respond to questions or how their behaviors and characteristics are measured.
No matter which course of action your company decides to take, the first step is always begins with an EDA. It’s an important component of the marketing research process that allows data to be organized, reviewed, and interpreted for the benefit of your business.