Shopping Basket Analysis with Excel 2007 and SQL Server Data Mining

Hi my name is Mary Brennan I'm a technical writer for Microsoft sequel 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 that are frequently purchased together this information can help you create bundling recommendations design product placement and evaluate the impact on your bottom line to begin select the associate and shopping basket analysis tab and click anywhere inside the table to activate the table analysis tools under the table tools menu select the analyze tab to open the table analysis tools ribbon double click the shopping basket analysis button to launch the wizard 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 website each order would have an order ID or a transaction ID that is associated with one or more purchased items 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 when the wizard finishes analyzing the data it creates two new worksheets shopping basket item groups and shopping basket rules when you click run the reports display in two new worksheets 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 two or more products or order the bundles by average value let's take a look at the first row it tells us about customers who purchase rode bikes and helmets together this result displays first because it's the most valuable 805 customers bundled these two items with an average value per sale of one thousand five hundred and seventy dollars and a total value to the company of more than 1 million two hundred and sixty dollars the second row tells us at five hundred and sixty nine customers bundled mountain bikes with tubes and tires with an average value per sale of two thousand two hundred and eight dollars and a total value to the company of more than 1 million two hundred and fifty thousand dollars this bundle has a higher per sale value than the first but it occurs less frequently and is therefore of less value to the company one way the company can use this information is that when a customer purchases a road bike the website might automatically recommend a helmet and when a customer purchase a purchase as a mountain bike it might automatically recommend tubes and tires one tip before we move on to the next report you may want to select each column and standardize the rendering of numbers so that they all have the same number of digits after the decimal point the shopping basket recommendations 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 then they are likely to purchase tubes and tires the rules can be used to create recommendations each rule has supporting statistics that help you evaluate its potential strengths so that you can make a recommendation only if the rule exceeds a certain probability threshold if we review the report it's interesting that bundling tubes and tires is the highest value recommendation for four different products 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 for additional help with the table analysis tools I recommend viewing the other table analysis tools video tutorials and the help documentation included with the data mining add-ins thank you for viewing this tutorial
YT Stats Views: 0 Likes: 0 Dislikes: 0