Investigating crime data at small geographies

A bit of background (skip if you’re familiar with h-map)

I’ve been doing some work with Herefordshire charity the Bulmer Foundation (and a lot of other organisations) to create a central resource of data about how sustainable Herefordshire is becoming. The Foundation has been working on this for some time and, following a lot of consultation, they have identified the key indicators of sustainability for the county.

If these things are getting better, Herefordshire is developing more sustainably. If these things are getting worse Herefordshire is developing less sustainably.

Identifying the indicators is important but it’s not the whole task. Next we have identify the data that tells us if things are improving or not for each indicator. Then we have to display it in a useful way.

The Foundation has commissioned some development work which is still in progress but play-about-with-able at

Understanding how things are changing at the whole county level might be interesting but for most practical purposes it may not be useful. We need to get into the data at much smaller geographic levels. As a way of investigating how this might work I decided to investigate crime data which is a nice, rich dataset.


This post is really a set of notes about how I processed the data. Partly for my records but also to encourage les autres and to enable people to suggest ways this could be done better.

I’ll write about the implications for the h-map project on the project blog in due course.

The website publishes crime data in a number of ways. There is an API but I haven’t explored that. Humans can download see some data on maps and they can elect to download some CSVs

I asked for everything they had on West Mercia ( the police force serving Herefordshire, Worcestershire, Shropshire and Telford). I received a zip file containing a bunch of csvs. One csv per month for all of the months starting Jan 2011 and ending Decemb 2014. Each csv contains a row for each crime recorded across the force area. Against each row is a type of crime, lat, long, outcome and sundry other data including the LSOA.

That’s great but much more than I need. All I ultimately want to know is the number of crimes recorded per year in each LSOA. If I had better coding/scripting skills I could probably have got a few lines of Python working on the task for me. But I don’t.

Also it was an excuse to play with This new cloud thingy has sprung from the Dapaas project involving the ODI and other cool linked/open data folk. It allows you to create transformations in a reasonably point and click manner. There is a pipeline metaphor so transformations proceed sequentially. There is a bit of a learning curve and the documentation is early stage but I got the hang of it. It allows you to preview the effect of your transformation on real data which enables effective pressing of buttons until the right thing happens.

So, after a bit, I managed to build a little transformation that pulls out rows here the name of the LSOA contains “Herefordshire” and then creates a 2 column table from this with LSOA and month the respective columns.

I still have 1 csv for each month. It might be that datagraft will append rows onto an existing column but I couldn’t work out how to do this.

So I had a happy couple of hours uploading each csv and downloading the processed file.

What I was aiming for was number of crimes per year per LSOA. So I had to get my monthly files into 1 big yearly file. Which I did manually with the assistance of the excellent TextWrangler application. It really made this tedious manual task a breeze.

Then a simple pivot table for each year gives me the totals I was looking for.

There was a little bit of spreadsheeting to decide if crime was improving, worsening or not changing.

And finally the application of Google Fusion Tables to link the LSOA codes in my dataset to polygons (described as KML) and I have a lovely map painted red, amber and green.

Datagraft enables me to save the transformation so when all of 2015’s data becomes available I’ll be able to repeat the process. It also enables me to to publish my dataset and to RDF it.

Maybe next week for that.

If you have any suggestions for ways I could cut out steps, or improve my data wrangling I would love to hear them.

Things your web team didn’t tell you about Google Analytics.

Photograph: Monkey seems to whisper into the ear of another monkey.You have a lovely shiny website. Your in-house team or your external contractors wired it up to Google Analytics account and handed over to you (in marketing, digital or comms). They may even have muttered something mysterious as they left.

But there are some things they didn’t tell you.

They (probably) don’t actually know much about Google Analytics.

Now some of my best friends build websites and I promise you that they know a lot of clever things. (I am already wincing in anticipation of their outrage as they read this, but let me explain).

Your web team definitely know some things about Google Analytics. They installed the tracking script. They made sure it worked and, I imagine, they can talk with great confidence about bounce rates, pageviews and unique users.

Which is the sort of thing webby people care about.

But that’s not even half of what you can find out about your users. Analytics tells you who is visiting, how they got there, and what they do on the site. And it blends those data so if you want to know “these people that signed up for our newsletter, where did they come from?” it will tell you that.

Google Analytics is an immensely powerful tool and your web team probably knows how to get the most out of it for web things. But there is so much more.

They are oblivious to the joys of Campaign Tagging

One of the many fascinating things about Google Analytics is that the useful stuff doesn’t happen on your website. It happens in Google’s data centres.

The tracking script your team installed collects a bunch of stuff each time your users visit a page and sends it off to Google who turn it into useful reports for you.

But the tracking script is limited by what the user’s web browser will tell it. And that is limited by what it knows.

One of the areas that is a real problem is “referers”. Essentially the Google Analytics script asks the browser, what page brought you to this site ? But the browser often doesn’t know, especially if the user clicked on a link in the Facebook app or in an email (like the ones you send out to get people to your site).

So you are missing some crucial data.

But don’t call your web team, they can’t help you.

The solution is in your own hands. You need Campaign Tagging. Essentially you add some codes to the end of the links you share. The tracking script sees these and uses them to understand where the user found the link.

It’s all handled away from your website so your web team is quietly oblivious.

They are unmoved by the awesomeness of attribution modelling

Create some goals (you know, the things you want people to actually be able to do on the website) and Google Analytics will tell you amazing things about your users.

It will tell you which pages are really helping get people to the goal and which pages are really causing a problem.

It will tell you which marketing channels are really working for you and which are a waste of time (and money). And it’s pretty sophisticated. Let’s say you run an advert on facebook which brings people to your website but they don’t go on to buy your thing. Should you give up on that advertising channel? What if those people come back and are more likely to buy next time? You could be cutting off your nose to spite your face.

Google Analytics will give you the answer to this.

But, I’m afraid, your web team probably won’t.

And if you want a little help, give me a call.

Image credit: Can you keep a secret? by jinterwas used under a Creative Commons licence

Don’t turn an emergency into a crisis

(this is cross-posted from LinkedIn for completeness)

Diagram shows crisis in a circle and emergency in a circle. The circles overlap but only partly.

I tell people that I work in emergency communications and, to be honest, most of them suddenly find they have an urgent appointment.

The vast majority of those that are too slow to make a convincing excuse will almost immediately say

“So, you work in crisis comms do you?” and I will almost certainly say

Yes” because I don’t want them to leave and, really, what does it matter?

Actually I think it matters quite a lot.

Crisis comms and emergency comms are not indistinguishable and being skilled in crisis comms won’t, of itself, help you when an emergency calls for your communication expertise.

It’s like this.

Crisis comms is concerned with the health of the organisation. Emergency comms is concerned with public safety.

Embarrassing tweets, chief executive suddenly resigning, and a leak of customer data all fall into the bucket marked crisis communications.

Floods, fires, and terror attacks all fall into the bucket marked emergency comms.

And there is great potential for overlap. If your business is producing food, and you accidentally introduce food pathogens into delicious cooked things, this is both a crisis and an emergency. But frankly any sensible crisis comms practitioner will tell to to do everything you can to keep people safe before you start worrying about the reputational fall out (so it’s an emergency).

Many of the practical approaches will be the same too. If you have a team ready, willing and capable you can call upon at 3 o’clock on a Sunday morning to help warn people about impending dangers they’re probably going to be pretty good in a crisis too.

But there are differences. At the top of your emergency comms plan will be written your default strategic aims:

  • protect life
  • keep people safe
  • protect the environment
  • support the affected
  • protect property
  • return life to normal where and when possible

At the top of your crisis comms plan will be written:

  • do the right thing
  • behave like a human
  • don’t hide or run away from the crisis
  • reassure and inform stakeholders

Your tactical decision making in an emergency should be driven by a simple cycle:

  • who needs to know what?
  • how will I let them know?
  • how will I find out they have heard?
  • how will I find out they have responded as a result?
  • who needs to know what, now?

Something similar wouldn’t go amiss in a crisis too. In an emergency though the situation is complicated because in a whole set of organisations there are other comms professionals going through the same cycle. And some of them are also in a crisis as well.

To make sure people are effectively warned and informed in an emergency requires the coordination and sychronisation of a whole set of organisations with the same goals in common but not the same tasks at hand.

Both practices need senior and experienced communications professionals, they need trained and practiced comms staff throughout the process and they need good quality and effective leadership within organisations.

But they are not the same thing. And one of them is more important that the other.

What happens next and who decides?

Middle-aged man in corduroy jacket sits with arms folded on a stage with a black backdrop
Photo by Chris Boland /

If you read the Guardian you probably won’t have been able to miss the fact that Paul Mason (from Channel Four News) has a new book out. It’s called PostCapitalism.

You should read it.

It’s a good book, well argued and interesting. And, you know, book-length. I mention this because the temptation is to try and explain in a blog post what Paul Mason has taken chapters to explain. I’ll try to resist this temptation.

Broadly he explains how the information revolution is threatening market-based capitalism. Then he suggests a way we could organise our economy better and proposes some ways to get there (my resistance didn’t last long).

Not everyone is going to agree with his vision for a future economy (he’s a bit of a lefty) but his arguments about how the information revolution are challenging market capitalism are interesting and fairly urgent.

One of his arguments is that the action of free markets will tend to reduce the price of information goods to close to zero. This is bad for people trying to make money out of information goods. The strong temptation for these people might therefore be to reduce the freedom of markets (like demanding more protection for intellectual property). Or to go bust.

This seems like a pressing problem for people trying to make money out of information goods (which is most businesses) and for society as a whole.

Paul Mason’s solution may not be the only or even the best solution but we do really need a solution.

Or we’ll sleep-walk into a very different future decided by people who do not have our best interests at heart.

Anyway I strongly recommend you read and argue about this book with immediate effect.

Why my next laptop will be a MacBook

A couple of years ago my ancient Dell laptop finally gave up the ghost and I went looking for a replacement. I wanted something lightweight, fast and capable of running Linux.

(I know, I know: you’re shouting “MacBook”)

I rejected MacBooks out of hand based on, frankly, bare prejudice.

I wanted to buy the Dell XPS DE which is an ultrabook with Ubuntu pre-installed but I couldn’t find a way to get them to sell it to me.

I’m a bit of a Google fanboi so I was interested in the immensely cool looking but scarily expensive ChromeBook Pixel. When I learned that through the Crouton package I could run full fat Linux packages on the Pixel I jumped and invested £1K of my own, hard-earned money.

And I loved it, still do. It’s beautifully made, starts in seconds and attracts admiring glances wherever it goes. Increasingly I just use the ChromeOS as I live in the Google ecosystem much of the time anyway.

So why will my next laptop be an Apple?

Because there’s more to selling laptops than delivering cool kit.

It started early on. The insulation on the charger started to fray. I contacted Google support and they leapt into action.

“The way this is going to work”

they explained

“Is we’re going to place a £1K charge on your credit card and send you a new ChromeBook and charger. Assuming you return the old machine to us within the correct timescale, we won;t take the money from your card”

“Well that’s not going to work for me”

I said

“Leaving aside the fact I don’t want you to muck around with my credit card, it’s just the charger that’s broken. I’ve customised my machine and I’d rather not have to go through all that again”

Obviously they disagreed that I *had* customised my machine or that such a thing was possible (it is). But through dogged persistence my query was escalated through various layers until it reached someone who decided the simplest thing would be just to send me a new charging unit.

At no point did I ever feel anyone was trying to resolve my problem. They were perfectly polite but they weren’t, in any meaningful way, helpful.

In a while the insulation began to fray again and this time there was a smoother process to exchange my broken kit for a shiny new item (which seemed much improved in the insulation stakes suggesting I had not been alone).

And now I have managed to lose my charging unit. It’s annoying and it may be returned to me but there’s no guarantee. I need something to power the machine.

Google no longer offer a suitable charging unit in their store. So I spent a fruitless and frustrating period on a text chat with a very polite support person who totally failed to solve my problem and, in fact, repeatedly gave me the incorrect advice that the Universal Charger in the Google Store would work with my device (it really won’t).

Eventually they escalated my request elsewhere and I have just received an email confirming that Google don’t supply this charger. They have no arrangements with third parties to supply chargers. They did suggest a search term I could use to hunt for third party kit.

It’s not the end of the world because a trip to Maplin will enable me to procure a properly universal charger.

Just as soon as Google confirm the specification for the DC power supply, which they have only partly managed so far…

It’s absolutely clear now that the 2013 ChromeBook Pixel was a Beta product. The current model of the Pixel looks awesome, is more sensibly priced and has a much longer battery life. I can’t recommend that you buy it though.

I’m down with the Beta testing. But I probably wouldn’t have forked out a grand if I’d known I was taking part in a testing programme.

So when the time finally comes to buy a new laptop, I’ll be nipping to the iStore. Because I trust Apple to look after me more than I trust Google. And that’s brand value.

Am I normal?

I should probably have written this a few days ago.


Are we normal?

It all started when I was training some local government web folk on the dark arts of Google Analytics.

One of the pieces of data that Google Analytics (or indeed many other analytics tools) will give you about visits to your website (which Google Analytics calls sessions) is what proportion of them are “bounces”.

Someone bounces on your site if they only view one page in their visit*.

So if your page is there to provide helpful information, you’d probably be quite pleased with a high bounce rate because it suggests people are searching for information, finding it and leaving again.

On the other hand you might have designed a site which encourages people to browse around, stumbling across new things. In which case bouncing would be bad.

And, of course, local authorities are service providers. If people come to your website to pay for things, request things, or book things then they (hopefully) won’t bounce either.

So the question these folk asked was “Is our bounce rate typical for local government?”. Which is an interesting question and one I couldn’t directly answer.

Is that the right question?

Now obviously the key question should be “Is it the bounce rate you expected when you designed the site?” In fact I argue you should always know what good looks like before delving in to analytics tools.

But it’s always nice to compare to others. It’s always useful to be able to think “I would expect my site to behaving very much like X council” and then find out if it is or isn’t.

And a question that always bothered me when I was responsible for local government digital services was what level of mobile traffic I should expect. Other councils had higher proportions of mobile traffic than my council. But was this a factor of serving a rural population? Was it a factor of the sort of services and content we provided? Was it an aspect of the way we marketed our services.

The more I thought about it, the more interesting (and useful) I thought it would be if we could see this data for each local authority.

So I decided to ask them.

It’s my fault

So, I’m afraid I’m responsible for a flurry of FOIA requests flying around the country. I’ve tried to keep the request specific and simple to answer.

I hadn’t quite appreciated how much email traffic it would generate or how much admin it would involve me in. But hopefully in a few weeks I should have a large dataset of some key metrics.

This is not about league tables or declaring winners: the only correct bounce rate is the one you intended for your site. This is about trying to help describe the ranges and differences between types of authorities and different parts of the UK.

I’ll be sharing it all back and I hope that it will be a useful contribution to the sector.

That said, I may think twice before embarking on a mass FOIA again…

*Inevitably it’s actually more complex than that but that’ll do for the purposes of this blog post.

Four steps that will transform your relationship with Google Analytics


The world is divided into two groups of people

The first group has Google Analytics wired into their website. They have dashboards set up that they check daily. They run weekly and monthly reports and pour over the data with confidence and enthusiasm. They advise managers in their organisation of changes that should be implemented in the website and in services in general based on the insights from this data. And then they see the fruits of their labour reflected in the reports they run next month.

The second group also has Google Analytics wired into their website. This group logs in every so often and pokes about in the back-end in a desultory fashion. They look at some graphs which seem to be going in the right direction and print them out and send them to some people who examine them in a somewhat confused manner. Nobody changes anything as a result of insights gained from the data.

I’m going to guess that you are in the second group. Most people are. Google Analytics is extremely widely used and very powerful but it’s very power can make it seem confusing. Even if you are confident interpreting what it is telling you affecting change in the rest of the organisation can be tricky.

Here are four simple things you could do differently that should help

1. Never look at Google Analytics unless you have a question that needs answering.


Google Analytics is a reporting tool. It tells you what happened and when. It reports an awful lot of things. Most of these things aren’t relevant to you right now (possibly ever). Decide what it is that you want to know and your journey into the data will be smooth and pain-free.

2. Decide what you think the answer should be before you look

Have you just run a marketing campaign? Did it go to plan. Logically then lots more people should have visited the service you were promoting right? You would expect to see sessions and probably pageviews increase. You would expect to see lots of people visiting your site via Facebook (if that’s where you ran the Campaign) and so on.

Decide what you think success would look like and then you are looking for a yes/no answer: did it happen as I was expecting?

3. Be clear about what the link is to the organisation / service objective

It’s nice when people visit your website but there has to be more to it than that. If you sell things then, presumably, the service objective would be to… er… sell more things.

In other walks of life (say local government), perhaps you hope that people will be less likely to phone you if they’ve gone online (and so will save you money) or will be able to do something new that will help them in their lives.

If you can’t see the link to the organisation / service objective then you’re going to find it hard to do anything useful with the Google Analytics insights. If that is where you are, put the analytics book down and go and talk to the service.

4. Understand what you will do with the answer

Essentially you can recommend three broad things as a result of what the data show:

  • carry on doing the same thing (because what you are doing is working)
  • do something different (because what you are doing is not working)
  • stop doing anything (because what you did worked so well the job is done)

Make sure you know will actually make that decision in your organisation. If you know what the link to the service or organisation objective is And then understand what information they are going to want from you.

Some people might simply want an email saying

“I’ve had a look at the website stats, things aren’t going to plan. Unless we do something different you’re not going to meet your service target. I suggest we switch our focus to Twitter for the next few weeks”.

Some people (like me) will want a graph for every step of the way

“ Before we started the campaign visits to the page looked like this: [GRAPH]. We have been running the Campaign for four weeks and visits have changed like this [GRAPH]. As you can see the few people that have used the service came via Twitter [GRAPH] so I recommend we switch our emphasis to Twitter for the next few weeks. I’m sure I don’t need to remind you that your service target is for lots more people to use this service [GRAPH]”

And some people just love the numbers instead (accountants especially).

Every journey starts with a first step

These steps will not turn you into a Google Analytics ninja overnight. But they will mean that your relationship with the platform starts to become more productive. Gradually, step by step, it will become less confusing and more useful. And before you know it you will be well on the way to joining that hallowed group of people making analytics work for them and their organisation.

My company The Likeaword Consultancy can help you get the most out of Google Analytics.

It’s all about the podcast

For several months now I’ve been recording a podcast with Helen Reynolds from Social for the People. We talk about things that have been in the news that interest us: a bit of social media, government, housing, data, emergencies and PR generally. Sometimes we have a guest.

It has been and continues to be an interesting learning curve. In fact Helen has just posted some tips for new podcasters.

The feedback so far has been pretty positive and so we’ve gone so far to set up a proper home for the podcast online: The Natteron Podcast. We really appreciate the fact that people listen and that they go to the trouble to get in touch about the show.

If you’ve got a suggestion, comment or question please do drop me a line or comment below.

Oh, and the latest podcast, Sixth (non)sense, is up. Have a listen, it includes:

  • Mike Bracken’s dramatic departure from Government Digital Service
  • The company that banned emails and managers
  • Hacking cars
  • Festival of Code winners
  • The value of the Chartered Institute of Public Relations
  • some stuff about analytics


Just in case anyone is still having arguments with civil engineers

A recurring theme in my professional life in local government web / comms has been whether to call the thing on the side of the road where people are supposed to walk the pavement (like normal people do) or the footway (like civil engineers do).

In my happier moments I like to believe that those days are behind us.

But just in case they aren’t here’s the Google Trends data for those two terms.


I think the pavements have it.