Tutorial on how to log all your search queries when you use Adsense for Search

First when you setup your adsense for search choose Open search results within my own site

After you successfully setup your adsense for search you should access your search result pages via this kind of URL:

(you should get one really long URL)

now all we must do is to get query string from request URL and store it into database.
In order to do so we first create table to hold query summary:

CREATE TABLE `keywords_stats` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `keyword` varchar(128) character set utf8 NOT NULL,
  `cnt` mediumint(8) unsigned NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `keyword` (`keyword`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Now this is fine, we have place to put our queries but what if we want to make application that not only track queries, but also enables us to track queries on given date or in a time range? Second table to the rescue:

(this enables us to have query stats by date)

CREATE TABLE `keywords_stats_byday` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `keyword_id` mediumint(9) NOT NULL,
  `query_date` date NOT NULL,
  `cnt` mediumint(9) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `keyword_id` (`keyword_id`,`date`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Next, we create code that reads q parameter from URL and writes it int database

<?php

require_once('pathh/to/adodb.inc.php');
//CHANGE THIS TO YOUR DATABASE SETTINGS
$db_username='root';      //mySQL username
$db_password='pass';    		  //MySQL password
$db_name='test';       //MySQL database name
$db_host='127.0.0.1';     //MySQL host name or IP

$db = &ADONewConnection('mysql');
$db->Connect($db_host, $db_username, $db_password, $db_name);

$db->Execute("SET NAMES utf8");
$query_string=mysql_real_escape_string(str_replace('+',' ',$_GET['q']));

$db->Execute("insert into keywords_stats (`id`,`keyword`) values (NULL,'$query_string') ON DUPLICATE KEY UPDATE cnt=cnt+1;");
$keywordID=$db->getRow("select id from keywords_stats where keyword='$query_string'; ");
$db->Execute("insert into keywords_stats_byday (`id`,`keyword_id`,`query_date`) values (NULL,'$keywordID[id]',CURDATE()) ON DUPLICATE KEY UPDATE cnt=cnt+1;");

?>

now we sould save this php code as queries.php and include it somewhere on your search result page.

<?php
    require_once('queries.php');
?>

<div style="width:805px;margin:40px auto;">
<!-- Google Search Result Snippet Begins -->
<div id="googleSearchUnitIframe"></div>
<script type="text/javascript">
.
.
.
//(rest of search results code)

you can test your script by pointing your browser to:

somestring should appear in your tables.

now all that is left is to build application that show query stats.

SQL query to get all time top 20 keywords

SELECT keyword, cnt
FROM `keywords_stats`
ORDER BY cnt DESC
LIMIT 0 , 20

SQL query to get top 20 keywords today:

SELECT keywords_stats.keyword, sum( keywords_stats_byday.cnt ) AS wordcount
FROM keywords_stats
LEFT JOIN keywords_stats_byday ON keywords_stats.id = keywords_stats_byday.keyword_id
WHERE query_date = CURDATE( )
GROUP BY keywords_stats_byday.query_date, keyword_id
ORDER BY wordcount DESC
LIMIT 0 , 20

You may also get query keyword percentage, queries in some data range (good for export to excel, or to draw graphs).

Resources:

• Adsene for search
• ADOdb manual

Viewed 64751 times by 17885 viewers