Commodity recommendation using association rules of SQL Server Analysis Services data mining (IV)

If you have a shopping website, how do you recommend products to your customers? This function is available on many e-commerce websites. You can easily build similar functions through the data mining feature of SQL Server Analysis Services.

 

 

This article is independent of the first three articles. It mainly introduces how to use Excel data mining to independently implement the product recommendation function. It will be demonstrated through the sample data provided by Microsoft.

 

 

Excel is a familiar tool, and it is one of the components in Microsoft Office. In the Baidu encyclopedia, you can find the following description:

Excel is an important part of Microsoft Office software. It can process, analyze, and assist in decision-making operations on various data, it is widely used in management, statistics, finance, and other fields.

Data processing and statistical analysis are what we often use Excel to do, while assisting decision-making corresponds to the highest level in business intelligence, that is, the Excel Data Mining function provided by Microsoft.

For data mining in Excel, you need to install a plug-in: SQL server data mining tools add-ins for office.

 

Download the SQL server data mining tools add-ins for Office page:

Http://www.microsoft.com/en-us/download/details.aspx? Id = 29061

The versions used in this article are SQL Server 2012 and office 2010. The download link corresponds to this version.

When downloading, you need to pay attention to the language version, which corresponds to the office version installed on the machine, and whether you have installed 32-bit or 64-bit, all have corresponding versions.

For previous Office 2007 versions, there are corresponding SQL Server versions of 2008 and R2, which can be found at the bottom of the download page.

 

 

After the download and installation are complete, first find the sample data provided by the tool to see what functions can be provided for data mining in Excel.

If you have installed Windows 8, enter the start interface, and then click EXCEL to enter the application search. The second result is usually.

This interface may pop up when you open an Excel file:

Select the second item here. Do not care about the version 2008 information.

Another tool will pop up after clicking:

Click next:

Specify an analytic service instance and click Next:

Here is a prompt. We can see from the prompt that the add-ins in Excel will create a temporary Mining Model in the analysis service library, this model will be automatically deleted after the user closes the connection.

Click next:

Here, we will specify whether to create a new database or an existing database. By default, we will create a new database. Click next:

Here, you must specify permissions for the user. In this demonstration, follow the default settings and click Finish.

The tool automatically creates an analytic Service database and corresponding user permissions. Click Close to close the tool.

Above, the configuration of Excel Data Mining add-ins is complete.

 

 

Go to the opened example Excel, and you can see the sample data of data mining provided in the example:

Select the last associate and shopping basket. Associate rules with the shopping basket.

The sample data in the Associate label is displayed. This tool uses Association Rule Model algorithms based on sample data to discover the purchase rules.

The order number indicates the order number, followed by the product category, product name, and product price.

Pay attention to the ribbon Toolbar of Excel after installing Data Mining add-ins for office:

An additional table tool is provided. Shopping Basket analysis is used here.

First, pay attention to the Connection Tool on the right. The data mining plug-in Excel requires the support of SQL Server Analysis Services. Click it to connect to an Analysis Service.

Click new to create a connection.

Add the Analysis Service to be connected. Select default for catalog name. Click OK to complete the settings.

Click shopping basket analysis.

The tool will pop up later. You need to specify the column information based on the characteristics of the model algorithm.

The transation ID directly corresponds to the order code order number. In the demo, to reduce the number of items, select category directly, and choose Product Price for Item value.

Click Run To start data processing:

After processing, two tabs are automatically generated to record the model analysis results:

In the first tab, some sales bundle suggestions will be provided.

The result is the total bundle sales in the last column, and the mining results are sorted by this column. The information corresponding to the preceding columns is the size of the bound package. For example, if a coffee and companion are used, the package size is 2, and the second column is similar to the support level, that is, in the sample data, the number of products purchased by users is the total price of the bundle.

For example, in the first line, we recommend that you bind road bikesh and helmets. The package size is 2. In the sample data, that is, the historical data, these two products are included in 805 orders. The price of this bundle is 1570.228025, and such products in the sample data are sold for a total of 1264033.56.

Through this report, we can know Which bundle products are worth creating.

On another tab, you can see the recommendation information for the product:

Here we will list several comments that are most recommendable. The information in the last two columns is the same as that in the previous report. The first two columns of information are the given product in sequence and the product most recommended according to the given product.

The following columns show in sequence how many orders contain the given product, and the next column shows how many of the orders that contain the given product contain the recommended product at the same time. The following percentage is the ratio.

 

 

The statistical information provided in the above report is an important reference for us when making sales bundle decisions, and the decisions made by different people are certainly different.

For example, a conservative boss thinks that there are many combinations of helmets and tires and tubes, and the number of helmets purchases is large enough, in order to make the products that are not selling well sell better, for example, if bike racks sells very little, but it is obvious that tires and tubes sell a lot, then I can implement a combination of bike racks and tires and tubes to promote sales.

Another boss may think that the current sales should be hot-selling, so he will definitely vigorously promote the matching scheme like helmets and tires and tubes, because Helmets has been sold well, therefore, we hope to drive sales of tires and tubes.

In short, according to different sales strategies and characteristics, the statistics can provide users with different decision data basis.

 

 

The following error message may appear during data processing:

This error occurs mainly because the Connection Analysis Service fails. Check the role settings of the Analysis Service.

 

 

Summary:

Based on the sample data provided by Microsoft, this article uses EXCEL to implement the shopping basket prediction function. The result is two reports in Excel. Through this article, we believe that you have a general understanding of the sample data required by this mining tool. In subsequent articles, I will conduct a practical operation to demonstrate how to organize data for similar predictive analysis in our actual business.