Find Upsell Opportunities With Association Rule Mining and BI

Read time: 7 minutes

Hey there,

Today you'll learn how to apply one of the most powerful "advanced analytics" techniques to BI. And it has doesn't even have something to do with AI (although some people think so).

The technique is called Association Rule Learning and it helps us to identify hidden patterns and relationships between variables - especially in transactional data.

Association rule mining can be applied in so many areas - from retail ("market basket analysis") to web analytics to genomics - you can literally apply it anywhere you have things that occur together given a unique identifier (e.g., website visits given a user session, or items purchased together during a checkout).

We will build a dashboard like this that shows us valuable upsell recommendations for our best-selling products:

Looks fun?

Let's go!

Problem Statement

Suppose we're business analysts working for an e-commerce store that sells all-occasion gifts. (The demo dataset for this use case is taken from the UCI Machine Learning Repositoy.)

As a baseline, we track sales in a BI dashboard that allows us to examine basic descriptive statistics such as total revenue, the number of orders (invoices) over time, or the list of best-selling products:

While this information is already nice to know, it's hard to translate into actionable insights and answer critical business questions such as:

How can we increase sales?

Well, the simplest approach would be to just sell MORE!

And one of the easiest way to sell more is to suggest relevant products to customers who are buying from us anyway.

Here's how we tackle this:

Solution Overview

The architecture of this use case is really simple. We load the data into our BI system such as Power BI and run the association rule mining algorithm here directly from here using a small Python script.

Be aware that for very large datasets (10,000+ transactions) you may want to offload the association rule mining task from the user front-end for better performance, for example using a small web service.

Solution Breakdown

Let's go through this architecture piece by piece.

Data Layer

All we need for this use case is a transactional data set. For association rule mining alone, we would really only need two things:

• The unique identifiers of the things we're interested in (e.g., Item Names or Stock Codes)

• A grouping variable for which we want to find relationships (e.g., InvoiceNo)

However, since we're not only interested in the rules per se but also in the overall store performance, we include some other information in this transactional data set, including:

• Transaction Date

• Order Quantity

• Item Price

This information gives us rich insights into customer buying behavior - but it's not used by the association rule learner.

Here's how our data set look like:

Let's go ahead and load this into Power BI as a single table. For the demo dataset, I applied some data cleaning/preprocessing steps, including

• Filtering transaction to the year 2011 and country "France"

• Ignore all orders with Null values for item names

• Ignore all negative orders (cancellations)

• Capitalize each word for all item names

• Get rid of item color descriptions

• Get Revenue = Quantity * Price

With these steps applied (e.g. using Power Query) let's jump into the analysis layer!

Analysis Layer

To find relevant association rules we need 10 lines of Python code with the help of the 'mlxtend' package.

I'm running the Python script directly from within Power Query - but as mentioned, you could do this outside of the BI tool, using a web service, for example:

The script covers 4 steps:

Step 1: Reshape the data

To bring our data into the right shape for the association rule learning process, we need to group the transaction by our grouping variable (InvoiceNo) and then aggregate by creating a list of the unique item names that appeared in each respective transaction (quantities don't matter here!).

We can now take this "list of lists" and convert it to a one-hot encoded table where the columns are the item names and the rows are the transactions. Each cell is a True/False flag, depending on whether the item appeared in the transaction.

Our data is now ready for the association rule mining algorithm!

Step 2: Run the Apriori algorithm

Apriori is an algorithm that helps us find frequent itemsets in our transactions. The idea is that every single item must have a minimum frequency in the data set (a metric called support) in order to be relevant enough to become a good candidate item for a rule. If an item does not appear often enough in the transactions, then no rule can be created with that item.

Support is simply the frequency of a particular combination of items in the data set.

In our case, I defined a support threshold of 0.05 - meaning that the respective item (or set of items) must have appeared in at least 5% of all transactions to be considered "frequent" enough.

As a result, the Apriori algorithm gives us a table of all item sets that meet our desired threshold. We call this table our frequent itemsets.

Step 3: Get the association rules

We can now look at our frequent itemset and search for association rules.

An association rule can be expressed as "X --> Y" (read: If X then Y), where X can typically be a collection of items and is also called the antecedent (the thing that has already happened). Y is typically just one single item and called the consequent (the thing that typically also happened when X happened).

Bear in mind that this statement cannot be reversed!

For example, there may be many transactions where we could observe Y without observing X. The rule X-->Y just states that whenever we saw X, there was a higher chance that we also saw Y.

The "how much higher" can be quantified by two things:

Confidence gives us the ratio of one itemset to another itemset. It is defined as the total number of transactions with X and Y divided by the total number of transactions with X.

Lift is the ratio of the observed support to the expected support if X and Y were independent.

If you find this definition as confusing as I do, then here's a quick interpretation of this metric:

• If Lift = 1 that means that occurrence of X and Y are independent (there's no rule)

• If Lift > 1 then that refers to the degree of which two occurences are dependent on another ("the gain of seeing Y, given X")

• If Lift < 1 then that means the items are substituting each other (when there's X it's even less likely there's Y)

We typcally use either Lift or Confidence as pruning criteria to extract rules from the frequent itemset. In this example, I was looking for rules where the lift was greater than 1.

The result of this process is a rule table which we can now use to find the most useful ones.

Step 4: Filter the rules

Let's say we get 80 rules from our frequent itemset. The final task:

Find the rules that really matter.

That's something we can now do dynamically in the BI front-end.

All we need to do is to create a relationship between our new rules table and the existing transaction table so the user can filter it:

Let's see how that goes!

User Layer

In the report dashboard, we see the two charts from our baseline - the list of our best-selling products and the total number of orders over time.

Time to plug in our association rules!

We'll create a simple table visual that shows the association rules we've mined from the data set. This table allows us to filter the rules by confidence and lift to find the rules that have the strongest statistical effect:

Seems like people who ordered the Pack of 6 Skull Paper Cups really enjoyed the Skull Paper plates along with it and vice versa!

However, we're not interested in rules that have a high statistical effect, but rather those that have a high business value.

For that, we need to take a look at the total sales volume. For example, the "Pack of 6 Skull Paper Cups" just grossed £262.20 in the whole year - so even if we could drive some upsell here, it wouldn't be a big leverage.

A much better approach is to select our best-selling products and cross-filter the rules accordingly - thanks to our data relationship, this happens automatically!

For example, if we select one of our bestsellers, "Rabbit Night Light", we can see that customers tend to buy the items "Lunch Bag Apple Design" and "Toadstool Led Night Light" along with it. While the expected lift for these two products isn't as high as for other rules, the total monetary effect could still be higher because the Rabbit Night Light product is purchased so frequently.

This leaves us with actionable insights we can use to promote these items along with the bestsellers in our store or create price bundles to further drive sales.

We could also look at the other bestsellers and see if we can find some interesting rules - but I will leave that as an exercise for you.

Here's the final dashboard, which you can download as a PBIX file from the resources below:

Conclusion

Association rule mining can be a powerful tool for identifying hidden patterns and relationships in transactional data, allowing you to make informed decisions about upselling and driving sales.

By combining this technique with business intelligence tools, we can create interactive dashboards that provide valuable insights and recommendations for action.

So if you're looking for ways to increase sales and grow your business, consider adding association rule mining to your analytics toolkit.

Happy mining and see you again next Friday!

Cheers,

Tobias

Resources

• Dataset

• PowerBI File

• Wikipedia: Association Rule Learning

• Tutorial: Association Rule Learning using mlxtend

Want to learn more? Here are 3 ways I could help:

1. Read my book: If you want to further improve your AI/ML skills and apply them to real-world use cases, check out my book AI-Powered Business Intelligence (O'Reilly).

2. Book a meeting: If you want to pick my brain, book a coffee chat with me so we can discuss more details.

3. Follow me: I'm regulary sharing free content on LinkedIn and Twitter.

If you liked this content then check out my book AI-Powered Business Intelligence(O’Reilly). You can read it in full detail here: https://www.aipoweredbi.com