Free MS Excel template for analyzing queries
Updated December 19, 2006 (originally posted 12/12/06; added summary spreadsheet, much more functionality)
If you're lucky, you're using Mondosoft, WebSideStory, or some other commercial application to generate reports from your query data. If not, we've developed a pair of simple and free Excel templates (20Kb) that you can use to help you come to grips with your queries.
You don't need to be an Excel guru to use these templates. And you'll find they work well with the simple PERL parsing script we've previously made available; take the output and paste it into our spreadsheet. Make a few minor tweaks (instructions are included), and you'll get something that looks like this:
If you have the data available, you could add columns for # of results, # of results clicked through, whether or not a best bet is associated, and so on.
With additional minor changes, you the template will generate a nifty Zipf Distribution to really beat people over the head with the long tail/short head/middle torso message. Here's one which shows just the first 5% of unique queries (and look how long the tail is even with this subset!):
And here's another version of the distribution in logarithmic format:
An additional template is uesful for maintaining running totals of your numbers:
Feel free to add all sorts of customizations. We'd really appreciate it you'd share your own cool innovations and additions. If we use them in the next version of the spreadsheet, we'll credit you here and in the book.
And of course, please comment below if you have any questions or suggestions for how we can make this tool better.