How to Create a Customer Sales Map from Excel or Any CRM

Gain advanced insights on your business when you visualize your data analysis. You’re probably swimming in data, in Excel, Salesforce, or another CRM. Understand the story within your data by plotting it on a map. Any geographic information, from full addresses to names of regions can be converted to geo coordinates. Viewed spatially, you’ll get a whole other angle at your customers, leads, or assets data.

View Household income, average clustering in a full screen map

For example, the map above shows the median income for every county in the United States. Better yet, using clustering technology, we can get a summary of the underlying data at any zoom level. Now imagine that this contained your revenue by region or the spending power of your customers. Quickly, you’re able to see the most successful areas, in ways that are difficult or impossible when your data is sitting inside a spreadsheet or CRM.

Ready to map your customers? Let’s get started with the simple two step process.

1. Find Your Customer Locations

Before you can plot your customers on a map, you need to know where they live or work. This can come from many sources, and you likely have it already. If you don’t, you may be able to gather it from the data that you do have. The level of detail for location can vary, yet even non-granular data can provide good insights.

Some of the location data you may have about your customers includes:

  • Full address
  • Zip code
  • City name
  • County name
  • State name
  • Country name

Any of the above can be converted to geo coordinates. If you have none of the above, all is not lost. Here are two additional ideas for finding your customer’s locations:

  1. IP address: If they have visited your website, you have their IP address, or Internet Protocol address. This series of numbers identifies their computer, network, or internet service provider. Usually, these can be converted to at least a regional location. BatchGeo automatically detects and geolocates IP addresses in your data.
  2. Email address: Many companies collect the email address of customers when they create accounts or to send receipts for purchases. There are data enrichment companies, such as FullContact or Clearbit, that will take a list of email addresses and return name, location, and other data about the person behind the email address.

Once you have some location data, you can easily create a map from it.

2. Map Your Excel Data

Many companies keep customer data in Excel and other spreadsheets. Even if that’s not the “source of truth,” Excel is where many filter and analyze their data. That’s why BatchGeo has chosen spreadsheets as the primary way to get data into our mapping platform.

From within your spreadsheet program, highlight all your data, including the headers. Use Ctrl+C (Cmd+C on Mac) to copy the data. Then go to the Excel mapping tool on the BatchGeo homepage and put the data in the main box using Ctrl+V (Cmd+V on Mac) to paste.

The video above walks through a basic map, or you can find a full Excel mapping tutorial that walks through step by step.

View Example Sales Map in a full screen map

The result is a map with a marker for every row in your spreadsheet. In the case of the map above, we used aggregated state-level data, but you could show individual customers or leads. Any data that has a location is fair game for a map.

Export Data From Your CRM

Many times the source of truth for customer data is in one or more systems, such as customer relationship management (CRM) software. You’ll frequently want to use this data outside of your CRM, such as when you want to create mail/email campaigns, analyze in Excel, or create a map from the data.

Any good CRM should have the ability to export your data. If it doesn’t, you should probably get a new CRM, even though it will take a lot of effort to duplicate your existing data (since you can’t export). You might ask about an API (Application Programmer Interface), which is a more technical option to get your data. Unless you’re a programmer, you’ll need someone else to use it, but an API often means there are existing tools that others have wrote to help you retrieve your data.

The most common file formats when exporting from your CRM are:

  • CSV (Comma-separated values)
  • Tab-delimited
  • Excel

Any of the above will work to create a map using BatchGeo. Excel can read or import any of those file types, then you can copy from the spreadsheet as described above in step 2.

How you export will vary depending on the software you use. For example, in Salesforce you would use the Data Loader export wizard to create a CSV to download. You should be able to search the help section of your CRM or ask for technical support to help export your data.

Make Your Customer Map Today

Start uncovering the story behind your customer data. Create a map now with a simple copy-paste of your spreadsheet or CRM data.