Monday, October 20, 2014

Revisiting My Most Popular Articles

It has been a little over six years since I began writing this blog and I thought it is time to revisit my most popular articles to see how they stand up (and maybe update a few of them). On my blog page, on the left hand side, there is a “Popular Posts” section, showing the top ten most popular articles.


I will focus on these articles for the blog post.

My Themes

There are a few broad subjects I blog about:

Of these, tips and tricks seem to be the most popular with five articles. The rest are a bit of a mixed bag with one workflow article, my two Surface Pro reviews, a high-level piece on Gartner/Forrester reviews and my piece on moving my email to Office 365.

Tips and Tricks

Importing Records

Not a lot has changed with the Import Wizard but one difference stands out. In my original article I lamented at the speed, or lack thereof, of the import as the web service layer could only handle one record at a time. This has changed and batches of files can be passed through the web services for processing on the server side, greatly improving the import rate.

I have not done any testing on how this affects performance of the Import Wizard but my developer friends tell me it has made a significant difference for integration.

LinkedIn Integration

Here there is good news and bad news. The bad news is the LinkedIn integration no longer works and has not for quite a while. The good news is there is a free integration with InsideView now which gives you integration with LinkedIn, Twitter and Facebook.


Very powerful and very easy to install. Essentially you install the solution file, add the web resource it contains and label the section as required and you are good to go.

Advanced Find Limitations

Unfortunately all of the limitations from over a year ago are still in CRM. If you are looking to change this, you can lodge recommendations at Connect.

Running a CRM VPC

Three years ago, virtual PC images (VPCs) were the way to go for demonstrating Dynamics CRM; CRM Online was slow in Australia and wifi/network availability in board rooms was not guaranteed and certainly not fast. Things have changed. Frankly, I cannot remember the last time I spun up a virtual machine on my laptop. These days, I look to trial versions of CRM or, if I do need a virtual server, I go to Azure.

Making Records Invisible In CRM

This trick of assigning records to a team in a different business unit continues to serve me well, even though it was written about two years ago. There are often departments in an organisation who believe they have ‘special’ contacts and accounts. This solution means they can still get on board with CRM without losing control.

Workflow and Dialog Tricks

There are plenty of auto-numbering solutions out there but this basic one using workflows covers the essentials. My revised version using synchronous workflows is even better. As a point of note, for a client we recently reviewed whether it was possible to guarantee a unique number generation when using clustered servers. Unfortunately, there are rare occasions when a clustered environment can generate duplicate numbers, even if a plugin solution is employed, although it is rare.

Forrester and Gartner Trajectories

Like the workflow and dialog article, I have also written an updated version of this article. The same four CRM solutions which were leading two years ago, continue to lead today but with Oracle and SAP losing some ground. As the reports come out, I will continue to update the trajectories.

Surface Reviews

Two of my Surfaces review made it to the top ten list. I am writing this blog on my Surface Pro 1, which serves me as well today as it did back then. I occasionally have to flush the index to avoid running out of space but otherwise I have no complaints.

My Surface 2, unfortunately, has a failed battery which means it will only boot up on power and if that power is removed it immediately turns off. We still use it as home but, with no battery, it rarely leaves the house. Fortunately I will be in Seattle in a few weeks and will look to replace it before the 12 month warranty runs out.

Migrating to Office 365

Like my Surface Pro, I continue to use Office 365 as an exchange server and could not be happier. For the price of a couple of coffees each month, I do not need to worry about backups or PST file corruption and I get access to my email/calendar anywhere. In terms of space on my Exchange Server, I have used 19.3G. In my original article from January 2012, I had used 6.63G. This gives me a couple more years of use before I need to delete anything.



Overall, I am quite happy how my articles have stood to the test of time. CRM has moved on in some cases, for the better and my review/opinion pieces hold true to my thoughts today. As usual, if there are topics you would like me to blog about, feel free to let me know.

Sunday, September 28, 2014

Bringing Graphs Down to the Record Level

I was recently told that one thing that is well-liked about my blogs are the ‘codeless workflow workarounds’ I occasionally put out. I have not done one for a while so here is one I came across the other day. A client was facing a problem where they wanted to show a graph, usually used for a collection of records, on each record. A good example of this is the ‘Campaign Budget vs Actual Costs (By Fiscal)’.


Looking at the definition, we can see that this graph rolls up totals based on fields on the Campaign record.


If I wanted to quickly see how a specific campaign was going, it makes sense to have this graph on the Campaign form but this is not possible, although we can add graphs to the record form.

Adding Graphs to a Record Form

A little-known trick is being able to add a graph directly to a form. To do this you add a sub-grid and go to the formatting settings. Scrolling down to the bottom, you see how to do it.


Ticking the ‘Show Chart Only’ replaces the sub-grid with a graph and displays it on the form instead. In this case, I am replacing the ‘Recent Opportunities’ grid with a graph showing which Campaigns they came from. You can see how it looks at the bottom right below.


So all we need to do is add a sub-grid with the same record in it, replace it with our graph and we are sorted.

The Recursive Grid

First we add a 1:N between the Campaign entity and itself.


Then we set up a workflow to populate the resulting lookup with the same record, on creation. In this case I have made the workflow NOT run in the background so it appears the moment the record is saved but, for CRM 2011, the trick will still work but you will need to close and open the record to see the magic.


The result is a 1:N relationship where the child record will always be the same as the parent record.

Next we add the sub-grid to our form and create a new record to make sure it works (remember to format the sub-grid to have, say, ten lines so the graph is readable).


Sure enough, when we create a new Campaign record and save it with data, the graph appears and populates with data just from this record. In this case I set the budget to $10,000 and the Misc. Costs to $8,000.


Obviously, you can create a new graph to embed and change the title to give it more context.


If you have a customer looking for a ‘record dashboard’ or perhaps you want to summarise a set of fields on the form in a more graphical way, this trick may be just what you need and, as is my way, completely codeless. Enjoy ;)

Thursday, September 25, 2014

Using Email Templates to Communicate With External Parties

A common requirement for CRM systems is to communicate with non-CRM users as part of a process. In this case, my client’s scenario was regarding Cases where the contribution of a third party was needed. The requirement was to summarize the Case record, attach it to an email and send it off for review and comment by the third party.

The First Thought

My initial thought was an SSRS report. I talked through the solution with the client:

  • We will create the SSRS report for you, summarizing the Case record
  • The user will open the Case and run the report from the record
  • They will then save it to a PDF and attach it to an email
  • Send the email, adding in the recipient

The client was not a fan. There are solutions, such as MyCRM’s ePDF, which would auto-create the pdf but even then the client felt the process was a little awkward.

My Second Attempt

Mulling over the problem for a week, I realised there was a cheaper and simpler approach: email templates. It is quite easy to create an email template summarising the key fields of a record.


The client had renamed Cases to ‘Enquiries’ but you get the idea. I even added a default of ‘Unknown’ on the fields, in case they were blank.

I, again, reviewed the process with the client.

  • Open the Case and add an email
  • Apply the template
  • Remove the customer from the To line and look up the Contact you want to send the record to
  • Send the email

Again, the client was lukewarm. This time it was the replacing of the Customer in the To line which could easily be overlooked. I went back to the drawing board.

Third Time The Charm

Extending the previous solution, I set upon using a Dialog. The dialog could be run from the Case and prompt for the Contact to send the information to. The workflow engine then handled the rest.


In this case the process was:

  • Open the Case
  • Enter additional text in a field called ‘Additional Information For External Reference’ (a multi-line text field on the Case form, which you can see referenced in my screenshot of the email template above)
  • Run the dialog
  • Look up the contact to send the email to
  • Finish the dialog

The client was happy with the process. The text field populated in the second step could be populated by an Update Step in the dialog, rather than on the form, but this was not needed in my case.

Easy to set up, no coding required and very simple to modify in the future if the process needs changing.


Sometimes it takes a client to push back to deliver the best result. So, if you are a user of CRM, do not hesitate to explore the different options with your CRM consultant. There are often a few ways to achieve the same outcome and it is a case of finding the best result for you.

In this case, with a bit of creativity, the final result was cheaper, simpler, more manageable and more intuitive; a win for everyone.

Friday, September 12, 2014

Seeing Your CRM 2011 System in CRM 2013

If you are running CRM 2011 and are starting to think it is time to migrate to CRM 2013, there is a quick way to see what it will look like and to see if any of those tricky customizations will break. Minimal technical knowledge is required and it gives you the chance to review the system and document the issues before getting those expensive consultants involved.

Step One: Spin up a 30 Day Trial

The first thing we need is a CRM 2013 environment for us to review. These days this is quite easy and will also give you access to all the Office 365 goodness to try out as well. Start here and follow the prompts. The process used to be a little awkward, jumping between Office 365 and CRM, but this has been improved a lot in the last week or so. In about five screens, you will have an Office 365 account and a CRM Online trial instance.

Provisioning also used to take a few minutes but, on this occasion, it was almost instantaneous.


Step Two: Create a Solution File in Your Existing CRM 2011 Instance

This is the most technical step but still reasonably straightforward. Log into CRM as someone with the System Administrator role and go to Settings-Solutions and click the New button.


This record will contain all the bits and pieces of our system we want to review in CRM 2013 (once we add them in).

Give it a sensible display name and (non-display) name, add in a publisher (the default one is fine) and put in a version, say, 1.0, then hit the Save button. You should have something like this:


You now go through and, for each component type, add in the bits you want to try out. A good place to start is Entities (the different record types in your system). Pick the key entities you want to bring into 2013 e.g. Accounts, Contacts, Activities etc. When you add then in, a message box may open up referring to dependencies. Add these into the solution as well.

If you are unsure about all the other stuff, just stick to entities and you will be fine. If you want to remove a component from your solution, use the Remove button, NOT the delete button, otherwise you will delete the entity from your system!

Step Three: Export Your Solution

When you are finished, click the Export Solution button and follow the prompts, publishing and adding in any other required components. The system will sit and done nothing for a few seconds and then give you a zip file to save. This is your system (without the data).

Step Four: Import the Solution File Into Your CRM 2013 Instance

CRM 2013 is compatible with CRM 2011 solution files (mostly) so it is a case of getting past the smiling lady on the pop up screen and going to Settings (now up the top of the screen).


Then to Solutions.


Here you can import your solution by hitting the Import button. Again, follow the prompts and, if everything goes ok it will load in your system (maybe with some warnings, which you can ignore). If it errors, this is probably the end of the road as there is something complex in your solution file which the conversion cannot handle.

Assuming it has gone in though, close down the browser, re-open and get back to CRM 2013 and you should see your system in the new environment.


I am involved in a few upgrades for clients at the moment and, while the actual upgrade involves a lot more than the above procedure, if you want to get an idea the look and feel and whether specific customisations will translate to the new environment, this is a great non-invasive way to do it.

This is also a great way, as a consultant, to get people exited about upgrading and can be done easily in less than half an hour while on site. Within an hour you can migrate the system and set up a couple of users for the client to log in as and explore. Enjoy.

Saturday, August 23, 2014

Salesforce: Robbing Peter to Pay Paul

Another three months has passed and another set of results are available for review. In the previous analysis, I predicted how Salesforce would fare. I did not do that this time but will have a go for next quarter. Also, I will use my newly discovered method for predicting the Salesforce subscription numbers in this review.

The Numbers

Salesforce have revamped their web site, including the financials, removing the historical financials PDF which was increasingly out of date. Five years of financial data are available, eight years of annual reports and the SEC filings.

Here are the numbers for most recent five quarters.

  2014 Q2 2014 Q3 2014 Q4 2015 Q1 2015 Q2
Revenue 957,094 1,076,034 1,145,242 1,226,772 1,318,551
Subscription Revenue 902,844 1,004,476 1,075,001 1,147,306 1,232,587
Revenue Cost 217,717 268,187 273,530 292,305 307,831
Operating Cost 779,234 905,778 975,458 989,808 1,044,154
Salesforce Income 76,603 -124,434 -103,746 -96,911 -61,088
Revenue Growth # yoy 225,445 287,636 310,561 334,139 361,457
Revenue Growth % yoy 31% 36% 37% 37% 38%
Revenue Growth % mom 7% 12% 6% 7% 7%
Total Cost % yoy 34% 39% 46% 37% 36%
Staff 12,571 12,770 13,312 14,239 15,145
Staff Growth (yoy) 43% 37% 36% 38% 20%
Margin 8.00% -11.56% -9.06% -7.90% -4.63%
Growth Difference -3% -3% -9% 1% 2%
Cash 579,881 651,750 781,635 827,891 774,725
Accounts Receivable 599,543 604,045 1,360,837 684,155 834,323
Cash/AR 97% 108% 57% 121% 93%

Staff growth is significantly down, to about half of the rate previously, which is very surprising. one to watch.

As for profits, they made a 61 million dollar loss which, apart from that one quarter where they got a tax break, means Salesforce has not made a profit for three years.


With a strong correlation of transactions to subscribers, I derived the formula:

Transactions = 150.27 * Subscribers – 40,000,000. So, for example, when there was 53 million transactions, back in September 2006, the predicted subscriber count is around 619,000. We know that the subscriber count at that time was around 556,000, which means we are about 10% off.

The trust page tells us the highest recent level of transaction was July 22, 2014 which had 2,037,819,946 transactions. This gives a subscriber count of 13.8 million. This is a LOT of subscribers but, if it is true, it means the average revenue per user per month is $32, which is not even a Sales Cloud Professional subscription. It also means that, on average, Salesforce loses just under $1.50 per subscriber per month.

Revenue and Cost Growth


Revenue growth remains solid and appears to be accelerating slightly. Salesforce have also got their revenue growth above their cost growth, which means they are heading back to profitability. In terms of the difference between the revenue growth and cost growth being 2% or more, this is the first time it has happened in about five years, so it will be interesting to see how this progresses next quarter. If they are not careful, it might become a habit.

In the above graph I have also added four-period moving averages. This is an average used to look for trends in movements. As can be seen, the moving average for Cost Growth has remained above the average for Revenue Growth for the last four years. However, now they are approaching and, if they cross in the future, it will be a good sign for Salesforce.

Cash and Accounts Receivable


Despite optimism about the decrease in Accounts Receivable and increase in Cash in the previous quarter, this trend has reversed this quarter, which is a pity. I also mentioned in the last review that historically, Cash ran above Accounts Receivable and now they seemed to be coming to a similar level. By adding in the four period moving averages, we see this to be the case. We also see that Accounts Receivable, for the first time has nudged above Cash in terms of the actual amounts and as an average. For me, this is not a good sign. As I mentioned last time, cash in the bank is a better asset than money owed to you by customers.

Earnings Call Buzzword Bingo

The rule is the words on the list have had ten or more mentions in the past five periods with the text used being the call transcript after the introduction and up to, but not including, questions.

  2014 Q2 2014 Q3 2014 Q4 2015 Q1 2015 Q2
Number of words 3500 3700 3700 2400 4731
Customers/Customer 40 39 25 22 38
Revenue 37 37 29 19 27
Cloud 23 31 14 15 22
ExactTarget 24 21 15 7 8
Platform(s) 19 21 12 10 13
Service 14 19 13 13 15
Sales 14 16 4 6 6
Growth 12 14 12 9 18
Marketing 12 12 11 5 10
Cash 10 10 16 10 11
Mobile 11 7 5 2 8
Operating 9 7 10 11 11
Enterprise(s) 6 7 3 8 10
Social 9 6 3 2 4
EPS 10 5 6 6 6
Salesforce1 0 0 11 6 7
Dreamforce         11

Quite the word-fest this call, with CEO Marc Benioff, President Keith Block, Executive VP Graham Smith, and CFO Mark Hawkins all speaking.

Dreamforce is a new term this quarter (it is coming up in October, so this makes sense). Social has dropped off the radar, presumably because Salesforce is no longer the Social Enterprise. Similarly, EPS (Earnings Per Share) is falling out of favour as a measure for the earnings calls. The three most popular words, as usual are “Customer(s)”, “Revenue”, and “Cloud”.

One phrases of note: “running their entire business right from their phone” (two mentions). This is Marc’s vision of the future. Run it from your wristwatch, I say.

One word conspicuous in its absence was ‘profit’. It was not mentioned in the transcript once, nor in the question and answer session.

Google Trends

As usual, I pump “Dynamics CRM” and “” into the Google Trends analyser and see what comes out.


Red is “” and blue is “Dynamics CRM”. Dynamics CRM is flattening out while continues to drop.

Insider and Institutional Sales

According to Yahoo, this is how we stand, in terms of insider and institutional sales.

  2014 Q2 2014 Q3 2014 Q4 2015 Q1 2015 Q2
Insider Sales 0.50% 0.50% 0.50% 0.50% 0.40%
Institutional Sales 3% 2.75% 2.72% 2.71% 2.67%

It seems, in both cases, while shares are being offloaded, the rate it is happening is slowing down. Perhaps attitudes are changing towards the future of the stock.

Looking to the Future

Salesforce predict the next quarter will have revenues between $1.365b and $1.370b and a loss of between $80-86m.

I think revenues will be closer to $1.4b and the loss will be around $40m so we will see who gets closer.


If we listen to the earnings call, if would be easy to think Salesforce is a company going from strength to strength and there is no denying, for a company their size, they do have enviable revenue growth. However, no matter how it is dressed up, financially, it is a bit of a mixed bag. Obviously three years of unprofitable business is bad and a degradation of the quality in the assets is bad but then we have the improved sales/cost growth and slowing of the insider and institutional sales.

The lack of profits is often explained away by saying that Salesforce is making a loss so they can aggressively target market share. Similarly, I imagine the improved sales/cost growth could be explained by the degradation of assets i.e. relaxing payment terms in order to win more business. How sustainable these practices are I do not know but living in denial by not even talking about profits or the lack thereof is a recipe for disaster.

Sunday, August 17, 2014

Playing With CRM Data In Excel

In terms of Microsoft software, my second passion after CRM is Excel. I have used Excel to solve Maxwell’s equations, valuing the entire New York and Australian stock market for bargains and I use it to manage the household budget. Here are some graphs from the spreadsheet solving Maxwell’s equations.


Excel has come a long way since I put this spreadsheet together and the new BI tools make Excel ideal for analysing CRM data.

Giving Credit Where Credit Is Due

Firstly, I owe a big thanks to Jukka Niiranen for two posts in particular. They are here and here and are the essentials for connecting Excel to CRM. Thanks to Jukka, I now get Excel to talk to any Dynamics CRM implementation, regardless of deployment mode. What is annoying it I did not read these sooner, given he wrote them over six months ago. I will summarise the main takeaways of Jukka’s blogs below in regards to connecting Excel and Dynamics CRM and update them.

Closing the Loop

You may remember, a while ago I showed a way of getting Power View into CRM.  I tried replicating this for this blog post and I struggled. It seems the newer versions of SharePoint online do not like displaying files in an iframe which trips up my old trick. The dream of creating an Excel spreadsheet of CRM data, which can be refreshed, and have it displayed in a dashboard, is not quite possible for CRM Online but maybe soon.

***STOP PRESS*** Speaking to Mark Rettig of Microsoft, he reminded me of the trick to get the spreadsheet to work. Change the end of the URL to say “…=embedview”.

Connecting CRM to Excel

The obvious way to connect CRM data to Excel is via the CRM export. On practically any list of data in CRM you can export the data to Excel.


There are two main options for exporting data: static and dynamic. Static copies the data and dumps it into Excel. It works for any deployment method but to refresh the data, you have to export again, which is annoying. This is where the dynamic export comes in.

A dynamic export does not copy the data but rather creates a FETCH query in the Excel spreadsheet which retrieves the data when the Excel file is opened. The main drawback with this method is it needs the Outlook client to broker the connection. So what do you do if you do not have the Outlook client?

OData Connections

The next best option is the Organization Data Service. This provides an OData (REST) connection to the data in CRM and is one way developers chat to CRM. For those of us who cannot code, we access this via the web address at Settings-Customizations-Developer Resources.


You can stick this into a browser and it will return a list of CRM tables. You can even add a table to the end of the URL, say AccountSet, and you will get an RSS feed of the Accounts in CRM.


Fortunately, Excel understands OData, so all we have to do give Excel this address, a login and password and we have a dynamic connection.

Out of the Box Excel OData Connections

For Excel 2013, in the Data tab of the Ribbon, in the drop-down for ‘From Other Sources’ is an OData option.


Selecting the OData option asks for the URL, login and password, as expected.


This works for on-premise IFD implementations but consistently fails for CRM Online.


Apparently, Excel’s standard OData connection does not support passing through the Live ID or Office 365 credentials. Until Jukka’s articles, this had me stumped.

Excel’s Power Query

There are a whole bunch of new and exciting add-ons for Excel. One of these is Power Query, which can be downloaded here.

Power Query provides a whole range of new data query options like Online Search (which lets you search online data sources), MyData database source and, of course, OData in the ‘From Other Sources’ option.


Unlike the out-of-the-box OData connection, this one plays nicely with CRM Online.


What To Do With the Data?

Out of the Box, Excel 2013 comes with Power View, allowing you to display and manipulate your data in new ways (image care of Jukka).


Another add-on from Microsoft, currently in ‘Preview’ is Power Map, downloadable here (previously called Geo Flow). With Power Map, you can take data with address fields and Excel will use Bing Maps to plot your data. In this image, I take a few thousand account records from CRM, using the OData connection, plot their location and color-code them based on their industry.


The tool also allows you to create video fly-overs of your data for presentations and add soundtracks and commentary. There is also a layering option so you can overlay different kinds of data and compare them.


Microsoft is investing a lot of money into the Excel BI tools so, if you have a CRM system, and want to gain insights from the collected data, Excel is a good place to start. In the past it was difficult to get Excel to work with CRM Online but, this seems to be a thing of the past and, with tools like Power Map, it is possible to present and explore data in new ways. I strongly recommend upgrading to Excel 2013 and checking out what is available.

Sunday, August 10, 2014

Getting Demos for Social Listening and Dynamics Marketing

It is exciting times in Dynamics CRM with Microsoft’s acquisitions getting released in the wild. The two big ticket items are Social Listening (social monitoring tool) and Dynamics Marketing (a comprehensive marketing management tool).

Both of these are available from Office 365, but what if you want to try before you buy or you are a partner seeking to demonstrate the Social and Marketing goodness to your customers and prospects? It is not easy to work out the options available so here they are.

Options For Microsoft Partners

As a gold partner, you get access to both products as part of your internal usage rights, as well as Dynamics CRM. Partners receive special codes to use in an Office 365 subscription. If you do not have your codes, get in touch with your PAM (Partner Account Manager). This, of course, is not ideal for demonstrations because it is one instance for all prospects but it is better than nothing.

There are also the TAP (pre-release) programs which give early access to the new releases of the software although these are generally held under a non-disclosure agreement so you cannot use these for demonstration. Again, if you are not aware of the TAP programs, for you and your clients, ask your PAM.

There are also training materials on PartnerSource for Dynamics Marketing to gain more information.


Options For The Rest Of Us

For non-partners, there are plenty of ways to gain information. For Dynamics Marketing, a good start is the Microsoft Dynamics Marketing page. From here you can see a video overview and do a guided walkthough. However, as of writing, this is as good as it gets. I know of no time-limited or function-limited demonstration for Dynamics Marketing. Perhaps this will change with the fall release of CRM.

For Social Listening, there are plenty of training materials and videos. There is also a Microsoft Social Listening page. From here, you can also do a guided walkthrough but, unlike the Dynamics Marketing page, once you start the walkthrough, you can select a 30 minute trial.


The configuration is locked down but you do get to play with the social analytic tool. If your time runs out, rinse and repeat for another 30 minutes.


The information and demos for Social Listening and Dynamics Marketing is a little scattered and, while there is no available demo for Dynamics Marketing, there is information available to give an idea of what the product looks like and what it can do (not to mention many videos on YouTube for Dynamics Marketing and Social Listening).

Social Listening does have a trial and also lots of information in the form of training materials and videos and, if this is an area of interest, is well worth checking out.

I expect that, eventually, like Dynamics CRM, both of these products will have time limited trials available in Office 365 but, until then, hopefully, these materials will see you through.