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

• 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: http://www.microsoft.com/en-us/download/details.aspx?id=29061. For purposes of this blog post, I will be using Predixion Software’s Data Mining add-in for Excel 2013 which is located here: www.predixionsoftware.com/predixion/download.aspx.

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 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.

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.

Install and build a Sales Order Report with Jet Report’s Free Jet Express for GP

 

Part 2 of a 5 part series

In part 1 of this series, we talked about the benefits of Business Intelligence in this competitive environment. With a competitive environment and the down economy, the best way to start is with something free.

What is Jet Express of GP?

It’s a new self-service reporting solution from Jet Reports and it’s available for Microsoft Dynamics GP for Free.

Using Jet Express, business users can create reports in minutes, with no advanced programming skills required.

• Jet Express pulls information directly into Excel from Microsoft Dynamics GP.
• Data in your reports can be refreshed with the click of a button, so you always have up-to-the-minute information.
• You can combine data from any table, view or field that you have access to in Microsoft Dynamics GP and pull this directly into Excel. Then using standard Excel features like PivotTables, charting and custom styles, you can create smart and compelling reports. And with the click of a button, the data in your report is quickly refreshed.

How do I get and install it?

The software installation package can be downloaded from http://jetexpressforgp.jetreports.com/en/index.php. The site also has dozens of pre-built Jet Express reports that you can download to help you get started. Make sure you download for the correct version of MS Office you are running, either 32-bit or 64-bit.

To install the right click on the install package and run as administrator. You will be stepped through the following screens.

Once the installation is complete, stat Excel and click on the “JET” ribbon and then click on “Data Source Settings”

From the window that appears, we will be adding our SQL server name and company database information.

After we have created the connection, we need to build our sales report. Click on the “Table Builder” button in the “JET” ribbon to make the “Table Builder” window appear.

Next we want to select the table or view that we are going to use for our report. I have created a custom view named view_SOP_Line_Items for this blog that I will be using.

Once I select the view I then select the columns that I want to use for my report.

I then click ok and then click on the “Refresh” button on the “JET” ribbon to produce the results in the screen shot below.

From here I want to make the report a little more dynamic for the users that may be viewing it. To do that I insert a pivot table based off of the Jet Express data and then insert Timelines, Slicer and Pivot Charts.

The end result is a refreshable dynamic report.

Wow, that was a lot but well worth it! Hope you enjoy it.

In Part 3, I will the Use Sales Order view used in Part 2, to perform Market Basket Analysis in Excel 2013.