Hello
I'm trying to track the keywords that come from my google ads to my site. My other fellow affiliates use this code for its redirect page:
switch($_REQUEST['o'])
{
case '25-beautiful-homes':
$link = "http://scripts.affiliatefuture.com/AFClick.asp?affiliateID=XXXXXX&merchantID=XXX&programmeID=1838&mediaID=0&tracking=".$_GET['keyword']."&url=http://www.magazine-group.co.uk/magazine-group/categories/home-and-interiors/741/25-beautiful-homes.thtml";
break;
default:
$link = "http://scripts.affiliatefuture.com/AFClick.asp?affiliateID=XXXXXX&merchantID=XXX&programmeID=1838&mediaID=0&tracking=".$_GET['keyword']."&url=";
}
header("Location: $link"); // Jump to the hiddden affiliate URL above
exit();the link from google ads looks like this:
http://domain.co.uk/25-beautiful-homes?keyword={keyword}
and the link on the destination page from the ads to the merchants are below:
http://domain.co.uk/redirect.php?o=25-beautiful-homes&keyword=(php wrap)echo $_GET['keyword'];(php wrap)
But since pricetapestry already has jump.php, how do I implement the {keyword} tracking feature in the codes? I imported my links like below into price tapestry:
http://scripts.affiliatefuture.com/AFClick.asp?affiliateID=94549&merchantID=538&programmeID=1838&mediaID=0&tracking={keyword}&url=http://www.magazine-group.co.uk/magazine-group/categories/motoring/4141582/motor-caravan.thtml
Thanks very much.
Hi David
You've got it correct, but I tried the codes and it didn't work. :( My tracking says "no reference" meaning no keywords was registered.
Any other solutions?
Thanks
Hi,
First thing to check, when you have landed on a page via AdWords, hover the mouse over a link, and can you confirm that you see &keyword=.... on the jump.php URL?
It that looks OK - check I was correct about how you have registered Affiliate Future URLs into the database - they need to actually contain {keyword} which is what the modification in jump.php looks for in order to do the string replacement...
If still no joy, could you email me a link to a sample landing page (one with &keyword=.... in the URL) and i'll take a look for you (reply to your reg code or forum registration email is the easiest way to get me)...
Cheers,
David.
Hi David
I got it resolved. I had to re-import the datafeed as the {keyword} wasn't updated yet.
Thanks very much.
Hi David,
Have been looking for something like this for ages and delighted I stumbled across this thread. Tracking works great.
One thing though is the merchant will also know which keywords are converting. I found a script which enters the adwords keyword into a database and gives out the ID associated with that keyword so all the merchant will see is a number. The code checks to see if the keyword is already in the database and outputs the ID, or will create a new ID if the keyword doesn't exist.
Here's the code. I'm wondering if you can advise on how to implement this or something similar with jump.php.
<?
include(”dbconnect.php”);
//
$keyword = trim($_GET[’keyword’]);
$keyword = mysql_real_escape_string($keyword);
// Finding the keyword in the database if it’s there
$sql = “SELECT * FROM keywords WHERE keyword = ‘$keyword’”;
$sql_out = mysql_query($sql);
if($sql_out){
$row = mysql_fetch_assoc($sql_out);
$id = $row[’id’]; //gets the id associated with the word
$sql = “UPDATE keywords SET count = count + 1″; // sql to increment the counter for that word
mysql_query($sql);
}else{
// This chunk puts our new word in the database and grabs it’s ID
$sql = “INSERT INTO keywords (keyword) VALUES (’$keyword’)”; // sql to insert the new keyword we don’t have cataloged
mysql_query($sql);
$id = mysql_insert_id(); // gets the id of the record we just inserted.
}
// Here we’re redirecting to the end location with our replaced keyword with the new ID
$location = “Location: http://location.com/subid=$id”;
header($location);
?>Hope you can help.
Thanks
Adrian
Hi Adrian,
Have you already implemented the above script in your Price Tapestry installation? In other words, is the "keywords" table already in your database?
If not, the first step would be to add that table; and it looks like a simple 2 field table with an ID (primary key, auto-increment) and keyword field (I would recommend VARCHAR length 255).
Once that's in place, if you could post your modified jump.php i'll try and merge the scripts for you...
Cheers,
David.
Hi David,
Here's my modified jump.php. Have created a keywords database with id and keyword fields. Also, is there anyway that the KW variable could be tied in to a session(cookie) so that if a visitor clicks off the product page they arrived on and click out on another product page the keyword will go with them ?
<?php
require("includes/common.php");
$sql = "SELECT merchant,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id=".database_safe($_GET["id"]);
database_querySelect($sql,$rows);
$product = $rows[0];
$sql = "UPDATE `".$config_databaseTablePrefix."feeds` SET clicks=clicks+1 WHERE merchant = '".$product["merchant"]."'";
database_queryModify($sql,$insertID);
$url = $product["buy_url"];
$url = str_replace("{kw}",$_GET["kw"],$url);
header("Location: ".$url);
exit();
?>
Many thanks again.
Adrian
Hi Adrian,
Try this to turn the keywords into an ID...
<?php
require("includes/common.php");
if ($_GET["kw"])
{
$kw = $_GET["kw"];
$sql = "SELECT id FROM keywords WHERE keyword='".database_safe($kw)."'";
if (database_querySelect($sql,$rows))
{
$kw_id = $rows[0]["id"];
}
else
{
$sql = "INSERT INTO keywords SET keyword='".database_safe($kw)."'";
database_queryModify($sql,$insertId);
$kw_id = $insertId;
}
}
$sql = "SELECT merchant,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id=".database_safe($_GET["id"]);
database_querySelect($sql,$rows);
$product = $rows[0];
$sql = "UPDATE `".$config_databaseTablePrefix."feeds` SET clicks=clicks+1 WHERE merchant = '".$product["merchant"]."'";
database_queryModify($sql,$insertID);
$url = $product["buy_url"];
$url = str_replace("{kw}",$kw_id,$url);
header("Location: ".$url);
exit();
?>
To set the keyword as a cookie, you will need to make changes to products.php. Try the following code right at the very top of the script (after the opening PHP tag). I'm assuming that your keyword comes in from Google in $_GET["keyword"] as in the other thread...
if ($_GET["keyword"])
{
setcookie("kw",$_GET["keyword"],1262304000);
}
else
{
$_GET["keyword"] = $_COOKIE["kw"];
}That should be enough to do the trick assuming i've got all the variable names correct...!
Cheers,
David.
Hi David,
That's fantastic. Worked straight away :)
One thing though, I'm looking at Buy.at reports which displays the number of clicks, the link ID (from the keywords table) and referring URL. So lets say in adwords my URL is
www.mysite.co.uk/subdirectoryA/product/my-product.html?kw=hello
This shows up as the exact referring url with the keyword hello at the end, where as if the visitor clicks off that product page and clicks out on another, the referring url is the new product url but without the kw (which is stored correctly in the cookie). I presume its not possible to remove ?kw=hello from the url when a visitor enters the site. This would ensure that the merchant definitely doesn't see what keywords are making sales.
Also, I'm using subdirectories and my keywords table is in lets say subdirectoryA database which is specified in subdirectoryA config.php. How would I set this up using 1 separate keywords database with a keywords table and have jump.php in each subdirectory pointing to that table. Or would it be easier and is it possible to have a keywords table in each subdirectory database with the ID field having something like subB1 and incrementing SubB2..... and SubB2 showing up in the reports as the keyword ID.
Hope this makes sense, after re-reading and not too sure if it does :)
Thanks for all the help so far.
Much appreciated
Adrian
Hi Adrian,
I understand. The trick would be that if the visitor to your site comes in with the keyword in the URL, you then cookie the keyword and REDIRECT back to the same page but without the keyword.
Your Buy.at reports should then display the main product URL and not the version containing the keyword. To have a go at this, try the following code at the top of products.php in place of the version posted above:
if ($_GET["keyword"])
{
setcookie("kw",$_GET["keyword"],1262304000);
if ($config_useRewrite)
{
$location = $config_baseHREF."product/".$_GET["q"].".html";
}
else
{
$location = $config_baseHREF."products.php?q=".$_GET["q"];
}
header("Location: ".$location);
exit();
}
else
{
$_GET["keyword"] = $_COOKIE["kw"];
}Because this uses config variables make sure it goes after require("includes/common.php").
Hope this helps!
Cheers,
David.
David,
Absolute Genius!! This is a great addition. Looking forward to posting my site in the new sites section in a few weeks once the data entry is finished and the site is looking a bit better.
Thanks again :)
Adrian
Hi David,
I implemented the code of this thread and everything works great. However, I have a question concerning the cookie. It seems to me that the cookie is saved forever, which means that if the same user visits my site again by clicking on an other adwords ad, it will be tracked with the keyword when he or she visited my site for the first time. Is it possible to let the cookie expire after a while?
Cheers, IG
Hi,
The expiry time is set by this line:
setcookie("kw",$_GET["keyword"],1262304000);The numeric value is the expire date/time as a Unix timestamp value. 1262304000 is something like 2020 so effectively "lifetime" as far as cookies are concerned.
If you want to control the expiry period, the easiest way is to use PHP's strtotime() function, and specify a period that you would like the cookie to be valid for. For example:
setcookie("kw",$_GET["keyword"],strtotime("+1 Month"));...this would make the cookie valid for 1 month. You can use all sorts of keywords in the string-time value, for example "+1 Day" or "+6 Hours" etc. For the full spec, have a look at:
http://uk3.php.net/manual/en/function.strtotime.php
Cheers,
David.
David,
In one of my datafeeds, the deep links to the products have the following format:
http://xyz.network.com/click?a(XXXXXX)p(XXXXXX)prod(XXXXXXXX)ttid(X)epi({keyword})
I am using the code above, but the keyword is not passed on. Is this because of the brackets around "{keyword}"? Is there a way to get around this problem?
Cheers, IG
Hi Ivo,
It should work fine - the only requirement is that whatever you use inside str_replace matches exactly what you need to be replacing in your Buy URL. In the code above, it actually uses:
$url = str_replace("{kw}",$kw_id,$url);...so in your case, this would actually need to be:
$url = str_replace("{keyword}",$kw_id,$url);Hope this helps,
Cheers,
David.
Hi David
Thanks a lot for your support. The affiliate network actually had a huge delay (8 hours). Everything works fine now.
I have a few more questions, but first I try to figure them out myself.
By the way, can you please send me your email address and let me know which Amazon Store (UK?) you are using? Xmas is coming up....
Cheers, IG
Hi David,
Have a category page I want to send visitors too from Adwords. Changed the code above to the following thinking it would work but it doesn't .
if ($_GET["kw"])
{
setcookie("kw",$_GET["kw"],strtotime("+1 Month"));
if ($config_useRewrite)
{
$location = $config_baseHREF."category/".$_GET["q"].".html";
}
else
{
$location = $config_baseHREF."categories.php?q=".$_GET["q"];
}
header("Location: ".$location);
exit();
}
else
{
$_GET["kw"] = $_COOKIE["kw"];
}
Is there something else I should be doing. My Category link is
http://www.site.co.uk/category/category-name/
and looking to add ?kw=test to that link in adwords.
Thanks
Adrian
Hi Adrian,
This section:
if ($config_useRewrite)
{
$location = $config_baseHREF."category/".$_GET["q"].".html";
}
else
{
$location = $config_baseHREF."categories.php?q=".$_GET["q"];
}...should be:
if ($config_useRewrite)
{
$location = $config_baseHREF."category/".tapestry_hyphenate($_GET["q"]).".html";
}
else
{
$location = $config_baseHREF."search.php?q=category:".urlencode($_GET["q"]);
}(although since you're linking to a re-written page the second case would never actually happen). Other than that it should work. If it still doesn't, check the resulting URL that it does try to redirect to and check for any errors...
Cheers,
David.
Hi,
I just implemented the above code (with id instead of kw in order not to pass keyword conversion to merchant) but not entirely sure how all this works.
for instance, I clicked on one of my ads on google and clicked all the way through to the merchant but nothing got registered in the database table "keyword" .
Can someone please tell me how the above code tracks which keywords generates which sales?
Many thanks for all your help.
Anton
Hi Anton,
Could you perhaps email me the files that you have modified to implement this change and i'll
check them over for you.
Critical to this working is that the database table to log keywords has been setup correctly. If the table structure doens't match the SQL being generated then the queries will fail which is the most likely reason for the keywords not being inserted.
I'll check your code over, and return the files with any debug code that may be necessary to fix this for you. I'll also include the debug version of database.php that will show us whether it's a database problem.
Use the email address on this page to get me...
Cheers,
David.
Hi David,
First off your script is awesome! I am in the process of adding the Google Adwords tracking to my site with Commission Junction, and I'm stuck on one section. I've already implemented the changes above to do the tracking for products.php, and that works great. I'm now trying to do the same exact keyword tracking on the brands.php but so far the the cookie doesn't seem to be getting set, and the affiliate link is not adding the ?sid=keyword to the end of the url. I'm using the Search Engine Friendly URLs if that helps at all.
So my landing page is such:
http://domain.com/brand/timex/?keyword=timexwatches
I'd like to have it so that the affiliate url looks like such:
http://affiliateurl.com/?sid=timexwatches
I was able to do this for the products.php file but so far I've had no luck with brands.php.
Any help would be much appreciated.
Thanks!
Paul
Hi Paul,
Thank you for your comments!
With search engine friendly URLs in place;
http://domain.com/brand/ - is served by brands.php
http://domain.com/brand/timex/ - is served by search.php
So if you add the tracking code to the top of search.php and it should do the trick...
Cheers,
David.
Hi David,
I tried actually putting that in my search.php file but so far no luck. Though I'm not entirely sure why. Here is what in the search.php file:
<?php
if ($_GET["keyword"])
{
$expire = mktime(0,0,0,1,1,2010);
setcookie("keyword",$_GET["keyword"],$expire,"/");
}
require("includes/common.php");
$q = (isset($_GET["q"])?tapestry_normalise($_GET["q"],":\."):"");
$page = (isset($_GET["page"])?intval($_GET["page"]):1);
$sort = (isset($_GET["sort"])?$_GET["sort"]:"relevance");
$rewrite = isset($_GET["rewrite"]);The way I'm testing it is using firefox live headers and going through my test domain at:
{link saved}
So if, when I click through the jump link, I'm hoping to see something like this:
http://jdoqcy.com/?sid=testing
In the Live headers sidebar.
But right now I'm seeing just the http://jdoqcy.com. To me it looks like the cookie is not being set, but I'm not sure.
Hi,
I went to your test page and then checked cookies, but it isn't being set...
Could you email me these files as attachments and I'll check it out for you:
includes/tapestry.php
products.php
search.php
Cheers,
David.
Hi David,
I sent over the files. Hope you got them.
Thx again,
Paul
Thanks Paul,
I've returned a version of .htaccess that should fix it...
Cheers,
David.
Hi David,
I am using PT within Wordpress and have a tracking plugin that takes the keyword and assigns an id number to it. It will append the keyword id to the outgoing links if I add [TRACKINGID] as the clickref.
Following the information in this thread, I have added .'&clickref=[TRACKINGID]' to jump.php code in includes/tapestry.php like this.
function tapestry_buyURL($product)
{
global $config_baseHREF;
global $config_useTracking;
if ($product["merchant"]=="Amazon")
{
return $config_baseHREF."jump.php?url=".base64_encode($product["buy_url"]).'&clickref=[TRACKINGID]';
}
elseif ($config_useTracking)
{
return $config_baseHREF."jump.php?id=".$product["id"].'&clickref=[TRACKINGID]';
}
else
{
return $product["buy_url"].'&clickref=[TRACKINGID]';
}
}Would you be kind enough to confirm whether you think I have implemented it correctly or should I look to do it another way?
Many thanks.
Kind regards,
Hamish
Hi David,
I want to use this nice mod too. But it doesn't reload the right page. I'm using PTO Wordpress Plugin as you know
and used the code posted above.
<?php
if ($_GET["keyword"])
{
setcookie("kw",$_GET["keyword"],1262304000);
if ($config_useRewrite)
{
$location = $config_baseHREF."product/".$_GET["q"].".html";
}
else
{
$location = $config_baseHREF."products.php?q=".$_GET["q"];
}
header("Location: ".$location);
exit();
}
else
{
$_GET["keyword"] = $_COOKIE["kw"];
}
?>
But I think I need a different code for the pto_products.php ?
Help pls :)
Cheers,
Tony
Hi Tony,
In the plugin, it should be possible to set the keyword cookie within the pto_wp() function which is called before any header content is generated. Look for the following code at line 146 of pto.php
if ($pto_module || $pto_q)...and REPLACE with:
global $pto_product;
if ($pto_product && isset($_GET["kw"]))
{
setcookie("kw",$_GET["keyword"],1262304000);
header("Location: ".pto_common_productHREF((object)array("normalised_name"=>$pto_product));
exit();
}
if ($pto_module || $pto_q)Cheers,
David.
--
PriceTapestry.com
Hi David!
The cookie does not get set. :(
Any changes?
Cheers,
Tony
Hi Tony,
It looks like it may have hook into the WordPress URL handling - in the same file, look for the following code at line 312:
array_push($vars, 'pto_product');...and REPLACE with:
array_push($vars, 'pto_product');
array_push($vars, 'kw');And then instead of the replacement describe above:
global $pto_product;
global $kw;
if ($pto_product && $kw)
{
setcookie("kw",$kw,1262304000);
header("Location: ".pto_common_productHREF((object)array("normalised_name"=>$pto_product));
exit();
}
if ($pto_module || $pto_q)If the cookie is still not being set, could you confirm that the when visiting your site as, for example:
http://www.example.com/product/Product-Name.html?kw=Keyword
...that it is staying on that URL and not even redirecting?
Cheers,
David.
--
PriceTapestry.com
No still not setting the cookie, but redirecting correctly.
I found something interesting: http://scratch99.com/wordpress/development/setting-cookies-in-wordpress-trap-for-beginners/
With this code it setting the cookie correctly:
if ($pto_product && isset($_GET["kw"]))
{
setcookie("keyword",$_GET["kw"], time()+3600, "/", "www.mydomain.de");
header("Location: ".pto_common_productHREF((object)array("normalised_name"=>$pto_product)));
exit();
}What I dont unterstand is, if I look at the cookie, the domain being set is .www.mydomain.de ...with a "." in front of it!?
If I hover the merchant jump.php link, the kw= is also not being set.
But its sets the "kw=" value if I delete the header redirect function. That means it just can't read out the cookie? Maybe because its with a point "." in
front of it...?
Cheers,
Tony
Hi Tony,
Domain is optional so I would give it a go with just the path parameter, and that way the browser assigns the current host to the cookie.
The final stage therefore now is to modify the plugin generation of the jump.php link to include ke if set, so in pto_common.php look for the following code at line 81:
return $pto_config_externalBaseHREF."jump.php?id=".$product->id;...and REPLACE with:
$retval = $pto_config_externalBaseHREF."jump.php?id=".$product->id;
if (isset($_COOKIE["kw"]))
{
$retval .= "&kw=".urlencode($_COOKIE["kw"]);
}
return $retval;Hope this helps!
Cheers,
David.
--
PriceTapestry.com
It works!!
David..you are the best! Thank you a lot! :)
Tony
Hello David,
Which one of the above tracking modifications can I use to produce a tracking keyword searched by user, but not seen in affiliate network, but tracked in affiliate network as a unique identifier. I would like to track the keyword visitor searched for within ppc search campaign, but on affiliate network the tracked keyword replaced with a unique id or number and not keyword. Example being ?sid=boat in ppc campaign replaced and identified in affiliate network as ?sid=23514. Is this too involved to have each keyword searched by visitor put out a unique id and allow me to cross reference the search keyword and unique id some how?
Also my jump.php is modified with the dupe_hash modification to keep jump urls same.
Hi,
It would be straight forward to read the translation from an array in jump.php. First, add the code to set sid as a cookie at the very top of the script associated with your campaign landing pages e.g. top of products.php for product pages as the landing pages:
if (isset($_GET["sid"]))
{
$expire = strtotime("+1 month");
setcookie("sid",$_GET["sid"],$expire,"/");
}Next create a new file sid2id.php containing an array of sid => id pairs:
<?php
$sid["boat"] = 23514;
$sid["car"] = 54321;
// etc.
?>
And then in jump.php look for the following code:
header("Location: ".$product["buy_url"]);...and REPLACE with something like;
if (isset($_COOKIE["sid"]))
{
require("sid2id.php");
$sid = $sid2id[$_COOKIE["sid"]];
$product["buy_url"] = str_replace("%SID%",$sid,$product["buy_url"]);
}
else
{
$product["buy_url"] = str_replace("%SID%","",$product["buy_url"]);
}
header("Location: ".$product["buy_url"]);With that all in place, all you need to do is modify your Buy URLs using filters to contain a placeholder %SID% at the position where the SID should be inserted into the URL, or replaced with nothing (alternatively you could use a default value in the second call to str_replace above instead of the empty string ""). Typically this would be done with a Text After filter containing something like:
&sid=%SID%Hope this points you in the right direction!
Cheers,
David.
--
PriceTapestry.com
With all this in place, do I still need to create a keywords table from a keywords dmod.php? Do sid2id.php create a text file, xml file, or write to the keyword table? Does this modification work with normal search engine traffic. I would like to track visitor sales from any search engines along with the ppc adwords also.
Hi,
The above would just be a simple, no-database method of converting known incoming ids appended to your product URLs from your PPC campaigns such as sid=Keyword1+Keyword2 into a numeric value to use as your tracking parameter, but if you want to track arbitrary referrals then you would probably need to study HTTP_REFERER and store the relevant information that you wish to track in a database table to give you the ID to pass through in your affiliate URLs.
Can give a couple of examples of referring URL and how / what you wish to be able to track back to through an ID field?
Cheers,
David.
--
PriceTapestry.com
Hi,
Will it be the case that on a Price Tapestry product page, for example:
http://www.yoursite.com/product/blue-widget.html
...this will actually have been called with the URL:
http://www.yoursite.com/product/blue-widget.html?keyword=widget
If that's the case; the first step would be to propagate $_GET["keyword"] through to jump.php.
To do this, look for the following code in includes/tapestry.php (line 53):
return $config_baseHREF."jump.php?id=".$product["id"];Change this to the following:
return $config_baseHREF."jump.php?id=".$product["id"]."&keyword=".$_GET["keyword"];Then, in jump.php we need to replace {keyword} with the value of $_GET["keyword"]. Look for the following code, on line 14 of jump.php:
header("Location: ".$product["buy_url"]);Replace this with the following:
$url = $product["buy_url"];$url = str_replace("{keyword}",$_GET["keyword"],$url);
header("Location: ".$url);
I hope i've understood correctly - if so this should do the trick!
Cheers,
David.