Making the data real

Recently I've been lucky enough to be working with Age UK Kensington & Chelsea, helping them make the move to a new database/CRM system. Part of this has work has involved extracting the information from their outgoing system, which has given me the chance to do a bit of analysis.

This heat map shows two years, or thereabouts, of referrals to their Dementia services (advice, 1-to-1 support, befriending, groups and more). Maps and heat maps are fabulous. They certainly take a bit of work to create, but it's well worth it to have them in your reports and funding bids.

In a year or so, we should have much better data to analyse. This is just what I was able to pull out of the old system.

The geeky bit

The trouble with the source data is that it was all linked to postcodes. In places where clients had more than one referral each, or people live at the same postcode (common in sheltered housing), the "dots" on the map would all be on top of each other. Also, GDPR-wise, creating maps of things is a problem. We don't want to send out a map of all the service users' exact locations. A heat map goes a long way to solving both problems, because more than one "dot" in the same place does get more heat, and you can't see individual locations unless you really zoom in.

BUT it can all be solved with a bit of Excel geekery. First I extracted all the postcodes, then used random number generation to introduce some movement in the "dots". This picture shows a whole load of dots that would originally have been on top of each other. The randomisation "smears" them over a wider area. Obviously the tolerance can be tweaked depending on how much smearing is appropriate.