Analytics Roundup: infographics and visualizations

Visualization for the Masses: Information Graphics and the New York Times
He explained how a 30-person team creates the impressive infographics and visualizations we see on the newspaper every week.

information r/evolution movie
This video explores the changes in the way we find, store, create, critique & share information, a nice video illustration of some of Shirkey’s essays.

demographics by ZIP Code - ZIPskinny
Colorful visualization comparing demographic attributes of zip codes.

Mapping Phone Data in Four Easy Steps

Have you run into this problem: you have a list of phone numbers and associated values which would be best shown geographically to see patterns, but there isn’t a clear way to put the data on a map. Maybe you’d like to see a map of customer service calls by call duration or inbound sales by average order size.

I wanted to share how to MacGyver a solution with a piece of twine, bubble gum, Excel, and a free online map tool. To me, this is a nice testament to the simple but powerful data visualizations that can be accomplished without programming skills or expensive applications.

1. Pull out area codes

First I pulled the area codes from my list of phone numbers using the formula below. This simply checks if the phone number starts with 1, then grabs the appropriate three digits for the area code.



2. Convert area codes into states

For my purposes, mapping the phone numbers by state was sufficient. Ideally, we would map the phone numbers to precise latitude and longitude coordinates by doing a reverse lookup of addresses then using the Excel geocoding tool.

First I needed a lookup table that could link my list of area codes to states. I wasn’t able to track down a good data table, so I grabbed the data from All Area Codes and cleaned it up. Here is a lookup table of area codes by state.

An aside: I have a pet peeve with people who sell data that feels like it should be publicly available. You’ll run across these businesses when looking for basic information about ZIP codes, MSAs, or area codes. Here is an example of one of these parasitic businesses.

Zip code product

3. Create your summary data set

I used a pivot table to summarize metrics by state.

4. Create colorized map of the US

Our friend Ducky Sherwood has generously put together a online tool called Mapeteria that will generate a colorized overlay of US states. In Ducky’s words: "Want to make a choropleth thematic map (i.e. coloured based on your data) for Canadian provinces, U.S. states, or French départements?" This overlay can be viewed in either Google Maps or Google Earth.

Here’s where it gets a little tricky. You will need to provide Mapeteria with a URL to a properly structured CSV file. Posting a CSV file to a web server isn’t trivial if you aren’t running your own web site. I found one free service called FileDEN that did the job (other suggestions?). Beware all the advertising—and in all likelihood they immediately sold my e-mail address at registration. Nevertheless, you can upload a file here and it will give you a URL which can be used to create your map.

Here’s an example of the results:

State Map

Analytics Roundup: Visualization goodies

The Problem With Presentations
Don’t let presentation software keep you from getting your story across,

Webfoot’s Mapeteria: Map Colouring
Want to make a choropleth thematic map (i.e. coloured based on your data) for Canadian provinces, U.S. states, or French départements?

The Econ 101 Management Method - Joel on Software
Instead of having smart people figure out how to train their frontline customer service workers to serve customers well and profitably, they make up metrics that sound good and let the low wage, high-turnover customer service people come up with their own.

The rise and fall of IT | Perspectives | CNET
Scrap IT? A well-reasoned argument for scrapping the term "IT."

Gallery of Data Visualization

Summize - Summarized product reviews
A nice visualization for showing rankings.

The Extreme Presentation(tm) Blog: Choosing a good chart

GIS for the rest of us… or Another Excel-based Mapping Tool

A client recently asked us if we could help him find the five locations that have the most customers nearby. I dug into the annals of our blog to find two tools that together ("wonder twin powers activate... shape of mini-GIS solution") could solve this problem:

1. Juice geo-coding tool lets you get the precise latitude and longitude for a list of addresses and plot these locations in Google Earth.

2.ZIP code finder lets you enter a US address and returns zip codes within a certain number of miles of that address.

Led by our resident Excel guru David, we combined these features into an Excel tool that lets you answer common location-based questions such as:

  • How many libraries are within 10 miles of Worchester, MA?
  • Which cities have the most libraries within 10 miles? 20 miles?
  • Could I see the library locations on a map, please?

Here’s how it works:

1. Drop your location information into the "Geocode data" tab. We are using the Yahoo geocoding service.

2. Go to the results tab and fill in the table with your selected location addresses and distances. Press "Calculate."

Download the Excel file here: Juice Distance Tool

We had a couple other features in mind, but thought it would be better to get some reader reaction before we loaded it up.

This tool is released under the MIT license.

Hurricane Analysis with Google Earth

Brian Timoney of The Timoney Group just launched a great web site that gives a feel for the types of analysis that can be done with Google Earth. The site, entitled Energy Impact of Rita and Katrina, lays out a multitude of geographic views (available as .kml files in Google Earth) that show the hurricanes’ devastation in the Gulf region. For example, here’s a look at the Gas Production in 2004 vs. 2005:

2004/2005 Gas Production

That’s just the eye candy. Even better, Brian has developed a series of Google Earth tools that let you analyze the actual geographic data. Below is the description of the a tool that lets you choose a buffer distance around a path.

Timoney Buffer Tool

Building the Geotoolchain

We try to create simple tools to visualize our client’s data and flexibly expand them as business demands and information grows. One dimension of customer location that often gets overlooked is geolocation. Microsoft MapPoint and ArcGIS have made the ability to overlay different forms of business data. But they’re relatively expensive, complex and not exactly agile.

The question is how to open up geographic business intelligence to not just a few people in the organization, but to all.

Enter Google Earth. Below are crime rates in Portland, Oregon courtesy of Portland Maps.

The folks at Portland Maps are steps ahead of the rest of the Google Earth community with their visualization of Portland area information. What they’re doing is not just displaying points in a map, but processing those points to show density heatmaps. It’s easy to see how useful this could be to the real estate industry or anyone thinking of moving to the Portland area.

Webservices have made geocoding cheap and accessible. The challenge is to create tools for rapid data access and development. That’s why we’re excited about powerful development frameworks like Django. All the pieces are there; geocode your customer information, process and serve it through Django, and democratize visualization in your organization using Google Earth.

We’re working on a screencast that shows how we remixed these tools into a toolchain that provides real geographic insight.

Absolutely Google Earth – Complete Tool Collection

Google Earth can be a powerful tool for geographic business analysis. Here are some resources. We’re going to keep updating this page as new tools roll in.


KML Generation/Translation

  • GPS Utility by GPS Utility, Easy-to-use application that manages and manipulates GPS data. Now with Google Earth support.
  • KML Home Companion for ArcGIS posted 9/14/2005 by Jim Cser, Converts ArcGIS ( into Google Earth KML files.
  • Arc2Earth by Brian Flood, ArcGIS to Google Earth converter with emphasis on converting much of the rich map annotations from ArcGIS to KML
  • Microsoft Flight Simulator:Google Earth Translation keeps Google Earth view in sync with Flight Simulator

News & Information


  • Portland Maps — Excellent example of the analytical possibilities of Google Earth
  • EarthPlot — a data analysis and visualization tool that processes CSV files into KML overlays


Geomapping: the biggest little details

We’ve been using Google Earth to create simple geo-mapping solutions for our clients. The tool is eye-poppingly beautiful. Best of all, its easy to create XML files containing client data that can be overlaid on the maps. As long as you’re using a fast, Windows PC, this is a robust solution for agile geomapping. We’ve been able to get 10,000 prospects graphed with no performance problems.

Best of all, when you zoom all the way out, the Google/Keyhole people have included actual stars in the sky. Here’s a little blue ball posing beside the big dipper.