Basket Analysis

[#1]

Hi All,

I have a transaction table with the following format:

SalesID     SubProdID     Amount

1                 101                    1000

1                  102                    2000

3                  101                   5000

3                  102                   6000

4                  101                   7000

4                  102                   8000

1                  103                   1000

3                  103                   2000

5                   109                  1500

Let us say I have a list box with SubProdID, I multi select 101 and 102. Now I want to display a chart that shows what is the likelihood of customers buying subprodid 103 when they buy 101 and 102 in the same transaction.

In this case we have 3 transactions 1, 3 and 4 that have 101 and 102 in the same transaction. Out of thes 1 and 3 also have 103 bought, but 4 doesn't have it. which means 2/3 of the customers who buy 101 and 102 buy 103.

I want to plot a graph that has the SubProdID as the dimension and the percentages of customers that bought the other products in that transaction as the measure.

103                     66.6%

109                     0%

Thanks,

Ram

[#2]

Wondering is that even possible? I am playing with alternate states and P(), but reaching nowhere. Any help would be great.

[whiteline]

Hi.

Maybe Count({<SubProdID=, SalesID=P(SalesID)>} distinct SalesID)/Count({<SubProdID=>} distinct SalesID)

[tresesco · Accepted solution]

May be like attached sample?

[#5]

I am using personal edition. Could you share the script or chart expression?

[tresesco]

Alternate state is used:

Expression for chart:

=Count({$<[SalesID]=p([State1]::[SalesID]),SubProdID -=[State1]::SubProdID>}DISTINCT SalesID)/Count({State1}total DISTINCT SalesID)

Assuming your selection is in State1 , dimension is SubProdID and chart is in default state..

[#7]

Do you still need help?

Some time ago i made some chart examples of basket analysis. I think, there was exactly the same problem

Of course sometimes your user would like to select more than 2 product and analyse co-selling on transactions with all selected ....

I remember, that we trainded few approaches.

So, if you still need help, let me know and i will try to find those apps.

regards

Darek

[#8]

Ok, i found demo prepared for basket analysis.

From technical point of view key to achieve the desired effect in our case was the use of AND-mode.

After turning it on (it is some work in load script to do, you will easy find instructions how to do it), expression to find results you need is as simple as:

count(distinct SalesID)/count(total distinct SalesID).

Benefits from this solution are, that you may choose as many products, as you want. You also may use NOT for your subset definition.

For example: get into account transactions where customer bought article 101, but not bought article 102....

I hope it helps.

Let me know if you need more information.

regards

Darek

P.S - of course there will be no problem to exclude 101 and 102 from the chart when you select 101 and 102, as result for them is always 100% (1 in my fast example

[#9]

Hi Dariusz,

Thanks for your inputs. tresesco's solutions is working perfectly and that is what I initially wanted, which is a MBA-Association analysis based on a single field.

Having seen your response I am wondering if I can extend the MBA-Association analysis to more than 1 field, I think that'll be useful.

Would you mind sharing the app? This AND is interesting, not sure what needs to be done at the script level.

regards.

[#10]

I'am not sure if there is another solution than AND-mode if you would like to select ulimited number of products as AND basket criteria.

To turn it you have to load DISTINCT small additional table with field you would like to select in AND-mode.

Then (if load script is correct) you will be able to turn on AND-mode in listbox (Properties/Genelal).

Have fun with AND

I hope, it helps you.

regards

Darek