Shopping Basket Analysis Video Tutorial (Data Mining Table Analysis Tool)

Hi, my name is Mary Brennan. I'm a Technical Writer for Microsoft SQL Server. This video will help you get started using the Shopping Basket Analysis tool. The Shopping Basket Analysis tool uses the Microsoft Association Rules algorithm to detect the relationship of items frequently purchased together. This information can help you create bundling recommendations, design product placement, and evaluate the impact on your bottom line.

1. To begin, select the Associate and Shopping Basket Analysis tab and click anywhere inside the table to activate the Table Analysis Tools.

2. Under the Table Tools menu select the Analyze tab to open the Table Analysis Tools ribbon.

3. Double-click Shopping Basket Analysis to launch the wizard.

4. In the Column Selection window, the wizard automatically detects:

   Transaction ID and Item. To use shopping basket analysis, the items that you want to analyze must be related by a transaction ID. For example, if you are analyzing all the orders received through a Web site, each order would have an order ID or transaction ID that is associated with one or more purchased items.

5. Optionally, you can add a column that contains product values. Value-derived metrics are included in the report only if you select a Value column.

6. Click Run. When the wizard finishes analyzing the data, it creates two new worksheets, Shopping Basket Bundled Items and Shopping Basket Recommendations.

1. Open the Shopping Basket Bundled Items report. This report identifies patterns in the data and lists the items that frequently appear together in transactions. It shows you what items customers are buying together and the value to your company.

   You can filter and sort on the columns in the report. For example, you might want to view only those bundles with 2 or more products, or order the bundles by Average Value Per Sale.

2. Let’s take a look at the first row. It tells us about customers who purchase road bikes and helmets together. This result displays first because it is the most valuable. 805 customers bundled these two items with an average value per sale of $1,570 and a total value to AdventureWorks of more than $1,260,000.

   The second row tells us that 569 customers bundled Mountain Bikes with Tubes & Tires with an average value per sale of $2,208 and a total value to AdventureWorks of more than $1,250,000. This bundle has a higher per sale value than the first, but occurs less frequently and is therefore of less value to AdventureWorks.

   One way AdventureWorks can use this information is that when a customer purchases a road bike, the Web site might automatically recommend a helmet. Additionally, when a customer purchases a mountain bike, the Web site might automatically recommend tubes & tires.

You may want to select each column and standardize the rending of numbers so that they all have the same number of digits after the decimal point.

1. Open the Shopping Basket Recommendations report. This report uses the statistics derived from analysis to create rules about how items are related. For example, a rule might be that if customers purchase cleaners, they are highly likely to purchase tires and tubes. The rules can be used to create recommendations. Each rule has supporting statistics that help you evaluate the potential strength of the rule, so that you can make a recommendation only if the rule exceeds a certain probability threshold.

   In reviewing this report, it is interesting that bundling tubes and tires is the highest value recommendation for four different products.

2. For ease of reading the Average Value column, change the number format to two decimal points. Notice that the largest average value is not necessarily the highest recommendation. As in the previous report, bundles are ranked by overall value of sales.