At Juice, we work with web analytics APIs large and small, from Google, comScore and Omniture. The Google Analytics API is our favorite. It powers the world's best, most widely deployed analytics site. And it powers Juice products like Concentrate (innovative search analytics) and Vasco de Gapi (a tool for exploring the Google Analytics API).

We were approached by the Google Analytics API team to find ways to explore new ways of looking at data with the API, and we were excited by the possibilities. We've been working on our own visualization framework, JuiceKit, that integrates the power of the Flare Visualization Library with Adobe Flex.

The result is Analytics Visualizations, two visualizations powered by the Google Analytics API that are free to use. You just need a Google account with access to Google Analytics data to explore your own data.

Referrer Flow

Curious about what sites are linking to you and what content is benefitting the most? Referrer Flow answers those question and shows how results change over time. Here is a brief video introduction:

Referrer Flow is a stream of daily treemaps showing pageviews and bounce rates for various groupings of your website's pages. You can group by combinations of page title, referrer and url. Clicking on the treemap will filter all the data by the page, referrer or url that you clicked on. Click again to clear your filter.

Keyword Tree

A list of top keywords isn't enough to really understand how people are searching and finding your site. Keyword Tree visually displays the most frequently used search keywords and how they are used together. Here's a video overview:

You'll see a frequently used search term at the center and the words and phrases that are most often used in combination with that word. Pick a different starting word by typing into the box in the upper right or selecting from the top word across the bottom of the screen. The words are sized by their frequency of use and colored by bounce rate (or % new visitors or average time on site). Roll over a word to see details about that combination of connected words.

Depth and Discovery

In designing these visualizations we focused on the question: how can we let users uncover the unexpected? That means designing targeted visualizations focused on limited well-defined issues. The Referrer Flow monomaniacally focuses on a single question "What pages are people viewing on your site and where are they coming from?" The Keyword Tree is laser-focused on word ordering and what that means for keyword performance.

The Google Analytics reporting tool is a great general-purpose reporting solution. It gives the advanced users everything they need to answer specific questions. However, its generality means it has limited ability to focus on two issues; depth and discovery.

The Google Analytics API is Google's solution to this problem. It's an opportunity both for businesses like ours that can create new ways of analyzing data, and for large sites that can use the API for integration, custom analytics, and more.

Thanks to Nick Mihailovski at Google for his gracious support, help and encouragement and Avinash Kaushik for inspiring this idea.

Here at Juice we build fewer Excel dashboards than we used to. Excel itself is a decidedly imperfect vessel for any serious development--it's simply too easy to veer off of the disciplined track onto the underbrush.

Even so, Excel remains a playground where we can do surprising things. For instance, check out our Excel lightbox and an Excel tagcloud. We could appropriate everything that you find on the webbiest of Web 2.0 websites and build our Uruk-hai equivalents.

The key to staying on the rails when building Excel tools--either dynamic dashboards or simply to explore data--is discipline. At Juice, we use a methodology that we call "DTP" (Data Tansform Present). The foundation of DTP is the rigorous separation of data from presentation. This is similar to a well-known approach when building computer user interfaces called Model-View-Controller. I'm going to cover some of the key principles and we'll follow up with an example later on the blog.

Data

Data is the raw material of any visualization or report. It needs to be easy to add data or change data without having to change anything else about your dashboard.

We store raw data with dimensions preceding metrics in blocks in separate worksheets. If you want to sound pretentious, you can call this "first PivotTable normal form". Key points:

• Have one worksheet for each data source.
• Call these sheets "Data", or "{Title} Data".
• Place them at the end of your workbook.
• Data is snug to the top left of the spreadsheet. This allows us to use dynamic ranges. Dynamic ranges let you add data and have it automatically incorporated in all PivotTables.
• Ensure that column names are in the first row.
• Place your dimensions before metrics.

Transform

We use PivotTables to transform the data into the structure we need.

• Call these sheets "Transform" or "XXXXXXX Transform".
• Create one sheet for each issue that you are exploring. This doesn't mean that you will only create one PivotTable. You may have multiple PivotTables to support different views or perspectives on an issue.
• Turn on "show items with no data" for row and column dimensions.
• We are seeking predictability, we want to the PivotTable to always be the same size regardless of what the PageField filters are.
• Place all the dimensions that aren't used as rows or columns in the PivotTable as page fields. Every dimension should have a home.
  • Set all PivotTables to not store data and refresh on open.

Present

The Presentation page copies data from the Transform page(s) and formats it for display. It also allows users to control what data is being displayed.

• Build a user interface to interact with your data. There are many ways to let people interact with your data, but one of the easiest is to use a PivotTable as your interface. This is described below.
• We use an in-house style guide for graphs that you can see in our Chart Chooser.
• If the Presentation page is likely to be printed, preset the print range.
• When copying data from the transformation page to the presentation page, blank values will come out as zeros. We use a simple formula, =if('Transform!A2'<>"",'Transform!A2', ""), to ensure that blanks remain blanks.

Using a PivotTable as your interface

A simple way to let people manipulate your data is place a PivotTable containing only PageFields but no data on the presentation sheet. A Visual Basic macro triggered to run whenever the PivotTable changes then pushes out any changes to the master PivotTable to all the PivotTables on your Transform sheet.

Here is the code to make this happen.

This drives our PivotTables in concert and ensures they stay in sync.

That's a basic overview of our DTP technique. You can try a simplified version of DTP here.

DTP Example.xls

We'll be back soon to talk through this example.

Recently we wanted to show how Concentrate, our new long-tail search analytics tool, could give you a view of search patterns across travel websites. As political junkies, we were inspired by this chart from our friends at the NY Times.

The first tool we tried, simply on principle, was Excel 2003. As expected, making a NY Times quality bubble chart in Excel 2003 is a hard problem. Here's a draft of how far I got before giving in to label fatigue.

The bubbles themselves aren't tough, but getting the labels right is hard. I'd love to see a solution, so if any reader wants to tackle it eternal fame can be yours. Here is a CSV if you want to try.

travelpatterns.csv

Another of the tools we use at Juice is NodeBox, which we used to make this:

Here's the code that made the graph.

The power of a programmatic approach like this is that by changing a line or two, you can get the following. Click for a larger version. Click the text for the code..

Vary formatting by value

Drop the labels, tighten the spacing

Display the largest item in each row as a red square

With great power comes a great need to exercise restraint. Otherwise you end up like these poor chaps. Must... flex... restraint... muscles...

Here's a bash function I use for Django development to quickly set DJANGO_SETTINGS_MODULE.

function setdsm() { 
    # add the current directory and the parent directory to PYTHONPATH
    # sets DJANGO_SETTINGS_MODULE
    export PYTHONPATH=$PYTHONPATH:$PWD/..
    export PYTHONPATH=$PYTHONPATH:$PWD
    if [ -z "$1" ]; then 
        x=${PWD/\/[^\/]*\/}               
        export DJANGO_SETTINGS_MODULE=$x.settings
    else    
        export DJANGO_SETTINGS_MODULE=$1 
    fi

    echo "DJANGO_SETTINGS_MODULE set to $DJANGO_SETTINGS_MODULE"
}

I put this in my .bash_profile, then a quick setdsm sets the DJANGO_SETTINGS_MODULE to the settings.py in the current directory and add the current directory and it's parent to PYTHONPATH.