Skip to main content

Data doesn't have to be correct

Using Experian Pandora to configure usable addresses

Derek Munro 2 minute read Data migration

I was recently given a simple data validation rule by a Compliance Officer, "make sure that our business contact addresses are usable".

Very pragmatic; it didn’t have to be the correct address, or even a valid address, just usable.

Here in the UK, a postal code identifies about 80 houses, usually on the same street. The combination of postcode and a house number is therefore enough to uniquely identify an address and enable delivery of a letter.

The specification document I was given defined a usable address as “a valid UK Postcode, and a house number.” Although such an address can be considered “valid”, we were soon to realise that there are valid addresses which don’t have house numbers, and still more usable addresses which have neither.

The organisation I was working for holds addresses in six different fields, of which one is for house number and one for postcode. Easy, I thought.

Using Experian Pandora software we built the appropriate validation rule and discovered that the rule specification itself was wrong! Using Experian Pandora’s Data Profiling and Data Prototyping functionality we then analysed the actual data, discovering and building a validation rule to cope with the following variations:

  • The post code was in the wrong field, embedded with other address information.
  • Post codes could be “invalid”, but “usable”, e.g. S01 instead of SO1 for the town of Southampton.
  • The house number could legitimately be absent (houses with names, farms, castles, etc.).
  • House numbers present but not in the house number field.
  • Some addresses used a private mail delivery service reference code instead of a normal UK postal address. The code was sometimes split between 2 of the address fields and some other address fields (not postcode) contained values.
  • Some of the correspondence addresses were outside the UK, even though the business had been transacted in the UK.

As a bonus, we even created a corrected version of the data to feed back into the source system, and generated specifications for ongoing data cleansing rules.

Experian Pandora data management software enables organisations to understandtransform and manage the quality of their data more easily and quickly than any other software product or manual approach. If you are interested in knowing more, please leave a message to arrange a personalised demonstration.