Using Market Basket Analysis to increase sales using my 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 customer purchasing habits by analyzing previous purchases to determine items they buy together, the frequency of purchase and the order of purchase.

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, 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’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:

  • Increase sales with marketing and sales promotional campaigns
  • Segmentation of customers to create targeted advertising and marketing campaigns
  • Placement of goods in retail stores, catalog and web sites to increase sales via cross selling or up selling
  • Education of Salespeople
  • Inventory Management

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 Predixion 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],
  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 analysis 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 of 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. clip_image010

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

Until next time, where in part 4 we will be building an Excel 2013 Dashboards with our Microsoft Dynamics GP data.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s