TrueYou SMS Genlist

TrueYou SMS Genlist project

Detail

SMS messages are sent to customers by using a programmer to write SQL statements to generate a list of phone numbers that will receive SMS for each campaign. There are more than 300 campaigns every month. We are to develop a web based application to build a datacube in order to use it to create list of phone numbers based on criteria for campaign.

Challenge

– Have to move data from 3-4 source databases to a target database by using web application (move more than 40-50 million rows everyday without database link.)
– Difficult to design application database schema and build web application to handle a lot of data (all of the true customers).
– UI must be easy for a marketing people to use. Ex. create criteria and export as CSV.
– Store and track all of generated list histories..
– Ability to randomly select phone number base on query. It will take time to use the random function with user data more than 20-30 million records (Same sql query might return randomly results.)

Solution

– Integrate the web application with the oracle tool in order to move data.
– Use caching strategy and divide&conquer for pre-calculation
– Design database schema that can handle a lot of data (Ex. 1 campaign might have more than 1 million phone number)
– Create new database columns during staging data that contains random numbers. So we can just select one of the columns to sort.

Technology

Project URL

Internal