louisrosenfeld.com logotype

Home > Bloug Archive

Dec 19, 2006: I could really use some Excel help

For our new book on search analytics, Rich Wiggins and I are developing a set of Excel templates that anyone is free to use to analyze their site's queries. It can be used in combination with a log parsing script that we've also made available; both are a nice alternative for people who don't have access to commercial analytics applications and their nicely-generated reports. Here are a couple sample screen shots from our current spreadsheet:

sample search analytics report

sample search analytics chart

sample search analytics chart (logarithmic)

sample search analytics summary report

As helpful as these might be, I've already hit my exceedingly low ceiling of Excel expertise. And these templates could be far more useful with perhaps just a few hours of help from an Excel whiz. So: any volunteers? We can offer you lots of acknowledgments (in the book, on our site, in the templates), a copy of the finished book, an opportunity to be altruistic (hey, it is the holiday season), and our undying love. Have a few hours over the holidays that you might want to invest? Please let me know.

With a little help from our friends, here are some of the things that these templates could do:

  • Display a random sample of queries from the long tail and, more importantly, the middle torso (as these subsets of queries are so large, random sampling would go a long way toward improving analysis)
  • Display percentage of all queries that are URLs (I'm halfway there, but can't quite get it to work properly)
  • Display percentage of all queries that are empty
  • Better automate the generation of charts and some formulas
  • Reduce file size for these spreadsheets (if possible)
  • Anything else that might make these templates more useful and easier to use

Again, please let me know if you'd be willing to help out. Many thanks!

email this entry

Comment: Hadley (Dec 20, 2006)

Very untested:

Random sample: fill many cells with =OFFSET(E14,ROUND(RAND()* COUNT(D14:D46),0),0)

Percentage of all queries that are urls = sum(iserror(search(a1:a100), "www")) / count(a1:100) then ctrl + enter for matrix formula

Percentage of empty queries is trickier, and will need information about how far down your data reaches.

What other charts do you want? Is there some reason you're not just extending their ranges to the bottom of the sheet?

I'm happy to help out more, if the above tips are useful.

Comment: Lou (Dec 20, 2006)

Hi Hadley, just tried the first formula; great! Two follow-up questions on it:
1) I'd like to figure out a way to apply this formula to an automatically-specified range of source values. In other words, I'd like to grab 100 random queries from the Long Tail (queries with a count of 1). Is there a way to do this without going in and manually entering the range of items with a count of 1?
2) Can these values be "fixed" once they're randomly grabbed? One grab is sufficient and, actually, better than having them change each time the spreadsheet is opened.

I also can't quite get the second formula to work.

Empty queries would be the number of queries where the cell is either empty or includes only white space. I imagine that the formula would be quite similar to the second formula.

I've had to pre-specify ranges when the "D:D" range format doesn't work. Sometimes it does, sometimes it doesn't; I can't figure out why.

This is fantastically helpful Hadley; many thanks!

Comment: Lou (Dec 20, 2006)

Here's another question (any taker?): The logarithmic charts I'm using come straight from Excel. They only show one axis logarithmically; really, both axes should be logarithmic. That way the line would plot as a straight line. I know there's a "loglog" formula in Excel, but I've no idea how to get a chart to use this. Anyone?

Comment: Rob Fay (Dec 20, 2006)


You might consider touching base with the good folks over at Juice Analytics. The folks there are Excel experts and have a mission to present data in a clean elegant way.

As an example of what they can do, consider this podcast Chris did with Jon Udell back in August.

Tell Zach I sent you ;)

Tell Zach I said Hi!.

Comment: Hadley (Dec 20, 2006)

I think you'll need to use VBA for those other problems - any RAND based solution will select new rows whenever the sheet recalculates. (You probably can figure out the first and last entry with some tricky formula, but it's probably not worth it)

For the second formula, swap the "www" and the cell range, and make sure to use ctrl + enter.

For the log-log plot, make sure you've created a scatterplot (not a line plot) and then you can use a logarithmic scale on the x-axis as well.

Comment: Lou (Dec 21, 2006)

Hadley, got the proper logarithmic chart working thanks to you advice. Will implement it in the next "release".

With random numbers, I suppose we could generate one or more sets of 100, and then just refer to them when needed. That way we'd have random numbers to use, albeit not generated anew each time. Probably a fair trade off. The harder part for me is figuring out how to grab cell values for ranges (e.g., give me cell #s 37, 4002, and 216 from all cells in column E that have a value of 1 for their adjacent cells in column D).

Still can't quite get that search formula to work...

Thanks again!

Comment: Zach (Dec 21, 2006)

We'd be happy to pass on some tips and tricks for doing this sort of thing. We frequently create dynamic reports for clients that just require them to drop the raw data into a data sheet and everything else is set up. One fav:
=OFFSET($A$1,0,0,counta($A$A),counta($1,$1) for your data sheet creates a dynamic range that expands and collapses automatically.

Comment: RTodd (Dec 21, 2006)

I'll have a few hours over the next week or so to contribute. We spend way too much time automating data in Excel and writing VB code for folks. We also focus time on the usability of spreadsheets which can make a big difference in utilization.

Comment: Dr. Mohamed Taher (Dec 26, 2006)

Excellent step forward in Web analytics.
Happy holidays.
This is not spam comment.
I wish to share something with you as a librarian who believes in sharing knowledge 24 X 7. My 2007 resolution is a friendly deal to get more comments at my blog and promptly reciprocate.
Best wishes for 2007.

Comment: Jamie (Dec 29, 2006)


Maybe the people at Better Solutions will be able to help you:


regards Jamie

Add a Comment:



URL (optional, but must include http://)

Required: Name, email, and comment.
Want to mention a linked URL? Include http:// before the address.
Want to include bold or italics? Sorry; just use *asterisks* instead.

DAYENU ); } else { // so comments are closed on this entry... print(<<< I_SAID_DAYENU
Comments are now closed for this entry.

Comment spam has forced me to close comment functionality for older entries. However, if you have something vital to add concerning this entry (or its associated comments), please email your sage insights to me (lou [at] louisrosenfeld dot com). I'll make sure your comments are added to the conversation. Sorry for the inconvenience.