Description
Book SynopsisHelping tech-savvy marketers and data analysts solve real-world business problems with Excel Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data.
Table of ContentsIntroduction xxiii
I Using Excel to Summarize Marketing Data 1
1 Slicing and Dicing Marketing Data with PivotTables 3
Analyzing Sales at True Colors Hardware 3
Analyzing Sales at La Petit Bakery 14
Analyzing How Demographics Affect Sales 21
Pulling Data from a PivotTable with the GETPIVOTDATA Function 25
Summary 27
Exercises 27
2 Using Excel Charts to Summarize Marketing Data 29
Combination Charts 29
Using a PivotChart to Summarize Market Research Surveys 36
Ensuring Charts Update Automatically When New Data is Added 39
Making Chart Labels Dynamic 40
Summarizing Monthly Sales-Force Rankings 43
Using Check Boxes to Control Data in a Chart 45
Using Sparklines to Summarize Multiple Data Series 48
Using GETPIVOTDATA to Create the End-of-Week Sales Report 52
Summary 55
Exercises 55
3 Using Excel Functions to Summarize Marketing Data 59
Summarizing Data with a Histogram 59
Using Statistical Functions to Summarize Marketing Data 64
Summary 79
Exercises 80
II Pricing 83
4 Estimating Demand Curves and Using Solver to Optimize Price 85
Estimating Linear and Power Demand Curves 85
Using the Excel Solver to Optimize Price 90
Pricing Using Subjectively Estimated Demand Curves 96
Using SolverTable to Price Multiple Products 99
Summary 103
Exercises 104
5 Price Bundling 107
Why Bundle? 107
Using Evolutionary Solver to Find Optimal Bundle Prices 111
Summary 119
Exercises 119
6 Nonlinear Pricing 123
Demand Curves and Willingness to Pay 124
Profit Maximizing with Nonlinear Pricing Strategies 125
Summary 131
Exercises 132
7 Price Skimming and Sales 135
Dropping Prices Over Time 135
Why Have Sales? 138
Summary 142
Exercises 142
8 Revenue Management 143
Estimating Demand for the Bates Motel and Segmenting Customers 144
Handling Uncertainty 150
Markdown Pricing 153
Summary 156
Exercises 156
III Forecasting .159
9 Simple Linear Regression and Correlation 161
Simple Linear Regression 161
Using Correlations to Summarize Linear Relationships 170
Summary 174
Exercises 175
10 Using Multiple Regression to Forecast Sales 177
Introducing Multiple Linear Regression 178
Running a Regression with the Data Analysis Add-In 179
Interpreting the Regression Output 182
Using Qualitative Independent Variables in Regression 186
Modeling Interactions and Nonlinearities 192
Testing Validity of Regression Assumptions 195
Multicollinearity 204
Validation of a Regression 207
Summary 209
Exercises 210
11 Forecasting in the Presence of Special Events 213
Building the Basic Model 213
Summary 222
Exercises 222
12 Modeling Trend and Seasonality 225
Using Moving Averages to Smooth Data and Eliminate Seasonality 225
An Additive Model with Trends and Seasonality 228
A Multiplicative Model with Trend and Seasonality 231
Summary 234
Exercises 234
13 Ratio to Moving Average Forecasting Method 235
Using the Ratio to Moving Average Method 235
Applying the Ratio to Moving Average Method to Monthly Data 238
Summary 238
Exercises 239
14 Winter’s Method 241
Parameter Definitions for Winter’s Method 241
Initializing Winter’s Method 243
Estimating the Smoothing Constants 244
Forecasting Future Months 246
Mean Absolute Percentage Error (MAPE) 247
Summary 248
Exercises 248
15 Using Neural Networks to Forecast Sales 249
Regression and Neural Nets 249
Using Neural Networks 250
Using NeuralTools to Predict Sales 253
Using NeuralTools to Forecast Airline Miles 258
Summary 259
Exercises 259
IV What do Customers Want? 261
16 Conjoint Analysis 263
Products, Attributes, and Levels 263
Full Profile Conjoint Analysis 265
Using Evolutionary Solver to Generate Product Profiles 272
Developing a Conjoint Simulator 277
Examining Other Forms of Conjoint Analysis 279
Summary 281
Exercises 281
17 Logistic Regression 285
Why Logistic Regression Is Necessary 286
Logistic Regression Model 289
Maximum Likelihood Estimate of Logistic Regression Model 290
Using StatTools to Estimate and Test Logistic Regression Hypotheses 293
Performing a Logistic Regression with Count Data 298
Summary 300
Exercises 300
18 Discrete Choice Analysis 303
Random Utility Theory 303
Discrete Choice Analysis of Chocolate Preferences 305
Incorporating Price and Brand Equity into Discrete Choice Analysis 309
Dynamic Discrete Choice 315
Independence of Irrelevant Alternatives (IIA) Assumption 316
Discrete Choice and Price Elasticity 317
Summary 318
Exercises 319
19 Calculating Lifetime Customer Value 327
Basic Customer Value Template 328
Measuring Sensitivity Analysis with Two-way Tables 330
An Explicit Formula for the Multiplier r 331
Varying Margins 331
DIRECTV, Customer Value, and Friday Night Lights (FNL) 333
Estimating the Chance a Customer Is Still Active 334
Going Beyond the Basic Customer Lifetime Value Model 335
Summary 336
Exercises 336
20 Using Customer Value to Value a Business 339
A Primer on Valuation 339
Using Customer Value to Value a Business 340
Measuring Sensitivity Analysis with a One-way Table 343
Using Customer Value to Estimate a Firm’s Market Value 344
Summary 344
Exercises 345
21 Customer Value, Monte Carlo Simulation, and Marketing Decision Making 347
A Markov Chain Model of Customer Value 347
Using Monte Carlo Simulation to Predict Success of a Marketing Initiative 353
Summary 359
Exercises 360
22 Allocating Marketing Resources between Customer Acquisition and Retention 347
Modeling the Relationship between Spending and Customer Acquisition and Retention 365
Basic Model for Optimizing Retention and Acquisition Spending 368
An Improvement in the Basic Model 371
Summary 373
Exercises 374
VI Market Segmentation 375
23 Cluster Analysis 377
Clustering U.S. Cities 378
Using Conjoint Analysis to Segment a Market 386
Summary 391
Exercises 391
24 Collaborative Filtering 393
User-Based Collaborative Filtering 393
Item-Based Filtering 398
Comparing Item- and User-Based Collaborative Filtering 400
The Netflix Competition 401
Summary 401
Exercises 402
25 Using Classification Trees for Segmentation 403
Introducing Decision Trees 403
Constructing a Decision Tree 404
Pruning Trees and CART 409
Summary 410
Exercises 410
26 Using S Curves to Forecast Sales of a New Product 415
Examining S Curves 415
Fitting the Pearl or Logistic Curve 418
Fitting an S Curve with Seasonality 420
Fitting the Gompertz Curve 422
Pearl Curve versus Gompertz Curve 425
Summary 425
Exercises 425
27 The Bass Diffusion Model 427
Introducing the Bass Model 427
Estimating the Bass Model 428
Using the Bass Model to Forecast New Product Sales 431
Deflating Intentions Data 434
Using the Bass Model to Simulate Sales of a New Product 435
Modifications of the Bass Model 437
Summary 438
Exercises 438
28 Using the Copernican Principle to Predict Duration of Future Sales 439
Using the Copernican Principle 439
Simulating Remaining Life of Product 440
Summary 441
Exercises 441
29 Market Basket Analysis and Lift 445
Computing Lift for Two Products 445
Computing Three-Way Lifts 449
A Data Mining Legend Debunked! 453
Using Lift to Optimize Store Layout 454
Summary 456
Exercises 456
30 RFM Analysis and Optimizing Direct Mail Campaigns 459
RFM Analysis 459
An RFM Success Story 465
Using the Evolutionary Solver to Optimize a Direct Mail Campaign 465
Summary 468
Exercises 468
31 Using the SCAN*PRO Model and Its Variants 471
Introducing the SCAN*PRO Model 471
Modeling Sales of Snickers Bars 472
Forecasting Software Sales 475
Summary 480
Exercises 480
32 Allocating Retail Space and Sales Resources 483
Identifying the Sales to Marketing Effort Relationship 483
Modeling the Marketing Response to Sales Force Effort 484
Optimizing Allocation of Sales Effort 489
Using the Gompertz Curve to Allocate
Supermarket Shelf Space 492
Summary 492
Exercises 493
33 Forecasting Sales from Few Data Points 495
Predicting Movie Revenues 495
Modifying the Model to Improve Forecast Accuracy 498
Using 3 Weeks of Revenue to Forecast Movie Revenues 499
Summary 501
Exercises 501
34 Measuring the Effectiveness of Advertising 505
The Adstock Model 505
Another Model for Estimating Ad Effectiveness 509
Optimizing Advertising: Pulsing versus Continuous Spending 511
Summary 514
Exercises 515
35 Media Selection Models 517
A Linear Media Allocation Model 517
Quantity Discounts 520
A Monte Carlo Media Allocation Simulation 522
Summary 527
Exercises 527
36 Pay per Click (PPC) Online Advertising 529
Defining Pay per Click Advertising 529
Profitability Model for PPC Advertising 531
Google AdWords Auction 533
Using Bid Simulator to Optimize Your Bid 536
Summary 537
Exercises 537
X Marketing Research Tools 539
37 Principal Components Analysis (PCA) 541
Defining PCA 541
Linear Combinations, Variances, and Covariances 542
Diving into Principal Components Analysis 548
Other Applications of PCA 556
Summary 557
Exercises 558
38 Multidimensional Scaling (MDS) 559
Similarity Data 559
MDS Analysis of U.S. City Distances 560
MDS Analysis of Breakfast Foods 566
Finding a Consumer’s Ideal Point 570
Summary 574
Exercises 574
39 Classification Algorithms: Naive Bayes Classifier and Discriminant Analysis 577
Conditional Probability 578
Bayes’ Theorem 579
Naive Bayes Classifier 581
Linear Discriminant Analysis 586
Model Validation 591
The Surprising Virtues of Naive Bayes 592
Summary 592
Exercises 593
40 Analysis of Variance: One-way ANOVA 595
Testing Whether Group Means Are Different 595
Example of One-way ANOVA 596
The Role of Variance in ANOVA 598
Forecasting with One-way ANOVA 599
Contrasts 601
Summary 603
Exercises 604
41 Analysis of Variance: Two-way ANOVA 607
Introducing Two-way ANOVA 607
Two-way ANOVA without Replication 608
Two-way ANOVA with Replication 611
Summary 616
Exercises 617
XI Internet and Social Marketing 619
42 Networks 621
Measuring the Importance of a Node 621
Measuring the Importance of a Link 626
Summarizing Network Structure 628
Random and Regular Networks 631
The Rich Get Richer 634
Klout Score 636
Summary 637
Exercises 638
43 The Mathematics Behind The Tipping Point 641
Network Contagion 641
A Bass Version of the Tipping Point 646
Summary 650
Exercises 650
44 Viral Marketing 653
Watts’ Model 654
A More Complex Viral Marketing Model 655
Summary 660
Exercises 661
45 Text Mining 663
Text Mining Definitions 664
Giving Structure to Unstructured Text 664
Applying Text Mining in Real Life Scenarios 668
Summary 671
Exercises 671
Index 673
