A simple email open rate tracker with an MVC site

Most people track the open rate of email campaigns via their campaign provider such as Campaign Monitor, Pardot or Mail Chimp. However there might be a few instances when your emails might need to be sent via a legacy system or you may want to add this functionality ad hoc to the previously mentioned vendors as a side statistic. Maybe you're just curious how they can track this sort of thing. Whatever your motivations, I'd like to show you a simple way to accomplish this.

The magic trick

Actually it's not really a magic trick how this works, but in order to track the individual opening of emails we need some sort of mechanism that acts as a callback to a server. To do this it is common to simply load a transparent image via HTML. When the image is requested, we'll increment our counter by one. There are two drawbacks to this approach without adding additional complexity:

  1. If a user opens the same email more than once, the stats will skew a tiny bit. For the most part this is not a huge concern. To counter this you'd have to add a unique identifier to the request but we'll skip that for simplicity sake.
  2. If a user does not allow images to load via their email client, the image will never load and therefore we'll miss out on knowing that someone actually opened the email. The good news is that in practice, a good number of people allow images to load.

The implementation

To implement this we'll need a few things to make it all work:

  1. A database table to hold the incoming entries
  2. A controller to handle the image request
  3. A image to return to the email client
  4. Tests to make sure things work as they should

I'll also organize some of the code into unit of work, repository and service patterns.

Create a table

We will track the campaigns by a name and a source. So in theory you could send out your campaign via different channels and see which one was more effective (i.e. Campaign Monitor vs Mail Chimp). In your database, let's create a table to hold the opens with the SQL below:

CREATE TABLE [dbo].[TrackingPixelOpens](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[campaign] [nvarchar](50) NULL,
	[source] [nvarchar](50) NULL,
	[IpAddress] [varchar](50) NULL,
	[openedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_TrackingPixelOpens] PRIMARY KEY CLUSTERED 
(
	[id] ASC
))

CREATE NONCLUSTERED INDEX [IX_TrackingPixelOpens] ON [dbo].[TrackingPixelOpens]
(
	[campaign] ASC,
	[source] ASC
)

The model

We need a tiny little POCO to represent the incoming request which we will then persist in the DB. You can adjust to add a useragent or whatever you'd like. We'll add some attributes as we're using PetaPoco to handle our ORM interactions:

using System;
using PetaPoco;

namespace TestMvc2.Models
{
    [PrimaryKey("id")]
    [TableName("TrackingPixelOpens")]
    public class TrackingPixelOpen
    {
        public long Id { get; set; }
        public string Campaign { get; set; }
        public string Source { get; set; }
        public string IpAddress { get; set; }
        public DateTime OpenedOn { get; set; }
    }
}

Repository and service

Next we'll create a repository that'll run our needed SQL statements and a service that we'll use and call later on in our controller:

using System;
using DAL;
using TestMvc2.Helpers;
using TestMvc2.Models;

namespace TestMvc2.DAL
{
    public class TrackingPixelRepository
    {
        public static void AddCampaignSource(PetaPocoUnitOfWork unitOfWork, string campaign, string source)
        {
            var ip = "";

            try
            {
                ip = IpHelper.GetIpAddress();
            }
            catch (Exception ex)
            {
                //bad IP, log it or something
            }

            unitOfWork.Database.Insert(new TrackingPixelOpen()
            {
                Campaign = campaign,
                Source = source,
                IpAddress = ip,
                OpenedOn = DateTime.Now,
            });
        }

        public static int GetTotalForCampaignSource(PetaPocoUnitOfWork unitOfWork, string campaign, string source)
        {
            return unitOfWork.Database.ExecuteScalar<int>(@"
                SELECT COUNT(*) AS total
                FROM TrackingPixelOpens
                WHERE campaign = @0 AND source = @1
            ", campaign, source);
        }
    }
}
using DAL;
using TestMvc2.DAL;

namespace TestMvc2.Services
{
    public class TrackingPixelService
    {
        public void AddCampaignSource(string campaign, string source)
        {
            using (var uow = new PetaPocoUnitOfWork())
            {
                TrackingPixelRepository.AddCampaignSource(uow, campaign, source);

                uow.Commit();
            }
        }

        public int GetTotalForCampaignSource(string campaign, string source)
        {
            using (var uow = new PetaPocoUnitOfWork())
            {
                return TrackingPixelRepository.GetTotalForCampaignSource(uow, campaign, source);
            }
        }
    }
}

Note that the repository uses a helper to get the IP which can be found here.

The controller

We finally get to the part where we can create a public endpoint for emails to reach out and touch. The controller is pretty simple and will use the service we created earlier to add the 'open' to the DB. We'll also return a binary image which will become clear why in short order:

using System.Web.Mvc;
using TestMvc2.Services;

namespace TestMvc2.Controllers
{
    public class TrackingPixelController : Controller
    {
        private TrackingPixelService _service = new TrackingPixelService();

        public ActionResult GetPixel(string campaign, string source)
        {
            if (!string.IsNullOrEmpty(campaign) || !string.IsNullOrEmpty(source))
            {
                _service.AddCampaignSource(campaign, source);
            }
            else
            {
                //log the bad input
            }

            return File("~/assets/images/pixel.png", "image/png");
        }
    }
}

Add the tracker to your email

When the email is crafted, you'll need to put an HTML image tag into the email in order for this to all work, make sure you use an absolute URL:

<h2>Marketing email!</h2>

<!--- the tracker --->
<img src="http://testmvc.local/trackingpixel/getpixel?campaign=foo&source=bar">

Whenever a user opens your email, the image src tag will resolve back to your server where it adds the entry. Remember that if images are disabled on the users mail client, this will not work.

Testing

I've also taken the time to add a simple test to make sure our code works before sending it out in a real email:

using DAL;
using NUnit.Framework;
using TestMvc2.Services;

namespace TestMvc2.Tests
{
    [TestFixture]
    [Category("Pixel Tracker")]
    public class PixelTracker
    {
        private TrackingPixelService _service = new TrackingPixelService();

        [TestFixtureSetUp]
        public void Setup()
        {
            PetaPocoUnitOfWork.ConnectionString = "testDb";
        }

        [TestCase("test", "test2")]
        [TestCase("test", "")]
        [TestCase("", "test2")]
        public void Can_Add_Pixel(string campaign, string source)
        {
            var count = _service.GetTotalForCampaignSource(campaign, source);

            _service.AddCampaignSource(campaign, source);

            var afterCount = _service.GetTotalForCampaignSource(campaign, source);

            Assert.AreEqual(afterCount, ++count);
        }
    }
}

Summary

So if you need to add this feature to a legacy email service or enhance one you already pay for, the little pixel you embed in an email can be made to do just about anything. Javascript doesn't work in email clients so this little clever trick is one way to track email opens. You will of course have to create your own reporting to make the most of it (i.e. ratio of sent versus opened).

Complete code examples (along with other goodies) can be found here.