Report on Opportunities Lost to Competitors

There is an out-of-the box relationship between competitors and opportunities in CRM that allows you to track many competitors to any one opportunity.  However, when you close an opportunity as lost to a competitor, you may select one specific competitor within the Close Opportunity window.  This is a different relationship in CRM than competitor to opportunities.

What if you want an all-up view of all your lost opportunities showing the competitors that you lost to?

When trying to get this report, many people naturally turn to advanced find views (AFVs) to try to create a query for this data.  This approach won’t return all the data you need in an all-up Lost Opportunity report with Competitors because you cannot create an AFV on the Opportunity Close Activity.  The closest you can get with AFV is to look for Activities, filter the Activity Type = Opportunity Close, and filter Competitor = [fill in the blank]. 

This only works when filtering one competitor because you cannot display a column for Competitor in this view – there is a 1:N relationship between Activity and Opportunity Close.

Thus, the way to get this view of your data is to create a Report (Workplace –> Reports)using the report wizard.  The report wizard differs from AFV in that it allows you to JOIN data from different data tables (entities) rather than be restricted to lookup fields in 1:N relationships between data tables (entities).  Here’s how to do it:

Go to Workplace –> Reports –> Report Wizard:

Name the report and specify primary and related record types:

Set a filter to return all activities = opportunity close (like I mentioned with the AFV), and Opportunity Close Competitor “contains data”:

Lay out fields in your report.  In this example I’ve included Est. Revenue on the related Opportunity so you can quantify exactly how much revenue was lost to the competitor.  You can summarize by this amount in your report in order to create a chart in the next step:

Add a chart if you want to:

Final Report:

With pie chart or bar chart:

Enjoy.

Posted: Monday, February 16, 2009 10:37 PM by Laura Robinson

SQL 2008 and MSCRM 4.0

Friday, October 03, 2008 David Fronk Dynamic Methods Inc.

---

We successfully completed our internal upgrade to SQL 2008 and it is awesome. We did a complete revamp of our system and moved our CRM server and SQL server to a virtualized 64bit server then upgraded SQL to 2008 and it is great. The speed is fantastic and the move and upgrade were easy. The SQL 2008 upgrade was very easy and we're reaping the benefits. The enhancements to reports alone are worth it from a CRM perspective.

Check out some of the screenshots but if you are familiar with the slick looking reports that Excel 2007 has then you will be familiar with what SQL 2008 now has. There are gauges that Excel doesn't have (to my knowledge) that SQL 2008 has so that's one welcomed addtion.

Also, you can check out the Customer Effective Blog about this as Joel Lindstrom posted on this before SQL 2008 came out. My guess is that Joel has upgraded his system as well but you never know. We did and we're fans.

Here are some screenshots:

David Fronk

Dynamic Methods Inc.

Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 with Microsoft Dynamics CRM

Found on MS CRM Team Blog, by Reuben Krippner

SQL Server 2005 Analysis Services (SSAS) provides tools for data mining with which you can identify rules and patterns in your data, so that you can determine why things happen and predict what will happen in the future—giving you powerful insight that will help your company make better business decisions.

You can use data mining to answer questions such as:

• What will customers buy? What products sell together?
• How can a company predict which customers are at risk for churning?
• Where has the marketplace been, and where is it going?
• How can a business best analyze its Web site usage patterns?
• How can an organization determine the success of a marketing campaign?
• How can businesses prevent poor quality data from entering the system?

In March 2008, Reuben Krippner presented two breakout sessions at Convergence US on Extensibility with Microsoft Dynamics CRM.

During these sessions I covered a number of live demonstrations of how you can extend and integrate Microsoft Dynamics CRM. One of the “big hit” demos involved the use of the SQL Data Mining Add-in for Microsoft Excel 2007 with Microsoft Dynamics CRM. I have packaged up this example as a hands-on-lab for you to try for yourself. It is very important to note that as part of your investment in Microsoft Dynamics CRM the underlying SQL Server 2005 platform provides you with very powerful Analysis Services. Everything that is used as part of this example is included with SQL Server 2005 Standard and Enterprise editions and the Excel add-in is completely free of charge – incredibly powerful and your only investment is time!

For loads of background materials on SQL Server Data Mining go to http://www.microsoft.com/sql/technologies/dm/default.mspx

A few explanatory notes first! As always, the samples are supplied as is with no support or warranty provided by Microsoft. Feel free to take this utilize as you see fit. Special thanks go to Philip Rawlinson from the Microsoft Dynamics CRM UK team who set out the concepts for me to borrow and extend! This utility will work identically with either Microsoft Dynamics CRM 3.0 or 4.0.

Steps to follow:

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

Step 2: Start Excel 2007

Step 3: From the ribbon interface in Excel 2007, select Data à From Other Sources à From Microsoft Query

Step 4: We need to create a data source for Microsoft Dynamics CRM – this connection will point at the Microsoft Dynamics CRM filtered views (more on this later).

Step 5: Select <New Data Source> and click OK.

Step 6: Set the name of your data source to be Microsoft Dynamics CRM. Set the driver to be SQL Server and then click the ‘Connect...’ button.

Step 7: Pick a server from the list provided or enter ‘.’ if this is on the local SQL Server. Click Options >>.

Step 8: Select your CRM database from the Database drop-down list. It should have a name like organizationname_MSCRM. Click OK.

Step 8: Click OK.

Step 8: Click OK.

Step 9: Scroll down the Available tables and columns list and expand FilteredSalesOrder. Select the following columns from FilteredSalesOrder:

• salesorderid
• ordernumber

Step 10: Scroll down the Available tables and columns list and expand FilteredSalesOrder. Select the following columns from FilteredSalesOrder:

• salesorderid
• productidname

Step 11: Click Next>

Step 12: Click OK – we will manually build the join in the next step

Step 13: Drag and drop salesorderid from FilteredSalesOrder to salesorderid from FilterSalesOrderDetail – this creates the join between the two views so that the sales order relates to the individual products purchased as part of that sales order.

Step 14: Click the Return Data button to publish this data to our Excel worksheet.

Step 15: Click OK on the Import Data dialog box

Step 16: Delete the two salesorderid columns in our worksheet as we do not need them anymore. Right-click the column-heading and select Delete.

Step 17: Click the drop-down arrow on the ordernumber column heading and select Sort A to Z

Step 18: Select Data Mining from the Excel 2007 ribbon and then click Associate – The ‘Associate’ Algorithm is a mathematical formulae that is built to detect patterns in data and lends itself to analysis for cross sell initiatives.

Step 19: Click Next in the wizard

Step 20: Click Next in the wizard.

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

Step 22: Change the Minimum Support value to 5 for your first run to ensure you get a result set back (this is the minimum count of similar transactions to include in the model), if this also fails perhaps lower this and also the probability. Again to learn more about these parameters use the link above.

Step 23: Click Next

Step 24: Check the box marked Enable Drill Through and click Finish.

Step 25: View the results – Here we are looking for a high ‘Importance’ and then a high ‘Probability’. In the example here we can see that a rule has been detected where there is high probability of selling a ‘Road Bottle Cage’ with a ‘Road 150 Red’. Again for more information on the parameters please go to the article link above. Click the Copy to Excel button to bring the results back to your Excel worksheet.

Step 26: Click on the Dependency Network tab to see the rule set depicted graphically in terms of the associations between products purchased.

Step 27: With this insight you could now set up a marketing campaign and target those individuals where they have bought one product but not another. The possibilities are endless and this just illustrates the use of one of the data mining algorithms at your disposal, e.g. this could be used in service organisations to find the most common combinations of service issues or where one issue directly causes another.

Have fun with these samples! The world of predictive analytics awaits and is free-of-charge!

Kind regards,
Reuben Krippner
Published Monday, April 28, 2008 7:58 AM