[Analytic Block] Affinity Analysis

  • 9 February 2018
  • 1 reply

Userlevel 1

See the code for the block

Other applications:

  • Segmenting products in a 2x2 matrix (top performers, opportunities for bundling, opportunities for promotion, potential to rationalize)

  • Designing optimal product bundles

  • Identifying SKUs to rationalize

Product Segmentation

A classic way of segmenting items in a 2x2 matrix is by classifying them by Average Basket Margin vs Item Frequency (filtering out low-volume items, e.g. with less than $100 of sales per week or month, that add noise to the data).

We’ve added a couple of dotted lines to the image for illustrative purposes. Items in the top right “magic quadrant” are both top-sellers, and drive high-margin attachments (i.e. high rest of basket margin).

Items in the top-left quadrant drive high-margin baskets, but don’t sell very often. These are our loss leaders: items whose volume we want to drive, even if it means taking a hit on their margin, as they drive high-value attachments. These items are top candidates for x% off type promotions.

Items in the bottom-right quadrant are popular items, but that are often cherry-picked and not bought as part of a large basket. We want to use these items as basket volume generators, i.e. use their popularity to drive large overall baskets. These items are top candidates for bundle promotions with other items.

Identifying Valuable Product Bundles

We saw that the item in the bottom right quadrant is a great candidate for a bundle-style promotion. Let’s take a closer look at the product:

The Levi’s Men’s Jeans seem to be an ideal candidate to bundle with another product. In particular, the items we want to encourage customers to buy with the Levi’s Men’s Jeans are those that:

  • Naturally attach well to the Levi’s Men’s Jeans, i.e. that are usually purchased more often with the Levi’s Men’s Jeans than they are on average. This is what we call the attached item’s lift with Levi’s Men’s Jeans

  • Naturally drive high-margin baskets

We can plot this in Looker after filtering on the Levi’s Men’s Jeans:

Diving deeper:

We see that the item on the top right (the Calvin Klein Slim-fit Shirt) would be a great item to offer as a bundle discount with the Levi’s Men’s Jeans. These two items are bought together 1.7 times more together than they are on average — so they go naturally well together.

We also see that, as the Calvin Klein Slim-fit Shirt is high on the y-axis, it drives high-value baskets, so we can make a small sacrifice on margin (e.g. offer 5% off if purchased together) to encourage customers to also buy this item, along with the Levi’s Men’s Jeans. This pair of items would form a profitable bundled offering.

SKU Rationalization

For restaurants going through a menu simplification strategy, or retailers aiming to simplify their inventory, identifying the lowest-value items to remove from our offering is key. These are items which fulfill the following criteria:

  1. Infrequently sold

  2. Are not attachment drivers

  3. Are not loyalty drivers (not often purchased alone or by the same set of customers)

We can also use the Affinity block in Looker to identify such items:

Note that we are starting by filtering our analysis to items that are infrequently purchased (less than once a week over the past year).

The bottom left of the graph shows items that have particularly low purchase frequency (condition 1), and are in small-margin baskets, so are not attachment drivers (condition 2).

We are also applying a “customer loyalty” filter for condition 3, to only look at items for which customers loyal to only that product account for less than 10% of the product’s total orders. The idea is that we do not want to touch items that, if removed, would drive away some customers altogether (those who are fully loyal to this product).

The list of prime candidates for rationalization is shown in the Data section in the screenshot above.

For an older version of this Block, see example here.

1 reply

Hi Kabalan,

Thanks a lot for this. It has been incredibly useful. I made it work but still putting pieces together to understand the code fully. I just wanted to confirm something with regards to the code in the view file: affinity_analysis.view.lkml

On the JOIN in line # 209:

LEFT JOIN ${orders_by_product_loyal_users.SQL_TABLE_NAME} as loy2 ON prop.product_a = loy2.product


Shouldn’t it be?:

LEFT JOIN ${orders_by_product_loyal_users.SQL_TABLE_NAME} as loy2 ON prop.product_b = loy2.product


Thanks again!!