Basket analysis

[#1]

Hi all

I've been searching several times on this forum, but still haven't found what i am looking for.

Basically, what i want to do is what items are sold together. E.g - the best match for items is shirt & ties or shoes & socks or even milk & cereals.

I have thought of several ideas how to achieve that, and my best thought so far is creating a table, where all possible combinations of items sold on a transaction will be created. This should give the possiblility to sort on matches with highest frequencies etc. For example:

On transaction 1000111 there is sold 3 items..item 1,2,3. Then i should have 3 outcomes. 1+2,1+3,2+3. Then i could do a count calculation in a chart and get what i need. I just dont know how to do it.

Do any of you have any idea of hove to achive the above. All possible and workable solutions are highly appreciated. The might be a simple (or complex solution i still havent come up with).

Thank you for any help

/Martin

[#2]

Yes, you're definitely on the right path. I don't use combinations of items when I build mine but I think your plan makes good sense.

What I do is add a table of all combinations of Transaction ID and Item ID, lets call this table COMBO. The Transaction ID field is a linking field, but Item ID does not link to any existing fields. If I need Item Description, I duplicate that table to link to the new Item ID but still not link into any other parts of my data model. This creates a sort of "butterfly" model with Item dimension tables coming off both sides of the fact table.

One way to create a table of combinations is to join the COMBO table to itself several times. This runs the risk of exploding your memory requirements. I think this could be done better by LOADing the COMBO table again once and using complex peek() statements with sigma(2^n) fields, but that's more than I can work out here.

Hope that's some food for thought.

Jay

[Anonymous · Accepted solution]

I tried a little excersize - attached

[yblake]

As far as I know, this frequent itemsets data mining is not (yet) implemented straight forward in QlikView, as there is no function to get support and confidence for itemsets combinations.

Combinations calculations can be done in the underlying database using APRIORI algorhytm :

ORACLE :

http://download.oracle.com/docs/cd/B28359_01/datamine.111/b28129/market_basket.htm#BABEEGCB

SQL Server :

http://msdn.microsoft.com/en-us/library/cc280428.aspx

[#5]

Thank you for your thoughts about this solution.

I think I will give it a go with Michales excellent solution. At least i think this will point me in the right direction. The only issue so far, is the performance as we currently have a base of more than 60.000.000 (with salestransactions in our base.

Hopefully QlikTech can make an easier solution in future releases of QV.

Have a nice weekend.

/Martin

[#6]

Jay:

Does that mean that your solution is not suitable for presenting the most popular combinations? Or do I miss something?
At this moment I use a simple macro to achieve the same result, without creating an extra table in my reload. You first need to select a product and then press a button to see the related products based on transaction or customer.

Sub RelatedProductsTransaction
 ActiveDocument.Fields("Transaction_ID").SelectPossible
 ActiveDocument.Fields("Product_ID").SelectExcluded
End Sub
Sub RelatedProductsCustomer
 ActiveDocument.Fields("Customer_ID").SelectPossible
 ActiveDocument.Fields("Product_ID").SelectExcluded
End Sub

[Anonymous]

Thanks Martin,
I don't think my file is a solution, just trying an idea. Possibly it could be developed into a real solution even in the current QV versions.

Regards,
Michael

[#8]

Basket analysis determine what products customers are buying at the same time or at different times.

Provides opportunities to cross-sell and up-sell through relevant product recommendations.

In our case, we created a simple qv chart (pivot table) with time - product and customer dimensions and analyze what customer has bought.

Not sure if this helps, but it could give some directions.

Regards

Eraaj

[#9]

Mark,

In my implementation it does determine the frequency of each 2-combination of items. I haven't had the need to do it for 3 or more combinations.

J

[#10]

Jay,

If your implementation can determine the most popular combinations of 2 product I would love to here how you solved this. Can you share it with us?

Regards,

Mark