Skip to main content

Market Basket Analysis Tableau – How To Find Products to Cross Sell Together

Say for instance you’re in the business of selling bookcases– but in the basket, what should be sold together to promote cross selling?

Lets show you how it can be done with Superstore data!

Step 1: Identify which orders contain the product, category etc which fit your criteria.


Step 2: Use this as an identifier against the whole order.

We can do this with an easy level of detail calculation! We are essentially isolating to each order OrderID (since its fixed to this in the calc) then checking against which is the max value. Since any string value is inherently larger than nothing (the null value), it will return the string and populate against each order ID.

Step 3: Place this new calculation on filters

This will bring back all orders where it contains bookcases. But it will also keep its accessory products!

From here its just about rearranging the view to find what other product categories are being sold with bookcases. Place a count distinct of order ID to columns with subcategory to rows.

The view below tells us that for the 224 orders that contain a bookcase sold, we also had 56 orders that sold at least 1 quantity of a binder.

But when I place profit onto color, this tells me that for the past bookcase orders, not only have 42 orders had a bookcase and a phone in the same basket. But I’ve also made a nice profit of $2,687 from customers buying these items together! This is probably important as we are not making a profit from Bookcases!

Interested in the workbook? Check it out on Tableau Public!

Thanks for reading!


How to Find the Number of Active Projects at Any Given Point in Time

This analytics problem is a rite of passage – we all eventually get asked it. And it usually goes to a tune like this:

“I have a list of projects with start and end dates. I want to know at any given time, how many projects are active.”

(Btw the TL;DR for this is actually this great video by my TIL colleague Andre)

In this case, we looking for how many orders are being prepared for shipment at any given date?

Here’s my viz.

Using Superstore, the data you’re given looks something like this.

And visually represented, we know that these projects all overlap on March 16 2016.

But how can we express this as a line chart? i.e. not at a given point in time, but how many over time?

For such a simple question, it actually has a rather complex (but easy to implement!) solution. It requires a product join.

The difficulty with this easy to ask question is that …how can Tableau plot data for a datapoint that effectively doesn’t exist? That is, when we look at the data, March 16 only occurs 3 times.

So as smart as Tableau is, it does not know that all the orders in the list are in preparation mode, just like the other 3. (Well Tableau CAN know, but lets put densification aside for another day).

Step 1: Prepare your Data

Given this train of thought, that means we need to give each entry (order ID/employee/project/whatever your flavor of granularity) every date we want to compare against.

And since we want to be able to compare against any date, we are going to multiply each entry 365 times for 365 dates.  This will pad out the underlying dates and give the data structure we need to make this work!

That means you’ll need a dataset with dates you want to see in 1 column and a dummy variable in another. I’ve very creatively called mine “One”.

Then you’ll have to add the dummy variable to your original dataset. In this case, I added a “1” running a straight column down the Superstore data.


Set it as an inner join in Tableau

Almost there!

Step 2: Create Your Date Filter

.. then put this filter on columns.

Step 3: Start to create your view!

Put date from your dummy dataset on columns and add the count distinct of your chosen dimension on rows.

In mine, I get something like this.


Other resources I’ve used to get to this method include this great video by my colleague Andre and of course, the great Bethany Lyon’s talk on Cross Database Joins at TC16.


Happy Vizzing!~







Making Sankey Charts with Tableau and Alteryx – Download the Template to Make Your Own

Here’s my first step to making this a scalable viz for anyone interested in building this without all the legwork.

There’s a lot of demand to scale such a viz so I thought I would put my 2 cents in. It only works for visualizations with 5 stages and where each unique ID moves from dimension to dimension (rather than the flow of measures) –  but still a start!

Why 5 Stages?

I’ve chosen to release the first iteration with 5 stages because of its potential application for sales pipelines: lead, prospect, qualified prospect, committed and transacted. Apply this to your Salesforce data to visualize common customer paths and areas of improvement.

With this method, I built this beautiful Sankey below outlining a friends’ job search.

How do you get your hands on such treasures? You’ll need Alteryx Designer and Tableau Desktop (both which offer trial versions in case you are new to either software.)

Step 1: Download my Alteryx workflow from the Alteryx Gallery. 

Run the Alteryx workflow with your dataset, selecting which fields are in Stage 1 through 5. Then set the where you’ll want your .tde to save to on your machine. Note that this method only works for 5 Stage Sankey charts because the table calculations will break otherwise. Also your stages need to be field values before running the Alteryx workflow.

Step 2:  Download the template Tableau workbook

Once downloaded, switch the .tde connection with the new .tde you’ve produced from the workflow.

Here’s where you’ll need to click to switch the .tde files.

Once the switch is made, your viz should magically appear! Just change the alias’s to match your desired results and you’ve got a Sankey chart in 10 minutes!

Let me know what you think of the workflow/workbook or anything else in the post. More than happy to clarify if there are questions. I’m hoping this will be useful to at least someone on the interwebs!