Re: Cross Selling and predicting quantity sold?

Tech-Archive recommends: Speed Up your PC by fixing your registry

You're close, but you have a little ways to go yet.

First - MAXIMUM_INPUT_ATTRIBUTES sets the number of inputs that are
considered by the algorithm, not how many can be in a single case. For
example, if you use the default - 255 and you have 1000 products, only 255
of those products will be considered by the algorithm - the others won't
have any impact.

Next you have Item and Quantity as independent case level attributes. This
means that you have _one_ quantity attribute for all products. What you
likely meant to do is to have the quantity tied to the customer/item pair.
You could create a composite key which is Customer/Item, however, you are
still looking at a single Quantity measure for all products.

What you want to do is to simply make the quantity attributes in the nested
table predictable. For example, using DMX to describe the model, it would
look like this:

CREATE MINING MODEL QuantityPredict
(
CustomerID LONG KEY,
Products TABLE,
(
Item TEXT KEY,
Quantity LONG REGRESSOR PREDICT // Input and Predictable
)
) USING
Microsoft_Decision_Trees(MAXIMUM_INPUT_ATTRIBUTES=0,MAXIMUM_OUTPUT_ATTRIBUTES=0)

Then to get the quantity of a particular item you would issue a query like
this:

SELECT (SELECT * FROM Predict(Products) WHERE Item='Batteries') FROM
QuantityPredict
NATURAL PREDICTION JOIN
(SELECT (SELECT 'Radio' AS Item, 1 AS Quantity) as Products) as t

--

-Jamie MacLennan
SQL Server Data Mining
This posting is provided "AS IS" with no warranties, and confers no rights.
PLEASE POST NEW QUESTIONS AT
http://forums.microsoft.com/msdn/showforum.aspx?forumid=81&siteid=1
<anonymous_user@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:319c7157-d70b-4c0a-a4ef-6df0d00fdadf@xxxxxxxxxxxxxxxxxxxxxxx
Jamie,

Thanks for the response. I am trying, as you suggested, to create a
second model which will predict the quantity of a particular item that
someone may purchase. I'm trying to do this with nested tables again, and
am coming up with the following model:

Sales table is treated as both a nested and case table:
Case Table:
Customer = Key
Item = Input
Quantity = PredictOnly
Nested Table:
Item = Key, Input
Quantity = Input

The predictions I'm getting don't seem to change based on the quantity of
items in the nested table.

Is this the right way to answer the question? In the example I gave, the
business question would be:

A customer currently has 2 clock radios in their basket. Our cross
selling model (seperate model) tells us that their is a high liklihood
they will want to buy batteries. How many batteries are they likely to
buy given that we know how many clock radios they've got in their basket?

In this case I would run a prediction query which looks like:

Select FLATTENED
T.[Item],
PredictHistogram([Quantity])
FROM [Model]
NATURAL PREDICTION JOIN
( SELECT
'Batteries' as [Item],
( SELECT 'Clock Radio' AS [Item], 2 as Quantity
) AS [Basket])
AS t

What I would expect is for this to return that it expects 4 batteries to
be purchased, since I know people who buy clock radio's normally buy 2x as
many batteries as they do radios.

Am I writing this query incorrectly, or is my model wrong, or both? I'm
fairly new to using nested tables in analysis.

Maximum Inputs is defaulted to 255, so as long as I don't have more than
that many items in my basked none of them will be feature selected away,
right?

Thanks,
Tom

You need to make the quantity column predictable and not the nested table
(or nested key). Also make it input if you want to use it that way.

I would recommend creating one model to predict what's likely and another
model to predict quantities though. For quantities, you are going to
want
to use Decision Trees - be sure to set your feature selection parameters
(MAXIMUM_INPUT ... etc) or many things will be feature selected away.

--

-Jamie MacLennan
SQL Server Data Mining
This posting is provided "AS IS" with no warranties, and confers no
rights.
PLEASE POST NEW QUESTIONS AT
http://forums.microsoft.com/msdn/showforum.aspx?forumid=81&siteid=1
<anonymous_user@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7923333e-64e9-49ce-a3c2-2297f7b38d25@xxxxxxxxxxxxxxxxxxxxxxx
I've done a lot of web research and read a book on analysis services
but
still haven't found an example of how to develop a cross selling
algorithm
that can predict not only what is sold, but how much is predicted to be
sold.

For instance,
If I know customer A has bought 5 units of item 1, 10 units of item 2,
and
20 units of item 3, I may know there is a 50% chance they will also
want
to but item 4.

Is there any way to determine how much of item 4 they might buy?

For instnace, say Item 4 is batteries and Item1 is a clock radio. We
know
that if they buy one clock radio they are likley to buy 2 batteries,
but
if they buy 2 clock radios they are likley to buy 4 batteries, not 2.
In
each case, there is the same likleyhood that they buy at least some
batteries.

If my data set looks like this (in my sales table)
Customer / Item / Quantity

How do I design the data mining model to account for quantity? I
tried
several approaches to this and can't seem to get it to work correctly
when
taking quantity into account.

The method I used takes the sale table and makes it a case table and
nested table, treats the Customer from the cast table as a key, and the
item from the nested table as a key, input, and predict. This is how
the
books I've read explain how to create a cross selling model. I don't
understand how I make it take quantity of each item into account.

Would it be better to do this in a 2nd mining model and not use it in
the
cross selling model?

Thanks
Tom





.

Relevant Pages

  • Re: Cross Selling and predicting quantity sold?
    ... Notice in one I specify the customer has 1 radio ... 1,000 radios in their basket. ... of batteries they purchase should be 1,000x higher in the second example. ... NATURAL PREDICTION JOIN ...
    (microsoft.public.sqlserver.datamining)
  • Re: Cross Selling and predicting quantity sold?
    ... Sales table is treated as both a nested and case table: ... In this case I would run a prediction query which looks like: ... What I would expect is for this to return that it expects 4 batteries to be purchased, since I know people who buy clock radio's normally buy 2x as many batteries as they do radios. ... Customer / Item / Quantity ...
    (microsoft.public.sqlserver.datamining)
  • Re: Cross Selling and predicting quantity sold?
    ... NATURAL PREDICTION JOIN ... A customer currently has 2 clock radios in their basket. ... How many batteries are they likely to ...
    (microsoft.public.sqlserver.datamining)
  • Re: 9V NiMH batteries, high capacity, any good deals?
    ... At church we are using Ansmann 9V/250mAh NiMH batteries, ... Dry cells will give you more time above 7.2V than NiCd or NiMH, but if you die off at 7.2V you're cheating your customer out of half of his battery capacity. ...
    (sci.electronics.design)
  • Re: 9V NiMH batteries, high capacity, any good deals?
    ... At church we are using Ansmann 9V/250mAh NiMH batteries, ... -- dry cells tend to peter out, ... and only because the customer ...
    (sci.electronics.design)