Preparing Your Data for a Sales Force Mapping Exercise (Part 1)

Before you can start to re-align your sales force territories you need to import your data into AlignMix.  And before this is possible you must manipulate your data into an Excel file.  We chose Excel as our format of choice since its’s the most common data manipulation tool on the planet.  If you don’t already have a copy of Excel you can use one of the free alternatives.  I’d recommend Gnumerics.  You can even get a version which runs from a flash drive.

Importing Zip Code Data into AlignMix:

Figure 1

The most common format for importing data is by zip code (or postcode if you’re outside the US).  An example is illustrated in Figure 1.  In this format each row represents the data for one zip code.  Some points to note are:

  • One column holds the zip code identifier
  • The zip code can be in either numerical or string format
  • Only positive numeric data can be imported
  • If there are multiple row containing the same zip code, the data will be added together to give the final value for each zip code

Tips for Manipulating Zip Code Data:

Quite often you may have a number of different data sources.  For example you may have one source for demographic data (an external data base), another for sales data (e.g. SAP) and another one for the sales territory alignment data.  While you don’t need to have all of the data in the same Excel file I find it makes sense to create a Master Data File.  By creating the Master Data File you’ll most likely highlight any missing data which can then be corrected or accounted for.

Excel provides a number of tools to assist you in as you manipulate the data and create the Master Data File.  Pivot Tables are certainly not something every Excel user utilizes.  But they are a good way to consolidate account level data into zip code level data.  A full explanation is beyond the scope for this post.  However, you can find a good tutorial on Pivot Tables here or here.

Figure 2 - vLookup for Zip Codes

The second tip is useful when you’d like to merge two data sources into one.  For example you may have purchased some demographic data and you’d like to merge this with you latest sales data.  Be warned, you cannot just sort both list and copy the table from one list and merge it with the other list.  This will almost certainly result in errors.  Zip codes (and postcodes) change regularly and it rare for two lists of zip codes (no matter how up-to-date they are) to match exactly.  What you must do is a “look-up” to match the zip codes in one table with those in the other.  Fortunately Excel provides a way to do this.  It by using a function called vLookup.  This takes the following inputs:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In  most case the lookup_value will be a zip code.  The table_array will be the other zip code table.  The col_index will be the column offset you’re interested in. For zip code lookups the [range_lookup] should almost always be set to FALSE, otherwise it will find the closest zip code record (i.e. pull in the wrong data if there isn’t an exact match with the zip code).

As an example, in figure 2 you can see a table of zip codes and household counts.  The formula in cell D5 (shown in the formula bar) looks up the row containing zip code “32772” and then give the value taken from column two (i.e. 139,069).  Note that if the value was not found in the table, a #NA would be shown.  You can catch this using the =IsNA() function.

So I hope you can see the power of vLookups.  This is powerful technique which can not only merge two different data sources, but can also help to identify errors in the data.

In the next tutorial we’ll take a look at account level data and how this can be loaded into AlignMix.

Last updated by .

About Steve Maughan

Although Steve has a Master Degree in Engineering from Cambridge, he has spent the last 21 years helping companies Optimize their Sales Forces. He's originally from the UK but now lives with his wife (Stephanie) and two daughters (Evangeline and Pippa) in Florida.

No comments yet.

Leave a Reply