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

Or, yes I'm sick of http://localhost/projects/

When i work under linux, i setup virtual host for every new project, so i don't have to have to deal with paths and other stuff you get when you keep your project in /projects/ folder.
I never even tried to setup Virtual Host under windows, I was convinced that it takes some voodoo magic to get it work, but it turns out to be 5 minutes of work.

For web development under windows I personally use wamp. It's good any other all-in-one solutions, but for some reason i stick to it. You may also wanna try xampp.

So, lets get down to action.
Step 1.
First you must create new local directory for your new project, eg: C:\wamp\test-project

Step 2.
Next, you need to edit C:\WINDOWS\system32\drivers\etc\hosts, and add line with ip and your project virtual host name
eg:

      127.0.0.1 localhost
      127.0.0.1 test-project.example.com

Step 3.
Try to ping your new host. Go to command prompt and type ping test-project.example.com

C:>ping test-project.example.com

Pinging test-project.example.com [127.0.0.1] with 32 bytes of data:

Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
.
.

Step 4. Edit the httpd.conf file at the virtual hosts section (usualy at the end of file).

      NameVirtualHost *

      # this is the default mapping to http://localhost/
      <VirtualHost *>
          DocumentRoot C:\wamp\www
          ServerName localhost
      </VirtualHost>

      # the new virtual host mapping to http://project-name/
      <VirtualHost *>
          DocumentRoot "C:\wamp\test-project"
          ServerName test-project.example.com
      <Directory "C:\wamp\test-project">
          Options Indexes FollowSymLinks Includes
          AllowOverride All
          Order deny,allow
          Deny from all
          Allow from 127.0.0.1
          DirectoryIndex index.php
        </Directory>
     </VirtualHost>

5. Restart Apache and point your browser to http://test-project.example.com

Congratulations, now you can enjoy you virtual server and forgot about http://localhost.

Ajax Graph based on Prototype.js

Note: Updated to v.0.96 on January 16. 2008

Note: Updated to v.0.95 on April 10. 2007

For one of my projects, i needed simple graph to represent data for a single year by months.
I came up with this solution, and decided to make free version.

For now it has few limitations (only positive numbers, only integers), but ill fix those in next few days...

• Demo
• Download (zip, 14KB)