Introduction - Marketing Analytics: Data-Driven Techniques with Microsoft Excel (2014)

Marketing Analytics: Data-Driven Techniques with Microsoft Excel (2014)


In the last 20 years, the use of analytic techniques in marketing has greatly increased. In April 2013, Forbes magazine reported a 67-percent growth in marketing-related analytics hires during the previous year and an amazing 136-percent growth during the previous 3 years.

Given this growth of interest in marketing analytics and my love of Excel modeling, I decided in 2004 to create a 7-week MBA elective in marketing analytics (K509) at the Indiana University Kelley School of Business. Although there are several excellent advanced marketing analytics books. (I am partial to Database Marketing by Robert Blattberg, Byung-Do Kim, and Scott Neslin (Springer, 2008).) I could not find an Excel-based book that provided a how-to-do-it approach suitable for an MBA elective or an advanced undergraduate course. With no suitable book in hand, I wrote up course notes that I used in classes for 10 years. The course has been wildly successful with nearly 65 percent of all MBA's at the Kelley School taking the class. In May 2013, I was honored to receive the Eli Lilly MBA teaching award as the best teacher in the MBA program, primarily for teaching K509. In November 2011, Robert Elliott of Wiley Publishing approached me about turning my notes into a book, and this book is the result. In addition to being utilized in K509, portions of the book have been used to teach marketing analytics to senior managers at Deloitte consulting, Booz Allen Hamilton consulting, and 3M marketing analysts.

How This Book Is Organized

Since I started using Excel in classes in 1992, I have become a total convert to teaching by example. This book is no exception. Virtually every chapter's primary focus is to teach you the concepts through how-to examples. Each example has the following components:

· Step-by-step instructions

· A downloadable Excel file containing data and solutions

· Screenshots of various steps and sections of the Excel file for clarity

The downloadable Excel files provide complete solutions to the examples, but the instructions encourage you to follow along and work through them on your own. If you follow along using the provided Excel files, you can work in empty cells alongside the completed solution and compare your result with the provided solution to ensure your success.

The book has been organized around 11 topical areas.

Part I: Using Excel to Summarize Marketing Data

This part of the book introduces the marketing analyst to many Excel tools that can be used to analyze marketing problems: PivotTables (Chapter 1), charting (Chapter 2), and Excel statistical functions (Chapter 3), including the incredibly useful COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, andAVERAGEIFS functions.

Part II: Pricing

The determination of a profit maximizing pricing strategy is always difficult. In this section you learn how to quickly estimate demand curves and use the Excel Solver (Chapter 4) to determine profit maximizing prices. The Excel Solver is then used to optimize price bundling (Chapter 5), nonlinear pricing strategies (Chapter 6), and price-skimming strategies (Chapter 7). A brief introduction to revenue management, also known as yield management (Chapter 8), is also included.

Part III: Forecasting

Businesses need accurate forecasts of future sales. Sales forecasts drive decisions involving production schedules, inventory management, manpower planning, and many other parts of the business. In this section you first learn about two of the most used forecasting tools: simple linear (Chapter 9) and simple multiple regression (Chapters 10 and 11). Then you learn how to estimate the trend and seasonal aspects of sales (Chapter 12) and generate forecasts using two common extrapolation forecasting methods: the Ratio to Moving Average method (Chapter 13), and Winter's Method for exponential smoothing (Chapter 14) with trend and seasonality. Then you learn about neural networks (Chapter 15), a form of artificial intelligence whose role in marketing forecasting is rapidly growing.

Part IV: What Do Customers Want?

Every brand manager wants to know how various product attributes drive the sales of a product. For example, what is most important in a consumer's choice of car: price, brand, engine horsepower, styling, or fuel economy? In this section you learn how conjoint analysis (Chapter 16) and discrete choice (Chapter 18) can be used to rank the importance of product attributes and also rank levels of product attributes. For example, what type of styling on an SUV is most preferred? You also learn about the widely used tool of logistic regression (Chapter 17), which is used to estimate probabilities involving situations in which two, or binary, outcomes must be forecasted. For example, how a person's demographic information can be used to predict the chance that he will subscribe to a magazine.

Part V: Customer Value

Companies cannot make intelligent decisions on how to spend money acquiring customers unless they understand the value of their customers. After all, spending $400 to acquire a customer who will generate $300 in long-term profits is a sure recipe for going out of business. In this section you learn how to measure customer value (Chapter 19), value companies based on the customer value concept (Chapter 20), incorporate uncertainty in customer value models (Chapter 21), and use your understanding of customer value to optimally allocate resources (Chapter 22) between acquisition and retention of customers.

Part VI: Market Segmentation

No matter what product you sell, your market consists of different market segments. For example, in Chapter 23 you will use cluster analysis to show that that every U.S. city can be classified into one of four demographic segments. In Chapter 25 you will learn how classification trees can be used to segment a market. You are also introduced to the exciting concepts behind collaborative filtering (Chapter 24), which is the basis for and Netflix recommendations.

Part VII: Forecasting New Product Sales

With little or no history about sales of a product, it is difficult to predict future product sales. Given a few data points, S curves (Chapter 26) can be used to predict future product sales. The famous Bass diffusion model (Chapter 27) explains how sales of products evolve over time and can be used to predict product sales even before a product comes to the market. The little-known Copernican Principle (Chapter 28) enables you to predict the remaining time for which a product will be sold.

Part VIII: Retailing

Analytic techniques can help retailers deal with many important issues. The concepts of market basket analysis and lift (Chapter 29) help retailers derive a store layout that maximizes sales from complementary products. Recency, frequency, and monetary value analysis (Chapter 30) helps direct mailers maximize profit from their mailings. The widely known SCAN*PRO (Chapter 31) model helps retailers determine how factors such as seasonality, price, and promotions influence product sales. In Chapter 32 you learn how to use analytic techniques to determine optimal allocation of store space between products and also optimize the use of a corporate sales force. Finally in Chapter 33 you learn how to forecast total sales of a product from a few data points.

Part IX: Advertising

Department store owner John Wanamaker said, “Half the money I spend on advertising is wasted; the trouble is I don't know which half.” In Chapter 34 you learn how John Wanamaker could have used the ADSTOCK model to measure the effectiveness of his advertising expenditures. In Chapter 35 you learn how to allocate ads between the available media outlets to maximize the effectiveness of ads. Chapter 36 deals with the math behind online ad auctions.

Part X: Marketing Research Tools

Often the marketing analyst must deal with data sets involving many variables. Principal components (Chapter 37) and Multidimensional Scaling (Chapter 38) enable the marketing analysts to reduce data sets involving many variables to a few easily understood variables. Often the marketing analyst must classify objects into one of several groups. Naive Bayes and discriminant analysis (Chapter 39) are great tools for developing classification rules. When the marketing analyst wants to determine if a single factor or a pair of factors has a significant effect on product sales, ANOVA (Chapter 40 and Chapter 41) is a useful tool.

Part XI: The Internet and Social Marketing

In the last 20 years, the Internet has turned our world upside down, and marketing is no exception. Social media such as Facebook and Twitter create many interesting opportunities for the marketer, which require careful analysis. In Chapter 42 you learn how the theory of networks sheds light on how you can identify people who are the key to spreading the word about your product. Chapter 43 discusses the math behind Malcom Gladwell's bestselling book The Tipping Point (Back Bay Books, 2002). Chapter 44 discusses the math behind videos (such as the notorious “Gangnam Style”) going viral. Finally, in Chapter 45 you learn how text mining can be used to glean useful insight from Twitter, blogs, and Facebook posts.

Who Should Read This Book

There is plenty of material in this book for a one-semester course on marketing analytics at the advanced undergraduate or MBA level. I also believe the book can be useful to any corporate marketing analyst. With regard to prerequisites for the book, I assume you understand the Copy command in Excel. That is, you know when and where to put dollars signs in a formula. If you work hard, that's about all the prior knowledge needed to get a lot out of the book.

I always try to write my books in a modular fashion, so you can skip around and read about what interests you. If you don't want to read the book from start to finish, the following table should help you navigate the book.


Chapter Prerequisites

Chapter 1-Slicing and Dicing Marketing Data with PivotTables


Chapter 2-Using Excel Charts to Summarize Marketing Data


Chapter 3 Using Excel Functions to Summarize Marketing Data


Chapter 4-Estimating Demand Curves and Using Solver to Optimize Price


Chapter 5 Price Bundling


Chapter 6-Nonlinear Pricing


Chapter 7-Price Skimming and Sales


Chapter 8-Revenue Management


Chapter 9- Simple Linear Regression and Correlation


Chapter 10-Using Multiple Regression to Forecast Sales


Chapter 11-Forecasting in the Presence of Special Events


Chapter 12-Modeling Trend and Seasonality

5 and 11

Chapter 13-Ratio to Moving Average Forecasting Method

3 and 12

Chapter 14 Winter's Method


Chapter 15-Using Neural Networks to Forecast Sales


Chapter 16 Conjoint Analysis


Chapter 17 Logistic Regression


Chapter 18 Discrete Choice Analysis


Chapter 19-Calculating Lifetime Customer Value


Chapter 20-Using Customer Value to Value a Business


Chapter 21- Customer Value, Monte Carlo Simulation, and Marketing Decision-making


Chapter 22- Allocating Marketing Resources between Customer Acquisition and Retention

4 and 19

Chapter 23-Cluster Analysis


Chapter 24-Collaborative Filtering


Chapter 25-Using Classification Trees for Segmentation


Chapter 26-Using S Curves to Forecast Sales of a New Product

5 and 12

Chapter 27-The Bass Diffusion Model


Chapter 28-Using the Copernican Principle to Predict Duration of Future Sales


Chapter 29-Market Basket Analysis and Lift


Chapter 30- RFM Analysis and Optimizing a Direct Mail Campaign


Chapter 31 Using the SCAN*PRO Model to evaluate your Marketing Mix


Chapter 32-Allocating Retail Space and Sales Resources


Chapter 33-Forecasting Sales from Few Data Points


Chapter 34-Measuring the Effectiveness of Advertising


Chapter 35-Media Selection Models

4, 21, and 34

Chapter 36-Pay Per Click (PPC)OnLine Advertising


Chapter 37-Principal Component Analysis(PCA)

10 and 23

Chapter 38-Multidimensional Scaling (MDS)


Chapter 39-Classification Algorithms: Naive Bayes Classifier and Discriminant Analysis

37 and 38

Chapter 40-Analysis of Variance: One Way ANOVA


Chapter 41-Analysis of Variance: Two Way ANOVA


Chapter 42-Networks


Chapter 43-The Mathematics behind The Tipping Point

42 and 43

Chapter 44-Viral Marketing

10, 15, and 39

Chapter 45-Text Mining


For example, before reading Chapter 5 you need to have read Chapter 4; before reading Chapter 34 you need to have read Chapter 31, and so on.

Tools You Need

To work through the vast majority of the book, all you need is Excel 2007, 2010, or 2013. Chapters 15, 21, and 35 require use of the Decision Tools Suite. You can download a 15-day trial version of the suite from

What's on the Website

From the book website ( you can download all Excel files used in the book as well as answers to all of the Exercises at the end of each chapter.


A famous Chinese proverb (popularized by the late management guru Stephen Covey) states, “If you give a man a fish you feed him for a day. If you teach a man to fish you feed him for a lifetime.” Hopefully this book will teach you enough about marketing analytics so you will be well equipped to develop your own quantitative marketing models for most problems that come your way. Happy modeling!