• http://www.JoeRega.com/ Joseph Rega

    This was extremely helpful, thank you Annie!

  • http://twitter.com/Anthony_Mac85 Anthony Mcloughlin

    Hi Annie – This looks like an amazing way to manage and analyze big sets of data. However I am a little confused. How do you get the keyword difficulty tool report mailed to you in excel format?

  • http://twitter.com/asholstein Andrew Holstein

    Can’t even describe how awesome pivot tables are. Now I look for excuses to use them, even if I don’t really need them.

  • http://twitter.com/AnnieCushing Annie Cushing

    You’ll get an email saying that it’s ready if you’re an SEOmoz member. And, unlike most tools, you actually can get a fully formatted report. But I still get the csv and format it myself. 

  • http://twitter.com/AnnieCushing Annie Cushing

    You bet! :)

  • http://twitter.com/AnnieCushing Annie Cushing

    They’re more addicting than pistachios. :)

  • Lyndsy Simon

    I have to disagree with your recommendation to replace blanks with zeroes – a blank is actually “null”, and nulls are different than zeroes.

    Imagine you have a column with SERP rank for Google for a list of keywords. That column is populated by a script that goes and and looks at the first 100 results of a Google search for that keyword. If the page under analysis isn’t found, then the field contains a null value; otherwise, it gets the numeric rank.

    Now let’s pretend there are 3 rows. The SERP positions are 2, 4, and “not in top 100″, which is represented by a null. If you take the average of those three values, you’ll see your average rank is 3: (2+4)/2.

    If instead, your column contained 2, 4, and 0, the average rank would be 2: (2+4+0)/3.

    This is only one of the problem you run into, but underlines the point - it’s never okay to simply replace nulls with zeroes, as they are very different animals.

  • http://twitter.com/mitchmonsen Mitch Monsen

    Great work here, madam Annie. :)

    The day I discovered pivot tables is second only to the day I discovered importxml in my analytic life.

  • http://twitter.com/AnnieCushing Annie Cushing

    I would disagree that it’s “never” okay to replace nulls w/ 0′s. You overstate your point. That said, not all data sets are friendly to pivot tables. But in the example you state, you could easily replace the nulls w/ a value greater than 100 and then filter those rows out or just note that any value greater than 100 = null. Problem solved.

  • http://twitter.com/AnnieCushing Annie Cushing

    Right??? I’d include the day I learned how to use vlookups to that short list. :)

  • http://www.bigbags.tumblr.com Bags

     Mmmmm. Pistachios.

  • http://pulse.yahoo.com/_J6AB7B7E2MLHHWGL4SWKY5UEFM bizhacks.com

    Very nice article, very useful information and file, thank you!

    Go Yahoo!

  • http://watchthedarkknightrisex.wordpress.com/ Ravi Shankar

    Superlative stuff… Thanks for such a great Post. 

  • http://twitter.com/Anthony_Mac85 Anthony Mcloughlin

    Ahh – I’ve cracked it. You have to search for the keyword phrase first and then once the data loads showing you the top ten, you have to click on “Run full report”. I’ll look forward to having a play with the pivot tables tomorrow :) 

  • http://twitter.com/malcolm_gibb Malcolm Gibb

    Great post! I’ve always struggled building pivot tables but know how valuable they are in visualising different data sets. This post really helps!

  • Rajesh Magar

    Oh God that was nice and lengthy!

    yeah that’s the same situation (In picture ) I come to when it’s getting overflow with all such data. But post is so much helpful. Thanks Annie.

  • http://twitter.com/BarryLoughran Barry Loughran

    One new great feature is the ‘slicer’ function.  It’s unbelievable for creating dynamic charts and graphs.  I’ve set up some amazing charts that can be sliced by so many metrics.  It really did blow my mind when I found it out, as did grouping too :)

  • http://www.liquid-silver-marketing.co.uk/ Farky Rafiq

    I’ve been looking for this post everywhere, fortunately I
    posted it to my g+! (its like finding a bar of chocolate that I saved for later…
    Sweet!)

  • http://trafficdigital.com/ Mark Hughes

    Unfortunately I’m using Excel 2004 on a Mac and don’t get the same options in the pivot table –  I end up with this: http://imgur.com/qz7V9

    Looks like I’ll be going through the typically cumbersome Microsoft training sessions instead… disappointed!

  • http://twitter.com/AnnieCushing Annie Cushing

    Glad to help, Malcolm. :)

  • http://twitter.com/AnnieCushing Annie Cushing

    Ha! I know … I write monster posts. Glad it helped though. :)

  • http://twitter.com/AnnieCushing Annie Cushing

    Oh, I can hardly wait to cover pivot charts and slicers!

  • http://twitter.com/AnnieCushing Annie Cushing

    Yay, data = chocolate! Love it. :)

  • http://twitter.com/AnnieCushing Annie Cushing

    Yeah, just covering Mac and PC is very time-consuming. Going back two versions of each in a single post would be impossible. But I’ve been one of Microsoft’s critics of its subpar Mac version. Even 2011 is way inferior to 2010. You still can’t even create pivot charts in 2011, though PCs have had that functionality since 2000.

  • http://www.rachel-levy.com Rachel Levy

    Great post!  Are you able to share your “audit checklist” you referenced above? I’d love to see it!