Microsoft Dynamics CRM Team Blog: “Poor man’s” Business Intelligence for Microsoft Dynamics CRM

[Blog bot]

Источник: http://blogs.msdn.com/crm/archive/20...amics-crm.aspx
==============

CRM MVP Joel Lindstrom, a Solutions Consultant for CustomerEffective, blogs about BI solutions.

Business Intelligence (BI) is getting a lot of attention these days. It makes sense that companies want to use the data collected by business systems like Microsoft Dynamics CRM to make better decisions, be more effective and impact the bottom line. Technologies from Microsoft, such as SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and Performance Point have made BI more accessible and more affordable.

But where should a company that is just starting out with Microsoft Dynamics CRM start to “dig deeper” into their data? BI is more of a strategy than a technology, and there are several easy, inexpensive ways to analyze CRM data before spending the time and money to roll out a more complex BI platform.

1. “Advanced” Advanced Find. Anyone who has spent much time in Microsoft CRM is familiar with Advanced Find; however, from my experience, most users just tap the surface. Sure, Advanced Find can give you a list of Accounts where the Address 1: State/Province = “NY,” but it also can filter the list by virtually any related entity, so advanced find can be a pretty powerful BI tool. For example, with one advanced find you can get a list of all accounts in NY that have had a phone call in the current month where a contact is named “Frank.”



Advances Find is even more useful now that CRM 4.0 can display fields from other entities in the search results, and take your query into report builder to further enhance the output.

Advanced Find Considerations

As CRM users gain more experience with CRM, they typically come across some questions that Advanced Find alone cannot answer.

a. “NOT” questions—these are queries where you are looking for records where certain data does NOT exist. For example:

• Which contacts have NOT been called in the past 3 weeks?
• Which accounts ordered in 2007 but NOT in 2008?

The issue with “NOT” questions and Advanced Find is that Advanced Find queries the data that is in CRM. With a NOT question, you are looking for data that does NOT exist in CRM. In the case of a one-to-many relationship (such as orders related to accounts), if you search by “does not equal,” you will return any record that has a related record that does not equal the search criteria, even if another related record exists that does equal the search criteria.

For example, say I want to see which accounts ordered in 2007 but did not order in 2008, and I have an account that has two orders in history, one in 2007, and another in 2008. If I do the following advanced find:



This account would be returned because it has a related order that is NOT in 2008, even though it also has an order that IS in 2008.

b. Multi-step ad-hoc analysis—this is where you want to get a data set and slice and dice based on additional criteria. Once you perform an Advanced Find, you can save the query and run it again, or use it as the starting point for additional advanced find queries; however, sometimes there is a need to be able to get a data set and then further refine it based on additional criteria. For example, a sales manager may want to see everybody that has purchased product X, but then remove everyone who has also purchased product Y.

So where do we go when advanced find doesn’t get us there?

2. Marketing Lists—they’re not just for campaigns anymore.

Marketing list functionality in Microsoft CRM takes advanced find to the next level. One really powerful feature of a CRM Marketing List is the ability to not only add members to a list based on an advanced find, but also being able to use additional advanced find queries to further refine the members of the list. You can:

• Add Members
• Remove Members
• Evaluate Members

Say that you want to see a list of Accounts that have ordered in 2007 but have NOT ordered in 2008:

Step 1: Create a new marketing list, selecting Account as the member type



Step 2: Click the Marketing List Members navigation bar link and click the “Manage Members” button.



Step 3: On the Manage Members form, select “Use Advanced Find to Add Members.” In your advanced find, select Accounts where related Order submitted date is after 1/1/2007 and before 12/31/2007. This will return a list of all accounts that ordered in 2007.

Step 4: Click the “Manage Members” button and select “Use Advanced Find to Remove Members.” In your advanced find, select Accounts where related Order submitted date is after 1/1/2008 and before 12/31/2008

The resulting list will include all accounts that ordered in 2007 but did NOT order in 2008. So now say that the sales manager sees the list, and he wants to further refine it to only include those companies that have purchased product X. He can now further refine the marketing list to only include accounts that meet those criteria:

Step 5: Click the “Manage Members” button and select “Use Advanced Find to Evaluate Members.” In your advanced find, select Accounts where the related Order’s related Order Product equals Product X

Evaluate Members compares the Marketing List Members to the results of an advanced find and removes any members from the list that are not returned by the advanced find results. Now the list will include only the accounts that ordered in 2007 but did NOT order in 2008, where the ordered product equals Product X.

Marketing list provide very powerful, yet easy to use out of the box functionality to slice and dice your customer data.

Marketing Lists Considerations

The main limitation of marketing list functionality is that marketing list members can only include Accounts, Contacts, and Leads. You cannot create marketing lists of custom entity records.

3. “Excel”-erate your CRM data

There is good reason why the most widely used business intelligence application in the world in Microsoft Excel. It is affordable, it is ubiquitous, and it is easy to use. And it is also very easy to interface Excel with your CRM data. You can connect directly to the CRM data source and do very complex queries from Excel; however, you don’t have to go that far to analyze your CRM data with Microsoft Excel.

The most basic interface between CRM and Excel is the export to Excel button that is found on any view in Microsoft Dynamics CRM (even on the marketing list we built in #2).



As with Advanced Find and Marketing lists, I have found that many users have a basic understanding of the export to Excel functionality, but don’t see some of the real power of taking your CRM data into Excel.

For example, take the process of territory rationalization. Company X has grown from a small company with 2 salespeople to a larger sales staff. When they started, they didn’t really have well defined territories, but now that they have grown, the sales manager wants to organize the sales rep’s territories by state. To do so, she wants to see where each rep has the biggest concentration of accounts to figure out the best way to draw the territory lines.

She wants to find out which sales representatives have accounts in North Carolina who also have accounts in South Carolina.

Step 1: Export the “Active Accounts” view to Excel, selecting the “Dynamic Pivot Table” option and selecting the following attributes:

• Account Owner
• State/Province

Step 2: Create the pivot table, selecting owner for the rows, State/Province for the columns, and accountid for the Value.



Step 3: Filter the Pivot Table data using Excel’s filtering capabilities. This is somewhat tricky—at first it appears that you cannot filter the columns in the pivot table; however, it can be done.

a. In Excel 2007, select the data tab

b. Click on a cell directly outside of the pivot table

c. Click the advanced filter button. The cell range of your pivot table should auto-populate. If it does not, you can fill it in.

d. Click the filter button—you should now see the excel drop-down filters on each of the pivot table column headers.

See a screencast of how to use advanced filter with pivot tables.

Now the sales manager can filter both the SC and NC column to NOT show blanks, and return a list of every sales rep with accounts in NC and SC. She can then continue to slice and dice the list based on geographic criteria. This is just a simple example—once you get your data into Excel, you can perform very complex analysis of your data.

The point of my post is that you do not have to delay implementing a business intelligence strategy with Microsoft Dynamics CRM if you don’t have the time or resources to invest in a BI platform. I would encourage you to become more familiar with the out of the box analytical capabilities of Microsoft Dynamics CRM. These tools will not satisfy every BI requirement, but they are a great way to quickly dig into CRM data and get some quick wins without having to invest a lot of time, money or resources. They can give you an easy, affordable way to determine what questions you should be asking so you will be ready for a more complex business intelligence platform when the time is right.

Cheers,

Joel Lindstrom

CustomerEffective blog



Источник: http://blogs.msdn.com/crm/archive/20...amics-crm.aspx