In our recommendation today we will show how to create a market basket analysis, which allows us to determine the association that exists between the items that are usually purchased at the same time, getting to know the buying habits of the customers, giving vital information to the business in making decisions, for example in designing future promotions, location of the products in the warehouse, among others.
We present in the first instance our final result, which highlights the products that are associated at the customer’s order level in each subcategory, ie for the accessories subcategory we have a total of 658 products registered in the sale and in their order id a total of 63 products were associated for copiers, 84 for telephones and so on, when highlighting the subcategory Folders we see the association in the sales of the 974 products
To replicate this exercise we will use the Sample-Superstore data source, which is available in the body of this blog.
“We started by evaluating the minimum level of detail of our information:
We created a sheet called Order by subcategory (drag order id and next subcategory) It can be seen that there are several subcategories related to an id of the order, we add number of records to the view and it indicates that within a subcategory we find more than one registration, this is because the minimum level of detail of our data is at the product level) “
Directly in the reading of the data for the three registers marked from the order ID 100335, there is a detail of each of the characteristics of the order.
Create a sheet called Records by Subcategory showing Number of records (double click) by subcategory (double click), activate the label and then include in the view the count of product id, this comparison is made because it is easier to work in the fields calculated with numerical records instead of counting a Text field.
In a new sheet that we will call set SubCategory select the field SubCategory – Create – Set. Any value in the set is included and a name is assigned according to the need.
We will create a calculated field to which we will assign the name “Products in each purchase”.
We must apply a conditional that provides the quantity of products for the elements that are within the SubCategory set otherwise this result will be null (IIF ([SubCategory Set], [Number of records], NULL))
This result must then be set at the level of the order ID with fixed ({FIXED [Order ID]: SUM (IIF ([SubCategory Set], [Number of records], NULL))})
Then we duplicate the field Number of records and change the name to Products (change it to discrete and include it in the view) “Products”
We change the type of sight to bars to then have a double axis (synchronize the axes) and proceed to customize (We assign a color to each measurement name, remove the axis headers and format the background lines)
Activate the Products label with each purchase (hide the 17 null indicator)
Now we create a joint action to change the quantity of products according to the selected subcategory. (By selecting to change the set “SubCategory Set” and clearing the selection allows “Delete all set values”)
We will create a calculated field to which we will assign the name “Highlight” and its content will be “All fields must highlight”.
Finally we create a highlight action for all the subcategories through a specific field – Highlight “Highlight everything”,
Now we can know the products associated with the purchase by subcategory.
The video tutorial can be found at the following link.
Contact us