Category Archives: Propeller Head Content
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.
Growing up in Northeast Ohio, I do not recall ever seeing, let alone actually kicking, a soccer ball. In those times and in that place the term “football” meant something entirely different. It meant an oblong, leather-clad, brown inflated ball. It meant glorious Friday nights at Mollenkopf Stadium. It meant watching the Ohio State Buckeyes stomp on the University of the Sisters of the Poor every Saturday afternoon. And it meant exploring new and exciting ways to express one’s displeasure and disgust at the Cleveland Browns every Sunday. So naturally I wondered how the 2014 FIFA World Cup was playing in this nether world of chauvinistic American sport.
Through the magic of the Twitter API, R code, and a few extra moments of time on my hands, I set forth on the journey to find out.
The R scripts for this little project can be found here. https://github.com/dino-fire/worldcup
The Twitter REST API enables users to set a geographic parameter to limit searches to a specific geographical area. The search terms were limited to #WorldCup, #worldcup2014, or #Brazil. These terms were subsequently eliminated from the analyses, because we’re interested in what people are saying about those terms, not about counts of the terms themselves.
I started with the latitude and longitude of Columbus, Ohio, and specified a 200-mile radius. A word cloud, of course, yields larger, more prominent displays of words with higher frequencies. The basic word cloud of Ohioans’ tweets demonstrate some interest in the Spanish and Croatian futbol teams. Speaker of the House John Boehner garnered a few honorable mentions as well. What that has to do with the World Cup, I do not know.
Next I made a little side excursion that explored the tweets from the Youngstown/Warren area with those of residents of Youngstown’s sister city, Salerno, Italy. The outcomes were predictable but nuanced. The Youngstown and Warren folks tweeted about the generic USA. Could’ve been the soccer team, could’ve been native cuisine, like hot dogs, and could’ve been anything. Not so with the Italians, though; the national football club was front and center.
Ohioans are people of few words, at least as far as tweeting about the World Cup is concerned. The vast majority of Ohioans’ tweets comprised 8 or 10 unique words. The base R program provides a nice histogram.
Before we get into the deeper statistical analysis, I should point out that THE BIG BUZZ at the time was about England getting unceremoniously booted from the tournament in the opening round.
What’s the difference between England and a teabag? The teabag stays in the Cup longer.
A hierarchical cluster analysis of Ohioans’ tweets is intended to depict how words tend to cluster together in Euclidean space. It’s a fancy way of seeing how words correlate. And here are the results.
One group of tweets centered on England’s demise, and another seemed to be about who was showing up in Rio de Janeiro. Yet another group of words dealt with the Italy – Costa Rica match, while a fourth cluster seemed to inquire about who was supporting US soccer.
Disregarding the clustering of words, we can review the correlations themselves. I’m proud to say that Ohioans are expert analysts of English soccer.
Despite a seemingly infinite number of startups claiming to do better social media mining better than anyone else, sentiment analysis is an iffy proposition at best. For those who aren’t blessed with 50 unsolicited emails a day from social media mining companies, sentiment analysis refers to an evaluation of a tweet from a subjective, qualitative standpoint. The analysis tries to classify tweets or other textual content “scraped” from various websites into “good” or “bad,” “happy” or “sad,” or other such bipolar sentiments. But often that’s where the problem arises. For example, the following tweet would be classified as “good:”
Well, England, that was a good effort.
But unfortunately, so would this one:
Well, England, THAT was a good effort.
He or she whom invents a sentiment algorithm that can accurately interpret sarcasm wins the prize. Yeah, THAT will happen. Scrape THAT, you bums.
Nevertheless, I’ll hop upon the sentiment analysis e bandwagon and see how Ohioans feel about the World Cup so far. First of all, we see that there is no transformation of the sentiment-scored data required. The results reflect a very normal distribution, not skewing one way or another too badly.
We see that the sentiment scores are more positive than not, but as of this writing, the USA team is 1 – 0. Those scores are subject to shift later, to be sure.
In this case, the sentiment scoring algorithm freely admits that it is clueless about the context of many of the words it encountered. Still, it seemed predisposed to find and tag joyful comments.
The sentiment scoring algorithm output a nice comparison word cloud, which visually demonstrates the words and their respective classifications based on frequency. Yes, I always associate the term “snapshot” with “disgust.” Interestingly, “Redskins” got lumped into that classification as well.
So are Ohioan’s beliefs about the World Cup different from other, surrounding, and, some would believe, inferior types of people (based on their state of residence)? Well, let’s see.
Sentiment scores in Ohio, Michigan, West Virginia, Pennsylvania, and Indiana lean uniformly positive. But a careful look at the boxplots show that Ohioans and Indianans opinions tend to cluster in the middle: not too positive, and not too negative. That’s not the case among Michiganders, who tend to be extremely more positive or extremely more negative. Those Michigan folks represent very nicely the dangerous reality about averages: You can be standing with your feet in a bucket of ice water and your head in a roasting hot oven. But on average you feel just fine.
A comparison cloud shows just how different the tweets from these separate states really are. Michiganders seem obsessed with the Italy – Costa Rica match. Indianans seem strangely interested in the Forza Italia political movement. Pennsylvanians are engrossed in a game of “where’s Ronaldo?” Ohioans are losing interest, and starting to turn their attention toward Wimbledon. And West Virginians don’t seem to care much about the World Cup at all.
Back by popular demand…derived importance.
A great deal of research is designed to measure the relative impact of specific features of products or services on customers’ satisfaction with those products or services.
Sometimes, surveys are designed to measure importance of those features explicitly and in isolation—no further analysis is necessary than an understanding of which features are more important to customers than others.
In other cases, the importance metrics will be used to determine what, if anything, could or should be changed to improve the product. That’s where key drivers analysis comes in, but more about that later.
Measuring importance through traditional Likert scales, while certainly frequently done, is not the method FGI recommends to measure importance. There are 2 fundamental reasons for this.
First, importance scales often do not provide adequate discrimination and differentiation between product features, especially when viewed in aggregate.
Q: How important is price?
A: Oh, that’s very important.
Q: How important is product availability?
A: Oh, that’s very important.
Q: How important are helpful store employees?
A: Oh, that’s very important too.
Second, people use scales differently (and this problem is not limited to importance scales). Respondents tend to calibrate their responses to previous scores. For example, here’s Respondent #1, rating the 3 attributes in our survey.
Q: How important is price?
A: Let’s give it a 9.
Q: Now, how important is product availability?
A: Well, not as important as price, so let’s say 8.
Q: How important are helpful store employees?
A: Less important than price, but more important than availability. 8 for that one too.
But Respondent #2 may follow precisely the same response pattern—9 / 8 / 8—but start their ratings at 6 instead, yielding 6 / 5 / 5. Should we view these three features as more important for Respondent #1 than for Respondent #2? No. Do any of Respondent #2’s answers qualify for top-2 box summaries?
No. One’s person’s 9 rating may be another person’s 6 rating. The very nature of scales—that the values are relative, not absolute—can cause misinterpretation of the results.
There are occasions where stated importance is appropriate and useful. If this is the case, there are far better ways than Likert scales to measure it, but that’s a subject for another day.
Measuring derived importance
Key drivers analysis yields importance in a derived manner, by measuring the relative impact of product features on critical performance metrics like overall satisfaction, likelihood to purchase again, likelihood to recommend, or some combination of those. The structure of a key drivers questionnaire looks like this:
Q. This next question is about your satisfaction with XX in general. Please rate the store on how satisfied you are with them overall. 10 means you are “Completely Satisfied” and 0 means you were “Not At All Satisfied.”
This question is treated as the dependent variable for our analysis.
Q. Now, consider these specific statements. Using the same scale, how satisfied are you with XX on…
- Variety of products and services
- Professional appearance of staff
- Length of wait time
- Ease of finding things in store
- Length of transaction time
- Convenient parking
- Convenient store location
We can then do some analysis to determine to what extent each of these independent—aka predictor—variables influence overall satisfaction. This is done through something called Pearson’s R Correlations.
In correlations, we get a statistic called R^2 (R squared), which is a measure of the strength of the score of one item to another. In the case of Pearson R, 1.0 means a perfect, positive correlation and -1.0 reflects a perfect, negative correlation. An R^2 value of 0.0 means no correlation at all.
In a key drivers analysis, the higher the correlation between each of the specific attributes and overall satisfaction, the more influence that attribute has on satisfaction, thus the more important it is. Notice that we never have to ask the question “how important is…” since the derived importance tells us everything we need to know. But that’s only half of the equation.
As a result of the question structure, we get explicit satisfaction metrics on each of the individual attributes as well. This data tells us how well we perform on each of the attributes. The resulting output looks something like this:
In our example, “helpful staff,” “coupon policy,” and “items in stock” are the most important attributes; they have the highest correlations to overall satisfaction.
Now compare those attributes to “store location.” The correlation is still positive, but not nearly as powerful as the first two examples. Remember, derived importance measures importance of individual attributes in relative, not absolute, terms.
The second part of our analysis shows that our store’s employees are helpful. In fact, it’s the highest performing attribute of all (while importance is viewed on the X, or horizontal, axis, performance is viewed on the Y, or vertical, axis).
This means that our store does well on this important attribute, and is considered a core strength. This is not the case with the other important attribute, like having items in stock, however. Our store gets the lowest performance rating on that very important feature.
From our survey results, management can quickly see that resources should be directed toward reducing wait times (more cashiers), improving their coupon policy if they can, and especially keeping popular items in stock.
We’ve precisely identified the few items that need to be prioritized, as improvement in satisfaction with these things will have a direct and measurable impact on overall satisfaction.
Being a certified propellerhead comes with certain privileges, and few are more important than the unalienable right to have other propellerhead geeks as role models. One of mine is a Yale professor by the name of Edward Tufte.
Sparklines are tiny little graphs embedded in textual analysis. Dr. Tufte, the widely recognized guru of graphics for data-centric reporting, invented these little beasts. He refers to them as “data-intense, word-sized graphics,” also known as “data words.” They are useful in describing how linear or time-series data changes over time, or how one group stands out from the rest. Sparklines are easily added to Excel spreadsheets through the “insert” ribbon, although they don’t copy and paste very well into PowerPoint or Word documents. The high-resolution ones you see in this article were generated by the handy sparkTable R package (Kowarik, Meindl, and Templ, 2012).
As an interesting and pathetic side note, Microsoft has applied for a patent for their implementation of sparkline functionality in their software, which is particularly galling to the spirit of freely-available open source applications if not downright plagiarism couched in tech-giant legalese. A Google search on “sparklines” turns up an onslaught of search-engine-optimized content about how to use Excel to make sparklines (a good thing, too, since making sparklines in Excel requires all of the technical expertise of a contemporary third grader). Go ahead and patent your weak excuse for sparklines, Microsoft. I guess patents are cheap to come by in the software world. Where do I sign up?
Goodness good ¬ ness [goo d-nis] the state or quality of being good, excellence of quality. (dictionary.com).
A good predictive model is only as good as its “goodness.” And, fortunately, there is a well-established process for measuring the goodness of a logistic model in a way that a non-statistician—read: the senior manager end-users of your model—can understand.
There is, of course, a precise statistical meaning behind words we propeller heads throw around such as “goodness of fit.” In the case of logistic models, we are looking for a favorable log likelihood result relative to that of the null, or empty, model. This description isn’t likely to mean much to an end-user audience (it barely means anything to many propeller heads).
Saying that your model will predict the correct binary outcome of something 81% of the time, however, makes a lot more intuitive sense to everyone involved.
It starts with a standard hold-out sample process, where the model is trained and modified using a random part—say, half—of the available data (the learning set) until a satisfactory result is apparent. The model is then tested on the second half of the data (the testing set) to see how “predictive” it is. For a logistic model, a “confusion matrix” is a very clean way to see how well your model does.
Using existing historical data, say we’re trying to predict whether someone will renew their association membership when their current contract is up. We run the model on the testing set, using the parameters determined in the initial model-building step we did on the learning set.
logit.estimate <- predict.glm(fit, newdata = testing, type = ‘response’)
Let’s set the playing field by determining what the existing “actual” proportions of the possible outcomes are in the testing data.
# Actual churn values from testing set testprops <- table(testing$status) # create an object of drop/renew (actuals) prop.table(testprops) # express drop/renew in proportional terms
So historically, we see that 59% of people don’t renew when their membership period is up. Houston, we have a problem! Good thing this is a hypothetical example.
The elegance of logistic regression—like other modeling methods—is that it provides a neat little probability statistic for each person in the database. We can pick some arbitrary value for this predicted probability—say anything greater than 50% —to indicate that someone will renew their membership when the time comes.
testing$pred.val <- 0 # Initialize the variable testing$pred.val[logit.estimate > 0.5] <- 1 # Anyone with a pred. prob.> 50% will renew
With those results in hand, we need to know 2 things. First, how well does the model do in pure proportional terms? In other words, it is close to the same drop/renew proportions from the actual data? This is knowable from a simple table.
testpreds <- table(testing$pred.val) # create an object of drop/renew (predicted) prop.table(testpreds) # express drop/renew predictions in proportional terms
Recall that our original proportions from the “actuals” were 59%/41%…so far so good.
Second, and most importantly, how well does the model predict the same people to drop among those who actually dropped, and how does it do predicting the same people to renew among those who actually renewed? That’s where the confusion matrix comes in.
In a perfect (but suspicious) model, cells A and D would be 100%. In other words, everyone who dropped will have been predicted to drop, and everyone who renewed will have been predicted to renew. In our example, the confusion matrix looks like this:
# Confusion matrix confusion.matrix <- table(testing$q402.t2b, testing$pred.val) # create the confusion matrix confusion.matrix # view it Drop Renew Drop 310 55 Renew 62 189
Assign each of the four confusion matrix cells a letter indicator, and run the statistics to see how well the model predicts renewals and drops.
a <- confusion.matrix[2,2] # actual renew, predicted renew b <- confusion.matrix[2,1] # actual renew, predicted drop c <- confusion.matrix[1,2] # actual drop, predicted renew d <- confusion.matrix[1,1] # actual drop, predicted drop n = a + b + c + d # total sample size CCC <- (a + d)/n # cases correctly classified CCC  0.81 CMC <- (b + c)/n # cases misclassified CMC  0.19 CCP <- a/(a + b) # correct classification of positives (actual à predicted renew) CCP  0.75 CCN <- d/(c + d) # correct classification of negatives (actual à predicted drop) CCN  0.85OddsRatio <- (a * d) / (b * c) # the odds that the model will classify a case correctly OddsRatio  17
At 81%, our model does a pretty fair job of correctly determining the proportion of members who will drop and renew. It is capable of predicting the individuals who will renew their membership 75% of the time. More importantly, the model will predict who will not renew 85% of the time…presumably giving us time to entice these specific individuals with a special offer, or send them a communication designed to address the particular reasons that contribute to their likelihood to drop their membership (we learn this in the model itself). If we send this communication or special offer to everyone the model predicts will drop their membership, we will only have wasted (aka “spilled”) this treatment on 15% of them.
Now that’s information our managers can use.
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.