If a picture says a 1,000 words, then a beautiful interactive map overlaid with amazing advertising data must say 1 million.
If you haven't discovered it already, allow me to introduce you to a free add-in to Excel 2013 which gives you an eye-catching new way to display geographic and time-based data dynamically in 3D: GeoFlow Preview for Excel 2013.
GeoFlow is basically Excel charting plus Bing Maps on steroids.
With geographic data from reports, like clicks by geographical location in Bing Ads, it can automatically detect each city on the map and overlay your click data (see image below). It's amazing and easy to use. A step-by-step guide will walk you through it below.
GeoFlow was recently released from Microsoft Research. It's so new that the preview is only available for Excel 2013 running on Windows 7 or Windows 8. But as digital marketers looking for every edge in this quickly changing world, this tool is a must-know for the coming years.
Top Excel Resources for Marketers
Before we get started, here are a few valuable Excel resources for marketers:
- GeoFlow is one of five free Excel add-ins which help marketers decipher big data I've discussed in the past. If you haven't seen the others like Bing Ads Intelligence, check them out.
- My personal favorite Excel resource is Chandoo.org. It'll help make you awesome at Excel.
- Follow Excel MVPs and enthusiasts with RSS feeds or Twitter like: @MrExcel, @PowerPivotPro, @TomUrtis, @exceltricks, and @r1c1 via my Excel Pros Twitter List.
Now let's walk through a step-by-step guide on the main event, GeoFlow.
GeoFlow: A Glimpse of the Future of Data Visualization at Your Fingertips
Curtis Wong, a Principal Researcher for Microsoft Research who kicked off the project, explains, “The goal always has been to bring dynamic, interactive data visualization to the business world.”
Let's connect the dots between shiny new Excel toy, advertising data, and business value.
How-to Test Drive GeoFlow using PPC Data in Excel 2013
Here are the steps to use GeoFlow:
1. Download GeoFlow for Excel 2013
GeoFlow is a free add-in. Right now, it's so new that it only works for Excel 2013 or Office 365 ProPlus. With either version installed, you'll be able to download the add-in. The add-in will appear under your 'INSERT' tab in Excel ribbon as the 'Map' function.
2. Download Geographic and Time Data to Analyze
You'll need data to use GeoFlow. My data set is a Bing Ads geographic location report by day for the last seven days – but use data you're comfortable with (e.g., website analytics visitor reports).
- Download your data and make sure you've saved the file as an Excel .xlsx file type (not CSV for instance).
- Select your data and click 'Map' to launch GeoFlow.
3. Launch GeoFlow, Tell it What Columns Represent Geographic Data
The GeoFlow UI is an interactive globe with a Task Pane where you select the data to display.
- You'll first need to specify which columns represent the geographic data points. For instance, in my Bing Ads geographic location report there are columns called 'City', 'State' and 'Country.'
- Select these columns, and then click 'Map It' – I used 'City'.
4. Pick Your Main Metric like Clicks, Conversion, Sales, or Others
By now, cities from the data will begin populating. You can dig into clicks, conversions, or whatever interests you. For this example, let's analyze the click volume by city.
- In the 'Range' check the 'Clicks' box, since data is already 'mapped' to Bing Maps it will automatically populate.
- Further slicing is available, for example we can see which ad groups the clicks by city are coming from by checking the “Ad group” box under Range (see image below).
5. Zeroing in Even Further and Tell a Story
This is great, but what if you want to see what's happening in a specific market, like Los Angeles?
- Like using maps online, zoom into Los Angeles with your cursor and hover over any data you'd like more detail on.
- Not only do I see a graphic representation of the ad group's performance but a legend showing statistics is generated automatically.
- Maybe columns aren't the best way to tell the story? No problem – GeoFlow displays data in two other formats: Bubble Chart or Heatmap. Switching to either of these is as simple as changing the Chart Type in the Task Pane.
6. Last But Not Least: See Changes Over Time
As cool as this is, it gets better. You can easily add the dimension of time to find the latest trends.
- Scroll to the bottom of the Task Pane, and find the 'Time' section.
- Drag and drop the column name of time data – for instance 'Date'.
Excel will add in a 'play' button, and time stamp to your GeoFlow UI. Push play and look for trends.
If you've made it this far, congratulations! You have a great new tool to dive deep into your big data sets. These insights are sure to impress leadership and your co-workers.
Remember the next time you're solving a big data problem, here's GeoFlow in a nutshell:
- Automatically map clicks, conversions, revenue, and more within minutes.
- Create amazing visualizations like heatmaps & 3D column charts.
- Has play button functionality to show trends over time.
Even though GeoFlow is only available for Excel 2013, it is a powerful new tool. Because GeoFlow is new great marketing scenarios are just beginning to be discovered. Keep track of how others are putting this great add-in to use on the GeoFlow Facebook page.
If you've got some ideas for how marketers can use it please let us know in the comments.
At SES London (9-11 Feb) you'll get an overview of the latest tools, tips, and tactics in Paid, Owned, Earned, Integrated Media and Business Intelligence to streamline your marketing campaigns in 2015. Register by 31 October to take advantage of Early Bird Rates.