Tracking Result Counts over Time

advanced tipscreenshot tip63.gif

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

link

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: [Tip #11] syntax is just the ticket.

This tip 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 tip requires the Time::JulianDay (http://search.cpan.org/search?query=Time%3A%3AJulianDay) Perl module.


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};
}

Running the Tip

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.

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.

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
screenshot google-tips-0603.gif

Tiping the Tip

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;