Your data is meant for action.
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.
=VALUE(IF(LEFT(E7,1)=”1″,MID(E7,2,3),MID(E7,1,3)))
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.
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:
We were recently asked to answer some questions about our usage of Google Apps. We’re writing up some business-prosey answers, but poetry is another way of capturing the experience. Here are the questions and our answers in loose haiku.
1) Where are you located?
In Herndon, VA Beside the flowing traffic Grove Street, 555
2) What does your business do?
What are these numbers? Sea of corporate data Juice is your life raft
3) How many people do you employ?
The cat leaps, clawing The coiled bird escapes Seven feathers fall
4) Who are your main competitors?
Thundering feet pound Yet by the rippling puddle The mammal sips uncaring
5) Why did you decide to use Google Apps, and why did you choose Google over other commercial or opensource alternatives?
Spring air warms the tree Talking Heads song can’t fight O2 for free
6) What products did it replace and why?
Old friends whither In spring, new shoots grow Excellence in change
7) Are you using the Standard (free) version or Premier (paid) version and why?
The raven’s keen eye Gathers all he needs He has no wallet
8) Which applications do you use (Gmail, Talk, Calendar, Docs and Spreadsheets, Page Creator….All?) . Which ones give you the most benefit?
Star, thread, search Dinner for seven at seven Featureful sunrise
9) How many people are using Google Apps and how?
Does the happy frog count Beside the spring bullrushes How many croaks he hears?
10) What benefits have you derived from using the Google Apps? (quantifiable benefits if available)
Deep frozen roots Towering tree, branches drooping A nut in the snow
11) What features of the product do you appreciate most and why?
Was it you or me? Making rash changes Revision history
12) What’s been the overall impact of using the Google Apps?
Hive mind emerges Cicadia-like, a boon? or a pest?
13) Any advice you’d give others in implementing and using Google Apps?
Internet down? Keep a chair warm At local Starbucks
14) Are you using any other Google applications such as Maps? AdWords? AdSense? Please elaborate.
Reroute my route? Cool! Every trip now includes A stop at IKEA
15) What improvements would you like to see in Google Apps that would benefit your business?
A shopping list is useful, but PivotTables sparkle in sunshine
Care to share your experience with Google Apps? We’ll highlight the best haiku in a later post.
Last week, Google released Presentations to fill out their portfolio of online, collaborative document types (they already offer text documents and spreadsheets). The Google folks were kind enough to include us in a round of beta testing a few weeks back, giving us a chance to preview this application, find bugs, and offer feedback.
If you give Google Presentations a try, you may be struck by its limitations. It doesn’t offer much flexibility in creating presentations, especially when compared to Microsoft PowerPoint or Apple Keynote. The best you can do is create simple text slides on a few predefined templates. On the other hand, it offers unique capabilities you don’t get with desktop applications. In particular, we were impressed with how easy it was to share a presentation live online.
I have started to wonder whether calling Google Presentations a “web-based competitor to PowerPoint” or “a PowerPoint clone” was simplistic and misguided. Lumping together software tools is a natural reaction to long lists of features and techno-terminology. Software vendors don’t make it any easier to distinguish the differences when they attempt to convince us that their solution is the complete, do-everything tool to satisfy all your [presentation/data analysis/communication/networking] needs.
So, we assume our software tools fall into neat buckets. We assume the tool we are using today do everything we need “well enough.” And we assume any new tool is a direct competitor to what we use. As a result, we are severely limited in what we can achieve.
For a long time, I was a fan and a heavy user of PowerPoint. It did what I needed. Perhaps I told myself that what it did was all I needed. A while ago, I had to break off this exclusive relationship.
Now, I find myself using a bunch of different tools to communicate information. On the one hand, this has made my life more complicated. There are new applications to learn and the hassle of moving documents around. But in other ways, it’s easier. I use tools designed for the task at hand. And I have opened up a whole new realm of what is possible in terms of organization, polish, and audience engagement.
The table below shows the activities involved in business presentations. For each activity, I have a rough assessment of how well PowerPoint, Keynote, and Google Presentation perform. I also list the current Juice toolset.
Today’s post is brought to you by Andrew White of Gartner from an article in
their 2007 CRM conference brochure:
What’s the single biggest benefit of practicing MDM?
There are multiple drivers that help enterprises decide to
embark on an MDM [1] program. Implementing a CDI-focused [2] MDM program
will help implementations of CRM [3] achieve a higher return by
enabling better cross-marketing and selling.Implementing PIM [4] within MDM will help supply chains fulfill
orders more timely [sic] and introduce new products more quickly. Embedding
MDM in an SOA [5] environment contributes to business (process) agility
through support of more rapidly developed composite applications; and
others help cut costs by supporting better procurement practices.
Way to cut though to the heart of the issue, guys. Let’s see if we
can decode what they’re saying:
Knowing more about your customers will help you find
more products that existing customers want. It will help develop those
products too. And let’s not forget your web apps. They’ll be easier to
develop and easier for other companies to integrate with if you have
your data well organized.
It’s nice to be able to decode this, but semantically, there’s
nothing there. This response amounts to “Trust us, it’s great!”
[1] Master Data Management is another salvo in the eternal battle between centralization and decentralization in organizations. The wheel turns; today it’s MDM, in 5 years it will be called Centralized Metadata Integration.
[2] Customer Data Integration means centralizing how you track customer-related information
[3] Customer Relationship Management systems track interactions with
your customers
[4] Product Information Management is CDI for products–see how easy
this is getting?
[5] Service Oriented Architecture is a way of building computer
services as little pieces rather than big integrated applications
This New York Times cancer graph is a beautiful piece of work.
I wanted to see if we could reproduce it with everyday tools.
Click here to watch a screencast showing how it was done. Warning the screencast is a little long—14 minutes—and a little unpolished. One cut, no retakes, banzai analytics!
Derek raised an interesting question about how to find the fonts used by the New York Times. While I don’t think you can find a high quality free version of these fonts (Helvetica Neue, Univers?), Microsoft has made some very good new fonts for Vista and these are also available to Microsoft Office users through a compatibility pack. Here’s a link or google for “microsoft office compatibility pack“. I recommend using these fonts.
Here’s a version of the graph with these new fonts and more emphasis on getting the typography right.
We're in the process of preparing just that. Email us if you'd like to be included in the early access video at info [at] juiceanalytics.com ...
Missed both of these. Will they be available to review after the fact? ...
Amitp, That's a great point. It makes it clear that visualization is a toolkit that has to be properly applied by a data storytelling practitioner. Which raises the question: What other tools should a ...
This is werid there is not 100 ideas on here ..... LAME!! ...
Listening to Hans Rosling talk vs using his gapminder tools to explore the data is a great example showing how important the storytelling is compared to the visualization alone. ...
Show comments