Market basket analysis is an analytic technique that aims to uncover the relationships between entities and primarily used with retail data. For instance, a person who buys popcorn and beer in a supermarket on a Friday has a good probability of buying chips as well, because, most likely, he or she is planning and shopping for, say, a movie night. The objective, at the end of the day, is for businesses to use the insights from their own retail data to increase sales and market share by attracting more customers to their retail shop or online shopping portal.

In this post, we’ll take you through a step-by-step process on how one could conduct some basic market basket analytics with Oracle GUI tools and little to no coding.

Oracle Data Miner GUI

If you haven’t spent much time with Oracle Advanced Analytics you might not be familiar with Oracle Data Miner. Oracle Data Miner GUI is shipped as an extension of the Oracle SQL Developer tool. Although focused at the database level, it takes the Oracle BI platform to the next level, as it provides a number of cool features to perform data mining analysis with less effort and time that perhaps building something custom through traditional ETL development which some might revert to for this type of analytical effort.

In the screenshot below the Oracle Data Miner extension for SQL Developer should look familiar to users familiar with the Oracle SQL Developer GUI.

Figure 1: Oracle Data Miner GUI

We are going to use the sample project (refer to the screenshots below) called “Customers R Us” to perform the market basket analysis using the screenshots below to guide you through the steps.

Start by right-clicking on the project and creating a new workflow for Market Basket Analysis. Then follow the steps as outlined below.

Figure 2: Create a workflow for the market basket analysis

1)      First, add a data source to the workflow using the Data Source tool in the Components window on the right-hand side of the editor. As the dataset for this demo, we are going to use the SH dataset, which is usually shipped with the Oracle database instance for demo purposes. Add the SHSALES table in to the workflow window. All fields in the table will be taken so that we can perform analysis in many dimensions. Right-click on the Data Source icon and select View Data to display the data in the table.

Figure 3: Add a data source

2)      Next, add an Association Model to the analysis window, from the Components window. Connect the association model with the data source, which will open the following window. There, you need to specify a transaction as instructed below.

• Select CUST_ID (Customer ID) and TIME_ID (Timespan) as the transaction Id.
• Select PROD_ID (Product ID) as the Item Id.
• Apriori is selected as the default association algorithm. Keep it as it is.

Figure 4: Create an association model

3)      Now right-click on the association and select Run. You will see the following analysis model.

We have gone an extra step here and joined another data source to retrieve the product data from the PRODUCTS table. This way, we can get the detailed information of the products in the market basket.

Figure 5: Run the analysis

4)      Right-click on the “Assoc Build 1” and then click on View Models. It will display the results as shown in the following screenshot.

Figure 6: Outcome of the market basket analysis

Interpreting the results of the market basket analysis

There are almost 8,000 association rules generated from the analysis we did. That’s a lot. So, we can basically interpret the results shown in Data Miner with rationale such as:

“If a customer buys Fly Fishing and 256MB memory card (refer to the first line of the result set), he’s likely to buy Comic Book Heroes. This can be stated with a 74.3% confidence level.”

And, its all about the level of confidence. This is where decisions can be made with the belief that structuring an association on grouping the two or more items together is more likely to be successful.

You can sort the rule result set by confidence level. Each of these rules discovered through the database analytics we just did will help the organization improve how it does business.

You can automate this data mining execution to create a result set and export it to CSV files(s) or store it in a database for immediate retrieval in an ETL process or directly in Oracle BI. This makes the ability to have some near real time data mining activity take place within the organization and potentially even compare business or data mining logic with some level of dynamic variation and control.

Oracle BI, along with its Oracle Data Mining in database functionality is truly Advanced Oracle Analytics that takes business intelligence and your retail data to the next level. You can read more about this concept in our white paper, Achieving Unified Commerce in the Data-Driven Retail Industry.

Datavail is the largest provider of data and database administration (DBA) services in North America. The BI & analytics expertise of Art of BI combined with Datavail’s more than 600 DBAs, analysts, developers and consultants has resulted in a powerful resource for your integrations, implementations, and upgrades across the Oracle stack, including Hyperion, OBIEE, OBIA, GoldenGate, Weblogic and more.

Screenshots attributed to In-Database Data Mining for Retail Market Basket Analysis Using Oracle Advanced Analytics