Productivity tool

Several people over the years have taken me gently to task over the lack of resources in the PQF. “What’s the point of talking about how well council A is doing something compared to council B without knowing if they are spending twice as much to do it ?”.

This happened again the other day at ALBPO and it pushed me to do something about it. So, without worrying about stitching it into the PQF here is a stand-alone productivity tool. It’s a bit clunky (it asks for a breakdown of applications which already exists in the PQF and drives the ‘headcount’ graph) but if it works and is useful we might use it as the basis for something we use to extend the PQF.

I’ve tried to make it easy. Grab the spreadsheet here. The presentation below should set out the process. It will probably change when a few brave souls have given it a whirl. Let me know what you think (Richard Crawley).


What is this spreadsheet you’ve sent back ?

We’ve made a few changes to the way we process your data recently (skip over the detail – it’s all about “back ends” and stuff), which has meant we’ve changed a few things about the spreadsheet we send back out to you.  The spreadsheet now has lots of tabs on it – this is your cribsheet to understand what they all are and (importantly) whether or not you need to spend time on them.


This is a sheet that sets out who currently has data in the PQF. You can use it to pick your comparators. You want to choose people with recent submissions (column ‘D’) and high success rates (column ‘F’). You can also find contact details if you want to ask your peers something. We don’t need to mediate all these conversations.


This is the sheet you sent in, plain and simple.


This sheet should be empty. It contains any records with date errors – eg applications determined before they were received. Any records on this sheet should be fixed on your system (often we see problems with mis-typed years) so that they are fixed forever. If you just fix them in your applications sheet, the next time you do a download the problem reappears.


This sheet should be empty. It contains any records that have the same application reference. It’s not clear to us why it happens, but some people are running reports that pick up refunds (or partial refunds) as two separate records.


These are the lookups that are needed when we look at your data. You should be able to recreate the lookup table by using a pivot table on the appropriate column from your applications data if you want to check our workings.

A lookup is matched and happy if there is a value in column ‘D’ (system_value). You’ll see some extra values in columns F to I that we use to generate some of the graphs and things – you can safely ignore them.

You’ll need to populate the system_value from the master_lookups tab. I find it easiest to pick one set at a time.


This sheet should be blank. If you provide lookups that were not recognised by the system they are dumped into this sheet. Sometimes we change how we deal with some issue (eg we changed how we think about blank values recently) and so you’ll see a bunch of lookups appear. Sorry. If you understand why they are there you can just ignore them.


This is a sheet that will show you lookups that you presented that were not matched in your applications data. Often this happens because people know that there are potential matches but some of the more unusual combinations of development code / application type are not present in your application data.

However, more usefully, it can be useful as a diagnostic. If you submit a bunch of lookups but your data comes back as being largely unmatched you might find all of your lookups, pain-stakingly assembled, dumped in lookups_spare. This is when you have to carefully work out why there are differences – the lookup must be an *exact* match, including spaces, capitals – the whole lot.


This is the master set of the lookups that we recognise. It is much easier to use if you filter by column A (‘list’) and resize the columns.


Actually, many people are using very similar coding systems. If you want to copy or crib you can use this sheet. It is surprisingly effective at spotting problems or common patterns (although it may be because we’ve spent too long looking at this sort of stuff).


What do I have to submit, again ?

Running these things every quarter is tough on people. It’s just enough time to forget what needs to be done. That’s OK.  This is a quick reminder of what you need to do to keep your data up to date:

  • We want all applications received from the 1st April 2013 to today’s date (note the ‘received’ – things like PS2 reports use date ranges based on ‘determined’). This should be in a worksheet called ‘applications’
  • It makes sense that you also submit the lookups you did last time. This should be in a worksheet called ‘lookups’.
  • These two worksheets should be saved in an Excel workbook called ‘yourcouncil.xlsx’. Note that ‘yourcouncil’ is a specific name your council has, and it needs to be exact for the machine to make a match. Note that this must be the modern ‘xlsx’ format, not the older ‘.xls’ version
  • This should be emailed to “”

Don’t worry about manually trying to keep the ‘lookups’ tab up to date. If (for example) you have decided to make a new type of application (eg one of the new permitted development classes) the machine will create a new line for you in your lookups tab, and describe it as ‘_missing’. You can then do a match and resubmit.


Errors we often see include

  • Using a filename that makes sense to humans not machines (eg ‘application data updated for September submission’ or ‘second try.xlsx’).
  • Using the wrong excel format (eg ‘council.xls’). We need the newer, xlsx format.
  • Submitting only the most recent quarter. We need *all* the data all over again, because some of your older cases may now be determined

Note that some helpful people are submitting data over a longer period to help us understand the impact of the NPPF. We don’t really know what we’re going to do with this data or what sort of analysis we’re going to do yet, but the additional effort for you is almost zero so if you feel like helping out you can start your date range from the 1st April 2010.

New, “standard” report in pdf

We’re sending out a big dollop of reports today.

Several people have always stuggled with them, and there seems to be some kind of security lock-down thing that stops people opening html in their council-provided browser. Corporate IT, eh ?

So we’ve gone for pdf. It has its own set of problems (including looking quite ugly) and it has broken some of the formatting. But we’ve had several new sets of councils join, and some of the reports have been tweaked to make them more intuitive (we hope).

Anyway, if we waited for perfection we’d never do anything. So we’ve mailed them out along with an invitation to talk them through with us. Each plot has some kind of narrative, but I’ve noticed that people struggle to understand what the report says and how they might use it. By far the best way of doing this is to get a bunch of people in the room so they can compare notes.

See you in London ?

Try the customer surveys

Click here to have a look at the 4 PQF customer surveys. The surveys go out by email and ask customers the same 4 things – how did we do? did we manage time well? did we manage information well? did our decision make sense? There’s a surveys for:

  • applicants
  • agents
  • neighbours (that have commented on an application)
  • peers (planning officer’s pass their own judgement on how well the application was handled)

Each survey relates to a specific planning application. Just imagine – feedback on every application you issue decision on.

Your council can use the surveys now for free – just follow the ‘get started‘ instructions. The surveys are administered via the web – each council will have its own surveys account and log-in details.

Use the Forum to comment and let others know what you think.

City regions and a platform for development

We’re often talking to interesting people attempting to do interesting things. Last week I was in Manchester at AGMA’s invitation to meet the heads of planning. They are busy thinking through what devolution means and how to prepare for a Mayor (great song title that).
They helped me to see that our framework can play a useful role in bringing a city region together.

Working backwards from their offer to investors and applicants they need to do two things

  1. Make a city-wide view of development opportunities (a combined SHLAA in the jargon) [more on this later and separately]
  2. Guarantee a good customer service from every planning department

It’s this second one that they can get more-or-less for free from our planning quality framework. By the way you can see the latest version of a report (using real data but not identifiable data) here (dummy report). Those who can read these things will see that the variation across these particular councils is too wide for a city region to pretend it is pulling together. (Council 13 is indeed unlucky for some)

The PQF operates at a very fine level of detail to help people understand how they are performing and (where necessary) work out which particular areas are worth digging into.

All we need to add is a public cut of the data which can act as a transparent performance snapshot. And, which is exciting for me, when they get going on the survey part of the framework this performance guarantee will *not* be based on some silly 8-week target or indeed solely speed-based measures. It will be based on how people think they have been treated.

More on this soon.

Percentages are rubbish (sample datasets are cool)

We use percentages all the time in planning. It’s in the DNA – percentage compliance with a national indicator is what makes many planners get up in the morning. But they are useless at understanding what is going on.

Take this output from a report I was looking at today, pretending I was from Salford. It’s section 2a – a breakdown of approval rates by category of development.


As you’d expect, most of the approval rates are 90%+, so perhaps you should investigate what’s happening with prior approvals and certificates of lawfulness ? But hold on. Continue reading

Explaining to agents what this is all about

Ideally this should be done face-to-face. If you run an agent’s forum, the customer surveys is something you should put on the next agenda.

Here is a presentation created by Medway Council you could adapt and use it at your next agent’s forum:

Medway Council agent forum presentation

If you don’t run a forum, this is a letter you could use as a way of explaining the surveys and the approach to your agents:

Customer letter