How to use Google webmaster tools stats with Excel

Posted on 29 November 2007 at 0:34 UTC, filed under Tricks, disclaimer

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?

excel-webmaster-tools.gif

  • 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 :)

webmaster-ranking.gif

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

There are 20 comments to this post.
  1. Hi John,

    Tried to download the zip file here : http://johnmu.com/files/wmtextract.zip , but I got a “Error 404 – Not Found” page :(

  2. Sorry about that, I just fixed the link – it should go to http://johnmu.com/files/wmtextract01.zip instead. Thanks!

  3. Let me know if you guys are interested in a simple Windows EXE file instead of the Python script.

  4. Richard Hearne (29 November 2007 at 2:15 pm):

    Sign me up for EXE.

    Now if we could cross pregnate this with the log file tool… ;-)

    BTW – how/why was my captcha pre-populated?

  5. How do you mean that, Richard? Display the rankings in the log file tool? I’m not sure how useful that would be since the log file tool will probably show more phrases…

    The captcha is pre-populated via javascript :) . I noticed it keeps the bots out just as well.

  6. Richard Hearne (29 November 2007 at 3:07 pm):

    couldn’t see the script for JS…

    remember the small log reading app you built that spat out reports of what buttons people used on Google etc. I know this wouldn’t relate to exporting sitemaps data, but I always found that to be quite a cute little app. Actually this makes no sense even to my deranged mind – ignore :)

  7. Here’s the normal Windows executable version: http://johnmu.com/files/WmtExtractW01.zip
    This executable opens a window, lets you choose the stats-file you downloaded and then generates the clicks and queries files separately.

    It needs the Microsoft .NET Framework v2.0, which you probably already have installed, but just in case, you can get it directly from Microsoft at http://www.microsoft.com/downloads/details.aspx?familyid=0856eacb-4362-4b0d-8edd-aab15c5e04f5

    Let me know if you run into any problems!

  8. John, that’s an awesome piece of script. I’m trying to bring in URL matching to bounce rates from G Analytics for some super targeted page tweaking actions.

  9. Awesome, thanks soo much for this utility!

  10. How can I add Month ranges in the excel? So, basically I want it to sort it by month. Also, I want it to sort keyword position by Top 10.

    Thanks
    Ankit

  11. Online Store (8 August 2008 at 9:14 am):

    Nice utility tool for google webmaster your tool is really easy to use and usefull.

    Jiten

  12. Who’d name their kid Online Store?

  13. Wow, massively useful. Many thanks, John. I just tried the Windows EXE, and it works like a charm. Cheers!

  14. This is one great little piece of code! I’ve been thinking about writing an excel script to do some of this but didn’t have the time or the expertise to do it properly. Thanks SO much for taking the time and adding a very useful tool.

  15. Hi John

    Great tool many thanks. When you’re donwloading the data from GWT, how do you know which period is covered? And also do you have any ways of extracting the % that tells you the request weigth amongst all requests? (I believe you get this data in the excel file if you click “download this data set” as opposed to cliking “download all data for this site”)

    Thanks again!

    Franck

  16. Hi John,

    great tool, but wondered how you get the keywords listed separately as opposed to keyword, percentage, position keyword, percentage, position in the same cell?

    That’s my real issue with the download of the information.

    Thanks,
    Sarah

  17. Hi John,

    Great Script!!! Have you noticed Google’s new webmaster interface and subsequent changes to the download of Top Search Query data? They’ve changed whats downloaded and now your script no-longer works :(

    Do you have any plans to update your script for the new data set from Google?

    regards,
    Christian

  18. Hi John,

    I realise this is an old post but I love this tool! There is a problem however… I’m not 100% sure if I’m correct here but it seems Google have changed the format of the webmaster tools downloads with the new look WMT. Your WMT extractor doesn’t functioon like it used too (for me anyway). Do you see this? and if you do could you possibly update this tool as I have found it very usefull.

    Thanks for all the tips,

    Jim

  19. Seriously, great script. I started playing with the Python version and then found the exe :)

    You should setup a new category called tools. Keep posting!

  20. Sorry but in my output folder is only blank CSV file? BTW, thank for yout trying!

Feel free to leave a reply to this posting.

Warning! Your comment will be lost if you mistype the spam-test or forget to enter your name or e-mail-address. Copy your comment to the clipboard to be sure.

You may use these tags within your reply: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>