Pointers - Creating a model for predictions

[Greyco]

I'm a student doing a project for a retail company that is trying to calculate their lost sales due to malfunctioning products. To do this we've done a survey among customers who had such products.

The main question was "If your purchase level was 100% before you bought this malfunctioning product, what is it now?"
(Surprisingly there was a slight increase in their purchase level thanks to great customer support.)

Now since they want to predict this in the future for all of their products to have a complete impact analysis of the costs of such malfunctioning goods, we've asked some other questions which are easy to check up on for this company and might influence this purchase level. These questions were:

- Purchase price of the product
- Type of product
- Type of problem
- Evaluation of customer service
- Time it took to solve the problem
- Time or usage after which the problem occurred

The descriptive statistics were just within my capabilities and I was able to figure out that "Time it took to solve the problem" had the most influence on the future purchase level of those disgruntled customers. I've just spent an afternoon filling in the mean number of the purchase level from each possible option in above questions. This gives some indication for future reference but isn't exactly what that company is looking for.

They want a concrete model they can use in excel where they're able to select "Price, producttype, sort of problem, ... and come up with one concrete purchase level so they can predict the missed sales when they delivered malfunctioning products.

Since I'm a complete novice in this, I just don't even got a clue where to start. I'm not expecting miracles here, but am wondering if someone can just point me in the right direction so I know where to look for examples and which stuff to read up on.

If it's any help, I'm using SPSS as my software and if necessary I could share the database here.

Thanks for taking notice

- Greyco

ps: Sorry for the sloppy text, I just spent over half an hour writing a perfect opening post for this forum with a fancy layout but then lost it all because I had to log in again - d'oh

[noetsi]

Personally I think asking a theoretical question this way causes the customer to guess, which is rarely a good idea. Asking them something like: "In the past when you have an encountered a defective product, how likely were you to buy it again" would be more concrete.

I am not sure how you would do this with descriptive statistics. If your answers are from 0 to 100 percent (that is future purchase levels your dependent variable) then you can do this with linear regression which is the simplest regression. It allows you to put all the factors that contribute to future purchases in the model at once so you can see which is relatively more important and control for the other. But you have to understand regression to do this and you can not check for violations of the assumptions in excel to the best of my memory. Its dangerous to do regression without checking assumptions. You could run the results in SPSS which does have the diagnostics and then (maybe) find a way to transfer them to excel. In honesty I stay away from any statistics in excel, I use it only as a reporting tool.

You can do a chi square test to determine if one of the variables you mentioned appears to influence purchasing, but you have to do that one variable against purchasing at a time (not all of them against it at once). That is the simplest statistic I know of and requires few assumptions.

[Greyco]

Thanks for answering Noetsi.
You're absolutely right that this isn't a good way to calculate the potential loss in sales. Ideally you'd track such customers and see how their purchase level evolves after encountering a defect product. But this would require retraining your customer service and developping the software to track this. Quite costly for something that you won't know is worth it.

I opted for this way because they wanted to quantify those potential lost customers in tangible costs. That's why we included a slide bar in the survey going from 0% to 300%. Surprisingly the people that bought small electric products, such as blenders, thought their purchase level increased slightly with 2.9% to 102.9%. People who bought larger products, like wooden furniture, reported a decrease of nearly 20%. Main culprit wasn't the price of the product but the time it took to solve the problem which could take months for the furniture because that's imported from overseas and not easily in stock.

But I'm afraid it's just not feasible giving them a model in excel which they can manipulate to calculate potential customer losses of any product in their inventory. I'd be fine if they could just use spss and manipulate the data themselves but they want a single defined amount that they are able to put after transportation, waste, processing, ... costs of defect products.

Thanks again for putting me on track, going to read up on how to create dummy variables in spss now and hopefully let you know if I can report some kind of success.

[noetsi]

I was more suggesting rewording the question than changing the way you pull in the data. I am sure you are right alternatives to the survey would be expensive. It is not surprising to me that the big effect is on larger ticket items. People there are already commited to spending a good amount of money, so the cost is less of an issue. But having spent a lot of money, they want it to be spent well so product quality (and customer service) becomes critical.

There are software, like dashboards, that allow manipulation including pretty serious graphical features. But they are not easy to do and you have to have a lot of expertise to create them. Also you have to have very good timely data. Unless you are willing to invest a good deal I would not go that route. Especially if the people using it don't understand what they are doing.

Good luck, you got a tough assignment. I, sort of, work in the same type of work (although I do the SQL and stats not the dashboards). One final piece of advice, keep it as simple and foolproof as you can.