For an application that sends bulk email, it’s very important to have visibility for customers into the analytics of their email messages. Analytics help us to understand a bunch of metrics about your email sends. For example: Are all of your emails being delivered? What does the engagement look like? Are people clicking, or just reading? What does the overall health of an email subscriber list look like? Are there email addresses that don’t work and are undeliverable? The list goes on...
Email delivery reporting isn’t complicated but there are some nuances. I’ve been working through these with Air Mail for about six years now and have it mostly down to a science. I recently needed a simpler version of email reporting for Electragram so I’m distilling down what I’ve learned from Air Mail into a few pieces here.
The Delivery
First, let’s look at the structure of the raw email delivery data itself. In this example, I have a simple model called MessageDelivery
which handles the per-recipient details about an emailed message. The Message model contains the copy, design, imagery, etc for what is going out to a recipient. Each Message
has many message_deliveries
. A very basic, but effective structure for this purpose.[1]
For each “delivery” we’re concerned with the following metrics:
- Was the delivery successful in reaching the recipient’s mail server? (I call this a successful delivery, versus a “send” which is an attempt to deliver the message.)
- Did the recipient “open” this message? [2]
- Did the recipient click on a link in the message? If so, how many times?
- Was the delivery marked as spam?
- Did the recipient unsubscribe from communications via this message
Based on that, here is the basic structure of the message_deliveries
table:
# id :bigint
# message_id :bigint (reference to the message itself)
# recipient_id :bigint (reference to the recipient model)
# status :integer (draft or sent)
# sent_at :datetime (the timestamp when sent)
# bounced :boolean
# clicked :boolean
# clicks_count :integer
# opened :boolean
# spammed :boolean
# unsubscribed :boolean
# unsubscribed_at :datetime
The structure of our message_deliveries
table, backed by MessageDelivery
model.
Now we have a simple table to gather the details of each message that is sent, a.k.a. a delivery.
For the sake of this post, it doesn’t matter how this data is populated or updated. I’m just relying on the structure and assuming the data is assigned correctly elsewhere. This type of separation of logic is really helpful for a small development team because each piece can just be individually built and tested without having to worry about its surroundings.
Daily Snapshots
Keeping track of an individual delivery’s metrics is great, but not super useful in aggregate. How can I use this delivery data to report on the message itself, and even more importantly, across multiple messages? This is where I’m layering another model: the analytics snapshot.
The goal of the snapshot record is to capture an aggregate of the deliveries for a particular time period: daily, weekly, monthly, yearly, etc.
I’ve named this model MessageAnalyticsSnapshot
and here’s what it looks like:
# id :bigint
# account_id :bigint
# message_id :bigint
# sends :integer
# deliveries :integer
# day :date
# interval :integer
# bounces :integer
# clicks :integer
# opens :integer
# total_clicks :integer
# total_opens :integer
# spam_reports :integer
# unsubscribes :integer
The structure of our message_analytics_snapshots
table, backed by MessageAnalyticsSnapshot
model.
This is a table used to aggregate the delivery stats for an entire message at a point in time. So the structure looks very similar to deliveries itself! With a few exceptions of course:
sends
– the total number of delivery records for a given message. This app does allow sending a message to groups at different times, so the number of sends may change over time.deliveries
– the total number of delivery records that we’re counting as “deliverable”, which means they were accepted by the recipient’s mail server were not marked as spam. The deliveries figure is what we’ll use to calculate our percentage metrics later.day
– the date for this snapshot capture.interval
– an enum for the time period we’re reporting for here. This defaults to “daily”, but also stores weekly, monthly, quarterly, and yearly snapshots.bounces
,clicks
,opens
,spam_reports
,unsubscribes
– summed values of their corresponding columns in themessage_deliveries
table. These values are what we consider “unique”, which means they can be at most 1 for each particular delivery.total_clicks
, andtotal_opens
– non-unique values for the total number of times someone clicked or opened the emails. We don’t typically use non-unique values in reporting, but I do think it’s a helpful stat to display and understand.account_id
– a reference back to the message owner’s account. This column is helpful so we can aggregate reports for all messages in an account.
The interval
enum is helpful in this context so we understand what time frame we’re reporting on. In a later post, I’ll talk about the user interface and design of how this is all manifested online. I had an idea in mind already for how I wanted to do the design before I created these models, so that certainly helped inform the structure. In this case, I really wanted users to be able to show their analytics and compare across periods of time. Day over day, week over week, month over month, etc. The most granular interval is daily, which is the basis for the rest of the snapshots.
Saving Snapshots
In order to save regular snapshots of this reporting data, I needed a background job to run periodically. I use Sidekiq within this app, so that's what we’ll use to create the background job for capturing daily analytics:
class CaptureDailyMessageSnapshotJob
include Sidekiq::Job
def perform(message_id)
message = Message.find_by(id: message_id)
return false unless message.present?
today = Time.current.to_date
snapshot = message.analytics_snapshots.find_or_initialize_by(
interval: :daily,
day: today
)
snapshot.account = message.account
snapshot.populate_today
snapshot.save
end
end
For the observant readers, you may notice that I am intentionally not using ActiveJob here. For most of the jobs within this app, I do use ActiveJob. But for this feature I am taking advantage of Sidekiq Pro’s batching features. I’ll discuss why another time.
This job is very simple! It just takes a message, finds a snapshot record for today (if one exists), or creates a new one. The meat of the calculation happens from a model method populate_today
, which is located in the MessageAnalyticsSnapshot
model itself. It looks something like this:
def populate_today
self.sends = message.deliveries.sent.count
self.clicks = message.deliveries.clicked.count
self.opens = message.deliveries.opened.count
self.unsubscribes = message.deliveries.unsubscribed.count
self.bounces = message.deliveries.bounced.count
self.spam_reports = message.deliveries.spammed.count
self.total_opens = message.deliveries.opened.sum(:opens_count)
self.total_clicks = message.deliveries.clicked.sum(:clicks_count)
end
Again, very simple! This certainly isn’t the most efficient means to gather this data. There is a query being run for each of these stats. Each query is fast right now, and we can always adjust this as needed as the app scales. This is why I love abstracting methods like this out of the job and into the model where we can modify the logic at any time. The background job’s role is to save the data, and it doesn’t care where the data comes from or why. The model handles that for us.
In fact even this method doesn’t really know where the data is coming from. Given this line of the method:
self.clicks = message.deliveries.clicked.count
This is assigning the number of unique clicks for a message from the deliveries table to the snapshots table. The clicked
method here is just a named scope within the MessageDelivery
model:
scope :clicked, -> { sent.where(clicked: true) }
This sort of separation of logic and calculation is really nice for me. I love that the root data model (in this case MessageDelivery
) is the only place that is concerned with the underlying data structure and SQL parameters. deliveries.clicks.count
is a very readable line, and it’s very clear to me what’s happening here.
Scheduling
Last but not least, I’m using Sidekiq Cron to run this job periodically throughout the day. Right now it’s running hourly, which is more than sufficient for the scale of this project.
Each hour the CaptureDailyMessageSnapshotJob
background job runs and updates the snapshot with the latest stats for the day. Again, the smallest unit of measure we're concerned about in this app is a single day, so that's why this structure was chosen.
For the next day, the job just continues along adding snapshots for that date and today’s stats are finalized and left alone. [3]
Next Steps
I think this is enough for part 1 of this series. Here’s where we’re at:
- We have built a raw deliveries table for storage of per-recipient delivery information (
MessageDelivery
). - We have a model to store daily snapshot information aggregated from the deliveries (
MessageAnalyticsSnapshot
). - We have an hourly background job (
CaptureDailyMessageSnapshotJob
) that is responsible for calculating and saving the snapshots.
Next time, I’ll write about how we use the daily snapshots to roll up by week, month, quarter, and year. Then we’ll get into the UI. ✨
Drop me a line any time.
-
I’m simplifying here for the sake of clarity, and to focus on what we’re discussing in this post. More about the messaging itself can be covered later. This is also much lower bandwidth app than Air Mail, so I'm cutting a few corners that make the developer experience easier. ↩︎
-
I don’t like tracking opens. It’s not reliable, for many reasons. And worse it’s certainly privacy invasive. If this was entirely my app I wouldn’t track it, but that part is out of my hands. Unfortunately, it’s still an industry standard and shipping a messaging app without the ability to track email opens is a non-starter in 2025. ↩︎
-
The daily job code is simplified a bit here for illustration purposes. If I’m running this job hourly (at the top of the hour) then the last time it will run is 11pm each day. The next iteration would be at midnight the following day. If we’re not careful here, we’ll miss capturing any analytics changes from 11:00pm until midnight each night. I'm accounting for that in my app, but it’s not included here.
An easy solution would just be to run the job at :59 past the hour. ↩︎