I’m going to show you how to track ad campaigns in Magento to find out how many orders they are generating.
Imagine you have an ad campaign running on Facebook for your Magento store and you want to know how many of those clicks are being converted in to orders. The way to achieve this is to set a cookie for all visitors who arrive to your site via Facebook. If you give each cookie generated a unique id and then store the data in a database, you will be able to track each visitor if they then return within a given period and place an order in your store. To do this you will need to add a new table to your Magento database, add a few new files, and edit a few files…
1 First up, you will need to check your theme for the following files:
app/design/frontend/default/YOUR_THEME/template/checkout/success.phtml
app/design/frontend/default/YOUR_THEME/layout/checkout.xml
Don’t worry if they don’t exist in your theme. If you don’t have them, simply copy them in to your theme from the following locations:
app/design/frontend/base/default/template/checkout/success.phtml
app/design/frontend/base/default/layout/checkout.xml
Now, you will be able to modify these files without disturbing your installation of Magento.
2 Now, you will need to create a new template file for your ‘success’ page. The ‘success’ page is the page that you are sent to after completing an order. Don’t worry, this is not complicated — it’s just a case of duplicating the template file that you are currently using for the checkout process, and giving it a new name. In my case I am using a template file called ‘2columns-left.phtml’, so I duplicate it and rename it ‘2columns-left-success.phtml’.
The template files for your theme are located here:
app/design/frontend/default/YOUR_THEME/template/page/
3 Now, to get Magento to use your new template for the ‘success’ page, you will need to edit the ‘checkout.xml’ file that you copied in to your theme in stage 1.
Open it, and locate the following line (line 412):
<action method="setTemplate"><template>page/2columns-left.phtml</template></action>
Edit it to use your newly created template file:
<action method="setTemplate"><template>page/2columns-left-success.phtml</template></action>
4 OK, now it’s time to work on the database to store your tracking data. You need to create a new table in your Magento database called ‘tracking’ with the following structure:
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id | varchar(16) | NO | | NULL | |
| source | varchar(60) | YES | | NULL | |
| time | datetime | YES | | NULL | |
| sale | enum('0','1') | NO | | 0 | |
| sale_time | datetime | YES | | NULL | |
| order_id | varchar(50) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
You will be storing the following data:
- id – this will be a random 16 chr string that will be used as the name of each cookie created.
- source – this will be the value of the cookie, and will contain the domain of the referring site.
- time – this will contain the date and time of the cookie’s creation.
- sale – a simple boolean, 0 if no purchase, 1 if a purchase is made before the cookie expires.
- sale_time – if a purchase is made, this field will contain the date and time of the purchase.
- order_id – finally, if a purchase is made, this field will contain the corresponding order number.
Here’s the SQL to create the table:
CREATE TABLE `tracking` (
`id` varchar(16) NOT NULL,
`source` varchar(60) DEFAULT NULL,
`time` datetime DEFAULT NULL,
`sale` enum('0','1') NOT NULL DEFAULT '0',
`sale_time` datetime DEFAULT NULL,
`order_id` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
5 Now that you have the database set up it’s time to start adding the necessary code. Firstly, in the root level of your Magento site, create a new file named ‘cookie_settings.php’.
Add the following php to this file and save it:
<?php
#####################################################
## add all domains you wish to track to this array ##
#####################################################
$cookie_arr[0] = "www.google.com";
$cookie_arr[1] = "anet.tradedoubler.com";
$cookie_arr[2] = "www.facebook.com";
#######################
## cookie parameters ##
#######################
$number_of_days = 30;
$cookie_path = '/';
$cookie_domain = '.yourdomain.com';
?>
This pretty much speaks for it’s self.
Add any domains you want to track to the $cookie_arr array.
Set $number_of_days to the amount of days you want each tracking cookie to last for.
Leave $cookie_path as is.
Set $cookie_domain to the name of your domain (careful, don’t omit that leading full stop).
6 The landing page code. Open the template that is used for your landing page, and add the following code to the top of the template, the comments in the file pretty much sum it up:
<?php
//random string function (source: http://www.lateralcode.com/creating-a-random-string-with-php/)
function rand_string( $length ) {
$chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
$str="";
$size = strlen( $chars );
for( $i = 0; $i < $length; $i++ ) {
$str .= $chars[ rand( 0, $size - 1 ) ];
}
return $str;
}
//get the cookie settings...
require_once( 'cookie_settings.php' );
//get the refering url and parse it...
$refer_url=parse_url($_SERVER['HTTP_REFERER']);
$source=$refer_url['host'];
//if the referring url is in the list...
if(in_array($source,$cookie_arr)) {
//if it is a valid source, set a cookie...
$date_of_expiry=time()+60*60*24*$number_of_days;
//set the name of the cookie as a random string of 16 chars...
$identifier=rand_string(16);
$source=str_replace(".","_",$source);
setcookie( $source, $identifier, $date_of_expiry, $cookie_path, $cookie_domain );
//register the information in the database...
require_once("app/Mage.php");
Mage::app('default');
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("insert into tracking (id, source, time, sale) values ('$identifier', '$source', NOW(), '0')");
}
?>
CREDIT: the random string function comes from: http://www.lateralcode.com/creating-a-random-string-with-php/
7 OK, that’s the landing page dealt with — now it’s time to add the code to the success page. Open the ‘success.phtml’ file that you copied in to your theme in stage 1, then add the following php to the top of the file and save it:
<?php
//get the cookie settings...
require_once( 'cookie_settings.php' );
//loop through the process for each domain being tracked
foreach($cookie_arr as $value) {
$value=str_replace(".","_",$value);
//if a valid cookie is set when we get to the success page...
if(isset($_COOKIE[$value])) {
//get the value...
$cookie_value=$_COOKIE[$value];
//update the database entry...
require_once("app/Mage.php");
Mage::app('default');
$order_id=$this->escapeHtml($this->getOrderId());
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("update tracking set sale='1', sale_time=NOW(), order_id='$order_id' where id='$cookie_value'");
//delete the cookie...
setcookie($value, '', 1 ,$cookie_path, $cookie_domain);
}
}
?>
Once again, the comments sum it up.
Conclusion…
OK, so now your done — each time a visitor lands on a product page from one of the sites you have specified in the cookie_settings.php file, a cookie will be created and recorded in the database. Should that visitor then make a purchase within the time limit specified in the cookie_settings.php file, the record will be updated to contain the purchase time and order number and the cookie will be deleted!
You can download the code from this post here: Magento Ad Tracking
P.S. in this particular setup, any page using the 2columns-left.phtml template functions as a landing page. If you wish to limit the landing page to 1 specific product. Simply create a new template file, perhaps 2columns-left-landing.phtml containing the landing page code, and specify that template for the chosen product in the Magento Backend (In this case DON’T add the landing page code to the normal 2columns-left.phtml file). If you follow this route, you will need to reference the new template in the following file to make it useable in the Magento Backend:
app/code/core/Mage/Page/etc/config.xml
locate the <layouts> and add the following:
<two_columns_landing module="page" translate="label">
<label>2 columns landing page</label>
<template>page/2columns-home-landing.phtml</template>
<layout_handle>page_two_columns_landing</layout_handle>
</two_columns_landing>