In Business Analysis, MicroStrategy

Do you want fries with that?

In retail, market basket analysis, also called product affinity analysis, provides insight into what customers buy together most often, like pizza with breadsticks, a Bluetooth earpiece for your phone, or the matching hair bow and socks with your daughter’s new dress. And what about the sponge cakes and the red goop on display next to the strawberries in the produce section? Sometimes these items are verbally suggested for us to buy, such as a service plan to protect your laptop, or the fries with your cheeseburger.

Consider this common transaction:

Market Basket Analysis


If we have access to transaction details in our data warehouse with granularity at the item level, we would have one row per line item, so the above transaction would have three rows in the table.  Each line item is for one transaction, so the key is to relate the items by transaction.  The standard report filtering options would need to be extended to include this relationship.  Once the relationship is established, all products sold with the primary product can be shown in the report output with the appropriate time, geography, and other attributes and filters applied.  Metrics can include sales price, cost, revenue, or quantity sold.  These can be reported at the product level, so we would know how many fries were sold and for how much, or they can be aggregated up through the time and geography hierarchies.

As an alternative to a report showing all the products sold with the primary product, we have the option to be prompted on the add-on product or products.  This would enable flexibility for a wide variety of product analyses.  For example, in one analysis, we could report on cheeseburgers with fries, and in another example, we could report on cheeseburgers with a drink.  In addition, we can continue the analysis to report on products sold in trios, such as cheeseburgers, fries, and a drink.


Beyond the Obvious

The true power behind affinity analysis is to unveil market baskets that aren’t as obvious as the cheeseburger and fries. You may recall the diaper and beer association. If people who buy diapers are also likely to buy beer, should the beer be in the baby aisle? Maybe not, but Amazon has quite possibly perfected the art of suggestive selling with their “Customers Who Bought This Item Also Bought” selections and then presenting “Frequently Bought Together” items, already checked with the total calculated and a button to add them all to your cart together. The application could be extended beyond retail, like to Internet searches or social media posts. What words are most often Googled or tweeted together? It could also have its place in the medical world when analyzing symptoms or diagnoses commonly occurring together.

Often times, the add-on products are more profitable than the primary products. Add-ons can likely be very high margin products with primary products breaking even or possibly sold at a loss.  An understanding of customer behavior and patterns in terms of which products sell best together, or have the highest affinity, in combination with the knowledge of the financial value of these products, drives business decisions.  These decisions include when to run specials and coupons for pizza with breadsticks, how to train employees to suggestive sell, how to visually arrange a menu, or what items to display with the strawberries at the grocery store.  Tools like MicroStrategy, when designed properly, enable us with insight into the data behind these decisions, empowering business leaders with the relevant information to drive results.

© 2013 Rita Brasler. 


Recent Posts