Designing Mobile SMS text advertising system
- by Ramraj Edagutti
Currently, I am working on a product where we have an SMS text advertising system, and using this, we setup advertising campaigns for clients, and later these campaigns are sent to the end users. This is very similar to Google Adwords, but targeted to Mobile users via SMS.
Just to give an overview of the system
Each Campaign is mapped to an advertiser
Campaign has start date and end date
Campaign has a filter condition(s) or query to select the target user base from our database (to whom we send Campaigns)
Target user base can be fixed, for e.g send campaign to 10000 users
Target user base can also be dynamic based on query condition, for e.g send campaign to users who are active and from a particular state, district, town etc. (this way user base will be keep changing on daily basis)
Campaign can have multiple campaign messages
Each campaign message has start date and end date
Each campaign message can have multiple message texts for different locales, for e.g English,Hindi,Telugu etc
After creating an advertisement campaign, we run daily night job to provision the target user base for that a particular campaign in a separate table, and another daily job runs on morning times and checks provisioned table for campaigns and targeted users and sends the campaign to users via SMS.
Problem is, current UI for creating advertising campaigns is designed in a very technical manner, I mean, normal user or business owner or clients can not use the UI to create a campaign.
Below are reasons why the UI is very technical in nature
Filter condition(s) or query input filed, takes user ids or mobile numbers or SQL queries.
Most of times or almost every time, we use big SQL queries
So we end up storing SQL queries in a database for a campaign, later we use this SQL query to fetch targeted user base.
For scheduling these campaigns, we have input filed on UI which takes quartz cron expression(s) ( for e.g. send campaign on "0 0 9 1-10 MAR 2012" ), again very technical in nature
Normal user or business owner, can not use the UI for creating campaigns for reasons mentioned above, Currently, we ourself (developers) helping clients to setup/create campaigns.
we are trying to re-design the UI to make it more user friendly so that any user can go to UI and create an advertisement campaign by himself.
I am thinking of re-designing the current UI similar to Google Adwords interface, especially for selecting target users based on user geography like country, state, city etc. I also need to select users based user subscription(s), which might make system even more complex.
And also, for campaign scheduling, I am thinking of using weekdays with hours. For example, I will shows Monday to Sunday on UI, and user can select the from hours, to hours etc.
Any better ideas or suggestion on how to design UI in very user friendly manner and what design should be followed on server side code (we write backend code on java/jpa/spring/quartz)?
And I am looking for ideas or design patterns on how to build SQL queries (using JPA/Hinernate) programmatically on server side, based on varies conditions like based on country, state, town, village, and user subscriptions.
