Data Analysis Using SQL and Excel

Chapter 9. What's in a Shopping Cart? Market Basket Analysis and Association Rules

The previous chapter discussed everything about customer behavior — when, where, how — with one notable exception: what customers purchase. This chapter dives into the detail, looking at the specific products being purchased, to learn both about the customers and the products they are buying. Market basket analysis is the general name for understanding product purchase patterns at the customer level.

Association rules form a big part of market basket analysis. An association rule specifies that certain products are purchased with other products. Historically, the classical example is beer and diapers. The story goes that these are purchased together as young families prepare for the weekend. Although this is a classic example of market basket analysis, association rules were not used to find this “unexpected” pattern, because retailers were already familiar with the purchase trends of the two products.

Association rules can reduce millions of transactions on thousands of items into easy‐to‐understand rules. This chapter introduces the techniques for discovering association rules using SQL. The processing is rather complex, so the queries in this chapter are advanced, sometimes making use of temporary tables to hold intermediate results.

Some data mining software includes algorithms for association rules. However, such software does not provide the flexibility available when using SQL directly. ...