Automated selection of customer data for mailings
2009/01/15
In a longterm cooperation with GE Money Bank Austria (now Santander Consumer Bank) we designed, implemented and constantly improved a system written in SAS for the automatic selection of customer data for postal mailings. Legacy data in the form of more than 300 manually created Excel files with customer data of last five years mailings - with different fields, field names and field contents - were translated into an uniform format. During this development we conducted numerous complex troubleshooting and ETL tasks. The final system had the following functions:
- Automatic selection of customer data for a dozen of different mailing types (birthday, active and inactive customers, marketing actions, branch relocation, introduction of new products, etc.)
- Consideration of overlaps and constraints (e.g. no more than two mailings per month per customer)
- Integrity checks and validation of all data and all inputs, output of warnings and cases to be checked.
- Automatic deduplication of customer data.
- Extensive reporting as Excel files with embedded pivot tables, automatically created.
- Providing automatically produced data for the internal fraud department.
- Automatic correction of data quality issues with complex heuristics.
- Computation of the maximum amount of credit for non-secured cash credit for mailing output.
- Reduction of mailing costs by 25% via development and integration of a model to forecast the mailing reponse on customer level (see to Improving the Effectiveness of Mailings by Building A Response Model for Inactive Customers)
Using this system the effort for mailing selection could be reduced from more than 40h per week to less than 10h per week. Additionally, data quality was improved and reporting was unified.