In een vorig artikel bespraken we kort wat je met de postcodetabel van Nederland kunt doen.  Een postcodetabel kun je gebruiken om automatisch gegevens op je website in te vullen. Klantgegevens verrijken behoort ook tot de mogelijkheden. In dit artikel geven we voorbeelden vanuit Microsoft SQL Server 2016 hoe je klantgegevens kunt verrijken met behulp van het postcodebestand van Nederland.

Verrijken van klantgegevens

Een veel gebruikte toepassing die je wel zult herkennen is het automatisch invullen van je straat en plaats als je online iets gaat bestellen. Op de achtergrond van de website wordt er gebruik gemaakt van een postcode API die op basis van postcode + huisnummer de straatnaam en de plaats invult. Deze API maakt op zijn beurt weer gebruik van een postcodetabel. Een andere veel gebruikte toepassing van de postcodetabel is verrijking van data, bijvoorbeeld klantgegevens. Deze verrijking is noodzakelijk om goede analyses te doen, bijvoorbeeld geoanalyse in Excel.

Klantgegevens verrijken met de postcodetabel is handig. In dit artikel laat ik met een aantal voorbeelden zien hoe je dit kunt doen.

Klantentabel met postcode/huisnummer

In dit voorbeeld gebruik ik een klantentabel met 5.000 dummy klanten. De tabel ziet er zo uit:

Dit is een eenvoudige tabel met een zestal velden. Een echte klantentabel zal veel meer kolommen bevatten. We gaan nu de postcode in combinatie met huisnummer koppelen aan de postcodetabel met als doel de Latitude en Longitude coördinaten op te halen. Dit zijn geografische eigenschappen die een plaats op aarde aanduiden. Als je deze twee punten hebt weet je exact waar iets zich op aarde bevindt.

De postcodetabel uit het voorbeeld is van Postcode Store en ziet er als volgt uit:

De postcodetabel is zeer gedetailleerd, ieder adres van Nederland is een unieke rij. We kunnen de klantentabel verrijken met de laatste twee geel gemaakte kolommen. Dit doen we op basis van postcodevolledig in combinatie met huisnummer. Eigenlijk moet dit ook op toevoeging van het adres maar we houden het voorbeeld eenvoudig.

We gaan de klantentabel koppelen aan de postcodetabel. Let op dat we in de klantentabel een postcode hebben met een spatie erin, bijvoorbeeld “3318 JW”. Deze rij zul je niet terugvinden in de postcodevolledig tabel. Om de koppeling te kunnen maken vervangen we de spaties door een lege string. Vervolgens maken we de SQL join vanuit de tabel klant op het veld “Postcode” en “Huisnummer” naar de postcodetabel op het veld “Postcodevolledig” en “Huisnummer” waarbij we een REPLACE functie aanroepen. Dit is geen efficiënte manier van een join zoals de meeste SQL gebruikers weten omdat je per record een functie aanroept. Het is echter puur ter illustratie dat je de spatie eruit moet halen.

Koppeling klantenbestand aan postcodetabel

Dit gaat niet helemaal lekker omdat er in de postcodevolledig tabel meerdere records zitten met een postcode- en huisnummercombinatie. Dit komt door de toevoeging van het adres. Je moet er dus voor zorgen dat je een goede join maakt tussen de klantentabel en postcodetabel. Dit is het geval bij postcode 1011 ND  met huisnummer 204. Als we in de postcodetabel op deze combinatie zoeken zullen we de volgende adressen aantreffen:

Vanuit de originele klantentabel heb ik geen toevoeging op het huisnummer meegekregen voor dit specifieke adres. Ik weet dus niet welk record er vanuit de postcodetabel erbij hoort. Dit is een van de problemen waar je tegenaan gaat lopen. Wat je nu het beste kunt doen is een sub-query gebruiken en alle combinaties van postcodevolledig en huisnummer ophalen en niet meer naar toevoeging kijken. Op zich liggen de coördinaten praktisch naast elkaar. Het maakt dus niet zoveel uit welke van de vier je selecteert. We gaan nu de postcodetabel koppelen via een SubQuery en selecteren vervolgens de eerste rij. Dit doen we door Row_Number() in te zetten op de combinatie postcodevolledig en huisnummer:

Zoals je ziet heb ik nu de eerste postcode die gevonden wordt kunnen koppelen en pak ik de coördinaten lat/lon uit de tabel.

Hierna gaan we kijken hoe we de data kunnen visualiseren op de kaart met behulp van Excel Maps.

Gegevens tonen in Excel Maps

We hebben nu een SQL query gemaakt met de volgende velden erin. Als extra veld hebben we de omzet opgenomen. Het doel is nu om met behulp van Microsoft Excel maps de klanten op de kaart te tonen. Zo kunnen we bekijken welke gebieden van Nederland de meeste omzet genereren.

Deze data exporteren we naar Microsoft Excel. Dit ziet er ongeveer zo uit:

Selecteer vervolgens alle data door ergens op een vakje met data te klikken en CTRL + A in te drukken. Ga naar [Invoegen], [Kaart], [Power Maps Starten]

Klik daarna op “Nieuwe tour”.  Klik vervolgens aan de rechterkant Latitude en Longitude aan en geef ze onderin het scherm het type X/Y coördinaat:

Als het goed is wordt nu het scherm ingekleurd met balkjes. Klik onderaan op “volgende”. Op het volgende scherm kan je bepalen wat je wilt laten zien, in ons geval willen we de omzet op de kaart tonen:

Cool! Nu zien we precies in welke gebieden de meeste omzet zit. Wat kunnen we eventueel nog toevoegen? Bijvoorbeeld een meetwaarde “aantal klanten” om het aantal klanten per gebied te laten zien.

Postcodegebieden tonen voor marktbewerking

We kunnen ook postcodegebieden op de kaart laten zien. Een postcodegebied (postcode4) is voor ons als IT-er een vakje op de kaart van Nederland. Wanneer gebruik je dit? Stel je hebt een aantal salesmanagers die ieder een bepaald postcodegebied bedienen. Om te visualiseren welke salesmanager welke gebieden bediend gaan we een dataset maken. Je kunt ontdekken welke gebieden nog niet afgedekt zijn of gekke uitschieters snel laten zien.

Waarschijnlijk is er ergens vastgelegd dat persoon X postcodegebied 1234 t/m 2345 moet bedienen. Dit vormt de basis van onze dataset. De kunst is nu om deze ranges te gaan splitsen in losse records, unieke postcode4 gebieden. Nederland kent in totaal rond de 4000 (iets meer) postcode4 gebieden. Hier is eerder een artikel over geschreven die je hier kunt lezen. Ik ga er in het voorbeeld vanuit dat je alle ranges hebt gesplitst in losse records. De dataset die we uiteindelijk willen zien ziet er zo uit:

Exporteer dit resultaat in Excel net als we in de vorige stap gedaan hebben. Voeg een extra kolom toe “Land” en zet daar overal “Nederland” in. Dit is nodig omdat Excel anders bepaalde postcodes in een ander land zet. Zet alle data in een tabel. Selecteer de tabel en voeg de power maps toe.

Nu moet je het land en postcode koppelen aan de juiste geografische instellingen. Dit ziet er zo uit:

Als je het rechter menu niet kunt vinden, geen nood. Deze vind je bovenaan het scherm onder “Deelvensterlaag”.

Nu komt de truuk. Klik op “volgende”. Hier ga je bepalen wat je op de kaart laat zien. Sleep de salesmanager naar categorie en postcode4nummer naar waarde. Klik op het icoontje helemaal rechts voor visualisatietype “regio”.

Nu moet je even geduld hebben omdat alle gebieden worden uitgerekend. Het resultaat ziet er zo uit:

Conclusie

Klantgegevens verrijken is helemaal niet zo ingewikkeld. Ik hoop dat ik je met deze voorbeelden wat materiaal heb gegeven tot nadenken en dat je met deze voorbeelden zelf aan de slag kunt. Ik heb nu een voorbeeld gegeven hoe je geoanalyse met Excel kunt doen omdat de meeste mensen hier bekend mee zijn. Je kunt ook Microsoft Power BI gebruiken. Deze biedt diverse map visuals om mee te spelen. Om de data zeer nauwkeurig op de kaart te plotten zijn de Latitude en Longitude noodzakelijk. Voor een klein bedrag koop je tegenwoordig een zeer complete postcodebestand met 9 miljoen adressen. Je kunt met postcode4 gebieden ook leuke dingen doen zoals postcode4 gebieden intekenen op de kaart.

Je kunt nog wat spelen met diverse instellingen. Daarnaast kun je ook dataset combineren. In bovenstaand voorbeeld zou je bijvoorbeeld commerciële acties kunnen laten zien als staafjes of de omzet als staafjes, verzin het maar.