Mapping Customer Data
by Mary Branscombe
Discover new business opportunities and spot problem area. See how maps can help you visualise your business.
HardCopy Issue: 59 | Published: February 1, 2013
Do you know where your customers are? Do you know why you have customers where you do? Yes, you have their addresses, but looking at addresses as text is like looking at sales figures as numbers in a spreadsheet; it doesn’t give you the real picture.
To understand your sales figures and costs, you put them into graphs and charts and pivot tables. To understand your customers, you need to put them on a map. Just as looking at a graph helps you see the pattern of sales and expenses, looking at customer data on a map helps you see the opportunities and the problems.
Perhaps the most famous example is John Snow’s cholera map of London. By plotting the cases of cholera in the 1854 epidemic against the water pumps in central London on a map, he was able to identify a water pump in Broad Street as the source of the infection – and when the handle was taken off the pump, cholera deaths in the area dropped dramatically.
When you look at Snow’s map it’s immediately obvious where the problem is, and the same is true when you look at your customer and sales data on a map. Because the map adds features like roads, towns and other businesses you can see if sales look unusually high or low for a specific location, or if there’s an unusual pattern.
This isn’t just a handy tool for planning customer visits – although if you have a spare hour and you can see which of your customers in driving distance have recently started buying less or raising more support calls then it’s far easier to plan your trip than if you’re just looking at a list of addresses and postcodes.
Look at your data on a map and it’s obvious when you have a lot of sales in a surprisingly small town, or if your sales in an area are evenly distributed or clustered in specific places. Animate your data over time and you can see sales rising, falling and moving to new areas. If you’re a business bank and you see that you’ve got growth in one territory from vets and in another from undertakers, then you want to target undertakers in the first area and vets in the second to keep growing. Such opportunities come to life more easily when you see them on a map than if you just look at the data in Excel.
Add in demographics and boundaries and information about other businesses in those places and you can start answering questions like, “What are we doing in Banbury that’s generating so many sales?” or, “Is there something wrong with the delivery routes in west Oxford that’s giving our competitors an edge?” With a map, you can take a massive data set and get relevant information – and start asking useful questions.
Information everyone trusts
Using maps without making sure you have all the relevant information is as bad as using charts with misleading axes. Peugeot tried using basic geographical information to set the territory for franchised car dealers as a 30-minute drive from the showroom, but local road conditions and traffic meant that was far from accurate and dealers repeatedly complained that their area was wrong. Peugeot switched to the DriveTime version of MapInfo and used TomTom Speed Profile Data to get accurate driving times based on the average of actual daytime journeys by TomTom sat navigation users. The results were good enough to completely stop the dealers arguing, and the company now uses the traffic-based information for planning new franchise locations and responding to tenders for enterprise fleet leases.
Even a small business that sells online can get useful information from seeing customers on a map. If 90 per cent of your sales are in the UK, plotting the rest on a map will tell you if there’s one country where you’re becoming popular – in which case it’s worth devoting some marketing to reaching more customers there – or if demand is evenly spread between several countries and you can rely on organic growth.
Expensive GIS systems and complex geospatial development tools used to mean that such solutions were only for large companies with specialist geospatial developers. Even building a mash-up on Bing Maps requires programming knowledge, but the latest mapping products make it far easier to take customer data from Excel or a CRM system and view that on a map – even a sales director can do it in minutes.
This is the same thing that’s been happening with business information tools. Once you had to wait for someone in IT to build a report before you could analyse the data. Then you could generate canned reports on demand, but if you wanted a different analysis you had to wait for a custom report – and that probably had you asking for another one as soon as you’d looked at it. Now BI tools are so simple and affordable that you can answer most of those questions yourself in Excel straight away. That same democratisation is now happening to maps.
MapPoint Europe 2013 is the latest version of Microsoft’s venerable data mapping software. This is a standalone package with demographic information as well as routing and journey planning, so you can import your customer list or sales leads from Excel, Access or SQL Server and see them on a map together with information about businesses and services taken from Bing Maps. At the most basic level you can use the result to plan efficient routes, or to explore different strategies for allocating areas to your sales and marketing teams.
However you can also use the demographic information to understand more about your existing customers. If you run a car valet business, for example, do you do better in areas with more households that own a second car, or where more than one person in the family has a job? Knowing that can help you decide between a marketing campaign that concentrates on your premium service, or one that emphasises the time you save the customer.
You can also use the demographics to uncover opportunities. If you want to find the best place to open a new furniture store, you can add the demographics for households that spend more than average on furnishings and import the addresses of competing stores to find areas where there are potential customers who don’t live close to a shop run by your competitors. You can also map the habits of households in an area, to get ideas for new products and services that might appeal to them.
Similarly, MapInfo Professional from Pitney Bowes gives you demographic information like population density overlaid on multiple styles of map, and you can have your own information from Excel, Access, Oracle and SQL Server displayed as layers on the map, including heat maps and labelled pushpins.
MapInfo is a powerful tool that can be complex to learn. There are other tools that can more simply help you find the insights hidden in the information you already have. Add-ins like Power View or MAPCITE for Excel 2013 let you visualise data from Excel or Dynamics CRM.
Mapping with Excel
Excel 2013 integrates the Power View tool from SharePoint and adds maps from Bing to the visualisations you can create. This is extremely easy to use: you just drag geographic fields on the Power View sheet and choose to display them as a map. Add numeric fields to your Power View table and they show up as dots, or pie charts if there’s a series of data. You can cross-filter maps and charts together; select a bar in a chart or filer by searching and you’ll see just that data on the map, click a location on the map to see information for that location in your chart, so you can drill down from one country to one county to a single city.
If you want to add demographic information, you can access anything that’s in the Azure Marketplace. If you want to see maps in 3D, for example with bars showing sales figures rather than coloured dots or pie charts, Microsoft will be releasing the free GeoFlow add-in for Office Professional Plus 2013 later this spring. This has richer mapping visualisations including heat maps and 3D representations of data, and you can animate your data over time. You can add map layers using data imported from SQL Server as well as from Azure. When you find something interesting, you can export straight to a PowerPoint slide, or create animations of how the data changes and share them as videos.
MAPCITE for Excel adds a ribbon of mapping commands right into Excel that’s even easier to use than Power View. You can see any data in Excel that has addresses on a map, and you can view both heatmaps and map layers such as administrative or sales territories. You can import data to show on additional layers from SQL Server, from Azure Marketplace or from KML files you create on sites like Google Earth or Scribble Maps.
The heatmaps are live so they update as you zoom in and out, and you can animate them to see growth or changes in customer distribution.
Furthermore, once you’ve found something interesting on the map you can share it with colleagues; either by saving to Word or PDF, or using MAPCITE’s SharePoint Web part or Web application which lets you share a read-only subset of your data for the area of the map you choose. They can zoom in and out and interact with the map but they can’t add, change or delete your data.
Mapping from CRM
If you’re dealing with customers and leads then the best place to do it may well be inside your Customer Relations Management system. MAPCITE is developing a plug-in for Microsoft Dynamics CRM with similar features to its Excel tool.
If you’re just looking for simple mapping features then MyCRM eMap adds mapping tools based on Bing Maps to a hosted version of Microsoft Dynamics CRM 2011that you can buy monthly, without a fixed term contract. This geocoding plugin can show a map of all your leads, with driving directions, or filter current customers by area. That lets you get a list of all customers within 50 miles of Edinburgh, for example, so you can plan sales visits.
Not so bracing in Skegness
GPs in the UK write hundreds of thousands of prescriptions; in fact the database of prescriptions within the NHS adds about a million rows each month. That’s far too big a dataset to look through by hand so extracting the insights that doctors need requires business intelligence tools. Alongside more familiar BI tools, the NHS Information Center is using MAPCITE to make this data more accessible.
If you look at the prescription rates for Prozac and Viagra as a heat map, you see the expected high numbers for places like London, with large populations. But you also see unusually high prescription rates for Skegness; far higher than you’d expect for the population. That would be hard to spot by looking at graphs and charts, but it’s immediately obvious when you see the data on a map. Add in demographics like unemployment and other deprivation indexes and you can see there’s still something skewing the data.
Like any other good BI tool, mapping gives you an idea of the questions you need to be asking: in this case, why is one surgery in Skegness writing so many more Prozac prescriptions and how much does that cost? Excel could show you the figures, but it would be a lot of work to correlate that with local population figures, and Excel wouldn’t give you a handy list of surgeries within 15 miles so you can compare their prescription levels, which are far lower.
Drag to select an area on the map and eMap shows a table of all the entries in that area. This a much quicker way of picking customers in the same area than digging through their addresses, and you can turn it into a targeted marketing list, assign them to a specific user, run one or your CRM workflows on them, or copy the list into Word or Excel.
You can create multiple maps in eMap so you can have one for customers and another for high-value opportunities, or divide areas into territories. It’s also useful to just explore the map and see pushpins for accounts, leads and contacts. Click on a pin to add it to a route or to start any standard CRM activity.
You’ll see a lot of services like eMap and MAPCITE built on top of the Bing Maps API and SDK. It’s not just that Bing Maps has similar functionality to the Google Maps API (with the choice of Silverlight, AJAX, REST and SOAP interfaces) while being easier to integrate with Microsoft products like Excel and Dynamics: it’s also that the commercial terms for working with Bing Maps are better. That means there is a growing number of Bing Map Apps that let you view your own data on Bing Maps as pushpins or heat maps, which is a good way to try out mapping for yourself and to get some ideas for how you might use it.
Make your own maps
ESRI is one of the big names in geospatial information and its ArcGIS software is used by US government agencies for major projects like assessing the danger of wildfires and planning reconstruction after Hurricane Katrina. The free desktop and online versions of ArcGIS Explorer are particularly useful for making your own professional-looking maps. You can use the ‘base maps’ (including satellite imagery for the whole of the UK) and a number of layers of mapping data from ArcGIS like UK population, fuel prices, wind farms locations, driving test pass rates, road accident statistics, census data and other demographic information that might be useful. You can search within the ArcGIS databases or look for geospatial information on the Web to include on your map.
You can add your own data to maps in ArcGIS Explorer, including photos and videos as well as datasets of addresses, and analyse your data: for example drawing a shape on the map to find data within a specific geographic area.
ArcGIS Explorer is quite a sophisticated tool. You can select locations within irregular shapes as well as circles and rectangles, and you can measure distances as well as getting routes and directions. If you’re sharing the map with other people, you can add bookmarks, charts or even a presentation. However the professional GIS background does show through. If you want to query information in a map layer, for example, you’ll be working with values on database tables, and there’s no integration with your other business tools. ArcGIS Explorer is ideal if you need really detailed geographical information on your map, but other mapping products offer better business tools.