eMetrics: Beyond Reporting: Using Excel and other everyday tools to explore data by Chris Gemignani, Juice Analytics
Chris Gemignani of Juice Analytics wrapped up my tour of eMetrics with some great, in the dirt tactics to "juice" your use of Excel in web analysis. Chris focused his presentation on ways to maximize each aspect of the analytics triangle - reporting, exploration and presentation.
Can You Repeat (Function) That?
If you're a fan of the "over/under" charts in Google Analytics, you can mimic the visualization with the Repeat function. The syntax goes like this, REPT(characters, # of times), e.g. REPT(“x”,5) -> “XXXXX”
- To make bars = REPT(“|”, F3/10)
- Bar and text, REPT(“|”,5)&TEXT
Enter The Data Once, Chart Forever!
If you have a lot of data that you're trending over time, mashing those line charts together on one graph can be a little cramped. Chris suggested the Offset function to let you scroll through your data one line chart at a time on the chart. Here's the syntax: OFFSET(anchor (this is the row to start at, make this a reference cell), rows form anchor, columns from anchor)
- Right click toolbar – forms menu – choose the scroll bar onto a chart
- Right click on format control, create a cell link to the reference cell, set mins and maxs
Imagine All The Data - Spatially!
Breaking out of the mold of rows and columns can often illuminate interesting trends or unknown areas of opportunity. Juice has created a geocoding tool available on their site that adds latitude and longitude and let’s you view it on Google Earth. For another resource, try Batch Geocoder.
For another way to explore your data in non-traditional forms, try looking at unstructured text with some of the tools over at Many Eyes. You can look at word trees, which will show you all the sentences that follow a particular word or words and let you drill down into the details. You can also input data for your very own treemap (remember when I talked about that regarding Project Gatineau?). It's free, open to the public and accepts tab or comma separated values.