Tableau Viz: Affinity Analysis – Customers who bought product A also bought…

I think everyone who has made a hotel booking or has bought something on Amazon has noticed such information as Customers Who Bought This Item Also Bought or Customers Who Booked This Resort Also Booked. Today it is not unusual to set up such an extension on a website because of high-speed technological development. Companies must know who their audience is, what types of data they can collect, and how they can best use that data to discern what consumers really want.

Amazon is a good example of the approach a company might take. It uses a hierarchical model to recommend additional purchases to shoppers. Products are indexed in such a way that, for example, the system would always recommend batteries to someone buying a camera. The extension used keeps a record of all previously made purchases by the users. Then, when a user purchases any product it searches for the database for any other user who has purchased other products along with the currently selected product. If yes, it suggests those products to the customer.

Knowing about your customers and what they tend to buy enables companies to increase customer’s trust. Additionally, engagement in the site is also increased which then inspires customers to make bigger purchases.

In this article I would like to describe how to make such an analysis in Tableau. With the help of this you will find the answers to the following questions:

• How many people bought both Product A and Product B?
• People who purchased Product A also bought which other products?

The data I am going to use should contain Transaction ID or Customer ID information. It might be your internal data, Google Analysics data or any transactional data. First, you need to determine what level of the data it is worth analyzing, whether it will be category level, sub-category level or product name level.

Create a self-join

For this analysis the ‘Sample – Superstore’ database was used. You should create a SQL query that will join the table to itself in a self-join and then create table and heat map views for an affinity analysis. For this exercise the join condition has the goals to define unique order IDs and remove duplicate matches. First, you need to identify the field that describes a unique identifier. Then you should identify the field on which you want to perform an affinity analysis e.g. Category, Sub-Category or Product Name. In our example the unique identifier is the Order ID field, the Category in question is the Sub-Category field.

Self-joins are a variation of the following:

• Unique identifier = Unique identifier (Order ID)
• Category in question <> Category in question (Sub-Category)

SQL Query:

Select a.[City] AS [City],
a.[Country] AS [Country],
a.[Customer Name] AS [Customer Name],
a.[Order Date] AS [Order Date],
a.[Order ID] AS [Order ID],
a.[Postal Code] AS [Postal Code],
a.[Product Name] AS [Product Name],
a.[Region] AS [Region],
a.[Segment] AS [Segment],
a.[Ship Date] AS [Ship Date],
a.[Ship Mode] AS [Ship Mode],
a.[State] AS [State],
a.[Sub-Category] AS [Sub-Category],
a.[Discount] AS [Discount],
a.[Profit] AS [Profit],
a.[Profit Ratio] AS [Profit Ratio],
a.[Quantity] AS [Quantity],
a.[Sales] AS [Sales],
b.[Sub-Category] AS [Sub-Category Adj]
from [data$] a
inner join [data$] b
on a.[Order ID]=b.[Order ID]
and a.[Sub-Category]<>b.[Sub-Category]

Create a table view

To create a table view you should drag the field you want to analyze from one table to the Columns shelf. Then, drag the same field from the other table to the Rows shelf as demonstrated in the following example:

Step 1
From the Dimensions pane drag the following fields to the view:
• drag Sub-Category to the Columns shelf.
• drag Sub-Category Adj to the Rows shelf.

Step 2
From the Measures pane drag Number of Records to Label and Color on the Marks card.

In the view each number represents the number of times each category is represented with another item within an order. Notice that the numbers reflect each other along a diagonal axis where the same two categories are represented. Where categories intersect, the cell is blank.

Create a view with details.

Here’s an additional view you can create to supplement the affinity analysis.

Step 1
From the Dimensions pane drag the following fields to the view:
• drag Sub-Category and Sub-Category Adj to the Columns shelf.
• drag Number of Records to the Rows shelf.

Step 2
From the Measures pane drag Number of Records to Label and Color on the Marks card.

The view allows us to answer the following question: How many times other products have been purchased along with the currently selected product?

Dashboard creation

Now move views to the dashboard:
1) Create a new dashboard.
2) From the Layout Containers Menu on the lower left, drag a Vertical Layout Container Onto the dashboard.
3) Drag the Heat map graph into the Layout Container. Make sure the view is set to Entire view on the toolbar.
4) Drag the Details into the Layout Container as well and drop below the Heat map graph.
5) Create a Dashboard Action with the Source Sheet being your Heat map graph, and the Target Sheet being your Details. Run the Action on Select and Exclude Values in the Clearing box.

6) Test your filter by clicking on one of the items in the Heat map view. Your Details view should change.

7) Clear your filter by hitting Esc and your Detail view should minimize to the bottom of the container.

You are done! Congratulations!
Eugene

Posted under Analytics Attribution, Tableau, Vizualization by Eugene Shiman on 4th June, 2015

Leave a comment
Send to a friend

Have your say

Thanks for your contribution.

You can use Gravatar to upload an avatar that will appear next to your comment.

Please be polite and respectful to others.

<