Marketing Analytics – Data–Driven Techniques with Microsoft Excel

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