Traditional direct marketing suffers from low data quality in databases that directly impacts their analytics and marketing rate of return. A classic case study for analytics is creation of unique customer identity keys, thus making the analytics of the business customer -oriented rather than account oriented. This account orientation versus customer single view orientation tradeoff can be due to the varying degrees of complexity in data population across several accounts , sub -business domain and marketing relationships.
I may be A Ohri in a telecom company’s account as a broad subscriber, but in another table my name may be written as Ajay O as a prepaid card holder. Different account numbers are allotted to the same customer (here-me) because of the different relationships I may have (mobile broadband and pre-paid mobile). My address may be R32b in one address field, and R Block 32 in another table.
Yet it would be bad marketing by the telecom company if it sends me advertising messages on my broadband Internet and short messages (SMS) on my pre-paid mobile for different marketing activities without taking a customer centric view and only relying on account centric view.The problem in creating unique id from multiple accounts , can be resolved by text mining, and building in short relationships. Using fuzzy logic to match also results in better matching and analysis than simple exact joins.
For a certain client, we had 88 million accounts belonging to 55 million customers. It took us 3 hours to write a single query using SQL to create a modified key to this dataset. In the end we managed to create a rule which created an alpha-numeric primary key to the database – as First Initial of First Name concatenated with surname concatenated with Pin Code of the address. Of course , there were many permutations and combinations that were tried out – and the uniqueness of each one of them was validated and compared along with curves. For each permutation and combination of digits of names and addresses , resulted in different sets of total unique customers. If you narrow down the criterion too much (like just the first initial in name concated to last initial of surname) the number of unique values would shrink as many many duplicates would be created. Thus the tradeoff was between duplication and uniqueness.
From this alphanumeric key- we could ensure uniqueness in the database marketing efforts.
What were the results of this marketing campaign? Well, it found that some customers had been mailed with the same offer three times earlier while some customers were treated as dormant accounts because the earlier unique account id was insufficient for analytical purposes. Of course, one of the primary ways to validate was that the business knew approximately how many unique customers would exist in total, based on the billable relationships and other data.
The new unique account id eliminated the need for excessive marketing or spamming, and each customer could be treated based on total value across entire relationships. Thus this very basic analytics exercise in providing a valid unique key paved the way for advanced analytics like Recency Frequency Monetization and regression modeling.
Take a look at the databases that you are working on. Are you sure you are looking at customers and not accounts. An account number is just 8 bits of information, but a customer is a human being. Treating people better through analytics than treating them as just 8 bits of numbers is the key to a successful analytics practice. Creating a better unique id is the first analytical step to better knowledge discovery in databases.