Google Hacks

Hack 63 Tracking Result Counts over Time

Query Google for each day of a specified date range, counting the number of results at each time index.

Sometimes the results of a search aren't of as much interest as knowing the number thereof. How popular a is a particular keyword? How many times is so-and-so mentioned? How do differing phrases or spellings stack up against each other?

You may also wish to track the popularity of a term over time to watch its ups and downs, spot trends, and notice tipping points. Combining the Google API and daterange: [Hack #11] syntax is just the ticket.

This hack queries Google for each day over a specified date range, counting the number of results for each day. This leads to a list of numbers that you could enter into Excel and chart, for example.

There are a couple of caveats before diving right into the code. First, the average keyword will tend to show more results over time as Google ads more pages to its index. Second, Google doesn't stand behind its date-range search; results shouldn't be taken as gospel.

This hack requires the Time::JulianDay (http://search.cpan.org/search?query=Time%3A%3AJulianDay) Perl module.

63.1 The Code

#!/usr/local/bin/perl
# goocount.pl
# Runs the specified query for every day between the specified
# start and end dates, returning date and count as CSV.
# usage: goocount.pl query="{query}" start={date} end={date}\n}
# where dates are of the format: yyyy-mm-dd, e.g. 2002-12-31

# Your Google API developer's key
my $google_key='insert key here';

# Location of the GoogleSearch WSDL file
my $google_wdsl = "./GoogleSearch.wsdl";

use SOAP::Lite;
use Time::JulianDay;
use CGI qw/:standard/;

# For checking date validity
my $date_regex = '(\d{4})-(\d{1,2})-(\d{1,2})';

# Make sure all arguments are passed correctly
( param('query') and param('start') =~ /^(?:$date_regex)?$/
  and param('end') =~ /^(?:$date_regex)?$/ ) or
  die qq{usage: goocount.pl query="{query}" start={date} end={date}\n};

# Julian date manipulation
my $query = param('query');
my $yesterday_julian = int local_julian_day(time) - 1;
my $start_julian = (param('start') =~ /$date_regex/)
  ? julian_day($1,$2,$3) : $yesterday_julian;
my $end_julian = (param('end') =~ /$date_regex/)
  ? julian_day($1,$2,$3) : $yesterday_julian;

# Create a new Google SOAP request
my $google_search  = SOAP::Lite->service("file:$google_wdsl");

print qq{"date","count"\n};

# Iterate over each of the Julian dates for your query
foreach my $julian ($start_julian..$end_julian) {
  $full_query = "$query daterange:$julian-$julian";
  # Query Google
  my $result = $google_search ->
    doGoogleSearch(
      $google_key, $full_query, 0, 10, "false", "",  "false",
      "", "latin1", "latin1"
    );

  # Output
  print
    '"',
    sprintf("%04d-%02d-%02d", inverse_julian_day($julian)),
    qq{","$result->{estimatedTotalResultsCount}"\n};
}

63.2 Running the Hack

Run the script from the command line, specifying a query, start, and end dates. Perhaps you'd like to see track mentions of the latest Macintosh operating system (code name "Jaguar") leading up to, on, and after its launch (August 24, 2002). The following invocation sends its results to a comma-separated (CSV) file for easy import into Excel or a database:

% perl goocount.pl query="OS X Jaguar" \
start=2002-08-20 end=2002-08-28 > count.csv

Leaving off the > and CSV filename sends the results to the screen for your perusal:

% perl goocount.pl query="OS X Jaguar" \
start=2002-08-20 end=2002-08-28

If you want to track results over time, you could run the script every day (using cron under Unix or the scheduler under Windows), with no date specified, to get the information for that day's date. Just use >> filename.csv to append to the filename instead of writing over it. Or you could get the results emailed to you for your daily reading pleasure.

63.3 The Results

Here's that search for Jaguar, the new Macintosh operating system:

% perl goocount.pl query="OS X Jaguar" \
start=2002-08-20 end=2002-08-28
"date","count"
"2002-08-20","18"
"2002-08-21","7"
"2002-08-22","21"
"2002-08-23","66"
"2002-08-24","145"
"2002-08-25","38"
"2002-08-26","94"
"2002-08-27","55"
"2002-08-28","102"

Notice the expected spike in new finds on release day, August 24th.

63.4 Working with These Results

If you have a fairly short list, it's easy to just look at the results and see if there are any spikes or particular items of interest about the result counts. But if you have a long list or you want a visual overview of the results, it's easy to use these numbers to create a graph in Excel or your favorite spreadsheet program.

Simply save the results to a file, and then open the file in Excel and use the chart wizard to create a graph. You'll have to do some tweaking but just generating the chart generates an interesting overview, as shown in Figure 6-3.

Figure 6-3. Excel graph tracking mentions of OS X Jaguar

63.5 Hacking the Hack

You can render the results as a web page by altering the code ever so slightly (changes are in bold) and directing the output to an HTML file (>> filename.html):

...
print
  header(  ),
  start_html("GooCount: $query"),
  start_table({-border=>undef}, caption("GooCount:$query")),
  Tr([ th(['Date', 'Count']) ]);

foreach my $julian ($start_julian..$end_julian) {
  $full_query = "$query daterange:$julian-$julian";
  my $result = $google_search ->
    doGoogleSearch(
      $google_key, $full_query, 0, 10, "false", "",  "false",
      "", "latin1", "latin1"
    );

  print
    Tr([ td([
      sprintf("%04d-%02d-%02d", inverse_julian_day($julian)),
      $result->{estimatedTotalResultsCount}
    ]) ]);
}

print
  end_table(  ),
  end_html;