Can you recommend profitable return customer product bundles?

This week Candra Mcrae provided a market basket analysis challenge which had a bit of all sorts going on as well as the core ‘what else was bought with product x’ puzzle – parameter actions, sheet swapping, custom toggle buttons.

Hopefully I’ll cover all the key elements :

  • Identifying the 2nd customer order
  • Identifying the orders containing the selected sub-category
  • Identifying the ‘other’ sub-categories ordered
  • Building the bar chart
  • Building the tree map
  • Creating the toggle button
  • Controlling what viz to display
  • Creating the dashboard and interactivity

Identifying the 2nd customer order

The requirements state the analysis is based on returning customers, and specifically their 2nd order. So we need a way to identify this set of data.

In the superstore data set, a customer only ever places one order on a day, so we will make use of this feature, but this technique won’t always work in other situations – I personally have been known to place multiple orders with Amazon on the same day, especially when Christmas shopping!

If we look at the dates customers have ordered, we’re looking to identify the orders associated to the customer & dates highlighted below

We can identify whether the date is the first order date using a FIXED LoD calculation

Is Customer’s First Order?

{FIXED [Customer ID]: MIN([Order Date])} = [Order Date]

The FIXED LoD statement within the {..} is finding the minimum Order Date for each Customer ID. This is then being checked against each Order Date, and returning true when it matches.

So this is giving us the first record, but we want the second….

I solved this, by first adding Is Customer’s First Order? = False as a data source filter (right click on the Superstore data source -> edit data source filters)

This essentially removes all the rows associated to the first order for each customer from the data set, and consequently, the Is Customer’s First Order? is now reported as True against what was originally the 2nd order…
If you compare the image above to the one further up the page where I marked the 2nd orders, you should see the values match what now seems to be listed as the first!

This works due to Tableau’s order of operations; the data source filter is filtering the data ‘early on’, so the rows of data we’re now working against, has no knowledge of those rows. Subsequently the customer’s first order is now actually reporting the customer’s 2nd order as the minimum (aka first) order date.

We can now add Is Customer’s First Order? to the Filter shelf and set to True, to just work with this set of orders.

NOTEIf the data source has not yet been extracted, which will be required if you want to publish to Tableau Public, then you will need to do one of the following :

  1. At the point of extraction, the data source filter will automatically be applied as an extract filter. If you keep it, then after extracting, remove the data source filter if its still there, otherwise you’ll find you’re reporting against the 3rd customer order OR
  2. At the point of extraction, remove the extract filter that is automatically applied. After extraction, re-add the data source filter if it’s been removed.

An alternative to using any data source/extract filters is to use the following LoD (which I only got my head round after building out the above, so I didn’t put it in my solution., but works just as well).

Is Customer’s 2nd Order?

{FIXED [Customer ID]:MIN(IIF([Is Customer’s First Order]=False,[Order Date],NULL))} = [Order Date]

The IIF statement is getting the Order Date for all the records which aren’t the Customer’s 1st order. The FIXED LOD, is then returning the minimum/smallest of these dates, which will be the date of the 2nd order. This is then compared to each Order Date to identify the appropriate record.

If you strip off any data source/extract filters, you can see how this works, and Is Customer’s 2nd Order? can be added to the Filter shelf instead, set to True, to get the required rows.

Identifying the orders containing the selected sub-category

To start with we need a parameter to store the selected Sub-Category. Right click on Sub-Category and select Create -> Parameter to create a string parameter which lists the sub-categories. I named mine pSelected_SubCat and it’s defaulted to Accessories.

What we’re now trying to find is the rows relating to Order IDs that contain the stated Sub-Category, in this instance Accessories

So we first need to identify the rows which match

Customer Ordered Selected SubCat

IIF([pSelected_SubCat]=[Sub-Category],1,0)

Add this to the view, and you can see the rows associated to Accessories are marked with 1

But we don’t just want these rows; we also need the rest of the rows on the same order, so in the case of order CA-2018-131534 above, we also need the row associated to the Paper Sub-Category.

For this, I used Sets as per the technique described in the link Candra provided within the hint.

We want the set of Order IDs where the sum of the Customer Ordered Selected SubCat field is >=1. Right click on Order ID and Create -> Set. On the Condition tab, set the relevant properties.

Adding the Orders with Selected SubCat set into the view, and we can see it’s picked up the rows we care about, and we can now filter by this set

An alternative to using Sets, is to use another FIXED LoD calculation

{FIXED [Order ID]: SUM([Customer Ordered Selected SubCat])}>=1

For each Order ID, sum up the Customer Ordered Selected SubCat field, and if it’s >=1 then it will return True, and you can then filter by this instead.

Identifying the ‘other’ sub-categories ordered

We want to count the orders which contain each Sub-Category, which is simply

Count Orders

COUNTD([Order ID])

With a basic view that is filtered as required, that lists Sub-Category by Count Orders (and sorted by Count Orders descending), we get

And you’ll see that Accessories is listed at the top. Change the parameter, and that value will also be listed. But we don’t want to see this – we need to filter it out. For this, we can create

Other Items Ordered

If [pSelected_SubCat]=[Sub-Category] THEN ‘N/A’ ELSE [Sub-Category] END

Pop this into the view, and you can see the ‘N/A’ is listed against the selected Sub-Category.

This means we can add a further Filter using the Other Items Ordered field and set it to Exclude N/A.

Building the bar chart

So now we’ve got the 3 filters we need to identify the rows of data we need to consider, we can easily build a bar chart.

To colour the bars, we need to work out the profitability

Profit Ratio

SUM([Profit])/SUM([Sales])

Is Profitable?

[Profit Ratio]>0

Add Is Profitable? onto the Colour shelf and adjust accordingly

You just then need to format to remove gridlines/axes etc, and add the relevant fields (correctly formatted) to the Tooltip to create the required text.

Building the treemap

The simplest way to do this, is to duplicate the sheet you’ve built for the bar chart, then use the Show Me option (top right) and select TreeMap. This will put most of the pills in the right places. The only thing that needs to be shifted is to add Is Profitable? back onto the Colour shelf, and to explicitly set the Sort order against the Sub-Category field. Count Orders also needs adding to the Label shelf, so it can be displayed.

Creating the toggle control

To create the toggle control which will control the switching of the vizzes do the following :

  • In the Columns shelf, type in MIN(0)
  • Then next to it, type in MIN(1)
  • Drag the MIN(1) pill from the Columns shelf and drop onto the MIN(0) axis, when you see the ‘2 green columns’ appear
  • Remove Measure Names from the Rows shelf
  • Change the mark type to Line
  • Create a parameter called Toggle which is an integer containing the values 0 & 1, defaulted to 0. Show this parameter on the view.
  • Create a new calculated field, Selected Toggle which just references the Toggle parameter
  • Add Selected Toggle to the Columns shelf and change to aggregate to MIN rather than SUM
  • Make it dual axes, and synchronise the axes
  • Remove Measure Names from the Colour shelf on the All marks card.
  • On the Selected Toggle card, change the mark type to Circle, and increase the Size. If you change the Toggle parameter the circle should shift to 1.
  • To colour the line based on the position, create a new calculated field

Colour

IF [Toggle]=0 THEN ‘grey’ ELSE ‘green’ END

  • Add Colour to the Colour shelf of the All marks card. With the Toggle parameter set to 0, set the colour to grey. Change the parameter to 1, and set the colour to green.
  • To set the tooltip create a new calculated field

Toggle Label

IF [Toggle]=0 THEN ‘Bar’ ELSE ‘Treemap’ END

  • Add Toggle Label to the Tooltip shelf of the Selected Toggle marks card. Adjust the tooltip text to just show this value.
  • Remove the text from the Tooltip of the Measure Names marks card.
  • Hide the axes headers and remove the row & column dividers

Controlling what viz to display

With the Toggle parameter set to 0, add the Selected Toggle field to the Filter shelf of the bar chart. and set it to be 0 (or at most 0 depending what filter control displays)

Then set the Toggle parameter to 1, and add the Selected Toggle field to the Filter shelf of the Treemap chart. Set this to be 1 (or at least 1 depending on the filter control displayed)

You should now find that if you switch to the bar chart sheet, nothing is displayed. Change the Toggle parameter back to 0, and the bar chart will now show, but there will be nothing on the treemap chart.

Creating the dashboard and interactivity

Create a dashboard sheet.

Add a vertical layout container

Add the bar chart into the vertical layout container. Set the chart to fit entire view and don’t display the title.

Add the treemap chart beneath the bar chart in the same vertical container. Whilst nothing is actually displaying, still set the chart to fit entire view and again don’t display the title.

The Toggle parameter should have automatically displayed on the right hand side, so test changing the parameter to see how the chart switches.

Add the Toggle Control sheet into the right hand container too.

Add a dashboard action to change the Toggle parameter when the Toggle Control is interacted with. It should take effect on Select of the Toggle chart, affect the Toggle parameter and pass the Measure Values field into the parameter.

Clicking on each end of the Toggle chart should now make the viz change from bar to treemap.

You just now need to tidy up the dashboard – add a title, remove any unrequired objects, set the container background of the right hand panel to grey (you will need to set the background on the toggle sheet to the same shade of grey too).

And hopefully that’s it. My published version is here.

Happy vizzin’! Stay Safe!

Donna

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s