Shopping basket analysis

1. Overview

1.1 Concept

By studying user consumption data, we can correlate different products and explore the analysis method of the connection between the two, namely "shopping basket analysis".

1.2 Problems to be solved

Find out the pattern of customer buying behavior:

For example, if a user buys product A, will it have any impact on product B?

Do different users have different purchasing patterns?

Which products should be put together for bundling?

1.3 Expected effect

For details of the dashboard link, please refer to: shopping basket analysis.

1.4 Implementation ideas

Association analysis is measured by the following indicators.

Support calculation formula: product combination purchase number/total purchase orders.

Confidence calculation formula: product combination purchase number/Number of purchases product1.

Lift calculation formula: support/((Number of purchases product1/total purchase orders)*(Number of purchases product2/total purchase orders)).

2. Example

Sample data: "SalesDEMO>Group Product Sales Summary Table".

According to the formula in section 1.4, the required indicators are: "product combination purchase number", "Number of purchases product1", "Number of purchases product2", and "total purchase orders".

2.1 Calculate "total purchase orders"

1) Create a self-service dataset using the "Group Product Sales Summary Table", name it as "total purchase order dataset", and check the "document code", as shown in the figure below:

2) Add a group summary, drag the "document code" into the "Group" and "Summary" columns respectively, and set the summary column's summary mode to "Distinct Coount", as shown in the figure below:

3) Add a new column, name it "total purchase orders", sum up the number of document codes, and click "OK", as shown in the figure below:

4) Save and update the self-service dataset.

2.2 Calculate the number of orders for a single product

1) Create a new self-service dataset using the "Group Product Sales Summary Table" and name it: "single product order number table", check the "product name" and "document code" fields, as shown in the figure below:

2) Add a group summary, drag the "product name" and "document code" into the grouping column summary column respectively, and set the summary column summary mode to "Distinct Count", as shown in the figure below:

3) Save and update the self-service dataset.

2.3 Calculate "the number of purchases of product1 and product2 orders"

2.3.1 Use left and right merge to find product combination

At this time, you need to copy a column of the same product category, and merge the two columns together to separate product combinations such as A+A, A+B, B+A, etc.

1) Use "Group Product Sales Summary Table" to create a new self-service dataset: shopping basket analysis table. Check "document code" and "product name", as shown in the figure below:

2) Add "Left and right merge", the merged dataset is "document code" and "product name" under "Group Product Sales Summary Table", as shown in the figure below:

3) Select "Union combined", and the basis of the combination is "document code", as shown in the figure below:

4) Use "product name" as "product1", and "product name1" as "product2". Add field settings to change the name, as shown in the figure below:

2.3.2 Filter out unwanted product combinations

Obviously, there is no need for a combination like A+A, so the data needs to be filtered out.

Add a"Filter", click "Add Formula", enter the function: product1!=product2, click "OK", as shown in the figure below:

2.3.3 Remove duplicate data

As in one order, the same product may be purchased multiple times. We do not need to calculate duplicate data, so add grouping and summary, as shown in the following figure.

Thus there is no duplicate value for such a product combination.

2.3.4 Count the number of purchases of product1 and product2 orders

1) Add an auxiliary column, which is a constant column with a value of 1, as shown in the following figure:

2) Count the number of orders for purchasing product1 and product2 at the same time, and calculate the number of 1s in the same category of goods as the number of times each product combination is purchased, as shown in the following figure:

2.4 Find "Number of purchases product1" "Number of purchases product2"

1) Add "Left and right merge", and select the "single product order number table" that we created before, as shown in the following figure.

Select "product1" and "product name" as the merge basis, and name the merged result as "product1".

2) In the same way, merge it with the "single product order number table" once again, select "product2" and "product name" as the merge basis, and name the merged result as "product2", as shown in the figure below:

3) Perform a field setting, and name the "single product order number table-document code" and "single product order number table-document code1" as "Number of purchases product1" and "Number of purchases product2" respectively, as shown in the figure below:

2.5 Find "total purchase orders"

Combine the field of "Total Purchase Orders" into this wide table for easy calculation later.

1) Add "Left and right merge", and left merge with the previously created "total purchase order dataset", as shown in the figure below:

2) Select "document code" as the merge basis, as shown in the figure below:

In this way, all the basic indicators "product combination purchase number", "Number of purchases product1", "Number of purchases product2", and "total purchase orders" are all in this wide table. Next, you only need to calculate the support, confidence, and lift.

2.6 Calculate "support"

Support = product combination purchase number/total purchase orders, and add a new column, as shown below:

2.7 Calculate "confidence level"

Confidence level = product combination purchase number/Number of purchases product1, and add a new column, as shown below:

2.8 Calculate "lift"

1) Lift = support/((Number of purchases product1/total purchase orders)*(Number of purchases product2/total purchase orders)), and add a new column, as shown below:

2) Save and update the self-service dataset.

2.9 Create a dashboard

1) Use the "shopping basket analysis table" you just saved to create a dashboard. Select "Custom Chart" and drag "product1" and "product2" into the horizontal and vertical axes respectively. Select the "rectanglar block" in the "Graphics Properties", and drag the "support" into the "color" bar. Since the mapping will cause the value of multiple orders to be added, the support summary mode needs to be "Average", as shown in the figure below:

2) Create a new component to display the detailed data of "support", "confidence level", and "lift", as shown in the figure below.

Among them, "support, confidence level , lift" will be combined and summed for multiple orders, so their summary mode should be changed to "Average". The value format is set to percentage, as shown in the figure below:

2.10 Effect display

For details, please refer to section 1.3 of this article.