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...

This is a follow up to "Target Long Tail Searches with Keyword Patterns"

To get a sense of the scale of the long tail in search, Dustin Woodard recently put together an analysis of U.S. search data collected by Hitwise over a 3 month period, during which they measured 14 million different search terms. How did these break down?

• Top 100 terms: 5.7% of the all search traffic
• Top 500 terms: 8.9% of the all search traffic
• Top 1,000 terms: 10.6% of the all search traffic
• Top 10,000 terms: 18.5% of the all search traffic

This means if you had a monopoly over the top 1,000 search terms across all search engines (which is impossible), you’d still be missing out on 89.4% of all search traffic. There’s so much traffic in the tail it is hard to even comprehend. To illustrate, if search were represented by a tiny lizard with a one-inch head, the tail of that lizard would stretch for 221 miles.

Yesterday, we described the concept of search patterns and how you can use them to summarize this type of long tail text data. Today, we will walk through a case study we put together to explain how Concentrate's pattern discovery feature will help you find new competitive insights.

You can replicate this study yourself by signing up for the Plus version of Concentrate and loading competitive search data from providers like Hitwise, Compete, Keyword Discovery, or comScore. The input search data used in our analysis consisted of a sample of unique queries leading to clicks on top travel domains during Spring 2006, along with their frequency of occurrence (the chart is truncated after the 20th query):

Raw search data: most frequent queries by site

We loaded the full dataset of queries into Concentrate to generate summary patterns for each of 5 top travel sites. After each file of unique queries and associated metrics is loaded, the application generates reports which include summary statistics based on the head (top 50) and tail queries for each site. This is a good way to start looking at the data if we want to get a sense of each site's long tail search strategy:

Head vs. tail queries for top travel sites

It appears that the long tail makes up the overwhelming majority of traffic for the travel planning and review sites, but is a much smaller percentage for transaction focused sites like Expedia and Travelocity. Measuring the size of the head and tail gives us a rough idea what is going on, but we need to dig deeper if we want to benchmark where we stand in various categories and produce actionable insights. Inspired by a recent New York Times infographic "Words They Used", our data visualization guru, Chris Gemignani, downloaded the Pattern CSV file that Concentrate generated for each of these sites and created the following view of competition in the travel search sector:

Comparing travel searches by pattern

This chart compares the proportion of searches that go to each travel site for the top 25 patterns in the travel sector. The site getting the most traffic for each pattern is highlighted. Only searches that wound up at one of these five travel sites are considered.

The difference in search pattern profiles for these sites is striking. Tripadvisor leads the pack in the long tail, which makes sense given the huge amount of long tail user generated content on the site. TripAdvisor owns most of the pattern categories, but Yahoo Travel and Hotel-Guides take the lead in niche areas like maps and hotels. Traffic to Expedia and Travelocity is largely composed of navigational and branded queries (not shown). The only long tail patterns they have significant share for are "[x] ticket", and "cheap [x]".

The input data we used reflects referrals to these sites from a sample population of users who clicked on search engine result pages. Factors which will affect the number and type of search referrals a site received in this data include: how representative the sample is of the population of U.S. searchers as a whole, how much relevant content a site has for a given query pattern, and how well that content ranks in google and other search engines.

If a travel website repeated this study with Concentrate using current competitive data, then uploaded additional search data for their own site including other metrics beyond search frequency (see our demo using Google Analytics), the results might reveal that "things to do in [x]" queries lead to high quality visits and their site has a chance at winning more searches for that pattern. Based on this information they might decide to make a move on TripAdvisor in that content category. Mark Jackson describes some strategies to apply within the travel sector in an article at Search Engine Watch: Should Your SEO Strategy Target the Head or the Long Tail?. Using Concentrate, a travel website could streamline the process by downloading thousands of real queries for this pattern sent to their competitor:

Some queries in TripAdvisor pattern: "things to do in [x]"

Take Action: Some ideas for next steps

• Use Concentrate patterns to segment your sites query referrals, then estimate revenue opportunities for underperforming long tail categories and target your efforts accordingly
• Expand into new content areas based on trends discovered in long tail search patterns over time and ensure your site's information supply (PDF) matches customer demand
• Get a subscription to Concentrate Plus and repeat this case study for your industry
• Use your industry search patterns as a guide to optimize internal website architecture
• Jumpstart your SEM efforts by using Concentrate to automatically create tight adgroups containing similar queries found within long tail each pattern
• Use your patterns as templates for dynamic filters within your website to show targeted ads, content, related links, or content recommendations based on query referral strings
• If you are a financial analyst or product planner, spot emerging trends in the long tail with Concentrate and use the information to guide decision making

On Friday, we launched our new search analytics product: Concentrate. One of its key features is a scalable algorithm that automatically discovers patterns in large amounts of search data and clusters long tail queries into manageable groups. This post will explain how using Concentrate's pattern discovery feature can simplify search data analysis and give you an edge on the competition.

To explain how valuable Concentrate's pattern discovery can be, we put together a case study of the travel sector using the Plus version of Concentrate and the type of competitive search data available from commercial providers like Hitwise or Compete. We will go into the details tomorrow, but here is a sneak peek at the results. This chart shows the share of travel searches by site in Spring 2006 and was generated using reports downloaded from Concentrate pattern discovery:

Travel Sector Searches: Comparing sites by pattern share

The Long Tail of Search

Search analytics starts by looking at the most frequent search queries driving traffic to your site or that of your competitors (these are often called the "head queries"). For most sites, these queries are a fraction of your total search traffic and just the tip of the iceberg in terms of insight about your audience. Queries like "cheap hotels in liverpool ny" may only occur once or twice in a given month, but when aggregated with other rare phrases can make up the bulk of your traffic.

The concept of the long tail in business intelligence has been a topic of debate over the last few years. One area where the long tail is alive and well is in search. The landscape of user search queries is dominated by the long tail, and most studies indicate that referrals from these long tail phrases are more likely to lead to purchases on your site. Natural search isn't the only area where the long tail turns out to be critical. Paid search efforts which ignore the long tail are potentially missing out on a large chunk of revenue. The challenge of the long tail is that dealing with massive amounts of query data quickly becomes unmanageable.

Traditional Search Reports: head queries for some top travel sites

If you have hundreds of pages of unique queries to sort through manually, forming a actionable view of that data is a painful process. This is why most people only look at the first few pages of queries.

Categorizing Queries using Patterns

Finding frequent search patterns is the key to making search data understandable. Patterns let you to treat groups of long tail searches like popular individual queries.

Our concept of patterns is similar to an example described by Brian Brown in a recent SEOMoz post. Patterns are templates for searches that have a similar structure. For instance, the pattern “jobs in [x]” represents searches for jobs in some location. The “[x]” is a wildcard that can stand for one or more words. These “masked terms” are often variants of a similar concepts, like locations or celebrity names. Depending on the nature of your site, up to 80% of your long tail search traffic could be summarized using just the top 20 query patterns.

Concentrate Pattern Summary View for TripAdvisor.com

The next iteration of Concentrate’s learning algorithms will replace many of these wildcards with named entity labels. For example: “hotels in [x]” will become “hotels in [City]”. See our FAQ for more details on special pattern categories like navigational queries. Tomorrow, we'll cover the travel case study in detail.

We are pleased thrilled to introduce Concentrate™, an innovative long-tail search analytics tool. Concentrate is for SEO and paid search professionals who want to make sense of search keyword data and make the most of search investments.

Check out the demo here. Or try out the free version here (you’ll need admin access to a Google Analytics account).

We built Concentrate because we saw a fundamental conflict in the world of search analysis: On the one hand, search keyword data is terrifically interesting and valuable. It can tell you what your visitors and customers want and how they think about you and your products.

Unfortunately, search query data is also big, messy, and hard to get your hands around. In a typical month, the Juice site gets over 10,000 visits from over 7,000 unique keywords.

Even if I could somehow wrap my head around our top 100 keywords, I’d only understand 25% of the visits. For people spending money on search engine optimization or paid search campaigns, that’s a big blind-spot to accept.

We want you to understand and act on all your search data. Concentrate ingests data from sources that most sites already have available (e.g Google Analytics, Omniture, Coremetrics, Hitwise, Compete, etc.), enhances this data by finding common patterns and query types, and visualizes search phrases for exploration and analysis.

Over the next couple of weeks, we will share examples of some of the interesting things you can do with Concentrate, including:

Pattern identification to condense the long tail into keyword phrases with similar structures. For example, here are some common search patterns from a cooking web site (the “[x]” represents a wildcard).

Keyword visualization to show the connections between keywords and the relative performance of phrases. This wordtree shows the frequency of words within phrases (size) and average time spent on site (color).

Congratulations to Chris, Pete, and Sal for all their hard work, diligence, and creative problem solving to launch this solution.

Presentation guru Andrew Abela recently published his first book Advanced Presentations by Design. Abela shares his 10-step technique for developing influential business presentations. Before reading this book, I thought I had a pretty good idea how to make a compelling presentation; it turns out I mostly knew how to throw together a bunch of non-boring slides. There are a few key themes that summarize the book for me:

1. Focus on your audience.

“Your presentation should be all about serving your audience. You need to show them that you see everything from their perspective — their problem, in their terms, their motivation and issues. This also means that you have to be bound by their constraints. There is no point in raising an important problem and proposing new investments to solve it if your audience just does not have any money to spend this year.” (p55)

2. Solve a problem.

“Focus your entire presentation deliberately and undividedly on solving an important problem of theirs (the audience)” (p6)

“Your objectives should be about how your audience will change as a result of your presentation: how they will think and act differently after they leave the room.” (p5)

3. Tell a story.

“An effective way to reframe your evidence and involve your audience is to present your information in the form of a story…Stories are a coherent whole, where one thing flows to the next, so we tend to remember the whole thing.” (p65)

“By presenting your information in the form of a story, by setting up a tension and resolving it, and repeating as necessary, you can create this physical desire in your audience for your message.” (p77)

If you make presentations for a living or just as a hobby, I can wholeheartedly recommend this book. Abela does an impressive job of teaching his process and keeping it interesting. My one point of concern is that I felt he didn’t offer much help with the critical transformation from story outline (he recommends you shouldn’t open up PowerPoint until you are most of the way through the process) to presentation slides.

I also enjoyed this book because it connects to, and expands upon, the messages we emphasize in our design of Information Experiences for reporting, dashboards, and analytical tools. (Even the introduction gives us a nod: “I’ve become convinced of how crucial the last mile of communication is to driving organizational impact.”) Here is a short checklist of considerations articulated by Abela that bridge any communication of complex information:

• When presenting data, pay particular particular attention to what is new or different.
• Drive action. Or in Abela’s words: “What does it allow them to start doing, stop doing, or continue doing that would be difficult or impossible without this information.” (p47)
• Respecting the challenges faced by users. Understand what problems and levers the audience has available to them.
• Consider your audience “type”. How does the audience best absorb information?
• Consider the presentation environment. In what context will the audience be engaging with the information?
• Use different types of data (e.g. statistical, anecdotal). Sometimes specific data points can help focus attention better than an aggregate metric.
• Identify problems, then give people the tools to address the problem. This parallels Abela’s storytelling technique of creating and resolving tension.
• Users before technology. Usability before features. Abela notes: “Presentation and advice and tools have been developed for the benefit of the presenter, not the audience.” (p5)

I have become curiously interested in this post that talks about how it's difficult to correctly write an application for the iPhone. The assertion is that writing software for the iPhone is harder than for a desktop, not because of the technology, but because:

"everything counts so much — every design choice, every line of code, everything left in and everything left out."

Very eloquently and precisely put. If you've ever used any sort of mobile computing platform, not just the iPhone, you know how much proper design can make an application really useful - or totally useless.

But then again, isn't this the case with any application? Aren't the best ones those in which the designer applied Brent's assertion for iPhone software? Some applications seem to have their genesis in the charter "build an application that allows the user to perform all these actions" while others are built on the charge "build an application that helps the user solve this problem" -- it's the battle of functionality versus purpose.

Take a look at ChartChooser based on Andrew Abela's "smart charting" guidelines. It doesn't help users figure out how to pick a bar chart or pie chart. What it does is to help them answer the "what's the right way to show this information" question. There's not a lot buttons or features, it just does one thing well. There are certainly other good (better?) examples out there as well (FlipVideo, Evernote, Tivo, to name a few). The better the software, the less the user will think about it when using it to get their job done.

In line with this thinking, we put together a short list of some design principles that we use to keep the user productive:

• Solve a problem - Make sure the end product provides a specific solution to a specific problem so the user can easily understand how it helps them.

• Enable casual use - Minimize the "barrier to entry" for new users by avoiding feature overload, minimizing clicks for each task, and by not letting polish become bling.

• Tell a story - Relate the data to the key questions, answering them in a logical order and revealing layers of detail as users express interest in knowing more, not before.

• Lead to action - Empower the user to finish their task quickly (btw, the "task" is not "using software").

• Encourage exploration - Use the experienced guide approach to give the user enough context to understand the problem and then point them in the right direction to learn about new factors that will expand their insight.