Using PHP to analyze Google Analytics Information
for Affiliate Sales

On my site, I have various reviews of products and places to buy them. The links for purchasing are, of course, affiliate links. But one problem sending someone to another site to buy something—you have no tracking because you can’t put any tracking scripts on someone else’s page.

What keyword did the person search on? Google Analytics can tell me keywords and campaign info that brought people to my site, and my affiliate network (such as Commission Junction) could show me my sales, but there was no way to relate the two. Or so I thought…

Unknown to me before recently, most affiliate networks allow you to add an id field to the end of your affiliate url. This id will show up on reports. Great, I thought. But what to pass as the id? There were so many things I wanted to track. I wanted to know the keyword, campaign, medium, source. But if I pass all this as an id, this will appear as a very daunting url (they are passed as query parameters).

Wait a minute, I thought. All that information is already being stored in the Google Analytics cookie on the user’s computer. So all I have to do is read the cookie. Then if I write that info to a table, give it a unique id, and pass that id in my affiliate link, I can relate campaign/keyword information with sales later.

So here are the steps involved.


Set up a table for storing Google Analytic information First set up a mysql table with the following fields:

id (an auto-increment field)
date_created (set the default to current_timestamp)
campaign
source
medium
content
term
gclid (not sure what this is, but I know it could be part of a Google Analytics campaign)
product (not part of the campaign, but I want to know what product they attempted to purchase. If everyone who went to buy the Droll Yankee Flipper actually purchased the Roller Feeder, I want to know this.)

Put your affiliate links in PHP and clean them up All of my affiliate links are already stored in a php file so that they can’t be read. This was fortunate. My existing file looks like this:

products.php

$m = $_GET['m']; if ($m == "") {$link = "http://www.mydomain.com";} if ($m == "Duncraft_TripleTubeHaven") {$link = "myaffiliatelink";} if ($m == "Yardiac_DrollYankeeFlipper") {$link = "myaffiliatelink";} //etc. header("Location: $link"); exit(); I call the file like this: mydomain.com/products.php?m=Yardiac_DrollYankeeFlipper This way my affiliate links are hidden, but the php redirects the visitor using the correct affiliate link.

However, I needed to alter some of these affiliate links to make the below code work.

• The affiliate links I copied from Commission Junction had html escape codes in them, for example
  http://www.tkqlhce.com/click-(myaffiliatenumber)?url=http%3A%2F%2Fwww2.yardiac.com%2Flong.asp%3Fitem_id%3D2136&cjsku=812528%22
  
  instead of:
  http://www.tkqlhce.com/click-(myaffiliatenumber)?url=http://www2.yardiac.com/long.asp?item_id=2136&cjsku=812528
  
  Notice that it uses %3A for :, %2F for /, etc. and it has an extra %22 at the end which is a double-quote. Note sure how that last thing got in there. In my original file, the redirect worked fine—I was seeing commissions from these vendors. But after I added the code below, I got errors. So I had to go through and replace all the special html escape codes with their values. I also removed the end %22.
• The links I got from ShareASale already had the afftrack variable in them:
  
  http://www.shareasale.com/r.cfm?(my affiliate number)&afftrack=&urllink=incentrakiii.stores.yahoo.net/noname138.html
  
  This means that if I add “&afftrack” onto the end of the link, that parameter will be there twice. It may not matter, but better to be safe; I removed the blank afftrack so that now my link is:
  http://www.shareasale.com/r.cfm?(my affiliate number)&urllink=incentrakiii.stores.yahoo.net/noname138.html

NOTE: The first site I did this with, I needed to modify the html escape codes, as I mentioned above. However, the second site I did this with, I did not alter the links. I left the escape codes in, with no errors. So try it with your unmodified link first and only worry about changing the escape codes if you get errors.

Add Additional PHP Code Now all I had to do was add php code to

1. Read the Google Analytics cookie
2. Write the values to a mysql table
3. Look up the last auto-increment number
4. Add this as the id field to the end of the affiliate link.
   This is a little tricky because some affiliate networks call it sid and some call it something else, so I need to set the correct variable based on the network I’m going to. I found this in a post on a forum:
   
   Linkshare: u1= Commission Junction: SID=
   BeFree: bfinfo=
   Performics: mid=
   Shareasale: afftrack=

First I put the database connection code into a separate file. This is so that I can call it using @ and suppress any errors—if there are any errors connecting, I don’t want to display them to the user. I just want to send them to my affiliate link without tacking on the id field.

connecttodb.php

//Connect to the database $db_host = "localhost"; $db_user = "myuser"; $db_pwd = "mypassword"; $conn = mysql_connect($db_host, $db_user, $db_pwd);


Next I edited my code in my file called products.php. products.php

$m = $_GET['m']; if ($m == "") {$link = "http://www.mydomain.com/";} if ($m == "Duncraft_TripleTubeHaven") {$link = "myaffiliatelink";} if ($m == "Yardiac_DrollYankeeFlipper") {$link = "myaffiliatelink";} //etc.
//Grab the Google Analytics data from the cookie. //This will set variables called $campaign, $medium, $source, $text, $content, and $gclid. //These will be blank if there is no cookie. if(isset($_COOKIE['__utmz'])) { $gainfo = $_COOKIE['__utmz']; $start = strpos($gainfo,"utmcsr"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $source=substr($gainfo, $start + 7); else $source= substr($gainfo, $start + 7,$end - $start - 7); } $start = strpos($gainfo,"utmcmd"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $medium=substr($gainfo, $start + 7); else $medium= substr($gainfo, $start + 7,$end - $start - 7); } $start = strpos($gainfo,"utmctr"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $term=substr($gainfo, $start + 7); else $term= substr($gainfo, $start + 7,$end - $start - 7); } $start = strpos($gainfo,"utmcct"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $content=substr($gainfo, $start + 7); else $content= substr($gainfo, $start + 7,$end - $start - 7); } $start = strpos($gainfo,"utmccn"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $campaign=substr($gainfo, $start + 7); else $campaign= substr($gainfo, $start + 7,$end - $start - 7); } $start = strpos($gainfo,"utmgclid"); if ($start!==FALSE) { $end = strpos($gainfo,"|", $start + 1); if ($end===FALSE) $gclid=substr($gainfo, $start + 8); else $gclid= substr($gainfo, $start + 8,$end - $start - 8); } } //Connect to the db, but suppress errors. @include '../code/connecttosfb.php'; if (!$conn !! !$campaign)) { //there was a problem connecting or there is no cookie; use the original link $link2 = $link; } else { //Write the Google Analytic info to a table and get a unique id for that record $db_name = "mydatabase"; mysql_select_db($db_name); mysql_query("INSERT INTO `mytable` (campaign, source, medium, content, term, gclid,product) VALUES ('$campaign','$source', '$medium','$conten','$term','$gclid','$m')"); $lastid = mysql_insert_id(); mysql_close($conn); //append the correct variable onto the link based on the affiliate network. // I know this by the prefix (vendor) found in the m variable that was passed to the page. $i = substr($m,0,5); switch ($i) { case 'Duncr': $idstring = "&mid="; break; case 'Yardi': $idstring = "&SID="; break; case 'BirdS': $idstring = "&afftrack="; break; default: $idstring = ""; } $link2 = $link . $idstring . $lastid; //append the id onto the end of the link } //Redirect to the modified affiliate link header("Location: $link2"); exit();


That’s it. Be sure you test it by calling your products.php page with the different products that go to the different affiliate networks. For most networks, you’ll see the id parameter appended to the end of the url. For Performics (now Google ConnectCommerce), the parameter disappears in the url. (A made-up parameter stays on the url, so I assume a disappearing parameter means it is working.)

Also test all different networks with a bad database connection (just edit your connection information so that there’s a typo in the username). You should get to each network with no id field appended to the url, and with no errors to the user.

Lastly, keep an eye on your affiliate reports. Make sure values start showing up in the id fields on reports. If they don’t, double-check that you have the correct syntax for the id parameter for that particular network.
Problem with Google Analytics Reporting

There is one little glitch with this method and Google Analytics reports.

I found out what the gclid field in the cookie is for. This is the field that gives Google Analytics all its information when the traffic comes from clicking on your Adwords ad. Without it, GA sees them as direct traffic, not Adwords traffic. The gclid field is filled in when you enable auto-tagging in your Adwords account.

Unfortunately, if auto-tagging is enabled, the utmccn (campaign) and utmcmd (medium) fields in the cookie have values of “(not set)” for traffic coming to your site from your Google Adwords ads. It seems that Google either sets gclid or utmccn/utmcmd, but not both.

I did notice that utmctr (keyword) is set regardless.

So the bottom line is that if you use my above method to store the cookie values but you also want your Google Analytics reports to see the traffic from Adwords properly, it means you have to turn auto-tagging on and that only the keyword will be stored in your table when you read the cookie.

Too bad there isn’t a way to read gclid and store its values (it’s encrypted).

Posted by susb8383 under Affiliate Marketing & Google Analytics | 4 Comments »