Database Design for email application

I would like to set up a database which sends emails as a marketing platform for our clients. The emails would be identical for each client, with the exception that the header, signature, and return email address would be unique to each client. I would like to be able to create the content for the email one time and schedule the email(s) to be sent. I expect each client to have on average 2500 emails.

Each client will have an email list which will need to be updated on a regular basis - could be weekly or monthly.

Emails could be segmented by a field within the list - but in general emails would be sent out to the entire list regularly (say weekly or bi-weekly).

The client would be able to log in and see their list and make updates - ie. correct invalid emails, manually unsubscribe emails.

Clients may also either send an updated email list (may include new and old emails) in bulk to update their list.

I would like to have data analysis on any of the progress of the emails - opens, unsubscribes, etc.

I am familiar with sending emails using SMTP on a one-at-a-time basis, but it is possible that I would have to send 25,000 or 100,000 emails in a single day should my client list grow.

I would like to automate this process as much as possible, and have questions regarding database design and implementation.

  1. Should I be using a mail service such as SendInBlue or Mailchimp, or maintain the list myself sending out emails with SMTP?

  2. Does anybody have experience with different email services? Good ones, ones to avoid?

  3. If a service is recommended, would you recommend maintaining a separate account for each client? Would you recommend using a Xojo database (I mean a SQL database that I maintain) to store emails and update the service list (is this possible, can it be automated)?

  4. If I maintain the entire database of emails myself, how is it recommended to send out such large volume? I know that if you push out too many at one time you can be blocked.

Just looking for advice in initial setup and design. Thanks.

Hi @David Schlam ,

If you want to use an email marketing service there are a few good ones out there. Mailchimp and ConvertKit are both good.
Mailchimp pricing is mostly based on the size of your contact list. I have worked for a couple places that used it, but they eventually outgrew it.
I have a couple friends who are writers and they really like ConvertKit since it does not over burden them from the technical side. I believe it is uses the size of the contact list for a base price.
The other end of the spectrum would be SalesForce Marketing Cloud. It is nothing short of amazing, but you pay for that in both price and level of expertise you need to implement it. Of course they have several modules so you could start small with a couple modules and go from there.
If you are going to write your own application to do this there are a couple things to be aware of:

  1. Warming up your IP: Wherever you are sending your emails from the IP address have to be recognized by the ISPs as legitimate and not a spammer otherwise all your emails end up in the junk or spam folders by default. So you slowly ramp up the sending of your emails. Maybe start with a 100 each day for a week, check to see if they are getting dumped into spam folders. If they are then work through that issue. If they are not then the following week up it to…300(?) per day. Or whatever you can comfortably manage.
  2. Large Email Providers: Gmail, Yahoo, Outlook, GMX, etc… all have rules in place to try to protect their customers from spam. You have to navigate those rules differently and carefully. You have some research and testing ahead of you for this one.

Unless this is something you really want to learn and dig into I would encourage you to investigate one of the established services and use them for a while. After that if you want to tackle it on your own then go for it.

Just my two cents.
Good luck!

Thanks a bunch, Craig. I think a combination of using a service and my own app seems a good direction to move. Since this is a startup, we want to keep our costs down.

I found at least one service (Mailgun) which supports the ability send to a group of recipients through a single API call or SMTP session. I could upload a list to them and keep a postgres database of the same list myself as a user interface. This way, a user could see their list as it appears on my database and if they, for example, unsubscribe an email, it would send an API call to the service to mark the email “unsubscribed” on their end.

To keep the email looking unique (as if it was emailed from each client), as far as I can see, Mailgun allows templates which can be called using the send email API.

It requires a little front end work on my end, but looks like it solves the issue of scale and managing multiple clients.

Thanks for your help. I appreciate you helping me to formulate the general structure in my mind first.

@David Schlam ~ you are welcome! I totally understand the need to control costs.
Best of luck!

You could check the mass emailer example coming with MBS Xojo Plugins. Maybe be useful if you plan to send hundreds of emails over a SMTP server.

Thanks, Christian. If we go the SMTP route, I will definitely check that out.

Here is a bit of email trivia that might help if you decide to build your own app.

If you have a real email like this:
SomeName@SomeDomain.com

You can successfully send it when you add this to the name:
SomeName+ExtraStuff@SomeDomain.com

All of the content between the plus (+) and the at (@) is ignored by all of the email transports BUT the extra text would be received at the destination and you could parse it to take some action. You could add something to your “ReplyTo” address and process it when received and it could possibly help you categorize your responses even if the body had been manipulated.