The OG of Scotland’s CRMUG

With the first ever 365 Saturday in Scotland rapidly approaching an entire month ago, the fatigue has officially shifted but the enthusiasm is still fluttering away under the surface, a fire was definitely lit on 25th January 2019 in the Scottish Dynamics Community.

In the few short weeks since then, all the organising team has caught at least one cold and all returned mostly to normality. Far more importantly, my very good friend and *wipes tear of pride from eye* mentor Mark Christie was named as a Microsoft Business Applications MVP, a tremendous honour that my 5yo son is adamant is worthy of a golden statue.

Without even time for a champagne cocktail to toast his success he got to work on the finishing touches for the inaugural meeting of the Scotland Chapter of CRMUG (User Group) next Thursday evening in Edinburgh, 28th February. As part of a ragtag bunch of overly enthusiastic dynamics mad Scots; Myself and Mark with Iain Connolly, Ryan Maclean, Reece Campbell and the invaluable cat herding skills of Hayley Talent are the OG of the UG.
As are any of the amazing people in this community who will give up an evening to make it along to the event.

Agenda for the event:

Where 365Saturday was a blast! A Hussein Bolt level extravaganza of celebrity and excitement, it was only one day; CRMUG is the marathon. Where #D365SatSco so spectacularly lit a fire, CRMUG will keep it burning.

Whether you made it along to Glasgow and just miss our smiling faces and joyous attitudes or didn’t make it and ended up with a serious case of #FOMO following the Social Media Activity. If you have a few hours to spare next Thursday, 28th February; why not sign up for a free ticket and become part of the OG.

Sign up for ticket and see us in Edinburgh on Thursday 28th February

Using Power BI to inform and enhance D365 Design

My entire presentation at the first ever CRM Saturday Scotland was Cross-Stitch themed and I promise, I will explain why. In fact, if I’d had time I would have made analogue slides (which would have been AMAZING right?) because I am that much of a loser, I would have gifted them to ‘asker-of-the-best-question’ and it would have been epic, but, being short on time I went digital instead – lucky for you blog readers. So… here’s the story.

Picture the scene; I was client side, I wasn’t technical, I had little to no involvement in the requirements capture or the design of our Internal CRM, I was just a little System Admin with an almost OCD level of attention to detail and the patience of a Saint. I was on the other side of the table – where most of our clients sit. It was my job just to make it work. To make it fit for purpose, the odd tweak here or there but to just fit the square peg in the round hole.

Now, to be clear, this is no slight on the Solution Architect on that project…*cough*….*Christie*…. but, I would be asked to produce a report on Market Share when we had no Standard Industry Classification data.
I would be asked to produce data quality reports on a particular data set but where a usable field value was present to run comparisons against third-party data, it was not mandatory and so wasn’t reliable. I was being asked to export and highlight missing or orphaned data for circulation to a team of users… I was even once asked to manually check if a list of contacts was in CRM… It was a CRM export file from a few weeks before!


This – through the medium of digital Cross-Stitch – is the story of how I fell through looking glass and into the world of Dynamics.

So I learned. Fast. And my session and this blog – through the medium of digital Cross-Stitch – is the story of how I fell through looking glass and into the world of Dynamics.

One night, in the depths of my frustration, in my darkest hour of late nights and self study, of trying and often failing to find ways to magic reports into existence …. I discovered @Badasscrossstitch

This was a woman who knew my pain, my exhaustion of having the patience to see something through to the end of a process. Of working stitch by stitch to create something greater than sum of its parts and so I took to embroidering niche IT based phrases in the evenings to relieve stress (albeit far too heavy on the curse words to use in a situation like this).

It was around this same time that I discovered Power BI and it truly was the answer to so many of my problems… excluding, obviously, the insanity induced needlework – there is officially no cure for that. Here was a bit of kit that I could use to report on the data in the CRM and append it with publicly available third party data using excel style conditional clauses to align those data sets in the model, I could then draw in financial transaction data from our ERP to support those reports with actual revenues and costs AND I could easily highlight any data misalignment and validation errors for repair.

This thing was magic.

And suddenly, the data we owned as a business became usable. Not only usable but valuable. The transactions that were captured from all users, all over the country, simply by adhering the processes of the organisation; put your time in, capture the lead source, use the area calculator... I realised that actually what we had on our hands was a goldmine and all I had to do was a bit of digging.

Now I think at this point, it’s important that we stop and we address an often unspoken truth in our game. Nobody likes reporting. Clients don’t really like doing it and would very much like that it did itself though, in the real world, we know that’s not truly possible. So, we often reluctantly accept that and we maybe kick it down the road a wee bit. But it doesn’t need to be that way.

I take you back to that first phrase that I embroidered all those years back, “rubbish in, rubbish out”. They’re words to live by, but fundamentally what a business does isn’t rubbish (sanitation and recycling industries aside), so why is it coming out like that? We don’t often find people saying “well, it didn’t look like that when I put it in!” So, maybe its more about expecting just slightly more out than you put in. Just like that cross stitch. Stitch by stitch, line by line, transaction by transaction – the data captured throughout the hours and days of a business just doing what it does – all coming together to become something greater than the sum of their parts.

Reporting requirements are AS fundamental to the core of a CRM design solution as any functional requirement.  Now, I recognise my bias – I’m a self-confessed graph pusher, I like working with what’s in the system, I don’t see the data in that database as obstructive or getting in the way of further improvement it’s why we do what we do.

And in the same way that, as consultants, we ask questions to look to drive efficiencies into business processes around data capture – we should be asking why organisations report the way they do?

Now there are any number of reasons – we could be looking at particular Industry Body Standards or Regulations, it might be that the annual board submission pack in Mid-November every year is specifically designed not to conflict with peaks but align with troughs in activity – Think Auditors in Q1!
Or it could be what is known as Institutional Memory. Often, people report the way they do because that’s the way it’s always been done.

Institutional Memory: Is a collective set of facts, concepts, experiences and knowledge held by a group of people. It is defined as “the stored knowledge within the organisation.”

Wee Angela who came up that spreadsheet retired about 12 years ago. She’s comfortably moonwalked into the sunset to a time-share in Marbella and left us with this cripplingly specific reporting requirement.
And the best bit is, Angela (while not looking to slander this poor made up woman) probably didn’t even invent it, It was maybe something that her boss, at her old company, used to do.

The truth is, we’re all a bit like Angela, we all have our preferred formats, our preferred tools and we take them with us wherever we go. But how often do we stop to ask why? What is the objective? Where is the value? What is it that the report is trying to communicate?

So the case study discussed is that of a Cash Flow Forecast Report (very suitable to the ERP Stream at #D365SatSco!), not the most glamorous, but I hope fitted the room. For context, it relies on a pretty chunky and impressive bit of CRM implementation. It uses PSA-like data architecture and North52 for code-light real-time calculations and decision tables both of which are amazingly cool but that’s as much as I’m going to say about it because this is all about me.

The fundamental principle is that this was achieved by ascertaining the 2-way requirement. What needs to go in and what needs to come out. Simple, right? We ended up with two requirements that aligned very nicely.

  • “We need to capture the likely billing profile of an opportunity in order to understand our capacity to fulfil and successfully deliver it as a realised project”
  • “We need to be able report on 18 months of forecasted and actual revenue by service line and product type for potential and secured projects billed on a variety of different schedules.”

Not quite rubbish in and rubbish out, let me give you an idea of where the business was starting from. The organisation had weekly 12-month forecast reports submitted from multiple areas of the business. Each of the nine Department Heads had their own format and it took them about an hour to keep on top of the data each week, so they just got on with it and submitted it.

Then on a weekly basis, a senior member of the Finance team would take these reports in their various formats be they excel, pdf once it was even scanned to Head office so arrived as an A3 jpeg?! Not a huge amount to go on, so they’d tally up the key numbers into (yet another) format, spending on average two hours a week to prepare it for board review.

Then the Board would have a little glance at it. Nod, sigh or grunt and it would be filed away and no one would think about it again until the next Thursday…

So that’s nine hours of senior management time, two hours of Finance team time and about 10-15 minutes per member of the board, purely for report generation and review. This doesn’t even include the time of other staff members who contributed information to the report!

So that’s 12 hours per week. One and a half working days every week across the organisation… and these are not cheap people.

Those twelve hours cost approximately £400 a week, a cost to the business of over £20,000 per year.

And why? Nobody was really looking at it, it was never used for version comparison, it didn’t correlate with the actuals in the WIP Report, it couldn’t be monitored for inaccuracies or manual adjustments and couldn’t be used for resource planning. Would you pay £20,000 a year for that?

So this was to be a comprehensive input and output requirements gathering process, not simply a replication of Angela’s spreadsheet. High Ho, we got our pickaxes out and we hooked Power BI up to their legacy system and started to look at their data.

I am now delighted to introduce, the #Mythbuster, a Power BI Report using a mockup of an OOB CRM Account Form. I’ve uploaded some sample data, why not #mythbust your way through the #D365SatSco speakers’ accounts? Consider the following:

  • Which fields would you leave out in a new design? Do you really need a fax number?
  • Challenge objections from departments about how the data is used and collected. “Well, we don’t use that but the graphs department definitely do!” Do they?
  • What are the key metrics of the organisation and do you actually collect what you need? If headcount is an indicator of potential project size, should it be front and centre?
  • Should it, in fact, be mandatory? Or will that encourage people to guess and ultimately lead to inaccuracies?

Access the #Mythbuster report

Self deprecatingly, it isn’t actually that clever but is pretty impactful. By overlaying a visualisation of field population rate on the form, there is a visual prompt for discussion; similarly you could include specific validity criteria to highlight usability. Think number of records with an @ in the primary email field over the count of records in the table and overlay on the form.

Add in a couple of filters so you can display the form per user and per department/division/territory/business unit and bob is officially your uncle. You are well on your way to Mythbusting your way to a beautiful data requirements workshop or perhaps, just re-evaluating your own perception of your organisation’s data.

Get in touch to get the instructions on how to create this report from a standard CRM Export to Excel.

Using a similar process on the Opportunity Pipeline data, we undertook an analysis of the time taken for the average sale to convert… Any guesses?… 18 months not 12 as in the current report.

So, their minds were officially blown. Everything they thought they knew about their data had to be re-evaluated. So, when they began to look at their system requirements they abandoned that institutional memory and they charged on with only the key measures. Those data points that when it came time for collation were on all the spreadsheets (albeit in different places). We looked at the performance metrics for the departments, teams and users and we built them in to the data model to prevent duplication of effort and to align the format of Board submitted documentation and thus, the Cashflow Forecast was born.

View the full report here

And when we came back to check on how they were progressing, we were stunned. We had saved them… almost no time. I was for throwing in the towel and making a permanent move to Arts and Crafts.

So where did that time go? Naturally, there was a period of upskilling and those Senior Managers had to start putting their data in to CRM instead. No easy feat for men of a certain age… and so, it took them a little longer at first to manage the output in the same way. Even the board spent a little bit longer looking at it in the first few weeks – clicking here, filtering there, one even did a pivot!

But then something wonderful happened. They started asking questions. They started challenging the report. They started to look at what was in it.

I was asked to add in modification history to the data set to allow for auditing of the report content and suddenly those poor Senior Managers, who had bought in on the basis of saving hours of their lives, were having to justify, validate and adjust the report – where once they had only to create it and email it into oblivion never to be seen again…

So, remember when I said that I hadn’t included the time for those other members of staff to collate and submit information to their managers? Well, even they were about to be side-swiped by my ‘time saving’ report. Soon, that information had to be put into CRM too. The process was formalised, became auditable, measurable… valuable.

Which brings me to the Finance team. They basically didn’t have anything to do now (they’ll kill me for saying that!). They didn’t have to format, they didn’t have to collate, they didn’t even have to challenge it – because that was coming directly from the board. They had pretty much successfully banked those two hours a week (a minor saving, phew, career change postponed) … they could have headed straight to the golf course or Marbella.

They didn’t. They got a strong cup of nescafe and a packet of chocolate digestives and headed to the server room with a laptop and no mobile. There, they began their own analysis. And, if you’re still with me, this is where it gets really interesting.
Within 12 months of implementation:

  • What was in their forecasting report was consistently more accurate than ever before. In fact, billing accuracy had improved by almost 23%
  • Resource requests were being made, on average, 3 weeks earlier meaning that there were more consistent delivery teams, ensuring efficiencies throughout the lifecycle of projects. Reducing the time spent on internal handovers
  • The ability to cross filter by product and service allowed for a comprehensive reallocation of Overhead costs across departments so that the high-volume/low-margin areas of the business were beginning to prosper

It was estimated that the ROI on the forecasting project was in the region of £30,000 in the first year alone. Definitely not retiring now, eh?

We had done it, we had delivered on both requirements. Each informing the other. We had leveraged Power BI to conduct some serious mythbusting analysis on the true use of their current system. To align their expectations with their true needs, in doing so, we streamlined their data input and improved their business processes – to the extent that they expanded their use to include teams and departments that had not been identified in the initial user group and then used Power BI to deliver the solution drawing in data from across the organisation.

Long story short. Power BI is magic.