Customer Address Matching Solution
Tenzing was engaged to help the client standardise the customer address information to meet the mailing address standards outlined by New Zealand Post.”
The client was looking to improve the accuracy of the customer address information they held in the SAP system. Mailouts were generating a significant proportion of undeliverable mail and were ineligible for New Zealand Post bulk mail discounts.
The client was looking to standardise the customer address information to meet the mailing address standards outlined by New Zealand Post. Tenzing were engaged to provide project resources to:
- Match existing customers postal and delivery address information to New Zealand Post standards
- Develop an internal solution to maintain changes to existing address information and sustain new address information to New Zealand Post address standards
The driving factor behind this solution was to make it:
- Easy to use for the business
The project team worked with key stakeholders to understand the scope of the address cleansing and the most suitable technology for the proposed address matching solution (AMS). The project used a rapid application development (RAD) approach to complete the AMS and produce the matching results.
- A new database and a set of new tables were created in SQL Server 2005. This was a deployed technology at the client and the business users were already familiar with the use of the tool
- The solution design was driven by the components available within the Microsoft SQL Server 2005 and related .NET technologies
- Six new SSIS packages were developed in SQL Server 2005 Business Intelligence Studio. Simultaneously these packages were imported on to the SQL Server and relevant jobs were setup.
- The solution’s brain child lies within the matching rules designed specifically for an address type such as street address, PO Box, Private Bag, and rural address. A set of around 20 matching rules were developed as stored procedures within the new database and a SQL server job was setup to execute these rules.
To make the tool user-friendly for the business, a .NET based front end tool was designed and developed in Visual Studio 2008 with basic features that included:
- Uploading source files such as PAF, address data and rule types via the SSIS packages
- Executing the matching rules
- Downloading matched and unmatched sets of data from the database
The solution was able to successfully match more than 50% of the supplied address data.
Another 25% of the data which held both a postal and physical address had the postal address successfully matched.
The design of the SQL server jobs is key to the successful execution of this solution. The benefits include:
- Reducing the load on the client machine
- Making it simple to maintain at the SQL Server end
- Development of new rules and procedures is less complex
- Lower development and maintenance cost.