2009 has been a year of sharing here at Juice. First there was our long-used DTP methodology for interactive Excel reporting. Then we released our JuiceKit SDK. Today, I want to share another bit of trickery we've used to solve a common PowerPoint presentation problem.

The challenge

Let's say you need to produce the same presentation month after month, updating the data each time. Or maybe you have a set of slides that need to go to a bunch of different audiences each with their own specific market, product, business line, or industry.

Updating all the slides by hand can be tedious, slow and error-prone. The presentation is basically the same, you simply want to swap out the underlying data. You need something that acts like a "mail merge" for PowerPoint.

Our approach

When we've helped clients with this situation, our approach has been to create re-usable PowerPoint slides (i.e. templates) that link directly to a database. This gives us the ability to stamp out new presentation by changing the raw data underneath. Simple enough to say; not quite so simple in practice. Here are a few of the hairy bits:

1. Data structuring. We populate the data into a Windows-accessible SQL database such as MS Access or SQL Server so we can use SQL queries to define the data needed for our charts and tables.

2. Slide templates. We create slides with charts, tables, and text boxes that are formatted to account for the variance in the data that may need to be displayed. Ensuring that the charts always look good is surprisingly hard.

3. Connect templates to data. Originally we rolled our own solution by creating a "templating" language that we embedded in the notes section of the slides. More recently, we discovered PTReportGen, a tool that extracts data from a data source and populates it into PowerPoint. PTReportGen allows you to connect objects in the slides (i.e. charts, tables, text boxes) to results from SQL queries from our data source. For each slide, there is a .PTR file that connects the contents of the slide to the database.

4. Scripted production. PTReportGen gives command line control, allowing us to write Python scripts to cycle through our data and populate the charts and tables in our template slides. Because we are interested in generating dozens (sometimes hundreds) of versions of a single slide, our script iterates over the database to pull different results across multiple dimensions. Below is a bit of pseudo-code to give a sense of how the scripting works to produce slides by market and by demographic:

markets = ('Market1','Market2','Market3')
demographics = ('Demo1','Demo2','Demo3')
PTRFileName = 'C:\Documents\UserName\Desktop\MyReportGenerator.ptr'

for demo in demographics:
    for market in markets:
        ReportFileName = 'PathName\FolderName\demo\market.ppt'
        cmd = 'PPTReport.exe PTRFileName -demo -market'

1. Post-processing. While most chart and data table designs can be achieved by clever template layouts, some advanced designs involve additional intervention to achieve the desired level of polish. A python script combs through the result template and adds coloration and layout improvements.

It isn't simple, but once constructed this "slide factory" is a valuable capability that can free up an enormous amount of time from presentation grunt work. Here's a short video that gives you a sense of what the process looks like. Personally, I find the production of slides vaguely hypnotic.

Other approaches and resources

We are not the first people to encounter or solve this problem. Below are a few other resources on the topic. I'd be curious if there is a native MS Office solution that I could include in this list.

• PowerPoint Automation Toolkit: "With the PPTATK, PowerPoint becomes a best-case union of a presentation tool and a report writer. With the Tookit, you can build presentations which combine static slides from a slide library and data-driven slides which display charts, tables, and graphs from structured data sources."

• PresentationPoint: "Generate new up-to-date multimedia reports with 1 click only - put real-time data in your presentations."

• Microsoft Help: "Working with PowerPoint Presentations from Access Using Automation. Create a PowerPoint slide presentation from scratch using Access data."

• Stack Overflow discussion on "PowerPoint Automation from MS Access…queries to chart?"

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.

The following is an excerpt from our three-part series: "A Guide to Creating Dashboards People Love to Use". It is chock full of best practices and practical tips for designing dashboards. This particular nugget is something we've used to great effect and wanted to make sure our readers didn't miss out simply because they were afraid of ending up on our mailing list. There is even a movie version.

We’d like to offer a simple framework for effective use of fonts in your dashboard. With a few simple decisions, you can ensure that the text on the dashboard will both look good and communicate effectively. The majority of text on the page falls into four categories:

• Body text is clean, readable content
• Headers separate and name major sections of your work
• Notes describe additional things the reader should be aware of. These should fade into the background unless we call attention to them.
• Emphasis text is what we want our reader to pay particular attention to.

The following table describes an approach for deciding how to display each of these text types. The yellow highlights indicate where you need to make decisions.

It comes down to three basic decisions:

• Choose size and font of the body text
• Decide if the header is going to flip to serif or sans-serif—and whether it is going to have any style
• Decide what to do about emphasis—color or (bold or italic)

A few things things don't fit neatly into one of the four text categories listed above, such as table headers and graph titles. We tend to use a combination of styles to handle these exceptions.

Stick to this framework and we guarantee your dashboard will look better. Take a look at this example, starting with a standard-looking Excel report without out much thought put toward the fonts: