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.

Conclusion
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:

Thursday, January 1, 2009

Perfect Storm of a CRM Upgrade

This is a post to the forums I did a while ago (edited a little). Hope it helps.

I've just completed what, I believe, amounts to the perfect storm of an upgrade. The client was moving from SBS 2003 (not R2) on a virtual machine to a physical Windows 2003 64 bit server. They were also upgrading CRM3 SBS to CRM4 Professional. As SBS was running SQL 2000 and CRM3 does not run on 64 bit machines the database couldn't be easily moved from one to the other. So we have an upgrade, migration across domains and a move to 64 bit.

The eventual path to success was the following:

1) Upgrade SQL 2000 to 2005
2) Upgrade CRM3 to CRM4 on the SBS virtual machine
3) Backup the database and transfer it to the new 64 bit server
4) Install CRM4 on the 64 bit server
5) Use deployment manager to attach the old database

Points of note:

* You can upgrade SBS SQL 2000 to 2005 but you may need to completely reinstall SRS as in my case the discs simply would not allow for SRS to be upgraded
* If you get "Malformed XML Found in Microsoft Dynamics CRM Saved Queries" this could be an issue with the saved queries table but it could also be a permissions issue. In my case the user who had installed CRM was not the domain administrator. Upgrading as the domain admin did not cut it. Promoting the original user to a domain admin and upgrading did the trick
* If you get "Action Microsoft.Crm.Setup.Server.InstallConfigDatabaseAction failed. Invalid user name. Failed to validate username for given domain. Only existing domain usernames and email addresses are allow" then try unplugging the network once the required components are installed. This cleared the error for me
* On a 64 bit box, make sure you are running IIS in 64 bit mode and therefore ASP.Net is registered in 64 bit mode. If not, CRM will get past the pre-checks, start installing and then bomb out leaving a mess which cannot be repaired, uninstalled or installed over
* Similarly do not try to install the 32 bit version of CRM on a 64 bit server. It will only lead to pain.
* The deployment manager can be used to add an organisation with the Professional license. It simply swaps out the default database for the new one and maps the users as usual (although, I again hit an issue with the setup user being someone other than domain admin. The error was "At least the setup user needs to be mapped before this organization can be imported". I had to swap map links to make it work) Good luck if you come across a similar situation ;)

How to quickly switch users when demoing Dynamics CRM

This is a little trick I got shown about six months ago which is just damn handy.

In some demo scenarios you want to switch to a different user to demonstrate the differences between users. In Dynamics CRM this is commonly used to show how entity access can be limited or how different roles have different rights assigned to them.

The key to this trick is that the user that runs IE, is the user CRM authenticates to for the web client. So if you run IE as 'Bob', when you go to CRM's web address, you will log in as Bob, even if you have logged onto the domain as someone else.

To set up a shortcut for IE which you can quickly run as a different user, create an IE shortcut (right-click, save to desktop) and then right-click the shortcut and go to properties.

Clicking the Advanced button allows you to specify 'Run with different credentials'. Ticking this box means when you double-click the shortcut, you'll be prompted to enter in alternative credentials. enter them in and now when you go to the CRM URL, you will be logged in as that person.

When hosting is a good option for CRM

Hosting business applications, that is, running business software on someone else's servers and accessing the system, typically through a web browser is gaining more and more attention as the technology becomes more user-friendly.

Outside of the cost considerations (do I want to pay a monthly fee or a large amount up front and hopefully less in maintenance in the future) there are other key factors in deciding which path to go down.

Here are a few other considerations before making the leap.

In-house technical ability
If you are running your own servers and have business-critical applications on these servers, you need staff dedicated to ensuring their function is aligned to the business' requirements and that if users have problems with the application, these staff can assist them. One of the key reasons I see CRM implementations fail is a CRM system is implemented and then falls into disrepair as no one within the company is tasked with championing its use or continually improving it.

Business attitude to information
Some businesses are fearful of housing their data outside of the four walls of their business. There is no wrong or right answer to this and it is simply something the business must consider.

Free CRMs

These are a couple of online CRM systems which you can play with for free. While they do contact management as well as any other system, often the areas where these free systems fall behind is in the ability to configure and in automated workflow.

While SugarCRM is open source, I do not consider it free in that there are still costs of installation, hardware and setup. Online CRM's have some of these costs but they are a fraction of an in-house solution's.


This relatively unknown CRM system allows for unlimited users. Its quite rigid in its layout and function but covers the basics.



Zoho have online apps for practically every office need. Zoho CRM is free for the first three users and covers the essentials, including advanced features such as workflow. Think of Zoho as Google Apps and Salesforce combined and at no cost.

Importing tricks for Dynamics CRM 3.0 and 4.0

The import tools came a long way between version three and four but they are far from perfect. Here are a few tricks to make the process of importing using the import wizard as painless as possible.

The main tip I can offer for version 3.0 is getting the GUID of records already in CRM. For details see my other post:


This is most commonly used for adding the parent account to contacts. The process is import your accounts, get the sheet of GUIDs and then use Excel's vlookup to insert the appropriate GUID into the Parent customer column of the contact import sheet.

For version 4.0, you no longer need the GUID as the import wizard will match off of the account name. This (rather naively) assumes the account names in the system are unique but the gain is it greatly simplifies the process of attaching contacts to their parent account. Either ensure account name uniqueness, try using the GUID or be prepared to manually attach those records which will fail.

Despite being a great improvement over its version 3.0 counterpart, the version 4.0 import wizard has a few shortfalls. The first one is you are almost certainly going to fail if you create your own data maps.

While the import wizard allows to define how the data from your source sheet gets mapped into CRM, if you choose to actually do this you are very likely to get errors on your import. The problem is manual data maps do not support mapping values to other tables without a lot of messing about. So, for example, if you are bringing contacts into CRM and you have account names in the parent customer column, this will fail with a manually created data map as the map needs to go to the account table. Even if you use the version 3.0 method of specifying the GUID it will fail.

Update: Apparently Rollup 2 has fixed this but if you haven't gone to Rollup 2 yet, the below gets around the issue.

The only way around this is to get CRM to automatically map the values for you. The only way you can get CRM to automatically create the map is to ensure the column headings in your source file match what CRM is expecting to see (and yes, capitalisation does matter). Generally speaking the values need to match the labels on the form. More accurately, the values need to be the attribute names. You'll know if it is working because when you go through the Import Wizard, the moment that you specify the entity you're trying to import, the data map value will show 'Automatic'.

If it fails and you're stumped which column value is confusing CRM, go to Data Mapping as if you're setting up a manual data map and you'll see the values it's expecting.

Another tip at this point is if your import file is sufficiently large, you will not be able to import sample data into the data mapping entity. The only way around this is to copy the first few lines of the source file and use this for your sample data.

Once you have the correct column headings and CRM is automatically mapping the import you're almost set. The only thing you need to check is that the source data is 'clean'. There are two things which commonly upset imports. The first is the last column in your source file needs to be completely populated. The order of the columns is not important to shuffle them around to make sure the last is completely populated otherwise you risk CRM not understanding the how many columns you have.

The second issue, and often most frustrating, is you cannot have line feeds or carriage returns in your source data. These are guaranteed to confuse CRM in regards to the number of columns. Finding which values actually have the offending characters can be tricky. The only tricks I know of are to run the cursor down the suspect column in Excel and if the value box at the top expands to more than one line, you have an offender. The other trick is to open the source file in notepad or some other text editor and look for rows which start in the 'middle' of a source row. Common columns including carriage returns and line feeds are address columns and note/description columns.

Once you've identified the offending column, Excel has a formula for stripping out the bad characters called SUBSTITUTE which allows you to replace one character with another in a string. Using the formula =SUBSTITUTE(SUBSTITUTE(ORIGINAL_CELL,char(10),""),char(13),"") will strip out the hidden characters and let the import go through.

Other issues can cause problems with import, such as a value in the source file not matching the value in a picklist attribute which you're trying to import the value into but the error messages for these are generally quite self explanatory. For version 4, to see the error messages, go to Settings-Data Management-Imports-Double click on the import job of interest and look at Failures. Alternatively, you may also get insight from Settings-System Jobs where the import job will also appear. Filter on Type: Imports if there are too many entries to navigate.

One time when the error logs will be of no use to you is with importing subjects and business units. The one thing to be absolutely sure of is that any subjects or business units you import are ultimately linked to an entry in the import file or to an entry in CRM. You CANNOT import the subject or business unit at the top of the tree. If you try, it and any entries linked to it simply will not appear in CRM. They are in the tables but never surface in the client.

As an aside tip, always populate a dummy field with an import-specific value. This way if you need to delete or modify the import in some way, the records are easy to identify in an Advanced Find. This is often used if you need to assign users to the records. Certain properties such as status and owner cannot be directly imported, so setting a value and changing the records within CRM is the only way to go if you're using the import wizard (the Data Migration Manager handles this better).

Another tip if you want to bring in records with non-English characters is to use the txt-unicode format for your import file. CSV simply won't work. The wizard works exactly the same way and the data should come in without a hitch. I've done this with Japanese, Chinese and Korean records and never had a problem.

Also, if you plan to import someone's contacts from Outlook, don't. Or at least be prepared for the consequences. Other than the data often containing hidden characters, CRM will not match the new record to its original in Outlook. Therefore, if the user is using the Outlook client and contacts are being synced, they will suddenly get a duplicate of every contact in Outlook as CRM syncs down the new records to their Outlook contacts. You can delete the originals by viewing your Outlook contacts in a list format and using the different icons for synced and non-synced contacts to work out which is which.

Finally, in a few cases, as you can only import into one entity at a time, you'll hit a 'chicken and egg' problem. The most common example is with accounts and contacts. Before you can link one entity to the other, one of them must be imported into CRM first. The upshot is you can either link the account to its primary contact (by importing the contacts in first) or you can link the contact to its parent account (by importing the accounts first) but you can't do both. Apparently you can get around this with the Data Migration Manager though.