I. An Overview of Market Basket Analysis
Concept
Market basket analysis is an analysis method that associates two different products and mines their relations by analyzing user purchasing data.
You can calculate lift according to this document. There is a high correlation between the two products with lift more than 1, indicating that the combination of the two products is effective and they can be sold together.
Note:What to Solve
Identify customers' purchasing patterns.
Will the purchase of product A by users have any impact on product B?
Do different users have different purchasing patterns?
Which products should be bundled together for sales?
Preview
Implementation Method
Association analysis is measured by the following indicators.
Definition | Probability | Example | |
Support | The probability that both product A and product B are purchased at the same time, or the proportion of a product combination's purchasing times to two products' total purchasing times Support indicates how representative the association rule is in all things. The higher the support is, the more important the association rule is. | Support of product A for product B P(A ∩ B) | There are a total of 10 orders, among which milk and bread are purchased six times. So the support of the milk and bread group is 6 / 10 = 60%. |
Confidence | The conditional probability of purchasing product B after purchasing product A (purchase product B due to purchasing product A) | Confidence of product B for product A P(A | B) | There are a total of 10 orders, among which product A is purchased eight times and product A with product B is purchased six times. The confidence is 6 / 8 = 75%. |
Lift | The lift of purchasing product A for purchasing product B. Lift is used to determine whether the combination of products has actual value (more purchasing times of the combination products than those of the single product). Combination is effective with lift more than 1. | L= P(A ∩ B) / [P (A) * P (B)] | There are a total of 10 orders, among which product A is purchased eight times, product B is purchased six times, and product A with product B is purchased six times. So the lift is 0.6 / (0.8*0.6) = 1.25 (> 1), indicating that the combination of product A and product B is effective. |
Calculation formulas:
Support: The order number of purchasing product A and product B simultaneously / total order number of purchasing
Confidence: The order number of purchasing product A and product B simultaneously / the order number of purchasing product A
Lift: support / ((number of purchasing product A / total number of purchasing orders) * (number of purchasing product B / total number of purchasing orders))
II. Steps to Create a Market Basket Analysis
Obtaining Product Combination Through Join
Set the field Order Number as the join basis to obtain all product combinations in the order.
1. Create an analysis subject called Market Basket Analysis. Add data, click Field Settings, and tick the fields Order Number and Product Name.
2. Add data again, click Join with the fields Order Number and Product Name in the table Market Basket Analysis 1 as the join datasets.
3. Click Left Join and set Order Number as the join basis.
4. Click Field Settings. Change Product Name into Product A and Product Name 1 into Product B.
Calculating Indicators Needed
Indicators needed include order number of purchasing product A and product B simultaneously, order number of purchasing product A, order number of purchasing product B, and order number of the total purchasing.
Calculating Order Number of Purchasing Product A
Add a column, group it according to the field Product A, and count unique values to obtain the number of purchasing product A.
Calculating Order Number of Purchasing Product B
Add a column, group it according to the field Product B, and count unique values to obtain the number of purchasing product B.
Calculating Order Number of Purchasing Product A and Product B Simultaneously
Add a column, group it according to the fields Product A and Product B, and count unique values to obtain the order umber of purchasing product A and product B simultaneously.
Calculating Order Number of the Total Purchasing
Add a column and count unique values for all order numbers to obtain the total number of orders.
Next, you need to calculate the support, confidence, and lift.
Calculating Support, Confidence, and Lift
Calculating Support
Support = order number of purchasing product A and product B simultaneously / order number of the total purchasing. Click Formula Column.
Calculating Confidence
Confidence = order number of purchasing product A and product B simultaneously / order number of purchasing product A. Click Formula Column.
Calculating Lift
Lift = support / ((order number of purchasing product A / order number of the total purchasing) * (order number of purchasing product B / order number of the total purchasing)). Click Formula Column.
Removing the Combination of Same Product A and Product B
It is meaningless to calculate the support, confidence, and lift for the same product (same fields of Product A as that of Product B). You can filter the combination of same product A and product B.
Save and update the analysis subject.
Creating a Component
1. Use Market Basket Analysis to create a component. Select Custom Chart and drag Product A into Horizontal Axis and Product B into Vertical Axis.
Select Graphic Property, set the chart to rectangular block, and drag the field Support into Color. Due to the addition for the values of multiple orders, you need to set Summary Mode to Average.
2. Create another component to display the detailed data of support, confidence, and lift.
Set Summary Mode to Average due to the merging summation of multiple orders. Set Numeric Format to Percentage.
Demonstration
For details, see the section "Preview".
