Clickthrough Link Counter
We've had a few requests recently from Affiliates wanting to track their clickthroughs from their websites through to Kingdom of Pets.
So without further ado, we present a simple way of tracking your links on your website
You can see a preview of what the final report page looks like, or a scaled down version below.
Prerequisites
For the purposes of this example we are using php and MySQL. The core logic is the same for ASP or Ruby on Rails, but we use LAMP exclusively for our websites and this tutorial reflects that.
You can download the entire source code for this tool. It should be straightforward to plug this into your website. Any questions please email nigel@librosmedia.com
The Flash charts are provided by the excellent Fusion Charts
What exactly are we doing?
Simple, we are going to create an easy mechanism for tracking links on your website, and also some simple charting to display the results.
This tutorial has 3 main files.
- Public Page. This can be any page on your website that you wish to track links from.
- Gateway Page. Records the links and sends the browser on to our website
- Private Page. Provides some stats on the outbound links.
Create the Link Table in the database
We need to create a table in your database. This is used to keep track of the links, increment a counter when a link is clicked, and finally to produce the reports required. The sql to create the table is listed below:
Add the Links to the database
Fire up your favorite MySQL manager, MySQLYOG, phpMyAdmin or the command line. Add the Links listed below:
Problems with your Dog Barking?
Stop your Dog Biting!
Problems with your Dog Biting?
Stop my Dog Barking!
You should now have 4 links in your database tbl with the column `l_id` having values 1,2,3 and 4. These are the unique identifiers for your links and are used on the Public page.
The Public Page
For the purposes of this tutorial I've created a simple HTML page with 4 links on it. There are in fact only 2 links to SitStayFetch, they have just been duplicated twice using different key phrases. By recording the clickthroughs you are able to see which link gives you a better rate. The page as seen in a browser:
My Website
| Problems with your Dog Barking? | Stop your Dog Biting! |
| Problems with your Dog Biting? | Stop my Dog Barking! |
The HTML for this page is:
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
</head>
<body>
<h1>My Website</h1>
<table>
<tr>
<td>
<a href="Gateway.php?l=1?url=www.kingdomofpets.com/dogobediencetraining/Barking">Problems with your Dog Barking?</a>
</td>
<td>
<a href="Gateway.php?l=2?url=www.kingdomofpets.com/dogobediencetraining/Biting">Stop your Dog Biting!</a>
</td>
</tr>
<tr>
<td>
<a href="Gateway.php?l=3?url=www.kingdomofpets.com/dogobediencetraining/Biting">Problems with your Dog Biting?</a>
</td>
<td>
<a href="Gateway.php?l=4?url=www.kingdomofpets.com/dogobediencetraining/Barking">Stop my Dog Barking!</a>
</td>
</tr>
</table>
</body>
</html>
The important part of the page is the actual links. The HTML code for the first link is listed below:
Problems with your Dog Barking?</a>
Take note of the two GET variables, 'l' and 'url'.
- 'l' is a unique identifier for this link. This will be used to update the row in the database. These values correspond to the `l_id` column in the database.
- 'url' is the physical link to the SitStayFetch page. While we don't have to specifiy this on the page, it does make the link search engine friendly!
The Gateway Page
This is the page that does all the magic. When a link is clicked on your public page, the code on the Gateway page captures the link, increments a counter for that unique link, and then redirects the browser to the correct page on SitStayFetch. The code is fairly self explanatory, the only thing you will need to change is your database connection information.
############################################
# Author: Librosmedia, www.librosmedia.com #
# Nigel@librosmedia.com #
############################################
// This page captures two GET variables, updates the database and redirects the browser.
// Database Connection string
// I'm using MySQL improved, you will need to add your db connection information here
$dbserver = "localhost";
$dbuser = "test";
$dbpasswd = "user";
$dbname = "gateway";
$objGateway = new mysqli($dbserver, $dbuser, $dbpasswd, $dbname);
// for security we should test to make sure that the two GET variables exist
if (isset($_GET['l']) && isset($_GET['url'])) {
// Ok both GET variables exist. We can increment the counter and redirect the browser.
# please note, there is no code here that checks if the actual link exists in the
# database. There really should be, as a non existing `l_id` will generate an error
# and stop the page dead in it's tracks!
// Lets update the database and increment the counter.
$sql = sprintf("UPDATE link_tbl SET l_hits = l_hits + 1 WHERE l_id = %s", intval($_GET['l_id']));
$updateSuccess = $objGateway->query($sql) or failedQuery($objGateway->error, $sql, $_SERVER['PHP_SELF'], $objGateway->errno);
// redirect the browser
$pagegoto = "http://" . $_GET['url'];
header(sprintf("location: %s", $pagegoto));
exit;
} else {
// they don't exist. Redirect back to the homepage
$pagegoto = $_SERVER['DOCUMENT_ROOT'];
header(sprintf("Location: %s", $pagegoto));
exit;
}
?>
The Private Page
This page provides some basic reporting functions, showing you which Links have been the most effective. It uses some Flash based charts provided by Fusion Free Charts.
- We query the database for all links
- Create some XML code from that data
- Parse that data through to the Flash file to generate the chart
The code for this page is outlined below:
############################################
# Author: Librosmedia, www.librosmedia.com #
# Nigel@librosmedia.com #
############################################
// For security you should really make this page only accessible i you are logged in!!
// Database Connection string
// I'm using MySQL improved, you will need to add your db connection information here
$dbserver = "localhost";
$dbuser = "test";
$dbpasswd = "user";
$dbname = "gateway";
$objGateway = new mysqli($dbserver, $dbuser, $dbpasswd, $dbname);
// Include the required Fusion Free Charts
include_once('FusionFreeCharts/FC_Colors.php');
include_once('FusionFreeCharts/FusionCharts.php');
// init some variables, if these two are empty then we don't have to show the graphs
$barXML = $pieXML = '';
// we need to get the data out of the database
$sql = "SELECT * FROM link_tbl ORDER BY l_hits DESC";
$selectSuccess = $objGateway->query($sql) or failedQuery($objGateway->error, $sql, $_SERVER['PHP_SELF'], $objGateway->errno);
$numrows = $selectSuccess->num_rows;
// if there is data in the database
if ($numrows) {
// start the XML for the graphs
$barXML = "<graph bgcolor='ffffff' caption='My Outbound Website Links' yaxisname='Hits' xaxisname='Links' hovercapbg='FFFFDD' hovercapborder='000000' numdivlines='4' rotatenames='1' decimalPrecision='0'>";
$pieXML = "<graph caption='My Outbound Website Links' bgColor='ffffff' decimalPrecision='0' showPercentageValues='1' showNames='1' showValues='1' showPercentageInLabel='0' pieYScale='45' pieBorderAlpha='40' pieFillAlpha='70' pieSliceDepth='50' pieRadius='150'>";
// loop through all the data and create the XML
while ($row = $selectSuccess->fetch_object()) {
// set the color for this link
$thiscolor = getFCColor();
// do the bar graph
$barXML .= "<set name='" . htmlentities($row->l_name, ENT_QUOTES) . "' value='" . $row->l_hits . "' color='" . $thiscolor . "'/>";
// do the pie graph
$pieXML .= "<set value='" . $row->l_hits . "' name='" . htmlentities($row->l_name, ENT_QUOTES) . "' color='" . $thiscolor . "'/>";
} // end loop
// end the xml
$barXML .= '</graph>';
$pieXML .= '</graph>';
} // end if there is data in the database
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<!-- include the Fusion Charts javascript file -->
<script language="Javascript" src="FusionFreeCharts/FusionCharts.js"></script>
</head>
<body>
<h1>My Link Report</h1>
<!-- we need two DIV's in here for the Fusion Charts -->
<div id="bargraph">
</div>
<?php
// check to see if we need to display this Chart
if ($barXML != '') {
echo renderChart("FusionFreeCharts/FCF_Column3D.swf", "", $barXML, "bargraph", 700, 700);
}
?>
<div id="piegraph">
</div>
<?php
// check to see if we need to display this Chart
if ($pieXML != '') {
echo renderChart("FusionFreeCharts/FCF_Pie3D.swf ", "", $pieXML, "piegraph", 700, 400);
}
?>
</body>
</html>
Where to next?
This is a fairly simplistic tutorial, but it provides a basic foundation for tracking your links, measuring their performance and optimising your website to increase your Affiliate earnings.
If you have any suggestions or comments regarding this tutorial, please contact nigel@librosmedia.com
