How to: Market Basket Analysis

Posted by William on 04/11/2009 under How To | 3 Comments to Read

A common data mining method is Market Basket Analysis. This method checks what kind of items are being sold in combination with other products. It delivers insight in buying behavior which helps to make better deals and, for example, bargain discounts. A famous example discovered this way is the cross selling of beer and diapers in supermarkets. The reason: after the birth of a baby it’s often the father who is buying diapers. Because company know more about their customers trough the use of credit and customer cards, useful information is ready to pick up. The good news: it’s actually quite easy to implement this method in Qlikview.

For this how to, you can use the attached marketbasket example with tables for customer, order, order line and product. You can load these tables in a new Qlikview file to get the following model.

In order to know which products are sold with a particular product we need to create a new table called Market Basket. This table contains the OrderID, ProductName and ProductGroup of each Order Line.

To make this table, first create a temporary table with the fieldname OrderID from Order

and

OrderLineID, OrderID, ProductID from OrderLine

and

ProductID, ProductName, ProductGroup from Product.

Now create the MarketBasket table with a resident load. In your script it will look like this:

//**********************************************************************
//*******  Market Basket Analysis  ***************************************
//**********************************************************************

TmpMarketBasket:
LOAD OrderLineID    AS TmpOrderLineID,
OrderID        AS TmpOrderID,
ProductID        AS TmpProductID
RESIDENT OrderLine;
LEFT JOIN
LOAD DISTINCT
OrderID        AS TmpOrderID
RESIDENT Order;
LEFT JOIN
LOAD DISTINCT
ProductID        AS TmpProductID,
ProductName        AS TmpProductName,
ProductGroup        AS TmpProductGroup
RESIDENT Product;

MarketBasket:
LOAD
TmpOrderID                 as %%MarketBasket,
TmpProductName            as MBProductName,
TmpProductGroup             as MBProductGroup
RESIDENT TmpMarketBasket;

DROP TABLE TmpMarketBasket;

After this we need to associate this table with the orderline table. Use the OrderID field and name it %%MarketBasket:

OrderID             as %%MarketBasket,

Save and reload the script. With this done, it’s time to build the dashboard.

Dashboard

• Add two multiboxes:

MBProductGroup
MBProductName

• Create a new pivot chart with the following dimensions:

MBProductName
ProductName

Create the following expressions:

Order Quantity
if(ProductName=MBProductName,0,Count(DISTINCT %%MarketBasket))

Quantity of products sold
IF(ProductName=MBProductName,0,sum({<ProductName=ProductName-MBProductName>} Quantity))

Set calculation condition, chart properties, general tab.
count(distinct MBProductGroup) = 1
The pivot chart will show the message below when there is no MBProductGroup selected.

Set error message on calculation unfulfilled, general tab, error messages
Please select a Market Basket Analysis Product Group

Set conditional show, chart properties, layout tab, show conditional
count(distinct MBProductName) <> 1

The chart only shows up when there is no MBProductName selected.

Selecting the MBProductGroup ‘Drinks’ gives the following result:

Order quantity is the number of orders where both the MBProductName and ProductName are sold. The ‘Products sold quantity’ is the number of products (ProductName) sold in those orders. Example: the market basket product water has 4 orders combined with orange juice. In those 4 orders, orange juice is 6 times sold.

Next thing to do is creating a bar chart which displays the products sold in combination with a selected market basket product.

• Create a bar chart with the following dimension:

ProductName

Create the following expressions:

Top Ten Associated Items by Quantity Sold
sum({<ProductName=ProductName-MBProductName>} Quantity)

Top Ten Associated Items by Revenue
sum({<ProductName=ProductName-MBProductName>} ProductPrice)

When you have information about costs it’s interesting to create profit and margin expressions as well.

In the chart properties, layout tab, show conditional, add the following expression:
count(distinct MBProductName) = 1

The bar chart only shows up when we have selected a Market Basket product.

Of course it’s also necessary to show information about the selected market basket product itself.

• Create a chart with no dimensions.

Create the expressions

Orders
count({<ProductName=MBProductName>} DISTINCT %%MarketBasket)

Quantity Sold
sum({<ProductName=MBProductName>} Quantity)

Associated Products
count(DISTINCT {<ProductName=ProductName-MBProductName>} ProductName)

Revenue
sum({<ProductName=MBProductName>} ProductPrice)

Tab general, window title:
=MBProductName

Tab general, calculation condition:
count(distinct MBProductName) = 1

Tab general error messages, calculation unfulfilled:
Please select one Basket Product to perform an analysis.

• Create a pie chart with no dimensions:

Create the expressions

Market Basket Product
sum({<ProductName=MBProductName>} ProductPrice)

Associated products
sum({<ProductName=ProductName-MBProductName>} ProductPrice)

Tab general, window title:
=MBProductName

Tab general, calculation condition:
count(distinct MBProductName) = 1

Tab general error messages, calculation unfulfilled:
Please select one Basket Product to perform an analysis.

Last thing to build for the dashboard is another chart with information about the associate products.

• Create chart straight table.

Add the dimension ProductName

Create the following expressions

Quantity of orders
count({<ProductName=ProductName-MBProductName>}  DISTINCT %%MarketBasket)

Quantity of products sold
sum({<ProductName=ProductName-MBProductName>} Quantity)

Revenue
sum({<ProductName=ProductName-MBProductName>} ProductPrice)

In the chart properties, general tab, windows title add
=’Market Basket Analysis for product: ‘&MBProductName

Calculation condition
count(distinct MBProductName) = 1

Error messages
Please select one Basket Item to perform an analysis.

The result of this is a fully functional Market Basket Dashboard

Select product group Drinks

Select product Coffee