In a previous article we talked briefly about what you can do with the postal code table of the Netherlands. You can use a postal code table to fill in data on your website automatically. You can also use it to enrich customer data. In this article we give examples in Microsoft SQL Server 2016 of how to enrich customer data with the Dutch postal code file.

Enriching customer data

A common use you will recognize: your street and city fill in automatically when you order something online. Behind the website, a postal code API fills in the street name and the city based on postal code + house number. That API in turn uses a postal code table. Another common use of the postal code table is data enrichment, for example of customer data. You need this enrichment to do good analyses, for example geo analysis in Excel.

Enriching customer data with the postal code table is useful. In this article I show with a few examples how you can do this.

Customer table with postal code/house number

In this example I use a customer table with 5,000 dummy customers. The table looks like this:

This is a simple table with about six fields. A real customer table will have many more columns. We now join the postal code in combination with the house number to the postal code table, to get the Latitude and Longitude coordinates. These are geographic properties that mark a place on earth. When you have these two points, you know exactly where something is on earth.

The postal code table in the example comes from Postcode Store and looks like this:

The postal code table is very detailed: every address in the Netherlands is a unique row. We can enrich the customer table with the last two columns, the ones marked yellow. We do this based on postcodevolledig in combination with huisnummer (house number). Strictly speaking we should also use the addition of the address, but we keep the example simple.

We are going to join the customer table to the postal code table. Note that the customer table has a postal code with a space in it, for example “3318 JW”. You will not find this row in the postcodevolledig table. To make the join work, we replace the spaces with an empty string. Then we make the SQL join from the table klant on the fields “Postcode” and “Huisnummer” to the postal code table on the fields “Postcodevolledig” and “Huisnummer”, and we call a REPLACE function. As most SQL users know, this is not an efficient way to join, because you call a function for every record. It is only here to show that you have to remove the space.

Joining the customer file to the postal code table

This does not quite work, because the postcodevolledig table has several records for one combination of postal code and house number. This comes from the addition of the address. So you have to make sure you make a good join between the customer table and the postal code table. This is the case for postal code 1011 ND with house number 204. When we search the postal code table for this combination, we find these addresses:

The original customer table did not give me an addition to the house number for this address. So I do not know which record from the postal code table belongs to it. This is one of the problems you will run into. The best thing to do now is to use a subquery that gets all combinations of postcodevolledig and huisnummer, and to ignore the addition. The coordinates are practically next to each other anyway. So it does not matter much which of the four you select. We now join the postal code table through a subquery and select the first row. We do this with Row_Number() on the combination of postcodevolledig and huisnummer:

As you can see, I could now join the first postal code that is found, and I take the lat/lon coordinates from the table.

Next we look at how to show the data on a map with Excel Maps.

Showing data in Excel Maps

We now have a SQL query with the following fields. As an extra field we added the revenue. The goal is to show the customers on a map with Microsoft Excel maps. That way we can see which areas of the Netherlands bring in the most revenue.

We export this data to Microsoft Excel. It looks roughly like this:

Then select all data: click a cell with data and press CTRL + A. Go to [Insert], [Map], [Launch Power Map]

Then click “New Tour”. Next, on the right side, tick Latitude and Longitude, and at the bottom of the screen give them the type X/Y coordinate:

If all is well, the screen now fills with colored bars. Click “Next” at the bottom. On the next screen you choose what you want to show. In our case we want to show the revenue on the map:

Cool! Now we see exactly which areas have the most revenue. What else could we add? For example a measure “number of customers” to show the number of customers per area.

Showing postal code areas for sales territories

We can also show postal code areas on the map. For us IT people, a postal code area (postcode4) is a box on the map of the Netherlands. When do you use this? Say you have a few sales managers who each serve a certain postal code area. To show which sales manager serves which areas, we make a dataset. You can find out which areas are not covered yet, or quickly show odd outliers.

It is probably recorded somewhere that person X has to serve postal code areas 1234 to 2345. This is the basis of our dataset. The trick is to split these ranges into single records, unique postcode4 areas. The Netherlands has around 4000 (a few more) postcode4 areas. I wrote an article about this earlier, which you can read here. In the example I assume you have split all ranges into single records. The dataset we want in the end looks like this:

Export this result to Excel, as we did in the previous step. Add an extra column “Land” and fill it with “Nederland” in every row. You need this because otherwise Excel puts some postal codes in another country. Put all data in a table. Select the table and add the power maps.

Now you have to map the country and postal code to the correct geographic settings. It looks like this:

If you cannot find the menu on the right, no worries. You find it at the top of the screen under “Layer Pane”.

Now comes the trick. Click “Next”. Here you decide what you show on the map. Drag the sales manager to category and postcode4nummer to value. Click the icon on the far right for visualization type “region”.

Now you need some patience, because all areas are calculated. The result looks like this:

Conclusion

Enriching customer data is not complicated at all. I hope these examples gave you some food for thought and that you can get started with them yourself. I gave an example of how to do geo analysis with Excel, because most people know Excel. You can also use Microsoft Power BI. It has several map visuals to play with. To plot the data on the map very accurately, you need the Latitude and Longitude. For a small price you can now buy a very complete postal code file with 9 million addresses. You can also do fun things with postcode4 areas, such as drawing postcode4 areas on the map.

You can play with the settings some more. You can also combine datasets. In the example above you could, for example, show sales campaigns as bars, or the revenue as bars. Anything you can think of.

This article is part of a series:

1. Part 1: Dutch postal code table + SQL script for import
2. Part 2: Split postal code ranges into single records
3. Part 3: Enrich customer data with the postal code table
4. Part 4: Visualize data with coordinates in Power BI