Basket Analysis

I was doing some exercises one day and just want to share with you how you can use basket analysis to help your business to make better decisions.

I have some mock up data as below. What I would like to do is to figure out which item is sold with which item in a single order. A use case scenario with this, for example item A is normally sold with B, is you can place item A next to B to maximise your sales or offer a discount if customer buy A and B as it’s highly likely they will do so.

1

The trick to easily analyse this dataset in Tableau is that you have to slightly modify it as shown below. Drag the same excel sheet twice and join them.(Note if you can not use “<>”when you join the “Item ID” , try save your excel workbook as 1997-2003 version, which is exactly what happened to me when first did this using Excel 2016)

2.PNG

Once you have prepared your dataset like this you can quickly do what I did in below graph. Drag “Item ID” to Columns and another instance of “Item ID” to Rows, finally right click “Order ID” and drag it to “Text” and select CNTD (distinct count). If you don’t see CNTD, use data extract instead of live connection.

3.PNG

We can see that whenever people buy item B they also buy item A or C.  A more nice looking version of the graph is as below (I used famous superstore dataset which shipped with Tableau). I want to point out that the square at the intersection between “Computer Peripherals ” and “Paper” is the biggest so it indicates that it’s very likely that people buy this 2 items in a single order.

4.PNG

Hope you get the idea. Drop me a note if you have any questions.

Once again I want to point out that how quickly you can get some insights out of your data with modern tools like Tableau. Next post I will write something about Alteryx, best friend with Tableau and show you how you can use both tools to make your life even easier in some situations. Stay tuned.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s