Cross-Selling Rate Calculation of Market Basket

Background

You consider each order a market basket and want to determine the association between one product and another. In that case, you can calculate the cross-selling rate.

If you use the market basket analysis to calculate association relationships, you need to use table joins (n:n), which can cause data inflation and is not suitable for a large data volume.

Therefore, if your data volume is large, you are advised to use the method described in this document to calculate the association relationships of the market basket.

Expected Effect

If you want to know the cross-selling rate of tomatoes with other products, namely to find out the products most related to tomatoes, you can select Tomato in the following dashboard to view the proportion of the sales quantity (related to tomatoes) for each product to the total sales quantity.

You can click and view the online demo: Cross-Selling Rate Calculation of Market Basket.

Data Preparation

Create an analysis subject and use the sample data Group merchandise sales summary, as shown in the following figure.

Judging Whether the Order Contains Data on Tomatoes

Go to the component page. Create a calculation field named Whether the Order Contains Data on Tomatoes, as shown in the following figure.

Creating a Column Chart

1. Use a stacked column chart to display the sales quantity for each product. Set Chart Type to Stacked Column Chart, drag fields into the analysis area, and set Horizontal Axis to Indicator Juxtaposition.

2. Copy the field Whether the Order Contains Data on Tomatoes to obtain the field Whether the Order Contains Data on Tomatoes1, click the  icon next to the obtained field, and select Convert to Dimension from the drop-down list.

3. Drag the field Whether the Order Contains Data on Tomatoes into Graphic Property > Color. Click the  icon next to the dragged field and choose Descend > Whether the Order Contains Data on Tomatoes from the drop-down list. Re-click the  icon and select Group Records with Same Value.

4. Click the  icon next to the field Quantity in Horizontal Axis and select Enable Stacking from the drop-down list.

In this way, the total length of each column represents the total sales quantity for each product. The column with the legend labeled as 1 represents the sales quantity of products in orders that contain data on tomatoes. You can roughly judge the cross-selling effect of tomatoes on the sales of each product by viewing the proportion of each column.

Calculating the Cross-Selling Rate of Tomatoes with Other Products

Relying solely on the length of the column chart to judge the cross-selling rate of tomatoes with the sales of other products is not accurate enough. You can calculate the numerical values.

The cross-selling rate of the sales quantity = The sales quantity of the product in the order that contains data on tomatoes (cross-selling quantity) / The total sales quantity of the product

1. Calculate the sales quantity of each product in orders that contain data on tomatoes.

Create a calculation field named Cross-Selling Quantity and enter the formula, as shown in the following figure.

Note:

The field Whether the Order Contains Data on Tomatoes in the filtering condition is the indicator field, not the copied and converted dimension field Whether the Order Contains Data on Tomatoes1.

2. Calculate the total sales quantity for each product.

Create a calculation field named Total Quantity and enter the formula, as shown in the following figure.

3. Calculate the cross-selling rate of tomatoes (The cross-selling quantity / The total quantity). Add a calculation field and enter the formula, as shown in the following figure.

4. Switch Chart Type to Custom Chart, drag the field Cross-Selling Rate to Horizontal Axis, and set Horizontal Axis to Indicator Aggregation.

Change the display format of the field Cross-Selling Rate to Line and drag the field Whether the Order Contains Data on Tomatoes out of Graphic Property > Color.

Click the  icon next to the field Cross-Selling Rate in Horizontal Axis, select Set Value Axis from the drop-down list, and set Shared Axis to Upper-value Axis. Re-click the  icon, select Value Format from the drop-down list, and select Percentage.

In this way, you can view the cross-selling effect of tomatoes on other products.

Although you can view the cross-selling effect of tomatoes on other products now, you also want to view the cross-selling effects of carrots and cabbages on other products.

At this time, you can consider changing the value Tomato to the values of other products with parameter.

Changing Products with Parameters

Adding a Parameter

1. To make it easy to change data on tomatoes to data on carrots and cabbages for viewing the cross-selling effects on other products, you can change the parameter value Tomato.

Create a parameter and set Parameter Type to Text, as shown in the following figure.

2. Use the created parameter to change the original parameter (Tomato) in the formula of the calculation field Whether the Order Contains Data on Tomatoes, as shown in the following figure.

3. Delete the field Whether the Order Contains Data on Tomatoes1, re-copy the field Whether the Order Contains Data on Tomatoes with the parameter in the formula replaced, and convert the field type of the re-copied field to the dimension type. Replace the fields with the ones in the chart, same as step three in section "Creating a Column Chart."

Assigning Values to Parameters with Filtering Components

1. Add the FineBI built-in data ENdemo_Product_Dimension_Table to the current subject.

2. Create a dashboard, add the filter component Text List, and bind the created parameter.

You need a text list of the field Product name to facilitate selecting and assigning values to parameters. However, you cannot directly use the fields (which may filter the current component if used) in the current table, so you can add another table and bind the Product name field of the added table to the filter component.

3. Click OK, drag the completed component into the dashboard, and select the Carrot in the text list to view the cross-selling effect on other products.

4. Go to the component page, click the  icon next to the field Product name in Vertical Axis, and select Filter from the drop-down list to filter out the products with values same with the parameter value of the column chart, as shown in the following figure.

Effect Display

For details, see section "Expected Effect."