Category Archives: Uncategorized

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.

This is Quality (Part Three)

Part 1 of this series introduced the Planning Quality Framework – the antidote to target based performance management. Part 2 explained how we’d made the framework ‘modular’ and much easier to engage with.

In Part 3 I’ll take you through what the framework actually is, explain a bit more about each module, how it all fits together and show you some of the newer outputs we’re developing.

How the Framework ‘works’



The framework brings 3 sets of data together in one place:

1) Facts: What does the data from our management system tell us?

2) Opinions: What do customers say and think about us?

The greatest compliment ever paid me was when someone asked me what I thought, and attended to my answer.

Henry David Thoreau

3) Development quality: How good are proposals and what eventually gets built?

It turns these 3 sets of data into…


A rounded (i.e. not just speed) set of measures set out in:

• Quarterly performance reports
• Annual performance report
• Bespoke localised reports (we’ll send you a database so you can create your own bespoke, local reports).


The reports help you focus on what’s important, to re-think what you do, and look for opportunities for…

Service Improvement

• Good practice
• Improvement support
• Peer-to-peer learning.

It’s Sector-led

The content of the framework has been developed with the help of the sector. To date, approximately 20 councils have piloted the first part (data). We are currently, with their help, putting the finishing touches on the reports, benchmarked against others.

We are currently testing the surveys with the help of Hastings Borough Council. The surveys capture the views and feedback of a variety of ‘customers’ of the planning service including applicants/agents, neighbours, councillors and staff.


The quality aspect is in two parts. The first is a review of the quality of the planning service itself – managers will review and score the council’s role (e.g. ‘did we negotiate well?’), processing specific applications. The second is a review of quality of the planning decisions on Major developments using a framework used by Camden and based on CABE’s Building for Life criteria.

This data schematic explains how the different data sets fit together:


We’ve only just begun

The “quality” parts of the framework will take time to filter through as we rely on councils to spend time collecting evidence about developments. This will also grow into something more valuable as we develop the framework to go beyond analysing transactions and get to thinking about whole developments. We can’t ignore costs forever either and may suggest a time-sheeting exercise every 2 or 3 years.

Reports – some new ways of looking at ourselves

The Framework reports will provide a rounded picture of what is happening in the service (I’ll blog here with more detail in August). The reports will be tailored for different audiences, and we are experimenting with some new views – here are a few examples:

(i) Development Investment – helping councils understand the investment value tied up in the applications they are processing at any one time:


The ‘Investment estimate’ is based on the build costs for different types of development – these are just PAS estimates for now, so are illustrative only. In this example, the trend is that development investment is going down, but even at its lowest point, this planning department is managing development applications that represent a £20 million investment in this place. Powerful stuff.

(ii) FTE Estimate over Time – how well matched are resources to work volumes?


The ‘FTE estimate’ plot is based on PAS 2012 Benchmark data. Marrying this data together with application volumes, fee income trends and the development investment data, adds a little ‘piquancy’ to many of the resourcing decisions facing the service. It may highlight a number of opportunities to re-focus resources.

(iii) The concept of ‘non-value’ work

A lot of the ‘applications’ in your management system aren’t. Many are what we have re-named “follow-ups” – things like discharge of conditions, and there are certifications and prior approvals. Conditions represent additional work and their inappropriate use is a continuing bug-bear of many customers. We should pay more attention to how we use conditions and on what types of development – the Quality Framework reports will allow you to do so:


The above picture is for Householder applications. I hope that Council ‘I’ is having problems with its data rather than over half of its householder applications having conditions attached.