imperium2335 imperium2335 - 1 year ago 86
MySQL Question

Email marketing statistics implementation

I will soon be tasked with making an email campaign manager, and part of it will be a statistics screen that will show how many emails were delivered successfully, how many were not, and how many people actually opened the email.

The main thing I can't get my head around is how to code the part that deals with people opening the email.

I know you could probably do it with a 1x1 white pixel, and then perhaps somehow have the server detect that it was loaded based on the image file name and trigger a script from that. But not all email clients have images enabled and in some cases they are disabled completely etc. I also think this method is quite dated?

I would like to know how the big players do this like Cheetah Mail etc.

What is the best way to do this using PHP/MySQL?

Thanks for your reply.
These are opted in subscribers and there will be afew links in the body of the email that will go to certain places on our websites. Tracking of those is easy because the link will have a string appended to it which will make it easy to detect if they visit the site from an email.

If whitepixel is the way to go at the moment, how do I go about linking the loading of that pixel to a script that is triggered on my server?

Answer Source

Firstly, I'd suggest using an off-the-shelf solution for this, rather than building your own - it's a complicated field, and others have already solved the problems.

As for the specific question, you can create an image tag in each mail along the following lines:

<img src="$IDofCurrentCampaign&uid=$emailAddressOfRecipient">

When this image is requested, the query string means it won't be cached, and your php script can store the campaign and email address in a database; you then need to respond with an image mime type, and send back a transparent gif.