We are a Financial Solutions Company and have numerous customer records that we have acquired over the years from a variety of sources, somewhere in the region of 40M.
These records are used for a variety of different campaigns for SMS, Email and Voice campaigns, across different product ranges.
We require a simple database structure to keep a timely record of all this, allow for further imports and also simple look-up and reporting.
All the data to be imported will be in the same format, with a template to be agreed with you for column headers etc.,
The first column will be the date that data was received, the file date; the second will be a file label specific to the source and date.
Thereafter all columns will be specific to the client such as Name, ID Number, Contact information etc......
After every imported a file, a report/export needs to be produced, based on the clients ID Number showing duplicates imported.
This will show the file date of ALL the previous dupes as well as the source and file label.
Any record will never be overwritten, just simply updated with the information for that client based on the most recent file date.
It is possible that clients will have multiple contact numbers, particularly cellphone.
The primary cellphone number will be again based on the last file date, and each time a new cellphone is added for the client, the previous one, becomes the secondary number and so on, keeping up to a maximum of ten contact cellular numbers for the client.
Each client will have a table showing all the Campaigns available and when they were contacted by SMS for each.
When an SMS is sent we need to be able to update the table with a bulk update.
Typical campaigns would be, but not limited to Debt, Cellular, Loan, Life Cover, Funeral etc....., with the ability to add campaigns as required.
Export Data for SMS
We need a select tool based on a number of records required.
Say we select 200,000 records to be sent an SMS for a LOAN campaign, the database needs to return the ‘next’ 200,000 available based on previous campaigns, this ensures we are not hitting the same client over and over.
For example, if we write a rule where a client is contacted every three months, then this is campaign specific, so they’ll be contacted for say debt every three months, but could end up getting four different sms in one month across different campaigns.
The export tool can also have other criteria such as age min/max.
When we refer to records, this is based on cellphone number rather than client.
Also want a simple export tool that will export JUST cellphone number only in ascending order in batches of say 100k.
This is campaign specific.
When replies are received, to opt-out etc, we need to match the cellular number and campaign, then add to that campaigns blacklist or opt-out.
When exports are run to send SMS any number contained in the blacklist for that specific Campaign must be EXCLUDED from the export.
This is additional information based on the client ID Number.
Where the client purchases a product, we need to add this to their portfolio, ideally selecting the product from a drop-down list, stored in a table.
This makes it easier to add products in the future etc. as required.
We need to capture the Company who made the sale together with the Agent and their Manager.
In addition, payment information will be received in excel/csv format which we need to link to the client and have a payment history.
Need to be able to pull reports for commission based on successful sales, that being where payment has been received. All first payments need to be reported as a matter of course across all products as these are the main drivers for commission.
Basic reports generic reports need to be built.
25 freelancers are bidding on average £284 for this job
I'm an expert database developer and can develop this CRM software for you to your specifications. I can deliver in 2 - 3 weeks. Good qualtiy work done. Thanks for considering my bid. -Steve
I have pre-build similar solution. if you are intersted i can demo you. it comes with subscription module as build on cloud platform called salsforce.com. let chats for further details.