Go with the flow

Reading Time: 4 minutes

I’ve been working on a project lately with a small team and a similarly select group of Scottish Enterprise account managers to create a slicker way of bringing businesses into their portfolio.

It’s a bit of a pathfinder project, to figure out how we might use Microsoft Power Platform technologies to deliver new services at scale and at speed.

So we settled on this one aspect of our High Growth account managers’ service to start with: getting new clients onto their portfolio.

The solution we’ve developed involves Power Pages (client-facing) and Power Apps (backend) developments, both reading from and writing to the same database, and all of this data is ultimately available in our CRM system.

All of which is fine. But, as a Service Designer, I instinctively want to be open and transparent about the data we gather. And the Digital Service Standard kinda demands that we are.

I struggled with this for a while. I wrestled in the swamps of the Dataverse against PowerBI, and the best I could come up with was this:

(Actual people’s actual names have been scrubbed out in this screenshot because they’re actual people. But all the data is real, and right now.)

But this wasn’t really what I wanted to achieve

What I was really looking for was a visual to show how well things were flowing.

In agile terms, flow is more important than speed. Or velocity, despite what your scrum training might have told you.

We designed the onboarding process to go through six phases:

1. Account manager action required
   • This is where the account manager can ‘seed’ a draft onboarding document with what they understand about the business, based on the interactions they’ve had until now
2. Waiting for customer to respond
   • Once the account manager is happy with their draft, they can change the status and get feedback from their clients
3. Customer responded
   • The client has responded. They might have changed nothing, they might have changed everything
   • Account managers can choose at this point to bat things back to their client, in which case GOTO: 2 (in BASIC terms) or GOTO: 4 send for approval
4. Sent for approval
   • A team leader needs to approve bringing the business into SE’s portfolio
   • Our research indicates a business will rarely get this far unless it is a shoo-in, because multiple conversations will have take place by this point
     • Nevertheless, it’s an outlier we need to consider
5. Waiting for customer to accept engagement details
   • A team leader has approved admitting the business to the portfolio, and so committed resources to supporting it
   • The contact acting on behalf of the business has not yet accepted these T&Cs
6. Completed
   • The business has accepted our terms of engagement, including:
     • Net zero commitments
     • Fair work commitments
     • What they can expect from us
     • What we, in turn, expect from them

This looks … excessive. But a lot of these steps are designed to slow you down, make you think, and consider what you’re doing. Sometimes, friction is not a bad thing in a design.

The decisions people make during this process – both our staff and our customers – can have consequences in financial and other ways. We want to minimise the possibility of anybody doing any harm to themselves, or anyone else.

Creating a flow diagram

What I ended up doing was this:

1. Create a view in Dynamics CRM that contains the data I want (and excludes the data I don’t want)
2. Export that to a dynamic Excel workbook (I know, the answer is always ‘in Excel’)
3. Fiddle about with that by taking a daily timestamp and plotting it as a stacked area chart
   • To do this, I had to create an Office Script which, basically, takes a snapshot of the state of play and updates a table with new data
   • Then I created a Flow that runs at 6pm every day to execute that script
     • The choice of 6pm is entirely arbitrary, but it doesn’t really matter what time we choose, as long as it’s always the same
4. Then embedding that chart in a Teams channel

The Office script does this:

1. Refresh all the data in the worksheet, so we’re working with the latest numbers
2. Select and define the table we want to work with
3. Generate placeholder data based on today’s date and how the statuses match in our 2 tables (this is just a bunch of COUNTIF functions)
4. Add a new row at the end of the table with the placeholder data, and
5. Copy/paste the values from that last row back into the last row, so the formulas don’t update every time the sheet is opened or edited

This is what we end up with

And, that’s what we want to see. It’s easy to see straight away how work is flowing through this system.

We can also easily measure cycle time and other metrics from this diagram. Right at the moment though, we’re not that interested in volume, or velocity – our high growth colleagues only support a few hundred businesses, and churn is fairly low.

This looks like a fairly happy picture for now.