How do I find out what searches people did to end up on my Web site?

There are lots of great applications that you can install on your server to get traffic statistics, programs that are going to do a far better job letting you visualize what's going on than anything you can cobble together in a shell script. Further, there are also great utilities like Google Analytics that are free and quite easy to hook in (see: adding Google Analytics to your Web site).

You had a pretty specific request, however, so let's have a look at how we could dig through the Apache log file to identify which hits are directly from Google and then how to extract them so that you get a clean summary in your mailbox.

First off, to have something run on a regular schedule, we'll use the cron facility in Linux. It's one of the very best features of a Linux system and if you have a Linux system, learning crontab is time very, very well spent.

But let's start at the beginning. You'll need to find where Apache is storing your log files, then you can just start out by searching for "google.com" with "grep". The output lines are llooonnnggg:

(I've added line breaks so it's more readable, but the output is one long line in reality)

As you can see, there are many fields in this output, separated by spaces. If you count, space by space, you'll see that the REFERRER field is #11, so we can isolate it by using the "cut" command:

That's a bit more readable. Now let's go further and observe that Google queries are name=value pairs separated by an ampersand (as are, of course, all CGI query URLs). Let's break the URL down and see what we get:

One more step and I think, by George, we have something:

One heck of a command for a small bit of output, but once we tweak the "head -1" which has let us just work with one match, we can now quickly see, say, the 20 most recent searches ("head -20"):

Uh oh, looks like that "grep" pattern isn't sufficiently isolating. Instead we'll try "^q=" and the results are more what we seek:

Interesting, but what about getting a useful report from it? We need to clean things up a bit (remove the "q=" and replace '+' with ' ') and we need to sort and tally things so that we can see the most common searches rather than every single search. This is done with "sed" and the power combination of "sort | uniq -c | sort -rn":

$ grep google.com /home/taylor/www/logs/askdavetaylor.com-access_log |cut -f11 -d\   |  head -20 | 
tr '&' '\012' | grep "^q=" | sed 's/q=//;s/+/ /g' | sort | uniq -c | sort -rn
   1 sony psp warranty claim
   1 psp won%27t play games because it says they corrupted, what to do%3F
   1 parallels cant see my other partition 
   1 iphone photos to mac
   1 installing windows on bootcamp
   1 i made up a yahoo email address for myspace now i cant delete it
   1 how to get spades on myspace
   1 how to download music to psp
   1 how does ebay work
   1 how do you put music on a psp
   1 how can i get on myspace at school
   1 converting wma files to mp3
   1 comcast remote codes"
   1 can i use two wireless routers
   1 build web page to embed youtube
   1 broken psp screen
   1 ask dave taylor"

Still a few things to tweak, but let's finally strip out that "head" and look at all the searches people have done to get to the site:

 108 convert wma to mp3
  43 myspace at school
  34 windows security alert
  34 how to convert wma to mp3
  33 virtual memory too low
  26 how do i delete my myspace
  26 google address book
  26 comcast remote codes
  24 converting wma to mp3
  23 how to install windows on mac

Nice. That's great information and ready to use. At least, ready enough for this quick and dirty solution.

My resultant script, when I take the command sequence and drop it into a Bourne shell script file, is:

Now, finally, use "crontab -e" to add a line to cron that invokes this new script on a weekly basis. It brings up your favorite $EDITOR with your cron file within - if you have one. Crontab entries are in the form: minute, hour, day-of-month, month, day-of-week, command, so lets pick midnight on Mondays as our desired date and time.

In crontab, that looks like:

There are two ways we can structure the command itself. We can just invoke the script, in which case the script itself will have to deal with turning the output into an email message, or we can do that within the crontab entry itself:

That's all there is to it. Make sure "SCRIPTS" is defined earlier in the crontab file, save and quit the edits, and you're done. Tuesday morning you'll have a report in your inbox.