How to use Google webmaster tools stats with Excel

Google’s webmaster tools has a neat feature that lets you download your query and click statistics (once you have verified ownership of your site). The data you can get from there is quite comprehensive, but hard to break down for use in Excel. As a fun exercise I put together a small Python-script that takes the CSV file downloaded from your webmaster tools account and turns it into new CSV files for queries and for clicks (both with the position numbers as well).

Python is a neat little programming language, I like it more and more as I use it .

Here’s how to get started:

1. If you do not have Python installed, go and download and install Python. I assume most Apple OSX will have it installed, but I don’t have a Mac so I can’t say for sure. It’ll almost certainly be installed if you’re one of the 3 Linux-users who have visited my blog. If you’re using Windows, take the version with the installer (it’s easier) and make sure that the folder where you installed Python is in your “path”.
2. Grab my wmtextract01.zip and extract it into a folder. You should have three files: wmtextract.py (the Python script) and ProcessAll.bat + ProcessAll.py.
3. Copy your query stats CSV files into that folder as well.
4. Double-click on ProcessAll.py (or ProcessAll.bat if you’re on Windows and don’t have Python set up to run scripts directly)
5. The script will now process all CSV files in the same folder, create a new folder called “output” and place the new CSV files there.
6. Open the new CSV files in Excel (or Open Office or even Google Docs + Spreadsheets)
7. Enjoy

Here are some more ideas for the CSV files when you have them in Excel:

• Select everything (Ctrl-A) and set up an “AutoFilter” (menu item Data / Filter / AutoFilter). Now you can filter your stats however you want them. Want to only see the queries for which you rank #1? How about the queries that people from Switzerland used to find your site?

• Set the Location to “All locations” and search type to “All searches”, now select everything and sort by the last column (menu Data / Sort / has header row / sort by Column E, ascending). Now select the last two columns (D and E) and click on the chart icon. Choose an “XY scatter” chart and let it create it. This chart shows you the ranking of your site for the search queries. There are some problems with the chart like this (keywords can be listed several times), but I think it’s neat anyway

What’s the neatest information you ever found in your webmaster tools query stats?