On the ups and downs of pixels…

www.chalkbeat.org was interested in allowing their stories to be copied and republished elsewhere but wanted a way to track the impressions. We initially setup repost.us which works wonderfully but other news outlets began complaining about the integrated ads (which is how repost gets paid).

So we decided to make our own a la ProPublica and Texas Tribune. It’s pretty easy to get WordPress to kick out the code and I used a little jquery and Eric Martin’s wonderful Simple Modal for the popup. The vexing part turned out to be tracking the articles. ProPublica developed a cute little tracking pixel implementation with Document Cloud called pixel-ping but after several days of trying to figure out Ruby (which I don’t know), Rails (which I also don’t know) Linux (which I don’t know enough, apparently) and other virtual server pains I gave up and stumbled across a wonderful little breath of fresh air on stack overflow. Developing a tracking pixel – in PHP (which I do know)!

My solution ended up being a combination of two of the answers. There is a php file that generates the pixel, the details of the article are passed as GET parameters along with image request and the hit is saved into a mySQL database.

The tracking pixel call looks like this:

<img src="https://www.itjon.com/phppt/pixel.php?a=12345&amp;b=123" />

The php file first generates a 1×1 image to return to the requesting page:

header("Content-type: image/gif");
header("Content-Length: 42");
header("Cache-Control: private, no-cache, no-cache=Set-Cookie, proxy-revalidate");
header("Expires: Wed, 11 Jan 2000 12:59:00 GMT");
header("Last-Modified: Wed, 11 Jan 2006 12:59:00 GMT");
header("Pragma: no-cache");
echo sprintf('%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%c%',71,73,70,56,57,97,1,0,1,0,128,255,0,192,192,192,0,0,0,33,249,4,1,0,0,0,0,44,0,0,0,0,1,0,1,0,0,2,2,68,1,0,59);

Next, we store our database connection info as a variable:

$con = mysqli_connect("[host]","[username]","[password]","[database]");

Grab the variables from the GET request, the viewer’s IP (for geolocation) and the referring site:

$article = $_GET['a'];
$bureau = $_GET['b'];
$ip = $_SERVER['REMOTE_ADDR'];
$referer = $_SERVER['HTTP_REFERER'];

Store our SQL insert statement as a variable:

$sql = "INSERT INTO Chalkbeat
(articleID, site, ip, referer)
VALUES
('". $article . "','" . $bureau . "','" . $ip . "','" . $referer . "')";

And finally, run the query with our connection and query variables:

mysqli_query ($con, $sql);

In wordpress, I generated a view that adds the tracking pixel with the original article permalink as the “a” variable and the Chalkbeat bureau as the “b” variable. The database also has a field that auto populates with the current timestamp so we end up with a database that shows each view, time, what article, where it was viewed and by what IP address. That’ll be adequate for tracking now until the data is finally included into MORI.