Internet Marketing and Web Development Resources
Home Site Map About Contact

Google Query and Link Stats File Conversion Tool

Webmaster Tools - Query and Link Stats File Converter beta


Google Stats File (csv/txt): 

Text Qualifier:     
Column Delimiter (Field): 
Row Delimiter (Record): 



See Download File Structure Documentation (as of December 2007) and my Guide to Google Webmaster Central Tools and Downloads.

Query Stats Tool for Windows Desktop

If you only want to use the tool for the Query Stats Report to bring it into a readable format, you can also use a small desktop based tool for MS Windows PCs. It was written by somebody else and does not have all the options this web based tool has, but it has multiple advantages over it too. It can handle larger files, you don't have to upload anything to anywhere and it shows the results right on the screen, which might be sufficient enough for some people already.

Here is the page with the download link. I posted a comment there already (it is a forum or blog) and recommended some additional features to be added that would be very helpful. If the downloads on that other website becomes unavailable for some reason, use the following links to the copy of the tools on my own server as backup. GoogleQueryStatsV1.zip and GoogleQueryStatsV2.zip, each is less than 10KB in size.

Version 2 has a feature to compare to reports with each other, but some of the existing features of version 1 were dropped as a result of this (due to incompatibility). I suggest checking out both version to find out, which one works best for your specific needs.

Requirements: Windows XP or Vista and Microsoft DOT NET Framework V2.0.

Also useful: This Guide by Amit Banerjee about "How to Organize Search Query Data from Google Webmaster Tools in Excel".

What is this tool good for?

The Files downloaded from Googles Webmaster Central are always in .CSV Format. CSV stands for "comma separated values". The file also uses double-quotes as text-qualifier, but only for the key-phrases. This is nothing bad and works probably for most people. CSV is a standard and also Microsoft Excel can open it right away without problems.

A real problem is the query statistics file for the whole site (including sub folders) where Google returns delimited values within delimited values, something Excel cannot handle. You would need technical know-how or you use this tool instead.

Purpose 1.

This tool is also helpful for people that can work with the CSV files from Google, but might have a slight problem with the file generated for the Query Stats for the complete site (including sub folders). The file from Google is structured like this.

		Site,Location,Search Type,Top search queries,Top search query clicks
		http://www.url.com/,(country) google.xx,search type,"[key phrase one:1][key ""phrase two"":2]",
		

You see, all Key Phrases for the same Site/Location and Search Type are grouped together in one cell, using "[,],:" and double quotes.

If you rather have a single record for earch key phrase, this tool can help.
The results would look like this:

		Site,Location,Search Type,Top search queries,Top search query clicks
		http://www.url.com/,(country) google.xx,search type,"key phrase one",1
		http://www.url.com/,(country) google.xx,search type,"key ""phrase two""",2
		

Update December 2007: The word is out that Google is planning to do something about this query stats download, which will make this tool more or less obsolete. There is no date set yet, but it is estimated to be introduced within the first quarter of 2008. A real API to access the data directly and via automated means is also on the horizon. Hallelujah.

Purpose 2.

This is more of a by-product and maybe useful for some folks who might want to use the stats downloaded from WebmasterCentral for something else, such as import it into a software application for statistical purposes. This application might or might not support clean CSV format. It is not unusual for some applications not to support the text-qualifier properly for example. Other applications might require feeds that use a different delimiter for columns (fields) and rows (records). The Query and Link Stats File Converter Tool could do that for you.

The tools supports various delimiters and text qualifiers. Here are the details (if you think that I missed one, let me know)

    Supported Row Delimiters (Records)
  • Linefeed/lf (unix/default) char(10)
  • Carriage Return & Linefeed/crlf
    (dos/windows) char(13) & char(10)
  • Carriage Return/cr (char(13)
  • Tab (char(9)
  • Semicolon (;)
  • Comma (,)
  • Vertical Bar/Pipe (|)
  • Tilde (~)
     
    Supported Column Delimiters (Fields)
  • Comma (CSV/default) (,)
  • Tab char(9)
  • Semicolon (;)
  • Vertical Bar/Pipe (|)
  • Tilde (~)
    Supported Text Quallifiers
  • Double Quote (default) (")
  • N/A (None)
  • ..any single ASCII character

Here is an example of the Query Stats file, but Pipe delimited with no text qualifier instead of the CSV format:

		Site,Location|Search Type|Top search queries|Top search query clicks
		http://www.url.com/|(country) google.xx|search type|key phrase one|1
		http://www.url.com/|(country) google.xx|search type|key "phrase two"|2
		

For information and news to Google Webmaster Central and additional tools and services provided to Webmasters by Google via the portal, visit the official Google Webmaster Central Blog.



Google Query & Links Stats Converter API

The converter is based on the downloads as of Spring 2007. Some additions and some changes occured since then. See the documentation of the download files available at Google WebmasterCentral as of December 2007.

If you want to automate the process, use the API of my Google Query and Link Stats File Converter Tool instead. No XML or fancy Web Service. It is really simple and straight forward.

API URL:http://www.cumbrowski.com/googlestatsconverter.asp

Required Parameters

url URL to Google Stats file (.csv or .txt) on your Web Server and accessible via HTTP (port:80) without authentication. The value (URL) should be url-encoded.
stats Numeric value. Currently valid are the numbers 1 to 7 where the following stands for each of the 7 possible values:

    1 = Top 20 Search Queries Stats
    2 = Top 20 Search Query Clicks Stats
    3 = Site Wide Query Stats (including Sub Folders)
    4 = External Links Stats per Page
    5 = External Links Details
    6 = Internal Links Stats per Page
    7 = Internal Links Details


Optional Parameters

rowd Row (Record) Delimiter - optional parameter, default is Linefeed (lf). Valid values:

    lf = Linefeed (unix/linux)
    cr = carriage return
    crlf = carriage return & linefeed (windows/dos)
    tab = Tab/Tabulator
    comma = Comma character (,)
    semi = Semicolon character (;)
    pipe = Pipe or Vertical Bar character (|)
    tilde = Tilde character (~)

cold Column (Field) Delimiter Character. The default is the comma character (CSV). Valid values:

    tab = Tab/Tabulator
    comma = Comma character (,)
    semi = Semicolon character (;)
    pipe = Pipe or Vertical Bar character (|)
    tilde = Tilde character (~)

txtq Text Qualifier Character. The default is double-quote the double-quote character(") Valid values:

    clear = No Text Qualifier character
    dbl = Double quote character ("), stardard for CSV files



Examples for API Calls

Tab delimited stats with cr+lh line breaks for Windows and no need for text qualifiers

http://www.cumbrowski.com/googlestatsconverter.asp?url=http%3A%2F%2Fwww%2Eyoursitec%2Ecom%2Fgooglestats%2Ecsv&stats=3&rowd=crlf&cold=tab&txtq=clear

URL with location to the stats (ficticious): http://www.yoursite.com/googlestats.csv
stats: 3 (All phrases for the Site)
rowd=crlf - Carriage Return and Linefeed
cold=tab - Tabulator character, practical for internet marketing, because "Tab" does not exists there.
txtq = clear, no text qualifier please

Second Example.

Much shorter URL, because advantage is being taken of the default options by not specifying them at all.

http://www.cumbrowski.com/googlestatsconverter.asp?url=http%3A%2F%2Fwww%2Eroysac%2Ecom%2Fg%2Ecsv&stats=3

URL with location to the stats (ficticious): http://www.mysite.com/123googlestats.csv
stats: 3 (All phrases for the Site)
rowd=(none) = Default
cold=(none) = Default
txtq=(none) = Default

Free Query and Link Stats File Converter Tool (c)2007 by Carsten Cumbrowski