Marketing database (MySQL / PERL)

We run classified adverts websites from London, United Kingdom. The site uses Linux, Apache, MySQL, mod_perl and HTML::Mason.

The following websites are based on one central database:

• Russia: www.slando.ru, slando.spb.ru, slando.e-burg.ru, slando.nsk.ru, slando.nnov.ru

• Ukraine: www.slando.com.ua

• Hungary: www.slando.hu

• Czech Republic: www.slando.cz

• Slovakia: www.slando.sk

[Task]

We need to build a marketing database, which will have ability to pull out data from our main database of advertisments, send out marketing emails and record actions in order to eliminate a possibility of duplicate targeting of the same person on the same topic.

The following entries are to be saved in the database:

• Details about users posting adverts

• Details about users replying to adverts

• Details about user subscriptions

[Details about users posting adverts]

The following fields will need to be pulled into the marketing database from the main database:

• date of action

• location

• category id

• email address

• contact phone number

• type of advertisment (e.g. wanted/offered)

[Details about users replying to adverts]

The following fields will need to be pulled into the marketing database from the main database:

• date of action

• location

• category id

• email address

• type of advertisment (e.g. wanted/offered)

[Details about users’ subscriptions]

The following fields will need to be pulled into the marketing database from the main database:

• date of action

• location

• category id

• email address

• keywords

• type of advertisment (e.g. wanted/offered)

[Creation of mailing lists]

Marketing database needs to have ability to create mailing lists on the fly. Each list should be treated as a separate item in the admin. Data each item should contain is:

• title of the mailing list (custom text)

• selection of countries/categories, in which users have posted to, replied to or subscribed to adverts

• selection of user types to send emails to – those who have posted ads, replied to them or those who have/had subscriptions

• number of emails to send in one go

• timeout between each email

• possibility to exclude emails which have already been included in one of other mailing lists.

• selection of format: html or plain

• message preview

Upon approval of all relevant details a new table should be created containing all the emails to send emails to and a new item added to the list of custom mailing lists. Afterwards sending emails would be started by a press of a button. Every email should have status ‘sent’, ‘not yet sent’, ‘unable to deliver’.

[Management of mass mailings]

Things to be included in CMS:

• Possibility to delete mailing with all of it’s emails.

• Possility to export emails of particular mailing list into CSV.

• Statistics of sent/unsent emails.

• Ability to automate mass mailings

[Other]

We will provide you with all the necessary technical information.

If we find the right candidate and they can do this task very well, there is likely to be a stream of work after this project.

Please provide some sample code from a recent project. We want someone whose coding is similar to ours. Please be prepared to explain your code over the telephone or Skype.

Good level of understanding, writing and speaking in English is required from the coder.

Good knowledge/experience working with different encodings (especially cyrillic and central european) is highly beneficial.