PART 1: Building Response Models in Excel

Use attached excel file.

Use Microsoft Excel to complete this section. Use the tab “CustomerTransactions_Amt” for the raw data.

We want to build several different market response models that connects our “input” (previous customer behaviors) to our “output” (what the customer spends on their next order).

For this first part, only use the first 200 customer records (the “CustomerID” label is from “Customer_1” to “Customer 200”).

Response Model 1: The manager wants to try first using a very simple response model.
“Whatever the customer most recently spent, that’s what we’ll predict is the next value of their purchase. If someone’s last order was $100 or less, we’ll predict their next order is 50% greater than their last purchase“.

Response Model 2: The manager wants to use a slightly more complex response model.
“Whatever the customer most recently spent, that’s what we’ll predict is the next value of their purchase. However, if it has been over 30 days since their last purchase, we will assume that their next purchase will only be 50% of what their most recent purchase was.“

HINT: For these two previous models, you can use an Excel =if() function to build your response model. These are “business rules” that are translated into mathematical forms.

What is the MAE for each of these two models?

Response Model 3: Using only the “MONETARY” variable, build a LINEAR response model that predicts the $ amount of a customer’s upcoming purchase. Using MAE as your guide, calibrate the parameters of the model.

Response Model 4: Now, build a POWER SERIES model (you can choose whether to go up to ^2 or up to ^3) that uses on the “MONETARY” variable to predict the $ amount of a customer’s upcoming purchase. Using MAE as your guide, calibrate the parameters of the model.

Response Model 5: Now, using the “MONETARY”, “FREQUENCY” and “RECENCY” variables, build a LINEAR model that predicts the $ amount of a customer’s upcoming purchase. Using MAE as your guide, calibrate the parameters of the model.

***********************************

Reporting your results for the 5 response models.

Using written text, tidy and organized tables, excel functions, and mathematical equations, discuss your analysis and results from Part 1. Things you may want to consider reporting and discussing some of the following:

• Providing written and visual (screenshots) guidance about how you actually accomplished creating the different response models.
• Showing the mathematical forms for the final calibrated models (Models 3 to 5).
• Comparing and contrasting the MAE for the models. Providing a clear, “manager-friendly” explanation of what the results mean.
• Providing an assessment of which of the 5 models you would recommend the company use to forecast the $ purchase of each of the next 800 customers. A more sophisticated discussion would provide (and defend!) the recommendation, even while possibly acknowledging some potentially appealing features of one or more of the alternative models.
• Discussing weaknesses or problems with your recommended model. This discussion can be both conceptual as well as empirical. Conceptually, you might take issue with the company not having better operationalization for the R, F, or M variables, or perhaps not providing your with relevant customer inputs you think would aid your forecast ability. On the empirical side, you may investigate where your model may notably inaccurate predictions.
• Since you have a response model built, you can easily explain your results in the context of scenario/hypothetical customers (plugging values into the inputs solves for the predicted output).
• Providing a publicly-accessible hyperlink to your Excel file that is tidy and organized, making it easy for an interested reader (like your professor) to understand precisely how you actually build your functions and models.

Note: I present these bullet points in order to aid your thinking. Do not simply copy/paste these bullet points and answer below them. They are not presented in the “correct” order, part of this assignment is precisely about you and your team thinking about how to structure your presentation.

PART 2: Predicting & Reporting with Your Preferred Response Model

Use Microsoft Excel to complete this section. Use the tab “CustomerTransactions_Amt” for the raw data.

For this first part, use the remaining 800 customer records (the “CustomerID” label is from “Customer_201” to “Customer 1000”).

Using the prediction model you selected in Part 1, apply that model in order to make Purchase$ predictions for each of the 800 customers.

Reporting your results for your applied predictions.

Using written text, tidy and organized tables, excel functions, and mathematical equations, discuss your predictions. Things you may want to consider reporting and discussing:

• Provide some basic summary statistics about the Purchase$ predictions for these 800 customers. Measures of central tendency (avg. is one example) as well as measures of dispersion (range is one example) can be used, including.
• VISUALIZE your forecast. PivotTables and PivotCharts may be excellent for this!
  [Prof note: Why yes, we *do* carry our skills from previous assignments forward to new tasks!]
• Discussing limitations, caveats, or other considerations you think the company should keep in mind while utilizing these forecasted numbers.
• Managers often like things organized into groups, even when the results themselves are continuous. For example, you may consider grouping the 800 people you have made sales forecasts for and sort them into some “Low Value, Mid Value, and High Value” groups. Provide the manager with a description of how members of these groups differ from one another.

PART 3: Prediction with RapidMiner

In this part, we are going to introduce ourselves to using RapidMiner. Our goal here is to replicate one of the response models we built previously in Excel, but this time do it using RapidMiner (plus a few small extensions). This is important for a few reasons. First, it will allow us to become familiar with a sophisticated, powerful piece of software that can be used in a wide array of real-world descriptive, predictive, and prescriptive marketing analytics applications. Second, RapidMiner is actually designed in a manner that patterns the conceptual workflow process of an end-to-end analytics project. In other words, learning how to do things in RapidMiner reinforces our ability to think conceptually through problems like a marketing analyst!

• VIDEO: Overview of the RapidMiner interface (first time users) (6 minute video)
• VIDEO: Overview of the RapidMiner process you will be building (3.5 minute video)
• LINK: Step-by-step written tutorial (with pics!) walking you through building this RapidMiner process

PART 4: Interpreting the RapidMiner Results in Excel

Using the *.CSV file you exported from RapidMiner, use basic univariate and bivariate analysis (like PivotTables) to provide management with more insights about their customers, based on their forecasted purchase amounts. Note: *.csv files are easily opened in Excel .

New Customer Information:
Hey, we have more information this time! You’ll notice you we have some additional information about these 800 customers. There are three additional variables that may be of interest – you are encouraged to use them, if useful, to derive additional insight for management about how to think about their customers.

CustomerType = Indicates if the customer is b2b or b2c
Instagram = Whether or not this customer follows us on Instagram
EMailNewsletter = Whether or not this customer subscribes to our e-mail newsletter