As part of my recent delve into sales analytics, I wanted to investigate the performance of my employer across the UK, our main sales market.

A Map of UK Regions
UK Government Office Regions

Since we had customer data from invoicing and shipping that included the UK postcode, this seemed like the most obvious way to group the data for a nice visualisation of sales across the UK. The problem with postcodes is that there are a lot of them, and plotting every one on a map vis would be cumbersome and look awful. So the question then became how best to group postcodes to give a simple and informative overview of the data. The choice here depends on how broad of a region we want to summarise, going from towns up to whole countries (England, Scotland, Wales & Northern Ireland).

Based on a quick bit of googling I settled on UK Government Office Regions, which categorise parts of the UK based on the highest level of Local Government, and form nicely sized regions on the UK Map. The next thing to do was find a source to map postcodes to regions so I could assign individual sales records to their respective areas. Unfortunately this proved a little more difficult than expected.

While there are a number of blogs and sites that have organised postcode prefixes (the first part of the code, without the number) may had conflicting assignments, and little explanation of how the assignments were derived. Another Blog by Robert Sharp used assignments from a map retailer to create the sort of table I was looking for but checking this against the official UK regions highlighted some errors, including the omission of one of the regions, that meant I decided to have a go at creating my own version. The rest of this post will cover how I assigned the postcodes to regions and created a visualisation in Microsoft powerBI to show the geographical sales data.

Screenshot of the ONS geoportal
Screenshot of the ONS geoportal.
Prefix Region
GY Channel Islands
JE Channel Islands
DE East Midlands
LE East Midlands
LN East Midlands
NG East Midlands
NN East Midlands
AL East of England
CB East of England
CM East of England
CO East of England
IP East of England
LU East of England
NR East of England
PE East of England
SG East of England
SS East of England
WD East of England
BR London
CR London
E London
EC London
EN London
HA London
IG London
N London
NW London
RM London
SE London
SM London
SW London
TW London
UB London
W London
WC London
DH North East England
DL North East England
NE North East England
SR North East England
TS North East England
BB North West England
BL North West England
CA North West England
CH North West England
CW North West England
FY North West England
L North West England
LA North West England
M North West England
OL North West England
PR North West England
SK North West England
WA North West England
WN North West England
BT Northern Ireland
AB Scotland
DD Scotland
DG Scotland
EH Scotland
FK Scotland
G Scotland
HS Scotland
IV Scotland
KA Scotland
KW Scotland
KY Scotland
ML Scotland
PA Scotland
PH Scotland
TD Scotland
ZE Scotland
BN South East England
CT South East England
DA South East England
GU South East England
HP South East England
KT South East England
ME South East England
MK South East England
OX South East England
PO South East England
RG South East England
RH South East England
SL South East England
SO South East England
TN South East England
BA South West England
BH South West England
BS South West England
DT South West England
EX South West England
GL South West England
PL South West England
SN South West England
SP South West England
TA South West England
TQ South West England
TR South West England
CF Wales
LD Wales
LL Wales
NP Wales
SA Wales
B West Midlands
CV West Midlands
DY West Midlands
HR West Midlands
ST West Midlands
SY West Midlands
TF West Midlands
WR West Midlands
WS West Midlands
WV West Midlands
BD Yorkshire and the Humber
DN Yorkshire and the Humber
HD Yorkshire and the Humber
HG Yorkshire and the Humber
HU Yorkshire and the Humber
HX Yorkshire and the Humber
LS Yorkshire and the Humber
S Yorkshire and the Humber
WF Yorkshire and the Humber
YO Yorkshire and the Humber

Luckily for me, in the UK we have a civil service obsessed with statistics and they very kindly maintain a large dataset with all the information I would need. All the data is under an Open Government License meaning you can use it for commercial and non-commercial activity and manipulate it as you like, as long as the source is acknowledged. So here’s where I got the data.

powerquery columns
Columns in the power query table

The csv that you can download is a huge file with all sort of interesting geographical data that I wasn’t really interested in and has over 2 million rows, so it cant be opened in excel, but you can import it directly into powerBI and trim it down using power query. I used the “choose columns” option to keep just the postcode, and region code for each record, then split the postcode and kept just the 1st section (the first 4 characters, including a space where it was present). Next I did a quick check to make sure that the 4 character prefixes only appeared in 1 region. Actually they didn’t, a large number of codes appeared twice, and some even 3 times across different regions, although there was always a clear majority in one region.

This meant I couldn’t just use the prefixes and this table to assign my sales records as there would be multiple possible regions for each record. Instead I simply split the postcode prefixes into the region they appeared in the most, using and max value index match in excel, as a somewhat crude assignment for my purpose. This left me with 3090 Prefixes assigned to a UK Region, you can get the assignments as a CSV here. Based on the same method, I created a second table for the 1 and 2 letter postcode prefixes, again to the regions they appeared in the most (based on the whole dataset, not the set with number duplicates removed). You can get that second CSV here. Or you can copy and past the table to the right:

Now I had my assignments I could move on to mapping my data records to create a visualisation of sales across the UK. I wanted to map my data on a UK region map, but since powerBI’s built in filled map only works for countries I needed to look a little further to find a solution.

powerBI not only has a range of built in visualisations, there are also a range of community submitted custom visualisations to expand its capabilities. One of the best I have used is the synoptic panel by OKViz. Synoptic panel allows you to create to create a map from any image and assign your data to different regions of the image to create great looking visualisations.

Synoptic Designer
The Synoptic Designer Interface

Using their online designer you can create your own map, or use one from a range of existing options. Since there was already a UK Region map I decided to try that one first. The only problem I found was that there were multiple mappings over the different regions, meaning that even though the colour saturation and region assignment worked, the data labels weren’t visible when hovering over the region.

UK Map No Data
Synoptic Map with assigned data, some regions don’t display the data values.

I wanted to be able to see my data when hovering, so I needed to create a new map. First I found a basic outline of the UK regions via google images (see the image top right), I converted that into a binary mask and uploaded it to the synoptic designer. The designer is simple to use, just clicking with the “Magic Wand” style tool highlights the regions allowing them to be named and then the whole image, with assignments, can be saved as an SVG. If there’s anyone interested in using this visualisation the SVG can be found here.

UK Map with Data Labels
My own synoptic panel of UK regions where the data labels appear correctly.

As you can see in the image above, the regions now show both the name and data value when hovered over, which was my aim for this visualisation. In this image I’ve mapped a count of postcode prefixes to the regions, but you can map any data with a postcode column by creating the required relationship in powerBI between your data and the postcode prefix assignment table. I’ve incorporated this visualisation into several dashboards for the MD of my employer, showing data about sales rep activity, sales across the regions and all sorts of other metrics.

Feel free to leave a comment below if you’d like any more information, or if you see any mistakes that need correcting.