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.

places

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.

applications

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

date_errors

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.

app_duplicates

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.

lookups

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.

lookups_wrong

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.

lookups_spare

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.

master_lookups

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.

everyones_lookups

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).

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 “submit@qualityframework.net”

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.

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 ?

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.