Using Market Basket Analysis to Increase Sales with Microsoft Dynamics GP Data: Part 3 of a 5 Part Series

Market basket analysis, what is it?

Market basket analysis is a data-mining algorithm common to marketing. It identifies customers’ purchasing habits by analyzing previous purchases to determine items they buy together, as well as the frequency and order of their purchases.

Why would I want use it?

Consumer behavior is complex and unpredictable — what appeals to one buyer may not appeal to another. This buying information will enable the retailer to understand the buyer’s needs and rewrite the store’s layout accordingly, to develop cross-promotional programs or even capture new buyers (much like the cross-selling concept). A widely used example of cross-selling on the web with market basket analysis is Amazon.com’s use of “customers who bought book A also bought book B.”

Here are some examples of what market basket analysis can be used to do:

How do I perform Market Basket Analysis on my data?

In Excel 2013, we have two options. Microsoft provides a Data Mining add-in for SQL server 2008 and 2012. The 2012 version can be downloaded from here.

For purposes of this blog post, I will be using Prodixion Software’s Data Mining add-in for Excel 2013 which is located here.

After downloading and installing the add-in, you will need to run the following SQL script in SQL Server Management Studio:
SELECT [SOP_Number]
,[Item_Number]
,[Item_Description]
FROM [view_SOP_Line_Items]
WHERE SOP_Type in (2,3)
ORDER by 1

After you run the SQL script, you can copy the output to Excel by selecting all of the cells and then right clicking in the top corner of the output section and selecting to “Copy with Header” data and paste into Excel.

Next we want to select the Predixion’s “INSIGHT NOW” tab and select the “Shopping Basket Analysis” button.

Predixion provides a wizard to step you through the process. The first window asks you for the data range you wish to analyze and if your data contains headers.

Step two of the wizard asks for Transaction ID, Item and Item Value. Below is a screen shot of how I filled out the required and optional fields in this window.

This kicks off a process that analyzes the data and creates two additional tabs within your Excel spreadsheet. Here we see the Shopping Basket Bundled Items recommendation tab.

This provides a clear association between how current customers are buying our products and how we create promotional campaigns, educate our salespeople on cross-selling opportunities and make changes to our retail layout or web store to increase sales.

In part 4 we will be building an Excel 2013 Dynamic Dashboards with our Microsoft Dynamics GP data.