Marketing Analytics: Data-Driven Techniques with Microsoft Excel

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