Chris is a business analyst who likes to practice data modeling in her free time.
She particularly enjoys building analytical models to achieve marketing objectives. For example: clustering models for auto segmentation, propensity models for customer lifetime value predictions, and attribution models for channel evaluations.
This is a blog for Chris to practice her analytical skills and connect with like-minded people.
To practice product positioning, I found a perfect data set from the 1995 Wisconsin Dell Case. This study draws data from 1698 face-to-face interviews conducted with visitors to this entertainment resort. We have the demographic data of these visitors and data about whether they had participated in or were likely to participate in any of 33 entertainment activities. The response for these activities constitute a binary response matrix, which is perfect for our multidimensional scaling.
In the previous post, we have calculated the actuals for every metric.
Now it is time to compare the actuals to the target to determine the scores. We will need monthly, MTD (month to date), QTD (quarter to date), YTD (year to date) and final score. The scores will be calculated by the performance (Actual/Target or the reverse) * weight.
So now that we have our targets and static monthly system data, we will eventually need to make a comparison to assign a score. However, static monthly system data needs to get turned into ‘Actuals’ first. Thus, our first step is to generate our actuals for each metric!
Other than generating actuals, we need to keep in mind that our end goal is to create dataset in a table format, so that it will make it easier for us to build a card.
How to create archive data with snapshot timestamp logic so that we can report accurately on static data? How to build logic to only keep snapshot records on the 1st of every month?
Collaborative filtering is a method of making automatic predictions about the preference of a consumer by collecting preferential information from various users. The underlying assumption of this approach is that if consumer A shares the same opinion as consumer B on an issue, A is more likely to share the opinion of B on a different issue than other randomly chosen consumer.
Unlike conjoint analysis that requires consumers to rank the level of each product attribute, discrete choice analysis need not to do that. Conjoint analysis uses multiple linear regression whereas discrete choice analysis adopts logistic regression, using maximum likelihood estimation and the logit model to estimate the ranking of product attributes for the population represented by the sample.
Today we’ll be building this model using a sample dataset mobilecarrier.xls, it gives the amount 77 representative are willing to pay per month for each service. The key to this model is to set up a spreadsheet that tells me, for any set of prices for each possible product combination, how much revenue we can obtain from this sample of customers. Then we’ll will use the Solver plug-in to determine the set of prices for the product combinations that maximizes the revenue.
There are several ways to conduct clustering analysis. For this study, I chose to use a partitioning method that requires that input variables have meaningful magnitude or be binary categorical variables. Except for age, the demographic variables in this data are multi-category variables, so we need to perform an extra step to convert multi-category variables into binary categorical variables.
There are many metrics to used to measure the impurity of a node. Here we’ll be using the concept of entropy to measure the impurity of a node. To compute for each attribute value category level combination, we will need the equation: P(i|X=a)*Log2(P(i|X=a) combined with IFERROR to ensure that when P(i|X=a)=0 Log2(0) the undefined value will be replaced by zero. In here, we use the formula =IFERROR((D15/$F15)*LOG(D15/$F15,2),0).
Conjoint analysis enables analyst to determine the product characteristics that drive a consumer’s preference for products. Through conjoint analysis, analyst will be able to learn the relative importance of the product attributes and within each attribute the ranking of the levels.
Site selection problem interests me as it usually involve data sets with more explanatory variable than there are stores. Each potential location, geocoded by longitude and latitude, represents a point on a map and may be associated with multiple variables including but not limited to population, housing, traffic, and economic conditions.
Lift is the most commonly used tool in market basket analysis. The lift for a combination of purchase products and/or day of week is defined by this equation: (The actual number of times combination occurs) divided by (Predicted number of times combination occurs if items in combination were independent).
To accurately forecast sales when the data has seasonality and trends, we’ll need to identify and separate them from the data series. Sometimes it is as simple as using moving averages to smooth data and eliminate seasonality (e.g. excel scatter plot with trend line. When monthly seasonal influence is suspected, use a 12-month moving average, when analyzing trend in quarterly data, use a four-quarter moving average). Yet, sometimes it is more complicated than that.
As we can see from the data, we already know the real choices of these 333 commuters. Now it’s time for us to build a predictive model and see how it works on the training data. Today we’ll use a linear combination of the four explanatory variables to predict commuter choice. We will also add a code in the end to evaluate the predictive accuracy of our model.
There is no one correct way to build a customer life time value model, or actually any models. Today I’ll build the model base off the method created by Gupta and Lehman to calculate the customer value multiplier. Their method utilizes discount and retention rates to forecast a customer’s long-term value denoted by multiplier. A multiplier of three means that the customer’s long term value is three times the profit he generates during the first period.
