No internet marketing blog would be complete without an Google Analytics rant.

It annoys the heck out of me!!

I know it’s very powerful, it can help you analyze your website and see where people are abandoning sales, etc.

But to me, Google Analytics is like the genius who can do complex calculus but can’t add 2 + 2.

Here’s what I mean: GA is all about telling you what percentage of visitors did X, how many went to Y, etc. but it can’t give you any specifics. It can answer ‘how many’ but not ‘which ones.’

For example, I set up a goal of reaching the sale confirmation page on my site.

I could see that I got 3 sales over a month. Great!! So I sat down with Google Analytics to try to figure out where these people came from so I could beef up that source.

To me, this seems like an obvious piece of information someone would want. But do you think there’s an easy way to get that info out of Google Analytics? Nope!

None of the goal conversion reports have a button or option for Show Source. Instead you have to try to guess where the visitor came from and then run the report from that angle.

Hmm, I thought, maybe they came from an organic search. So I went into Keyword report. Ok, so I could see that two of the keywords it showed me have a goal conversion.

But what about the remaining sale? Did it come from PPC? Nope. Referrals? Nope. You see the problem here. Even though GA knows this info, it doesn’t just tell you. You have to guess correctly to find out for sure.

At first I thought I was just being thick. I am after all a very newbie GA user. But after searching the web, I found a lot of people who asked this same question, how to relate goal conversion with source. And there was never an answer.

The closest I could get is to do a custom report and show the metric of Goal 1 Completion and the Dimension of Traffic Sources/Source. Aha, this shows me that 1 came from Google, 1 from MSN, and 1 from a website where I have a banner ad.

To see the keywords I have to add a submetric of Keyword, which means that for the Google and MSN results I have to drill down to see the keyword.

Again, this is such an obvious piece of data that people would want to see. Why goes Google Analytics make us jump through hoops to get it?

Another thing I was trying to do (ok, this is a little complex) was to assign a unique id to each visitor and then use GA to get some info related to that number.

Why would anyone want to do that? Because I was passing that number to my affiliate network and showing it on commission reports.

In other words, I knew that #2839 bought something from a vendor which gave me $22.50 in commissions but I wanted to know more about that person. What keyword did they search for? What browser did they use? What time of day did they come to my site?

I figured out how to do the hardest part of this, or so I thought: how to set Google Analytics’ User Defined Variable to be a unique number for that visitor. I thought the easy part was going to be running reports. But I was wrong. No such GA report would show me the value I set along with any source info.

As a matter of fact, I got so excited when I first discovered this new Custom Report feature (still in Beta). I thought that at last I’m going to be able to create reports with exactly the columns I want!

Alas, no, that would be too obvious. The best you can do is select a ‘dimension’ and a ‘metric’ to show, which means you can see a single piece of information for a visitor, but if you want any thing else, that would be a submetric that you drill down for.

I could see a list of all the ids for the visitors, but I had to drill down to see keywords. But you can’t export all the drill down detail, you can only export what you see on your screen at one time.

Here’s another complaint: since I have Analytic tracking code on every single page of my site, don’t you think GA would be able to show me all the pages someone visited? Why can’t I choose a single visitor (such as one of the people who reached goal 1) and see a trail of all my web pages he went to?

Google Analytics has all this info. Why can’t I see it?

So my conclusion is that Google Analytics is great for big corporations who want to know generalities and don’t have any inclination to get involved with specifics. But for me, it’s frustrating that it can’t tell me some really basic and obvious data on my visitors.

But it is free, which I why I use it. If I ever make money on internet marketing, I’ll get an analytics tool that is a little more user-friendly. I’ve heard good things about Hypertracker.

Posted by susb8383 under Google Analytics | 1 Comment »

In writing my own keyword tracking script that

1. reads the Google Analytics cookie
2. writes the fields of the cookie to a mysql table with an auto-increment primary key
3. appends that primary key number onto the end of my affiliate link as the subid number

I found one main problem.

The Google cookie is not always being created. Which means I get no data for that visitor.

I found lots and lots of records in my table that had blank fields. At first I thought these were robots. But then I noticed that some of them turned into sales. Never heard of a robot buying anything before.

A few days ago I was modifying my script to be used with a new campaign. As I tested, I discovered that one time when I clicked to my site, the google cookie was not set and I ended up writing one of these ghost records to my tracking table.

Before I wasted time with too much troubleshooting, I tried it again. This time the cookie was set.

So I don’t know exactly what the problem is, but I DO know that I can’t count on Google to always set that cookie. So I started looking at other alternatives.

I found a free open source keyword tracker called Prosper202.

Their original product is called Tracking202, which is the same as Prosper202 but it hosted by them. You log into a web account to manage your tracking. But I wanted something that I host myself, so I downloaded and installed Prosper202.

I will review it in another post. Stay tuned.

Posted by susb8383 under Google Analytics | 1 Comment »

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 »