3

We have an ecommerce application which sends out transactional emails to customers for events like order placed, order shipped, order delivered etc. We used to send one bcc of every email to a gmail address to keep record. But due to large volume of emails, we are facing two problems:

  1. We need to buy storage from gmail and
  2. Recently we had an offer sales, which caused huge surge in orders as well as emails. Due to which gmail blocked receiving on that email id, which resulted in bounces.

My question is - What could be best way of keeping logs of transactional emails? Logs are needed sometimes to look back what communication was sent to customer, at what time, its delivery status.

I am thinking of having a table in database, which would store the following:

ToAddress
Subject
Body
AttachmentFilePath
SentAt
DeliveryStatus

We daily send around 100K emails, I am thinking to clean records which are 3 months old.

  • Is this a right approach? Or there is some other more optimal way of storing emails?
  • Do big companies also keep logs of emails or not required?
  • Ultimately you should not be the one deciding how long to hold e-mails and with what criteria you should delete them, though you can definitely pitch in your idea. The reason I say this is that otherwise you'll be caught in the situations where your boss says, "So you decided to delete these e-mails after 3 months?!" Not a good place to be. Though I would highly recommend partitioning these tables by date (oracle has support for this), which both optimizes the table during searches by date as well as lets you easily delete as required. – Neil Mar 11 at 9:39
  • Yes you are right. We are currently following 3 month practice, so I just quoted that – Abhishek Sharma Mar 12 at 2:46
5

This question

I am thinking to clean records which are 3 months old. Is this a right approach?

is a question about your companies requirements. It has probably a lot of legal aspects, like the contracts with your business partners or required warranty times in your local jurisdiction. It depends also on technical alternatives to find out what happened when a transaction fails in your system. How long you need to store those emails is a management decision, nothing you can really decide by asking strangers from the internet about.

Or there is some other more optimal way of storing emails?

You could run your own mail server hosted in the same network where your application runs, from where it sends the outgoing emails, and send the BCC mails there. You could implement similar logging functionality in your application in a more comprehensive manner, and don't send BCC mails. You could change your email provider from Google to someone else. You could archive all older emails from your own server in some kind of document management system, there is a full Wikipedia article about email archiving. And you have to consider technical and legal aspects of data security and privacy of all of these solutions.

But whatever solution you prefer: it will require some storage space, network bandwidth, CPU time, and administrative effort, which all costs time and money. Finding out if it is cheaper for your company to do this all by yourself, or if it is cheaper to rent services somewhere in the cloud (and if the latter would be secure enough) is an economic question, not a software engineering question.

  • I would like to better understand should I go with BCC approach or storing sent emails in database. Only concern I see with database storage is when it comes to legal, how valid will be the copy stored in database. Could you please share your thoughts over this? – Abhishek Sharma Mar 12 at 2:45
  • @AbhishekSharma: legal requirements are a minefield and depend heavily on your local jurisdiction. They probably depend on the content of the mails, their relevance for tax paying or contracts, and if they contain personal information. You (or your company) need to ask a lawyer, if your legal department cannot give you the necessary information. To give you an idea about the complexity of the topic, here is link to some legal requirements in Europe for document management – Doc Brown Mar 12 at 6:06
1

I often use a table to store a log of emails sent by an application. The one item that I store that was not in your list items to be stored is a foreign key to tie the email log record back to the sales transaction and / or the customer. This allows us to tie the email log entry with the transaction that issued it.

  • Thanks for heads up, I missed it. Do you find any issues with storing sent emails in table? – Abhishek Sharma Mar 12 at 2:41
  • I don't store the entire text of the email. Just pertinent data such as timestamp, who it was sent to, the transaction type that caused the email to be generated (a sale, response to customer inquiry, etc), a reference to the root business transaction, etc. We have found it very useful for tying together the history of a transaction when talking to a customer or trying to determine when or why something failed. – Dennis Mar 13 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.