Basket Analysis Made Easy

This article is aimed at technologists who hear about Basket Analysis and need a simple explanation with examples. Basket Analysis used to be the preserve of high cost specialist providers. Now we have OLAP technology and hardware that makes Basket Analysis available at commodity prices. Everyone involved in retail analytics should understand Basket Analysis. Essentially, Basket Analysis supports analysis of sales by the combinations of items purchased.

To elaborate, I will provide two classic examples of Basket Analysis.

1. Menu Trim
The business would like to remove a couple of low value items from the menu (product list). A smaller menu generally supports lower operational cost and inventory investment. Candidate items might be the lowest by sales value or gross profit contribution. We observe that the lowest value items are Frozen Yoghurt and Fruit Salad, which we can find by a simple slice of items and their sales/gross profit. However, this does not take into account whether customers come in to buy the product on its own, or whether they are buying lots of other products too. We can see from the Basket Analysis query below, that Frozen Yoghurt customers buy lots of other products with their Frozen Yoghurt. Basket Analysis can sum all the products the customer buys with Frozen Yoghurt, which we call Affected Net Sales. This suggests that we can remove Fruit Salad from the menu we only risk the $15,414 of sales, but if we remove the Frozen Yoghurt, we risk $1,899,151 of sales. We say that it is "at risk" because the customers might be coming in especially for the Frozen Yoghurt, and purchasing other products while there. Without Frozen Yoghurt these customers might go elsewhere for their entire basket.

2. Attracting Large Value Transactions
Your business might have a strategy to increase sales by promoting menu items that tend to be involved in large value transactions. Basket Analysis can help you do this by identifying menu items (product items) involved in large transactions. In the example above, Avocado Toast has a high transaction average of $105, while the average transaction for all items is $45. So, the business strategy might be to discount and/or promote Avocado Toast to attract these high value customers.The marketing geniuses will work out a way to attract these customers, which might be by discounting Avocado Toast, offering a discounted side item, promoting quality/health features, inter-company coupon, etc. Once the campaign starts, Basket Analysis can very succinctly report on the success of the campaign. One of the key indicators will be the transaction average of Avocado Toast transactions during the campaign. The sales of Avocado Toast might increase two fold, but if the Transaction Average is halved, you are probably only attracting customers who purchase Avocado Toast alone. Ie, you are not attracting customers with large transaction value. In the chart below, we can see that the Avocado Toast promotion has been successful in increasing Avocado Toast transactions, however, these additional transactions have been small value transactions. The marketing geniuses might need to try a new strategy!

I hope this has shown how Basket Analysis can help business' create better strategies and measure how well these strategies are working. This is just two simple examples of an infinite number of possibilities, only limited by your creativity. Basket Analysis has become a commodity feature in retail analysis and is becoming an essential tool for successful retail strategies.

Basket Analysis IV - Menu Trim

This is the third post in a series on Basket Analysis. To read the first post go to Basket Analysis I.

Retail stores are always trying to balance range of stocked items with inventory efficiency. A common task is to perform a "menu trim" where low profit contribution items are removed from the menu. A simplistic approach would be to remove the items with the lowest associated sales value or gross profit. However, this can result in trimming items that, while not profitable in themselves, are attracting customers and have high "associated" sales and profit contribution.

Consider the menu items above that are ordered by item sales. The Berry Smoothie is the item with the lowest sales value and gross profit. A simplistic view would be to say let's trim Berry Smoothie off the menu as it only represents $795.23 of sales. However, from our basket analysis query, we can see that the Berry Smoothie has a high Associated Net Sales and Associated Gross Profit. If we took Berry Smoothie off the menu, we might be risking $19,400 of associated sales. This is the total value of all transactions that have included Berry Smoothie. It might be that customers are coming into the store primarily for the Berry Smoothie and getting other items with the smoothie. If we remove Berry Smoothie from the menu these customers might not patronize our store.
Basket Analysis does not answer these questions directly, but it does give us information to make a better informed decision. In the case above, we might consider trimming the Vegeburger from the menu, as it has the lowest Associated Net Sales and lowest Associated Gross Profit. It appears that customers who come in to buy vegeburgers, purchase that item alone. So if that item was trimmed from the menu, it would appear to put less sales at risk.

Basket Analysis III: Promoting Sales Items Associated with Heavy Transactions

This is the third post in a series on Basket Analysis. To read the first post go to Basket Analysis I.

One common retail strategy is to promote a relatively low value item that is associated with "heavy" baskets. That is to identify sales items that tend to be in baskets that have many other profitable items. Sometimes these items are a little quirky and customers go to that particular retailer for the quirky item, and while they are there, they purchase many other items. The rational is that if we can attract more customers to purchase the quirky item, which as little profit contribution, they will purchase many other, profitable, items. So the promotion on the quirky item can be discounted with low, or even negative, gross profit. Basket Analysis technology can help identify these items, and it can also accurately measure the performance of the campaign. 

Here is a graph showing some of the key performance indicators around a promotion of Green Smoothies. This particular promotion increased, by a factor of 5, the sales of Green Smoothies, but these increased sales did not pull through, as hoped, the additional sales. See the green line near the bottom of the chart - it shows the sales value of Green Smoothies, and you can see a clear increase in their sales during the promotion period. Now look at the Average Txn value including Green Smoothies (blue bar) during the promotion period. It drops from about $20/transaction to about $16/transaction. Also, notice the purple line showing the Green Smoothie affected sales. See how it has increased, but only by the value of the increased Green Smoothie sales. This line shows the total value of all transactions that have at least one Green Smoothie. This suggests that promotion shoppers are coming in to purchase the Green Smoothies, but they are only purchasing Green Smoothies. It is valuable to get an early appreciation of the effectiveness of this campaign, so that it can be halted or modified early.

Note, traditional sales reporting tools could easily report on sales values of Green Smoothies, but it is only with Basket Analysis tools that we can get metrics like Green Smoothie affected sales and Average transaction value including Green Smoothies.

For the next Basket Analysis post see Basket Analysis IV.

Basket Analysis II: Would you like a drink with that?

This is the second post in a series on Basket Analysis. To read the first post go to Basket Analysis I.

A common question a customer receives when ordering food is "Would you like a drink with that?" This is an example of on-selling, where the customer has come in with an order, and the salesperson, cashier, etc. looks for an opportunity to increase the size (and profit contribution) of the transaction, and potential satisfaction of the customer, by inviting the customer to add another item to the transaction.  The activity can be much more subtle, such as "We have some hot-from-the-oven blueberry muffins today - would you like one with your coffee?" The intention is the same, and the activity usually involves some staff training and encouragement. Naturally, some training sessions are better than others and some supervisors, motivations, etc are better than others at encouraging staff to invite these on-sell opportunities. Basket Analysis is a great tool to help monitor and analyse the effectiveness of staff, training, and promotional activities. For example, a program could be developed to encourage staff to offer oven-hot blueberry muffins. Without Basket Analysis tools, a sales report might display the ratio of blueberry muffins per 1000 transactions to compare various staff, training programs etc. However, this report will be inappropriately affected by a customer purchasing a dozen blueberry muffins in one transaction. Basket Analysis will provide metrics such as % of hot drink transactions that contain blueberry muffins.

The charts below report on the effectiveness of the training campaign.  The top graph shows that all 6 cashiers are handing similar numbers of transactions with hot drinks.  The training campaign was conducted during week 29. Notice how most cashiers increased their attach rate of muffins by about 30%.  Elizabeth did not change her attach rate (maybe she missed the training) and Barry's attach rate increased, but not as much as the other cashiers.

For the next Basket Analysis post see Basket Analysis III.

Basket Analysis I: What is Basket Analysis

This is planned to be the first blog in a series on Basket Analysis.  In this blog, I will try and answer the question, What is Basket Analysis?

Basket Analysis technology offers an exciting and innovative way of looking at retail data. Simple business intelligence will provide retailers with all sorts of analysis by individual products, including sales by cashiers, customers, profit contribution, waste, discounts, labour, time of day, weather,etc. All this analysis is essential for most retailers in today's market. However, more and more retailers are using the behaviour of shoppers to look for opportunities to improve their operation.

Basket Analysis is about analysing which sales items are bought together (in the one transaction, or different transactions for the same customer).   A Basket Analysis solution will enable you to query and report on which products are bought together and what the profit contribution of all the "baskets" (transactions) that involve a combination of products that are in, or out of, the transaction.

Basket Analysis is not a set of metrics or KPIs from which you can run your business. It is a data solution that allows you to ask questions about the product mix of transactions. Perhaps the most simple question is "Tell me information on the total transaction value by individual product items".   For every sales item, show me the

• Item Average Price
• Item Sales Quantity
• Item Sales Value
• Item Cost of Goods
• Item Gross Profit %
• Item Average Discount
• Transaction Sales (total value of transactions including this item)
• Transaction Count (number of transactions including this item)
• Transaction Average Value
• Transaction Average Quantity (average items/transactions)
• Transaction Average Discount
• Transaction Cost of Goods
• Transaction Gross Profit
• Transaction Gross Profit Percent
• Transaction Value Multiple (total transaction sales/item sales)

Here is an illustration report of that very simple question that might be asked of a Basket Analysis solution.  It is simple, because the analysis is by individual items.  A more sophisticated query would be on particular combinations (existence, or non-existence) of items.  That is for subsequent blogs in this series.

From this simple report there are already many interesting insights available.  For example

• Items that have a low sales item value but high transaction value (Transaction Value Multiple) are items that whilst they contribute very little profit themselves maybe bringing in customers who purchase more profitable items.
• Items with a very low Transaction Value Multiplier, close to 1, are bought alone and probably do not induce customers to buy other products.
• This simple report for one sales item, such as Cheese Cake, by waiter/cashier can give you insights as to which staff/outlets are good at on selling.
• Sales items with the highest Transaction Gross Profit are ones that are likely to be important to your business, even though they may not represent well in top individual sales items.

For the next Basket Analysis post see Basket Analysis II.

Optimizing Basket Analysis

Basket Analysis is the analysis of sales by basket (or transaction). It can be useful in understanding questions such as

• What is the total value of sales for transactions that include products x (and y, and z...)?
• What is the average transaction value for transactions that include products x (and y, and z...)?
• What proportion of transactions include products x (or y or z)? And what proportion of transactions that include products a (and b, and c...) also include products x (and y, and z...)?
• How did the attach rate of product x change during the sales promotion series? And was there a lasting effect.
• Any analysis that requires mix of products within transactions.

Traditionally basket analysis has been achieved by running SQL queries on very large, highly scalable, databases that dynamically summarize by transaction id. You can imagine that these queries involve huge numbers of transaction and item records and really enjoy massively parallel computers. However, it is now possible to run quite effective basket analysis systems using low cost commodity hardware and Microsoft OLAP cubes. Many people do this by creating a cube with a dimension on the transaction id. If you are considering creating such a solution, I have two major suggestions. This has helped me create basket analysis solutions that consume hundreds of millions of transactions in cubes that support relatively fast ad hoc queries (sub 30 seconds).

1. Rather than creating a dimension on transaction id, create a dimension on basket id. A basket is defined by the unique combination of items. So all the transactions that include items m, n, o and p, would be considered the same basket. Essentially, the basket id can be used as a dimension like the transaction id, but the basket it dimension is a fraction of the size. This makes the solution much more scalable and faster. A basket id dimension table can be created quite simply by creating a bit string with one bit representing each item. For example an 8000 byte string will support 64000 items. There is a little bit of math to create the bit string. but essentially each ItemID can create an exponential of 2 to get its position in the string. ie
   POWER(convert(bigint,2),ItemID). Of course, a bigint can only support 2 to the power of 64, so you will need to do this by a series of 8 byte strings. This would be very very tedious SQL to write, but I found that it is quite easy to write a little SQL to generate the full SQL. Also note, there is no need to keep the full 8000 bytes, it can be a varbinary to the right most byte with an item. See below.


2. The second major suggestion I have, is to create a dimension using an identity column from the basket id table (there is no need to have the varbinary(8000) column in the cube, and write calculated measures for the basket analysis. ie. I would resist using the distinct count physical measures in the cube. You really need a Basket dimension, so that you can identify specific combinations of items.

The result is a relatively small cube, with a relatively large basket id dimension. The cube will have two measure groups, one for transactions with transaction count and sale value, while the item measure group will have item sales value and item count measures. Here is the short SQL query that will generate the full query for the basket ids. It is only a few lines of SQL, but it will generate over 1100 lines of SQL that will form the query for calculating the unique basket id. You will want to run this query incrementally, so just put a where clause at the bottom to ensure it is over the high water mark. I would also encourage you to put the query in an SSIS package with an OLEDB call to a procedure to look up the identity column on the basket table, so that can be stored with the transaction. The rest, including building the cube, should be easy.

A complementary application to this basket analysis cube is a data mining model targeted at the relationships between sales items and useful for predicting sales items.

If you are having performance issues with your basket analysis solutions or want help with your application, don't hesitate to ask me to help. This is the type of BI activity I really enjoy.

For real-time demonstrations of OLAP, data mining and related BI technologies see http://EasternMining.com.au/Sites/Demonstrations