Wednesday, October 7, 2009

Dynamics CRM-GP Adapter is looking to be a Scribe-killer

In my last blog ( I suggested that the CRM-GP integration tool could be a Scribe-killer as it appears to be a generic web service connector which just happens to be locked down to Dynamics CRM and Dynamics GP for now.

Literally days later, the tool was released into the wild ( and the information confirms my suspicions.

The FAQ states:

"The integration is a web service to web service integration. The integration is comprised of an NT Service which binds together web services for both Microsoft Dynamics CRM and Microsoft Dynamics GP to read and write data. The integration service also leverages a transform engine to convert the data between the interfaces to ensure that the data moving between the two systems is compatible and in correct form."

So it's a generic tool for taking data from one web service and feeding it to another, with the ability to manipulate it in between.

The FAQ also tells us:

"Version 1 of the Adapter has been built to integrate Microsoft Dynamics CRM and Microsoft Dynamics ERP applications only."

That's it; confirmation it's going to be used beyond Dynamics GP.

What is really cool is if they allow you to use the tool to connect both ends to GP, you suddenly get a basic workflow engine e.g. when a product is created in one company, push it across the other companies in GP etc.

So is this the end of Scribe? Despite the title, not likely. While there is the opportunity to go beyond Dynamics CRM and the ERP camp I would think this is quite a way off. I think this will significantly damage Scribe in the Dynamics space but outside of this I don't think Microsoft have much incentive to develop the tool at this stage.

Thursday, October 1, 2009

Review of the latest Dynamics CRM Statement of Direction

Microsoft have released their latest Statement of Direction

While this is hardly 'hot off the press', I haven't seen much in the way of review of the content and what it tells us about what is likely to happen in version 4.0 and the elusive version 5.0.

Timeframes for release of version 5.0 ("V.Next")

Back in the August 2008, the proposed release time was "late 2009, early 2010" (, this has now moved to 2010. The rumour is towards the end of 2010 and, if history is anything to go by, December 2010 (v3 was released in December 2005 and v4 was released in December 2007). I'm guessing Christmas break is an excellent motivator for getting software out of the door.


The document makes mention of the free Mobile Express add-on for Dynamics CRM, which is now a standard part of the product and will be part of the rollups from six onwards. For those that want to do things like create new activities in CRM from a mobile device, the document also refers to the TenDigits alliance and the MobileAccess product which now supports Blackberry and Windows Mobile devices ( This is great, I knew about the alliance but not that they had finally changed the product to support Blackberry and Windows Mobile, nice work!


The big news here are the additional sales methodology accelerators that are coming out (Miller Heiman, SPI and TAS). SPI is already out and, as many have discovered is not open source like the other accelerators. It is free to try though.

The document also mentions the second wave of accelerators (Social, Portal and Partner management). All are slated for released Q3 2009. While there is plenty of information in the wild about the Social accelerator, there is very little on the others. SOD comes through with screenshots of the partner management tool, which is essentially for channel partner management.

You can assign opportunities to partners and even have them bid for jobs, if we believe the screenshots.

eService is being improved to allow customers to download documents from CRM, a nice touch. Event Management is being improved to support custom attributes.

Outlook Client
The Outlook client is being upgraded to support Outlook 2010. Synchronization of contacts and activities is apparently being improved (I wonder if they'll fix the address book

This should be out by the end of the year.

GP integration
Back in the days of version 3, there was a connector which allowed invoices and the like to be automatically sent to Dynamics GP. When CRM version 4 came out, a new version of the connector didn't...until now...almost. A connector between Dynamics CRM 4.0 RU5 and Dynamics GP 10 SP4 is slated for release by the end of the year. Two years is one big wait for this so let's hope its worth it.

No word on the other ERP products in the Dynamics stable (NAV, AX, SL) other than this esoteric sentence:

"The system is based on a transformation engine and uses specific adapters to identify source and destination systems".

For a connector specifically built for CRM and GP, an architecture supporting adaptors and automatic system identification seems a bit of an overkill. Could this be Microsoft's Scribe-killer?

Dynamics CRM Online
A question that used to be frequently asked at CRM meetings was "When is CRM Online coming to insert country outside of the USA and Canada here". Few people bother asking over here in Australia any more as local hosting providers have filled the gap. However, Microsoft are extending the reach of CRM Online beyond the borders of the North American continent and apparently going to be 'available in all major international markets mid-2010'. Let the price wars begin...

The SLA for CRM Online has been changed with an uptime guarantee of '3 nines', that is, 99.9% of the time. For the mathematicians, that's 8-9 hours downtime a year.

There is also the lead capture facility for CRM Online which basically sets up a web page for you and feeds the leads directly into Dynamics CRM.

Finally, the next service update of CRM Online (due in November) will give mobile access to CRM Online.

Features of Version 5 ("V.Next")
No big surprises that Microsoft are still holding their cards close to their chest on this one. A few morsels thrown out include:
  • Increased use of 'the ribbon'
  • Reduction in mouse-clicks and form opening to achieve common tasks
  • Users get to define their default views
  • Improvements in the KnowledgeBase Article editor and the ability to attach documents
  • Specifics were vague but the team concept in CRM seems to be getting a bit of a working over in the new version. There are already whispers of team-ownership of records in version 5 so this should be an interesting area to watch
  • 'Fixing' of email communications i.e. allowing attachments to outbound emails in areas other than workflow
  • Recurring activity support
  • Creation of a decent package management system for bundling customisations of the product which they describe as working like the Add/Remove programs feature of Windows
  • Restructuring to allow for plugins/custom workflows to run on the host server securely. Something very useful for hosting environments where, currently, a plugin has the potential to reach across all hosted organisations
  • Authentication through Windows Identity Foundation (WIF)
  • Auditing 'out of the box'
  • Improvements to the use of WWF (workflow)
  • Global and hierarchical picklists
  • Improved Visual Studio integration (could it be we get something more than a notepad clone for creating jscript?)

Interesting times ahead and it seems enough 'new shiny things' to keep us going until we see something more substantial on version 5.

Friday, September 4, 2009

Why Loyalty Programs Might be a Necessary Evil

At the start of this year I wrote a blog post on why loyalty programs offer little benefit to businesses (

This is still true, however, last night I saw a presentation by Don Peppers. Don is something of a known figure in the CRM arena. In this case I mean CRM in a marketing sense i.e. getting to know your customer better and interacting with them appropriately as opposed to CRM software. He doesn't have a wiki page to point to but you can see what he is about at his Twitter page (

Anyhow, his talk was primarily talking about loyalty programs and while he agreed they do not offer sustainable competitive advantage in themselves, he did provide a reason to have a loyalty program I had not previously considered.

If I'm in retail I have one big problem in getting to know my customer. While I know what goods leave the store and I know what money I receive for them, I have no idea who bought a specific item and why. This is a problem if I'm going to better understand the kinds of customers I have and how I can better anticipate their needs.

In comes the loyalty program. By handing over my personal details and swiping it when I buy my goods, the retailer now bridges the missing link. They know who I am and what I've bought. They can now build up a historical profile of purchases, repeat purchases, link it to my demographic data etc. etc.

Here is the thing, if the only purpose of a loyalty program is to link a customer to their purchases and to review their behaviour to better understand them, is there an alternative which may not be as costly but could also deliver sustainable competitive advantage within itself?

In my 'What is CRM?' post, I talk of the ultimate grocery store ( that knows your shopping list and puts the shopping in your car before you've even entered the store. Let's take it a step further. Here in Australia, the two major supermarkets offer home delivery. Unfortunately, they charge for the service, the range of goods is not as wide as in-store, the quality of the fresh produce is not as good as in store and in some cases they charge a premium of 10-20% on top of the in-store price. There is not much incentive to use home shopping.

However, if I register for home delivery, I provide all the details I provide for a loyalty card and the store can link it to my purchasing habits. They also have my physical address, not my postal address. No need for another card in my wallet, no need for the retailer to keep track of points and keeping money on their books to cover their value and no need to reinvent the wheel.

Delivery could be set up so that every week/fortnight/month the same goods are delivered to your house and the money automatically deducted from your bank account. It works for newspapers and public utilities, why not for groceries? Why can't groceries become another utility?

I raised this question in the talk and the response was essentially "not everyone wants home delivery". Really? People prefer to leave the house to crawl around a supermarket for 1-2 hours suffering screaming children (theirs or someone else's) and to haul a stack of shopping in and out of their car?

I asked my wife, who is in marketing. Her response was "not everyone is online, nannas wouldn't use it". Really? The elderly would prefer to haul a shopping trolley and shopping around rather than have it come to their house like the milkman used to? Sure, not everyone is online but the store could provide terminals where people could set up their shopping lists and deliveries. They could even have friendly staff to help those unfamiliar or unable to operate a computer.

What about the cost? Well the first response is how does the cost compare to a loyalty program? Even if a loyalty program is cheaper, there are considerations of improved inventory management from knowing the monthly purchasing requirements of your customers and knowing precisely which goods they want (if someone walks into your store and make a purchase, you do not know what they wanted, only what they bought from what was available). Arguably stores would not need to be as large or hold as much stock as they would only be catering to 'incidental shoppers' with the bulk shoppers taking the easy option. This provides savings in wages, rental and general store running costs.

A delivery service may not be the answer, but it should not be assumed that a loyalty program is the best fit for all businesses. Ultimately, the answer should be as close as possible to a system that captures the information a business needs, provides perceived benefit to the customer and is sustainable.

Wednesday, August 19, 2009

Codeplex hosting links to non-open source projects

Imagine my excitement to see the new Sales Performance International (SPI) Accelerator finally released on Codeplex ( Well a pdf pointing to it, at least.

I thought it a bit strange that I had to register with some mob called Mondo to get the software but there it is. The zip file you get contains an installer and some pdfs but that's about it. As it turns out the free version is a 3-user version with the full version coming at a cost.

So, here's the problem. The CRM Accelerators are created as 'springboards' for further development. To quote the accelerator home page (

'all samples are supplied with full source-code so they can be extended further to meet specific customer requirements'.

Moreover, the requirements for hosting a project on codeplex are (

  1. You must choose a license for your project (license resources: Open Source License page on Wikipedia)
  2. It must be an ongoing project (no "abandoned" projects)
  3. It must have source code (no non-software projects)

The codeplex page ( has no source code, so I emailed Mondo asking for the source code and got the following response from one of their directors:



Thank you for your question.

Our vision is to begin to drive sales effectiveness through day-in-the-life activities of sales professionals and managers by combining sales process, the supporting methods and tools, overlaid in a CRM system with formal training and informal knowledge management.
We sell the accelerator (full version) through a community of dedicated partners, but decided to make a free version with limited functionality available to the Microsoft community to generate excitement and awareness around the Microsoft accelerators.
There are Sales Performance International, SPI, (the company that owns the Solution Selling ® methodology), intellectual property rights that we have to respect and we have an R&D investment that we have to protect. Thus, unfortunately, we can’t provide you with a copy of the source code.

Please let us know should you have any questions.



I replied asking if the placing of the add-on on Codeplex is some sort of oversight and got the following.


I see your point. However, the decision was made and driven together with Microsoft who is hosting the site. I think it is a great opportunity for companies who want an introduction to the Solution Selling ® process and the MSCRM accelerators in general that they can download a whitepaper that talks about the opportunities in this area. The software itself is not available for download on the codeplex site – although the whitepaper is. To download the software you have to register for your trial copy at the Mondo website (

If you are interested we can talk about an arrangement for the Accelerator for you and your organization for for example extending it or otherwise modifying it.


So the point of view of Mondo and, by implication Microsoft is that the 'project' being hosted on Codeplex is the whitepaper and not the software and therefore it is fair use. This being said, even the whitepaper has restrictions on its reproduction as stated at the end of the document.

Addendum 23 August 2009:
After community feedback, Microsoft has decided to move the SPI accelerator from CodePlex to a new home:

See Microsoft's Reuben Krippner's comment below for the details. I think this is a great testament to the attitude of the modern Microsoft culture, considering and responding to the concerns of a passionate community.

Friday, August 14, 2009

Handy links for non-technical CRM administrators

I was asked to put this together for work and thought I'd also post it here for reference. If you manage a Dynamics CRM implementation and want to keep track of the latest developments/add-ons etc. these should keep you on the right path.

There are plenty of other, excellent resources out there but I've chosen these specifically because their content is not necessarily focused at developers but more at general administrators/managers.

There is a bias towards Australian resources, but even folk outside of the antipodes should get some value from these.

In person:

Sydney CRM User Group (SCRMUG):

Hosted by CRM MVP Guy riddle and generally held once a month. Always feature a roundup of the latest news in the world of Dynamics CRM


Twitter feeds:


@ SanjayJain369





Catherine Eibner (

Guy Riddle (

Menno te Koppele (

Dynamics CRM in Australia (

Dynamics CRM Team (

Dynamics CRM UK (


Dynamics CRM 4.0 Resource Center (

Thursday, July 30, 2009

The Microsoft Beast Stirs

An interesting confluence of events brings me to the conclusion that Microsoft has finally stirred from its slumber in relation to Google.

On the Google side we have, amongst others, the following:
A number of these directly hit at Microsoft. Google's online applications closely mirror Microsoft's Office, the OS, based on Linux will directly compete with Windows. Google Chrome is awesome although pages with .Net code behind them seem to have trouble rendering...

To date, Microsoft, in my opinion, hasn't really done too much or at least not too much of consequence.

However, this has now changed. Firstly, Microsoft has announced it will be taking Office online ( Google apps has frankly always underwhelmed me. While the concept is great the functionality was simply never there for me and hasn't evolved too much over time. I know of no Windows-based workplace which has abandoned Office to use Google Apps. Hopefully Google will now step up to the plate and actually provide something equivalent to Word and Excel.

Putting Office in the cloud is a passing shot across the bow of S.S. Google. The really smart play is getting their search engine right. Microsoft have recently changed their search engine from Live Search to Bing ( I'm still in the Google search engine camp because, frankly, it returns better results but let's assume Microsoft get it right. People will start using Bing instead of Google and suddenly no more Google Adverts in search. Based on Google's last annual report, ad revenue was about 30% of their business. That's a big hit to take. Sure there will be Google ads in web pages but I know I happily ignore them and many browsers remove them for you.

The latest Microsoft-Yahoo co-venture ( is the rallying of the troops and providing a potentially viable alternative to the Google search-ad business model. I'm expecting price wars galore, FUD campaigns and wars of attrition. Exciting times.

If you think Google search is too big to be toppled, cast your mind back to the days of Mosaic and Netscape Navigator ( There was a time when Mosaic was the only browser of choice for the internet. Now its a distant memory. For a more recent example, remember MySpace or GeoCities (

The only thing that is inevitable is change and that the victors write the history books.

Sunday, March 29, 2009

Sales Pipeline Management 101 and How CRM Can Help

Recently I was at a two-day conference talking about the slowing economy, business opportunities and the like. It was basically 16 hours of presentations with people offering a few insightful tips and advertising their services.

One of the great takeaways I got was the 'pipeline formula' from Simon Harris of ActionCOACH. I'm sure I'd seen it before, and it is in various places on the internet, but it is nonetheless a great little tool:

Number of Leads
Conversion Rate
Number of Customers
Number of Transactions
Average Sale Value

Most people are obsessed with the bold factors (Customer numbers, Revenues and Profit) and often people implement CRM systems thinking they will build it and one of these will come. Here is the secret; you can do nothing about the bold factors but you can do lots about the rest. Moreover, CRM systems help you track and measure all the non-bold factors. This is how Microsoft Dynamics CRM does it...

Number of Leads
Dynamics CRM has a lead record out of the box. Here you can upload all your generated leads or enter them on the fly

Conversion Rate
At the press of a button, a user can convert a lead to a sales opportunity, contact and company. Also as every record has a status associated to it in Dynamics CRM, tracking conversion rates is simply a case of counting those with a successful status against those with an unsuccessful one.

Number of Transactions
Dynamics CRM tracks your sales opportunities until they close. Looking at won sales opportunities, over a given period, we see how many sales have been made.

Average Sale Value
The value of a given sales opportunity in Dynamics CRM is either entered manually by the user or uses the products and services, also stored in Dynamics CRM, to determine the value. Therefore tracking the average sale value over a period is simply a case of averaging the value of all the closed and won opportunities over that period.

For a given product record, Dynamics CRM allows a user to store the cost of the product or service as well as the sales price. Therefore it is easy to work out the margin of a product or, for an opportunity with products attached, the margin on the opportunity.

So there you have it. If you want to improve your pipeline, consider the 'pipeline formula'. If you want to track how well you're doing, get a CRM system like Dynamics CRM.

Friday, March 27, 2009

Take profits in Salesforce? Unlikely

Those wise analysts at Morgan Stanley have suggested investors 'take profits' from their shares.

This is a nice way to say 'sell out'. Here is the thing, unless you'd bought your Salesforce shares before 2006 (three years ago) you wouldn't have any profits to take. Apart from a brief period in late 2006, any other buyers will be selling at a loss.

Where were these analysts back in September 2008 when the financials screamed 'overvalued':

or at the height of the bubble around three months before this? The financial reports were the same and yet the so-called experts felt $70/share for Salesforce was great value.

The analysts will blame the economic downturn but as can be seen in in the blog post above and in this one, the financials of Salesforce have been following a steady path for the last five years.

Moral of the story? Understand analyst motivation. It is not always to provide impartial advice to the market for the betterment of the average Joe investor.

Sunday, March 8, 2009

All Aboard the Cluetrain, its been waiting at the station for 10 years

"Markets are conversations"
"These networked conversations are enabling powerful new forms of social organization and knowledge exchange to emerge."

"To traditional corporations, networked conversations may appear confused, may sound confusing."

The above perfectly describe what we now see through technologies such as Facebook and Twitter. These quotes also describe the challenge corporations are having in working out how to interact with the new phenomena of 'social networking' and how to converse with their customers in these new channels. The Skittles Experiment is an excellent example of how corporations are jumping into the new pool to see if they can swim or not.

The 'gurus' now talk about authenticity in corporate messaging if communication in these new channels is to be successful.

Here is the thing. Social networking is not new. The three quotes at the top of this post come from The Cluetrain Manifesto, written ten years ago before Facebook, before Twitter and before the term 'Social Networking' was probably even coined. The Cluetrain strongly advocates corporations to interact with customers as if they are human beings and not demographic cross-sections of society.

Here is the next bit of good news. The Cluetrain Manifesto is available to read on the Internet for free, here it is. To understand how to speak to customers or see how social networking had its seeds in the world we now call Web 1.0, read the Cluetrain Manifesto.

Self-hosting Azure and removing the fear of the cloud

A very interesting article I came across outlining Microsoft's vision for their Azure technology:

Just like CRM can be provided as a service by hosting providers outside of Microsoft, so will be true of Azure. This is fantastic in terms of empowering organisations or even governments.

Imagine you have a series of embassies around the world. A government would not want to host constituent or public servant data in the cloud on someone else's servers but now they could do it all themselves. They achieve the power of the cloud but maintain the security, or perceived security, of keeping the data on their own boxes.

Where I can also see this being used is with companies with offices on the USA west coast as well as offices in London. This puts the time difference at around 8 hours, perfect for a cloud server to run at a sustained level of use for 16 hours a day, rather than running two servers at capacity for 8 hours per day each.

Microsoft's overall strategy is very smart in that it allows people who are nervous about jumping into the cloud to dip their toe in the water on their own boxes first. When they get used to it, assuming it will be cheaper to do the same on Microsoft's offering than someone else's, they will then fully embrace the Microsoft Azure offering on Microsoft servers. Any perceived security fears will be outweighed by the economic considerations. 

This lower of the barriers to entry and is a significant market differentiator in my opinion. For, Amazon's offering, and so on, you either embrace their servers or you don't. While the more technically savvy will have no issue with the jump, often the decision makers are less technically proficient and a lot more cautious. A DIY cloud is the perfect compromise.

Saturday, February 28, 2009

XML Notepad: It just works

Up until this week, when I needed to hack an XML.Config or Sitemap file, I pulled out WordPad and went to town. After CRM complained about the file not being valid for a couple of goes I normally figured out where I'd gone wrong and all was good.

I thought there had to be a better way so I googled for xml editors and up popped XML Notepad.

It just works. If you have to mess about shuffling bits of XML around, it does the job nicely. revisited. Still an overvalued stock?

Back in September I reviewed some of the financial measures behind . See here for details:

Then in October I predicted the stock would hit the twenties from $58 the month before and $36.50 at the time of the post. See here:

So with Salesforce releasing their results for 2008, I thought it would be time to revisit.

Still worth a bottle of beer?
In October I asserted the stock was worth the price of a bottle of beer ($3.55). So what about now? Well I've revised my estimate up to, at best, $10 but this is still a lot below its current level of $28. Why the low valuation? Read on.

P/E Ratio
By my calculations, the P/E of Salesforce is running at around 100. This means if you invest $1, it will take, at the current performance levels, 100 years to double your money. Ouch. Short of a big turnaround in profitability, this isn't going to improve any time soon unless the stock goes cheaper.

Tangible Book Value
To their credit, Salesforce has increase the tangible book value per share. This is a measure of how much you'd get if Salesforce did a fire sale, based on the selling of their assets. The value has gone from $3.55 to $4.88 . Still well below the share price of $28

Revenue Growth
Finally we saw that billion dollar revenue we had been hearing about for so long. Well done Salesforce. However,  with a good economy and few rivals in the SaaS space, Salesforce manages to slow its revenue growth down by 10% each year and 2008 was no exception. The series as per my September post was (2005-2008) 84%, 76%, 60%, 51%. And their latest revenue growth figure? 44%. Nothing changing here. 2010 should give us growth in the 30s at best and I'm thinking maybe in the 20s now that Dynamics CRM is in the space and engaged in a price war and the economy is so poor. 

As per my September post, Salesforce consistently has single digit pre-tax margins. That is, they make less than 10% profit on sales. The latest result? 6%, the same as the previous year and actually a bit less if we look a few decimal places in. Nothing remarkable here and certainly no big turnaround needed to bring the P/E ratio to sane levels.

Free Cash Flow still in the negatives and a little worse than the previous year. Cash is king. No cash, no business.

Sustainable Growth
This has increased, based on the latest numbers to around 11% but still far short of their actual revenue growth so more borrowing or share issues will be in order.

A lot of the praise for Salesforce regarding their latest results focuses on their revenue growth. Revenue is not everything and their ability to generate profitability and cash is not improving significantly. For every dollar in increased revenue, costs are increasing at the same rate. Don't expect this stock to leap up any time soon and I think it will still continue its journey towards $20 and perhaps go past.

Tuesday, February 24, 2009

Sacking Unprofitable Customers and CRM

An emerging paradigm in the business management world is that of sacking unprofitable customers and on the surface it makes sense. If I have a customer who costs me more in non-chargeable action than they do in chargeable action then in keeping them I'm losing money.

However, I get nervous with this view. Here are a few reasons why:

How are you measuring your costs?
A typical example of an unprofitable customer is they buy small value items and then absorb lots of time getting support from the helpdesk, tantamount to training. On the surface, this customer is unprofitable. They bought an item for, say, $1000 and every month they take an hour of the helpdesk's time as new users phone up to get help on using the product. Whatever value we put on the hourly rate of the support staff member, the maths doesn't look good.

And therein lies the flaw. If you are going to use cost allocation arguments to justify the 'value' of a client, make sure the allocation makes sense. Ultimately cost allocation is an accountant's trick to generate a number for the profitability of a component of a business; in this case, the customer. A fundamental assumption of most cost allocation models employed is the business is running at full capacity.

Its absolutely true that the helpdesk function costs money to run. However, if I have one customer call one day and two the next, my helpdesk costs are the same but the one customer does not cost twice as much to service as the two that called the next day.

In other words, if the helpdesk has capacity, why not help a struggling client? It will generate a lot more goodwill with the client than playing minesweeper. Of course if the business is looking to reduce costs by downsizing the helpdesk, then sacking the squeakiest wheels makes sense as this will reduce demand on the helpdesk staff, as long as it is done correctly.

Word of Mouth and Rising Stars
There are companies that screen incoming calls, determine the 'value' of the client through questions and if it is too low, route them to a low priority queue. The idea being that the 'low value' client will either go onto the internet (a low cost alternative) or go somewhere else.

If our helpdesk is close to capacity, we obviously want to service our profitable clients (assuming we have a valid measure for such a thing) to retain them and if we happen to lose a few less profitable ones along the way, so be it.

The problem I see here is twofold. First of all, a 'costly' client today may not be costly in the future. In our previous example, the staff, who often phone up the helpdesk for assistance may, after a time, become intimate with the product and strong advocates, internally promoting the purchase of new versions or add-on accessories.

They will go onto forums, their blog, twitter etc. and sing the praises of the product's functionality (which, in reality, is probably no different to the competitor's but they just know how to use it) and they will also move onto other companies and encourage them to use the same product.

By pushing people into long queues or onto often poorly designed support web pages with poorly designed knowledge base search engines, client adoption suffers and no one will sing the praises of the excellent post-sales support or product functionality. If you are going to try and make a customer more 'profitable' make sure the methods you use and the experience of the customer do not reflect poorly on the business. Otherwise, word of mouth suffers, customer upskilling suffers and a client who is a dog today will never become the star they could have been.

Don't get me wrong, I've known plenty of 'tire kickers' in my time and I still have a few lurking around but if I have the capacity to assist them or direct them to useful information, it costs me nothing more and there is the chance they will either promote me to others or engage with me later down the track.

Routing customers to long queues is disrespectful and offensive but offering an excellent support site with an intelligent layout and functioning knowledge base search engine or wiki is an excellent way to service customers at a reduced cost. Even better, offer up a forum where customers can interact with each other and support themselves. Not only will the customers get access to a vast body of knowledge, you get frank and honest opinions on your products and where they can be improved.

When is a Good Time to Cull and How to do it?
A great time to cull is when you are over capacity in servicing clients. At this point you have two options; either increase supply or reduce demand. Increasing supply, in most cases, means growing the business. Reducing demand essentially means getting rid of clients.

In my case, I currently work for myself. My goal is to obtain 40 hours of billable work each week. Let's say we were not living in tough economic times and I was finding it very easy to pick up 80 hours of work a week if I chose. What should I do? I don't want to work 16 hours a day and if I hire someone else to pick up the additional work (growing the business) I have the worries of salaries, government red tape and, in my opinion, the most important aspect, reputation management. If I screw up, I only have myself to blame, if someone else screws up, I might not be to blame but it is my reputation that gets damaged.

So my strategy would be to reduce demand. The simplest way to do this is to raise prices and there is nothing wrong with doing it. For some reason people attach a stigma for charging more for a desirable service. I know many businesses that, despite inflation eating into their bottom line, refuse to put up prices even modestly out of fear of a mass exodus of their customer base.

Another option would be to reduce your service offering to the most profitable services. In doing this, you must be absolutely sure removing one service will not damage another. A classic example is the grocery store that refused to meet the demands of a large soft drink company and subsequently refused to stock their product on their shelves. What they realised after the fact was that people were not interested in shopping there unless they carried that particular soft drink. The store may have had thousands of products but in removing just one, they compromised the sales of all the others.

If you are planning to reduce your service offering, look after those customers left out in the cold. Find someone who can meet their needs who have a good reputation. Nothing is louder than a disgruntled customer with an internet connection.

How Do you Ensure you Don't Obtain Unprofitable Customers to Begin With?
Obviously this depends on how customers are generated in your business but in the case of a sales team, make sure the incentives drive the behaviour you're after. If you are rewarding your sales team for bringing in new customers, of course they are going to cast their nets far and wide. If you reward them based on an increasing commission rate, based on the size of a given deal, not only will the salesforce go for larger customers, they will also encourage smaller customers to bundle orders together.

In short, be smart and think about how you would 'work the incentives' if you were in your employees' shoes. This also applies to support staff. If you are rewarding for the shortest calls possible, your staff will pick up and hang up on every second call to reduce their average call times. If your support staff are there to generate satisfied customers, ensure their incentives drive this behaviour. Being hung up on three times in a row (its happened to me) does not give me a warm fuzzy feeling about the company I'm trying to talk to.

Where Does CRM Fit in All of This?
The profitability of a customer is determined by their requirement for chargeable and unchargeable action in regards to your company. An accounting system generally only considers the chargeable actions i.e. goods purchased, service plans obtained etc. but it does not measure the unchargeable sales visits, support call volumes etc.

Most CRM systems have three primary modules:
  • Marketing (the activities performed to generate demand)
  • Sales (the activities performed to generate sales)
  • Support (the activities performed to keep customers satisfied post-sale)
These three modules cover a good fraction of the effort undertaken by a company in obtaining customers, selling to customers and maintaining customer satisfaction. If set up correctly, the chargeable and non-chargeable activities can easily be separated out and compared. While I am sceptical that the full costs of a customer can be accurately measured, given a company is rarely running at capacity, if any system can do it, it is the CRM system and certainly a correctly configured CRM system will be able to tell you the customers who are likely to be less profitable than the others in terms of the ratio of chargeable and non-chargeable time allocated to them.

More importantly, it will show you the nature of the activities they are undertaking. With this information, you can look to change their behaviour in a respectful way and make then profitable. alternatively you may find that the reason for their activities is a fundamental problem with your business. This article on Sprint's 'firing' of 1000 customers highlights the point.

Putting customers into little boxes of profitability is a complex and potentially dangerous activity. If you feel it is appropriate, make sure you have as much information as possible on your customers and their interactions with your business, as can be provided by a CRM system. Otherwise you could end up being the most efficiently run business in the world; one with no customers.

Monday, February 23, 2009

Push and Pull Marketing, CRM and Social Networking

What is Push and Pull Marketing?

Marketers often talk in terms of 'Push Marketing' and 'Pull Marketing' when describing their marketing communication. For the rest of us this is what they are talking about:

To summarize, if we think of a cola manufacturer who sells their product through independent cafes, if I give the cafes a larger margin on each can of cola, the cafe will 'push' the product more. If I do a promotion where every can of cola has a chance to win $10,000 and promote this on bill boards etc. the customer will demand the cola from the cafe.

So, at a really high level, 'Push Marketing' is where the seller tries to convince the customer to buy the product whereas 'Pull Marketing' is where the customer tries to convince the seller to sell the product to them.

The distinction gets murkier when there is no intermediate distributor as any direct conversation with the customer will have push and pull elements. For example:

Salesperson: If you buy today, you get 50% off (push in that the seller is trying to convince, although conventionally defined as pull as it has a 'call to action' for the customer)
Customer: I definitely need one but it has to be white. I need you to sell me one in white at the same discount (pull)
Salesperson: OK I'll sell you one in white at the discount, if you also buy a second one at the same price (push/pull)
Customer: Deal

CRM as a Push Marketing Tool

CRM systems are primarily built for push marketing. Here is why.

The traditional foundation of CRM systems is Sales Force Automation (SFA). That is, it manages the activities of the sales folk and the resulting sales opportunities generated therein. As time passed, CRM systems incorporated Marketing and Support modules. Generally the marketing module is focused around mass communication (mail merges) to the contacts in the CRM system and the support module was there to allow the support staff to manage their activities in regards to resolving customer issues once the sales team had sold a product to a customer.

The big problem with this is the CRM system is focused on who the individual users immediately speak with. In our cola example, the sales folk speak to the cafes. They populate the database with cafe contacts. The marketing team, if they are to use the contacts in the system, will be focused on push marketing, as defined above. There is nothing stopping the marketers engaging in pull marketing but the CRM system is going to be of limited help except in maybe managing high-level costs and activities. 

Support users are more interesting. In the case above, the support team, presumably would be speaking primarily to cafe owners but it is equally possible that they would speak primarily with customers, depending on the type of business. For example, in the case of a whitegoods manufacturer, the people phoning them up could well be the end user rather than the department store.

The support users will therefore be populating CRM with either the same cafe contacts we already have or end users who have already purchased and had reason to call the support desk. There is the possibility of pull marketing here as the marketers could target the end users in the CRM system the support staff have entered but it is also fair to assume that the most satisfied end users and therefore those most likely to be upsold to would be those that have not contacted support to resolve an issue and are unreachable through the CRM system. 

We could market to the 'unknowables' as a whole, but this is the antithesis of CRM where the relationship is king.

How Can We Make CRM Systems More 'Pull Friendly'?

Traditionally, it would have been very hard to achieve this. The body of unknowable end users would be difficult to access without a distributor giving up their customer lists. While this is possible it involves all sorts of privacy and logistical implications.

However, in the new world end user buyers advertise the fact on the internet all the time and communicating with them directly or indirectly could not be easier. In a world of forums, blogs, twitters, facebook groups and so on we not only have individuals telling us what they have bought, their thoughts on it and what they would like to buy next, they also cluster together to share thoughts making them an even easier target.

These conversations, interactions and clusterings is what Brent Leary refers to as 'Social CRM': has started to get the idea on how to tap this with their service cloud:

but this is merely support-focused, not sales-focused.

Imagine the potential of tapping into all the conversations going on regarding your product. 
  • Who is 'product-curious'? 
  • How to improve it? 
  • What products work well with it? 
  • What the next generation of it should contain? 
Imagine doing the same for your competitors products. Its all out there but requires effort to sift through, manage and interact with.

CRM needs to be able to tap this limitless supply of information, present it to users in a palatable format and allow them to interact with it. By my thinking, a good place to start would be with products. Using the products as a 'hook' the CRM system could fish for all related conversations and relationships. A nice tree view of conversations of various forms could then be presented to the user. Additional key words could then refine further depending on the requirement and eventually the user could trawl through, looking for gold.

Only when CRM is managing and interacting with both the conversations had by contacts directly with the organisation and those had without the organisation will CRM be truly managing customer relationships.

Thursday, February 19, 2009

Doing a 'null' search in CRM

One of the shortcomings of Advanced Find in Dynamics CRM is the inability to do a 'null' search. What I mean by this is the ability to pose a query like 'show me all accounts which have never had a phone call created for them'. If we were asking 'show me all accounts with  a phone call due this week' we have no problems but asking CRM to return records where related records don't exist simply can't be done.

Here are a few simple workarounds to this issue.

Using Workflow
You can get around this, relatively simply, using workflow. Let us take the example above. First of all create a bit field on the account form to store whether there has been a phone call created or not. Now all we have to do is create a workflow that triggers on the creation of a phone call activity and updates the account field we've created.

We now have a field we can search on to tell us whether a phone has ever been created and therefore if the account has ever had a phone call created against it.

In the case where we want to know whether or not there are phone calls in progress, we would need to resort to a counter to keep track of all the phone calls added/removed/completed. For details on this see the following post:

As with the counter, you will need to consider all events that could meet your criteria and whether you can capture them through workflow.

Using Excel (method 1)
It is easy to export all the accounts from CRM into Excel. It is also easy to export all accounts linked to an activity via the activity's regarding field where a compulsory field, such as when it was created contains data and where the activity is a phone call.

We now have a list of all accounts and all accounts with a phone call against them. Using a vlookup we can easily compare one list to the other and find the gaps. If you're being really fancy, export the two lists out as dynamic worksheets, combine them into one workbook, add in your vlookup filter and then all you have to do is open up the Excel file to get the results.

Using Excel (method 2)
When you export to Excel as a Dynamic Worksheet you can modify the query Excel uses. See here for details

In this case you'd modify the SQL query to do what Advanced Find cannot, that is return accounts with no phone calls attached. Again this can be saved and opened at will.

Using SRS Reports
Although a bit of overkill in my opinion, you could create a report to bring back the information you need. Personally I'd use one of the Excel methods above and add this in as a report if I was thinking of going down this route but the option of an SRS report is open to you.

Notifying Users of Events in CRM

A common requirement for a CRM systems is to notify various users of circumstances or events in the CRM system. For example, if a salesperson gives a substantial discount to a customer, the head of sales may want to know about it or if a support issue has been outstanding for a number of days, the head of the support center may want to escalate the issue.

Dynamics CRM provides a number of ways to do this. Three common ones are:
  • Creating an activity / sending an email via workflow
  • Populating a view for a user to periodically review
  • User generated reports (or reports periodically sent to a user via email) and dynamic Excel reports
With the CRM Accelerators, we also have another way to notify users, through an RSS feed ( Essentially the RSS feed is linked to a CRM view and passes the contents of the view directly to an Outlook folder.

The choice of method depends on a number of factors, some of which I'll highlight below.

Volume of Notifications
If a lot of events are going to trigger a notification, some methods are better than others. For example, the sales manager may want to know the new accounts or contacts that have been created in the system in the past week. Depending on user activity this has the potential to run into hundreds.

Obviously an email or task for each account creation would be a bad idea. No one wants their inbox or reminder window cluttered with hundreds of messages simply passing on the fact that CRM has a new account. The problem could be mitigated with Outlook rules in the case of email and with a registry hack in the case of Outlook activities ( but it would be better to deal with the issue at the source from a manageability perspective. In this case the other notification 'channels' provide a better option. A view could be created for the manager to review as he desires or a report could be generated for him to review as desired or that could be sent weekly showing the results for the past seven days.

The RSS feed may not be a good option given the nature of RSS feeds. RSS adds entries but cannot delete entries. Therefore, in the example above, as time goes on, the contacts created more than a week ago will not disappear from the list.

While views and reports are great for distilling large amounts of information in a digestible format, there is no guarantee a user will log on and view either in a timely manner. Sometimes information needs to be communicated to a user as quickly as possible. 

A common example for this are for service level agreements (SLAs). If a company guarantees that a customer service representative will call a customer back about their issue within 24 hours of it being raised, it can be costly if there is not a way to let someone know that a specific issue has been outstanding for almost a day.

Any notification method that requires a user to 'pull' the information i.e. perform a series of actions to access the information at a time of their choosing, is no good in time-critical situations and a 'push' method is needed. In this case, email and RSS are great choices as they feed the information to the right user almost immediately. Activities synchronizing down to Outlook could also be used but we are generally trained to action emails before tasks and appointments.

Richness of content
CRM systems contain a lot of information. Sometimes you need to sift through literally thousands of pieces of information to see the big picture (see for an example of extracting intelligent information from large data sources). A common CRM example of this is in reviewing products in high or low demand or in seeing if specific product lines cause more support issues than others. Not only can a company have thousands of products, the CRM system may also be storing thousands of sales opportunities and thousands of support cases.

Most CRM notification methods are quite poor at this. CRM views cannot show aggregate data i.e. counts or sums of records, and therefore neither can RSS feeds. They are also limited in their ability to convert data into graphical formats. Emails and activities generally communicate information about just one record in CRM and are also therefore limited in their capacity to communicate rich information to a user.

The best option here are reports. Both SRS reports and Excel exports provide the ability to aggregate large amounts of CRM data into useful and insightful information to guide the business. For details on using Excel for reporting go here:

Simplicity of Generation and Maintenance
This often overlooked consideration for CRM customization is equally important when considering the notification channels that the system is going to employ. Sometimes a user is not sure what they want to know about but they want to be able to put it in place quickly when it becomes clear. Similarly sometimes notifications are a 'work in progress' and need frequent adjustment.

An example of this is workplace monitoring. Often managers, empowered with a new CRM system, feel the urge to monitor their employees every step of the way. Soon they realise they are receiving a lot of information for little gain and want to reduce the monitoring.

If it is clear from the outset that a notification or a particular set of notifications are overkill or going to need frequent adjustment it is ethical to ensure the ease with which this can be done is considered. A consultant should be paid for adding value to a business, not for solving problems of their own creation.

In this case empowering the user to define and adjust their own notifications is the best option, if practical. Therefore, views and RSS are a great choice.
Notifications are a common component to a CRM system. Rather than employing a 'one size fits all' approach, consider the strengths and weaknesses of the options available to you and your customers will thank you for it. Better yet, educate your customers on the options and let them be part of the process on how the system will talk to them. The relationship between the user and CRM is at least as important as the relationships CRM is there to manage.

Wednesday, February 18, 2009

The Awesome Power of Network Analysis

While CRM technology is a wonderful thing which, if its set up right, lets salespeople manage their days more efficiently and effectively, lets managers get a better picture of their operations and lets customers be treated at least consistently, there is a point where the users of a CRM system ask themselves a very simple question:

"So what are we going to do with all this information we're now capturing?"

In fact this question can be thrown at most modern business application tools. The obvious answer is Business Intelligence, a fancy way of saying business report generation. This post is about a powerful but still underutilised technique that falls into this camp called 'Network Analysis' or more specifically 'Link Analysis'. Here is the wikipedia article for those interested.

The only industries I know of that have used link analysis to its full effect are government intelligence agencies and pharmaceutical marketing companies. Essentially the method consists of looking at interactions between people and concluding information in regards to those interactions. For example, if I'm a drug company that wants to promote my latest drug to the medical community, I can either send information to every doctor in the phone book or I can work out which ones are the ones the other doctors go to for information and just send them the details. Similarly, if I am a government intelligence agency trying to disrupt communication in an organised crime ring, if I can find out the people who everyone else go to for information, knock those people out and suddenly no one is talking.

So what relevance does this have to CRM given the lack of drugs or criminal activity involved in the average implementation? Lots!

In a CRM system we capture email interactions, company hierarchies, company-company relationships, salesperson-contact relationships and so on. All of these are fair game.

For example, a customer could be playing two salespeople off each other. Such activity can easily be picked up with this technique. You may want to know who customers go to or other members of staff go to when they need to know something. Again, this can easily be discovered and while some of these things could be determined with an intelligently crafted query to the database, you can go further to ask things such as which support staff consistently work together to solve customer issues?

So lets see an example. First of all, I recommend downloading NodeXL from codeplex.

This is an Excel 2007 template which allows the magic to happen (apologies to the 2003 folk).

You can import your own data from another workbook (spreadsheet), such as one exported out of CRM, use Outlook as the source or even point the spreadsheet to twitter. Essentially you need to populate the Vertex 1 and Vertex 2 columns in the Edges workbook to make it work. When you have your data in there, press the 'Read Workbook' button on the right-hand pane and a graph will appear. You can change the layout type to something that makes sense and if it is too cluttered, you can use the 'Tie Strength' column to weed out the weaker links (the higher the tie strength, the stronger the link).

My plan was to use the CRM VPC demo for the data source for this blog but the data is so sparse in it that there is really no point, so I pointed the spreadsheet towards my Outlook pst and after filtering the tie strength to a manageable level I got the following.

So what are we looking at? Well basically the little dots (vertices) are email addresses and the links and emails sent to or from those email addresses. So the big spot in the middle is my current email address. Other big vertices represent things like work email addresses, close friends, work colleagues and so on. Through literally thousands of emails, the graph quickly shows us the important people in my life. 

Similarly, collaborations are quickly shown as triangles/quadrilaterals etc. In this graph, they are typically of the type (employee 1, employee 2, client) or (employee 1, client stakeholder 1, client stakeholder 2). All excellent information to know when re-engaging with a customer to determine who will likely want to be 'in' on the project.

Finally, the arrow directions tell us the flow of information i.e. who was sending the emails to whom. For the main spot in the middle, the links above the spot are typically newsletters and the links below are typically one-way communication ranging from my wife's work email address (she is not allowed to send personal emails from work) through to unsubscribe requests.

CRM is all about communication and the management of that communication. Network/link analysis allows us to divine information about those communications not readily discernible through other methods. Download NodeXL and have a play one afternoon and I guarantee you'll think of half a dozen different uses for it in your business before the siren sounds.

Leon's Quick and Dirty Workflow Counter

Ask anyone who has played with Dynamics CRM 4.0 for a while how to show the total number of subrecords against a parent (for example total number of calls/contacts/closed meetings etc.) and they will tell you to go scripting.

Being adverse to scripting of any kind I thought I'd have a play and to this end I present my record counter using only workflows (two of them in fact).

So let's say we want to count the number of contacts against an account. First of all we need a place to store this counter, so we create an attribute against the account. We can set this to be a number or a date, both witll work, although we get more flexibility with numbers.

Now, we need to initialise the attribute, so our first workflow gets triggered on the account creation and sets the counter field to something that makes sense i.e. 0

Now we need to increment the counter when a contact gets added, so we add a second workflow against contact creation which updates the Parent Customer (Account) and using the dynamic value form assistant on the right of the form we set the value of the counter to one more than its current value. For number fields, you have the option of incrementing/decrementing or multiplying through the Dynamic Value Form Assistant in the workflow update form.

And there you have it, a counter which will count the number of contacts added to an account. Obviously if you were to use this, you'd need to capture more events than just contact creation as contacts can be reparented etc. but you get the idea. The decrementing may come in handy for situations where you are, for example, counting the total number of active calls against an account and decrementing when a call is closed.

You could also use this for scoring accounts or contacts if you use such techniques. For example, if an account gets 10 points for every closed-won opportunity against them. Perhaps they lose 10 points for every closed-lost opportunity against them etc.

I recently used this to create 12 monthly appointments, one being scheduled immediately after the previous one is completed. The counter worked nicely to determine when to stop the appointments from being created.

Sunday, January 18, 2009

Why Loyalty Programs Suck

I personally do not consider loyalty programmes as coming under the banner of CRM. The main reason for this is I come from the school of thinking that CRM is about analysing existing relationships with customers and modifying business processes to improve future communication. That is, understanding the needs of the customer and then anticipating future needs.

Loyalty programmes do not do this. They are simply a trick where the customer is duped into thinking they are getting something for nothing, giving them an incentive to return. This is not management of the customer relationship any more than putting up signs saying 'closing down sale' is CRM.

So here are my three main reasons why I think loyalty programmes should be avoided by all parties.

There is no real reward in a loyalty program
The promoters of loyalty programs claim the customer is being rewarded for loyalty. This is rubbish for the vast majority of loyalty programmes. Unless the business can derive some kind of economy of scale through the employment of the programme, all additional costs (the cards, stamps, advertising, 'rewards', back end systems to manage it etc.) will go one place and that is onto the price of the items being bought by the consumer. 

Loyalty programmes do not reward but simply introduce an additional cost to the process, which is occasionally offset for frequent purchasers and not offset at all for people not in the programme. If a competitor has access to the same supplies at the same cost, they can make the same product for cheaper, if they are not using a loyalty programme. With low switching barriers, the infrequent customers will go elsewhere and save a few dollars. In other words a loyalty programme drives customers to low cost competitors, promoting disloyalty.

Loyalty programmes are easily copied
No sustainable competitive advantage can be obtained from a loyalty programme. The main reason for this is it can be easily copied by a competitor if, despite all reason, it does derive benefit for a particular business. Once every competitor in an industry has a loyalty programme, they are all stuck with the same additional costs and offer nothing that is not offered by their competition. Everyone loses including the customer.

Loyalty programmes hinder business growth by tying up cash
If I have a loyalty programme and people can claim against it, I need to keep assets on hand to cover the possible redemptions EVEN IF no one claims. In reality this means a company with a loyalty programme has significant amounts of money sitting in a bank account doing very little when, without the loyalty programme, it could be employed into building real benefits for the customer. The average cafe would not worry about this, but for larger programmes, such as airlines, this is a significant problem where literally millions of dollars sit around idle.

To get around this, points used to expire. If you did not use your points within a certain time, they disappeared and the underlying cash was released. These days clever marketers think it is smart to have points never expire and then wonder why their marketing budget is getting smaller and smaller each year.

If you need a way to tie up cash, provide no sustained competitive advantage and burden you and your competitors with additional costs, a loyalty programme is a great start. Smart players in an industry will not copy their competitors who go down the line of loyalty programmes but rather employ the funds they would spend on such a project elsewhere. It could be in maintaining the product price while others are forced to increase theirs. It could be in increasing the price to match the loyalty programmes but then investing the additional profits into the business to reduce costs elsewhere, providing sustainable lower costs of production. It could be on additional advertising to drive demand. Anywhere is better than a loyalty programme and if you really want to generate loyalty in a business, give the customer something they genuinely want, not smoke and mirrors.

Monday, January 12, 2009

What is CRM?

Googling CRM will give you lots of opinions and thoughts on what is CRM. Some talk about the philosophy of CRM and customer-centricity, others talk about the technologies. This blog is all you need to read on what CRM is.

Most of us buy groceries. Many of us go regularly and buy similar stuff each time. Imagine if you went to the supermarket and on arrival you were greeted and told "Welcome back Mr. Smith. Because you shop with us regularly and buy similar things each time, we know what you need and already have your shopping ready for you. Let us know where your car is and you can be on your way". Would you go to another grocery store again?

That is the philosophy of CRM. A supplier anticipating the needs of a customer and fulfilling their requirement before the customer has even asked. In return the customer perceives such a loss of service in going somewhere else, it is simply not an option. For anyone selling a product, they could call the customer when an upgrade is available that will save the customer money or it could be calling the customer just prior to the life expectancy of the product is up to save productivity loss due to outage. 

As many consultants know, their job is to up-sell services, even when the customer may not realise they need them. If the 'value add' truly provides more benefit than it costs, this is also a form of CRM.

So what stops grocery stores getting your box of shopping ready just in time for your arrival? Or someone that sells air conditioners calling their customers and telling them that it is time for their unit to be serviced to stop it running inefficiently and wasting the customer's money? The answer is the systems in place. In the case of the grocery store they would need a system that stores each customer, when they have come into the shop and what they bought. 

Computers are excellent at storing large amounts of information and allowing it to be queried in a variety of ways quickly. This is the technology of CRM. A database with customer information. This information might be what you have done before a sale is made or it could be the information of the sales themselves. It could be the post-sale support information or it could be the marketing campaigns you've run to generate new customers. Any information relating to your business and the external parties that you deal with is fair game.

That is it. CRM in a nutshell. The philosophy of CRM and what it aims to achieve and the technology needed to support it.

As an interesting aside, I've implemented a lot of CRM systems and, for the most part, they are not there to meet a CRM philosophy. Often CRM systems are used to monitor staff, improve internal work processes or simply to help management better manage their reporting. There is nothing wrong with this and most were perfectly successful in achieving their aims. As long as the organisation is behind the system, gives it the resources it needs and they have their eyes open to what it will and will not do for them, usually everything is fine.

One other thing to note. An essential requirement for a CRM system meeting a CRM philosophy is the ability to extract the information you need to understand your customers. Therefore it is essential you understand what information you want about your customers before the first DVD hits the tray and make sure your CRM system will be able to give you this information. Moreover, for complex requirements, a business intelligence system (BI) should be seriously considered.

Reporting in CRM with Excel

One of the most powerful and under-utilised reporting options in Dynamics CRM is exporting to Excel. Practically any list on CRM can be exported as either a static list of data or as a dynamic list/pivot table which updates whenever the Excel file is opened.

Once a dynamic list has been exported, you can manipulate it however you wish. You can add columns, delete columns, add graphs, add extra worksheets etc. Essentially everything you can do to a normal Excel spreadsheet you can do with a dynamic list. Please note the default format when CRM exports is OpenXML, not xls. Therefore to do things like add graphs you will need to save the export file into an xls/xlsx-type format.

While this is powerful, you can go further. The major limit of the above is that you can only bring into Excel what you can define in an Advanced Find in Dynamics CRM. Plenty of queries you can write in Excel, you cannot write through Dynamics CRM's Advanced Find. For example, if you want to aggregate data from CRM and list it e.g. show the number of open cases per account, you cannot do this through the Advanced Find interface.

So how can we 'hack' the query? Its actually pretty easy.
  1. Right-click cell A2 and select Edit Query...
  2. Click the 'OK' on the warning you can't use the query wizard
  3. Click the 'SQL' button on the Microsoft Query box that pops up
And here it is, the power of SQL at your command. The first thing you might want to do is remove the 'top 10000' after the select. This will stop only 10000 records coming back. 

Please Note: This only works for on-premise as IFD does not use SQL in its Excel queries.

Once you've adjusted your SQL, click 'OK' and click the 'X' in the top right corner of the Microsoft Query window. The query will now be run and will populate the spreadsheet. One thing to watch out is if you've added extra columns to the query as these will appear in the spreadsheet and obliterate anything already in there.

The only real trickiness is with things like working out the correct name of the attributes, working out the right value for picklists and the correct names of the tables.

The names of the attributes, you can get by going to Settings-Customization-Customize Entities, selecting the entity in question and opening up the attributes. The name you want is the one in the 'name' column.

The right value for picklists is a little trickier, especially for entity status values. While you can figure most of them out by going to attributes and editing picklist entries to get the 'value', the easiest way I find is to do a new Advanced Find query, using the picklist value as a condition in the query, clicking the find button, exporting to another dynamic excel spreadsheet and examining the query.

For tables, you should only refer to the Filtered Views. This ensures security is preserved in terms of the records the spreadsheet user is allowed to access from CRM. It also ensures a supported solution i.e. the spreadsheet query should survive future upgrades of CRM

For the most part the view name is of the form 'FilteredEntityName'. There are a few exceptions such as for the Case entity (FilteredIncident) and Notes (FilteredAnnotation). If in doubt, open up SQL Server Management Studio and have a browse.

And there it is, the ability to write SQL queries against CRM without all that fiddling around in Visual Studio. In my opinion the use of Excel for queries instead of SSRS also fits in with Microsoft's aim of giving the power to configure to the end user. There are few end users who are brave enough to tackle Visual Studio but there are plenty that will play with Excel and most companies have at least one person that can write basic SQL SELECT queries.

One final note, Excel does not submit the SQL query in a secure way so, in principle, if someone was on your network sniffing the packets they could access the data being returned. To learn how to tighten the security of Excel queries, read the following blog post: