Use Predictive Analytics from SQL Server 2005, Excel 2007 Data Mining Add-In and Dynamics CRM Data to detect patterns in historical transactional sales order information to predict cross sell opportunities.
By popular demand I have now scripted the process so that you can all benefit from this!
1) Install the Data Mining add – in for Excel 2007 (check that this has installed correctly by starting Excel 2007 and you will see a new section in the ribbon called ‘Data Mining’.
http://www.microsoft.com/sql/technologies/dm/addins.mspx
2) Start Excel 2007
3) Select ‘Data’, ‘From Other Sources’, ‘From Microsoft Query’
4) Select the ‘Microsoft CRM’ data source – If there is not one then create one.
5) Select the following columns from ‘FilteredSalesOrder’
a. salesorderid
b. ordernumber
6) Select the following columns from ‘FilteredSalesOrderDetail’
b. productidname
7) Select ‘Next’
8) Select ‘OK’ – we will manually build the join in the next step
9) Drag and Drop ‘salesorderid’ from ‘FilteredSalesOrder’ to ‘salesorderid’ from ‘FilterSalesOrderDetail’ – this creating the join between the two views so that the sales order header is linked to its corresponding sales order product lines.
10) Return the data to Excel 2007 using the ‘Return Data’ button (Fourth in from the left)
11) Select ‘OK’ on the ‘Import Data’ dialog box
12) Remove the two ‘salesorderid’ columns as we do not need these any more as we have already linked the transactions in ‘Step 9’
13) Filter the ‘ordernumber’ column from ‘A to Z’
14) Select the ‘Data Mining’ column from the Excel 2007 Ribbon and then ‘Associate’ – The ‘Associate’ Algorithm is a mathematical formulae that is built to detect patterns in data and lends itself to detecting patterns in transactional data for cross sell initiatives.
15) Select ‘Next’ from the wizard
16) Select ‘Next from the wizard
17) In this next screen you can change the parameters of the Data Mining Model
To learn more about these parameters please refer to:
http://msdn2.microsoft.com/en-us/library/ms174768.aspx
18) Change the ‘Minimum Support’ to ‘5’ for your first go to ensure you get a result set back, if this also fails perhaps lower this and also the probability. Again to learn more about these parameters use the link above.
19) Select ‘Next’
20) Select ‘Enable Drill Through’
21) Select ‘Finish’
22) View the results – Here we are looking for a high ‘Importance’ and then a high ‘Probability’ so we can see that a rule has been detected where the there is high probability of selling a ‘Road Bottle Cage’ with a ‘Road 150 Red’. Again for more information on the parameters please read the article above. Select ‘Copy to Excel’ to bring the results back to your work book.
23) Select the ‘Dependency Network’ to see the rule set depicted graphically in terms of the associations between products
24) Perhaps now set up a marketing campaign and target those individuals where they have bought one product but not another and track the campaign responses!
Change the type of conversation you have with your customers \ partners \ prospects into one where you can help them increase their bottom line – all of this from utilising their existing investment in Dynamics CRM, SQL Server 2005 and the Office 2007 Productivity Suite – All working better together!
Enjoy! - Philip Rawlinson (UK CRM Technical Specialist)