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.
PrefixRegion
GYChannel Islands
JEChannel Islands
DEEast Midlands
LEEast Midlands
LNEast Midlands
NGEast Midlands
NNEast Midlands
ALEast of England
CBEast of England
CMEast of England
COEast of England
IPEast of England
LUEast of England
NREast of England
PEEast of England
SGEast of England
SSEast of England
WDEast of England
BRLondon
CRLondon
ELondon
ECLondon
ENLondon
HALondon
IGLondon
NLondon
NWLondon
RMLondon
SELondon
SMLondon
SWLondon
TWLondon
UBLondon
WLondon
WCLondon
DHNorth East England
DLNorth East England
NENorth East England
SRNorth East England
TSNorth East England
BBNorth West England
BLNorth West England
CANorth West England
CHNorth West England
CWNorth West England
FYNorth West England
LNorth West England
LANorth West England
MNorth West England
OLNorth West England
PRNorth West England
SKNorth West England
WANorth West England
WNNorth West England
BTNorthern Ireland
ABScotland
DDScotland
DGScotland
EHScotland
FKScotland
GScotland
HSScotland
IVScotland
KAScotland
KWScotland
KYScotland
MLScotland
PAScotland
PHScotland
TDScotland
ZEScotland
BNSouth East England
CTSouth East England
DASouth East England
GUSouth East England
HPSouth East England
KTSouth East England
MESouth East England
MKSouth East England
OXSouth East England
POSouth East England
RGSouth East England
RHSouth East England
SLSouth East England
SOSouth East England
TNSouth East England
BASouth West England
BHSouth West England
BSSouth West England
DTSouth West England
EXSouth West England
GLSouth West England
PLSouth West England
SNSouth West England
SPSouth West England
TASouth West England
TQSouth West England
TRSouth West England
CFWales
LDWales
LLWales
NPWales
SAWales
BWest Midlands
CVWest Midlands
DYWest Midlands
HRWest Midlands
STWest Midlands
SYWest Midlands
TFWest Midlands
WRWest Midlands
WSWest Midlands
WVWest Midlands
BDYorkshire and the Humber
DNYorkshire and the Humber
HDYorkshire and the Humber
HGYorkshire and the Humber
HUYorkshire and the Humber
HXYorkshire and the Humber
LSYorkshire and the Humber
SYorkshire and the Humber
WFYorkshire and the Humber
YOYorkshire 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.