I have created a db to act as a CRM software. What I am looking to do is to create email campaigns to assign to a contact. For example, I might have a campaign called "Past Clients" where there would be a series of emails sent periodically to the email address listed for that contact. The letter content would be different for each one, and the time interval would be different (1st letter may be sent 7 days after transaction, 2nd letter will be sent 90 days, etc.)
At this point, I am brainstorming on how to best build this within my db. I have some thoughts on how to make this work, but I need some help...my way seems too cumbersome:
tblLetterContent = Table to hold the letter name, and letter content.
tblCampaignNames = Table to list all the email marketing campaign names.
tblCampaignLetters = Table to connect the correct letters to the appropriate campaigns, and to specify an interval for sending the letters within that campaign.
Here's where I need the help. I need some ideas on the best way to:
1.) Associate what clients are assigned to what campaigns, and/or,
2.) how to "store" all the info on the letters that need to be sent (For example, if I have assigned a contact to a campaign that has 10 letters, I need to somehow automate by db to recognize when a letter needs to be sent in this campaign.),
3.) Also need by db to recognize when a letter within the campaign has already been sent so it will not send it again.
Any suggestions on how to best enact my idea would be great!