Setting Up Your Data in Excel

AlignMix is fast and easy to use (once you get the data into the software… ). That is the main question we are asked. “How do I set up my sales territory alignment data so I can easily import it into AlignMix?” The answer to that question is found in the video above. 

The main thing to remember when creating a file for import is to have all of your data columns appear in the same row. This will simplify the import process. Also, you will always need a base geography in your file in order to import any data. IF you are creating a zip code map then your base geography is the 5 digit US zip code. If you are creating maps at the US county level then your base geography will be the 5 digit FIPS code.

The Difference Between Account Data and Zip Code Data

This is a question that is asked quite often. What is the difference between account data and zip code data? Account data is linked to an account ID/name. Most often this is the location of an account with account data like sales and workload. Zip code data is linked to an entire zip code.Zip code data can be population data at the zip code level and also zip to territory assignments.

Accounts do not have to be assigned to the same territory that a zip code is assigned to. This allows you to create a hybrid alignment that allows account overrides/special ownership accounts.

Importing Sales Territories

Importing sales territories in AlignMix is very easy. All you need is an excel file that has one column of zip codes and a second column with the corresponding assigned sales territory. This type of data can be exported from most sales mapping services/packages and even some CRM dashboards.

To import this data (this data can be called “Zip to Terr”) first open AlignMix. Click on Import. Then select the data file to import.

Now all You will have to do is, drag the first column (Zip codes) to the corresponding location on the right side. Next, you import the second data column (Sales territory) to either sales territory name or sales territory ID. Click Next. And then Click Next again. You will see a load screen that shows the progress of the sales territory data being imported. When the import is finished you will see all of your sales territories displayed on the map.

If you do not have this type of data and are looking to create sales territories from scratch then this video is for you: Creating Sales Territories

Zip Code Population Import

Importing zip code data works the same as the other imports. Make sure you have the column labels in the same row. During the import process the 5 digit zip code will be dragged to zip code ID on the right and the data set will be dragged to zip code data. Just those two data columns are required for a zip code population import. 

Importing Sales Accounts into Existing Map

The account import will place icons on your sales territory map representing the location of every sales account. If you use an account ID for the sales account import you will see that the software will locate any duplicates and merge the data into one icon on the map. Using a segmentation will allow you to show each type of sales account as a different icon on the map. 

In AlignMix you can import Sales Accounts, Zip to territory assignments, Sales Reps, Employee roster, and zip code data. There is no specific order to importing the data into AlignMix. 

If you used a sales account ID for the import, then you will be able to import updates to the account data in the future. The account ID will also allow you to do a search for the accounts using the Paste from Clipboard tool.

Using an Account ID/Unique ID in your sales account import is highly recommended, but not required. If you import without an account ID then AlignMix will create one for you. The numbering will start at 1 and continue down the excel list until the last row of data that has been imported. If you did have duplicates in your excel file AlignMix will not locate and merge them. In order for AlignMix to merge the duplicates you will need to have the account ID for the import. When importing account data there are two pieces of data that are required. The Account ID and/or the account name and the location of the account (most often the zip code). Without these two columns of data you will not be able to import any account data into AlignMix. Instead of using the zip code/post code you can also use the latitude and longitude of each account. Geo coding accounts has become super cheap and you can easily upload a list of accounts with addresses into one of these services for a very low price. Then when importing with the geo location AlignMix will place the icon of each account in the exact location on the map.

You can also import street address, city, and state but these columns of data will not impact the mapping of the accounts. They are mainly imported so that when you do the final export these data sets will also be included in that excel file. If you have any additional data sets like sales, call, potential, or workload; you can also import that data. Any numeric data will be imported and linked to the accounts unique ID. You can also import the assigned sales territory and sales rep during the account import. If your accounts are segmented, you can also import that into AlignMix. Segmented accounts means that you have different types of customers. Each segment can be imported into AlignMix and will appear as a different icon on the map. You can then toggle on/off the icons from the view options.

During the import process AlignMix will default to showing the column labels from the first worksheet in the excel workbook. To select a different worksheet, click on back and then select the correct tab for import. From the main import screen, you simply need to drag and drop the column labels from the left to the corresponding section on the right. This will tell AlignMix what you are trying to import. If you are importing account-based data, then the majority of you data will fall into the accounts section. Sometimes when importing data you will notice that some errors will appear at the end of the import process. This is normal. You can click the save button and then view and fix the errors. The most common errors occur when there is a typo, blank data cells, or incorrect geographies. You can fix these errors in the error report and then import that error file back into AlignMix.

When the import is finished you will see all of your account icons scattered all over the map. If the all the accounts appear as a small blue box, that means you have imported your data without a customer segmentation. You will also notice that there are no colored in areas representing your sales territories. This is because you have only imported sales accounts and those accounts are assigned territories. The software has not yet assigned any zip codes to a territory. The next step would be to either import a zip to territory file or use the clean up tool to assign the zip codes to sales territories.

Importing Accounts into Blank Map

In AlignMix there is no specific order to importing data. You can import your zip to territory assignments first or last or start with your sales accounts. It does not matter, the data will be pulled into the software, no matter what order you import.

If you start with a blank file and import only account data then you may be wondering where your sales territories are… The only way to create geographic sales territories in AlignMix is either by importing the zip to territory assignments or by using a clean up tool after you import your sales account data into AlignMix.

After you import accounts into a new, blank AlignMix file you will see all of your icons representing your sales territories scattered all over the map. The import was a success because you have imported accounts and that is what you have on the screen. If during this import you also imported the territory assignments for each account then you have create an account based territory design. This means that accounts are assigned to territories and sales reps, but the zip codes are not assigned. This is common for companies that have a key account team where geography does not impact the sales territories. If you did want to create geographic territories from this import you would now need import the zip to territory file or use the clean up tool and have AlignMix assign the zip codes to territories for you. 

Importing Segmented Sales Accounts

In order to display different sales account groups (account segments) on your map you need to import them as different segments during the initial account import process. The video above shows you exactly how to perform this import.

If you do not have accounts segmented then you do not need to worry about imported segmented accounts. All of your accounts will be imported and appear as the same type of icon on your sales territory maps. If you do have different account types then make sure to have that listed in a “segment” column in the data set you plan to import into AlignMix. During the import process you simply drag/drop that segmentation into the segmentation section on the right. This will create a different icon for each account type on your map.

Importing Sales Reps

In AlignMix you are able to have sales reps assigned to sales territories. If you have created a hierarchy the you can also have district managers assigned to districts and region managers assigned to regions. You can create the sales reps manually from within the AlignMix software or import a full list of sales reps directly into the software.

Before importing a full list of sales reps into your AlignMix file its best to double check to make sure you haven’t already done that. Right click on a sales territory to open up the radial tool. Then click on territory properties. In this new view you will see the name and ID of the sales territory. You can rename the sales territory while in this view. There will be a drop-down menu on the third line showing you the name of the sales rep that is currently assigned to this territory. If this field is blank, that means that there is no sales rep assigned to this territory. If you click the drop-down menu and there are no names that means that you have not imported or created a sales rep in this AlignMix file.

To import sales reps into AlignMix all you need is the sales reps name and home zip code. Each of these should be in their own column in Excel. If you also have the assigned sales territory, then you can have that in a third column and then during the import process AlignMix will create the sales reps and also assign each rep to the correct sales territory. To import the excel file you simply click on file, import, import from excel and then import the data columns by dragging and dropping the correct fields.

Now that you have imported a full list of reps you will see the icons scattered all over the map. You can customize the way the icons appear in configure layers. Now when you open up the territory properties you will see a sales rep’s name appear in the drop-down menu. Click the drop-down menu and you will see a full list of every sales rep that has been imported into this AlignMix file. You can reassign a sales rep by clicking on another sales rep from this drop-down menu. You can also reassign sales reps by using the Edit Sales Rep tool on the home menu. As soon as you click that button the left side of the tool will populate with the names of all the sales reps that have been imported. You can then reassign sales reps by clicking on the assigned territory button and choosing another territory.

 

Importing Sales Personnel Roster

Importing a full sales rep roster operates the same way as the sales rep import with one additional data column, the assigned role. The most important thing to remember when creating a file for import is to have all of the column labels appear in the same row in your Excel file. During the import process you will drag/drop the Sales persons name, and home code from the left to the corresponding section on the right. Then you can drag/drop the assigned territory/district/region and the job title to the job role section. This will help AlignMix understand the hierarchy of assignments that you are about to import. After you complete this import you will have sales reps assigned to territories, district managers assigned to districts, and region managers assigned to regions. 

Importing County Based Sales Territory Data

In order to import any County based data you first need make sure you have FIPS codes for your data. FIPS codes are 5 digit county codes that identify counties. You cannot use county names for this import because over 30% of county names in the USA are found in more than one state. That is why you need the FIPS code in order to create county based sales territory designs.

The county import process is identical to the zip code import. You drag/drop the data from the left to the corresponding section on the right. But, before you start this import process you need to make sure that you have opened a new file that is configured for US FIPS codes. You can do this by clicking on File, New, and then select US counties as the base geography. This will reconfigure your current file from zip codes (which is the default setting, unless you have already changed that) to US Counties. 

Importing Canadian FSA Data

Importing Canadian FSA data operates the same way as the US zip code import. You will need all of your data column labels to appear in the same row in your Excel file. Then you simply drag/drop the data sets from the left to the corresponding section on the right. But before you start this import process you need to make sure you have opened up a Canadian file for this import. Click on File, New, and then select Canada from the drop-down menu. If you do not see the Canadian Shapes in this drop-down menu you may need to re-download those shapes. The link for that download is sent to your email address right after you purchase the Canadian FSA shapes from the AlignMix website. 

Once you see Canada on your screen you can start the import process. The 3 digit FSA is dragged to the FSA ID on the right. The remaining data sets are drag/dropped to the remaining section on the right side. Then click next, and next again to start the import process. When the import is finished you will see your geographic sales territories appear on your map. 

 

Hierarchy Import

If you have a hierarchy (territories assigned to districts, and districts assigned to regions) created in excel format, you can import that into AlignMix. This import works just like a regular zip to territory import. The main thing to remember when creating this data set in excel is to have all of your column labels in the same row. This will simplify the import process and prevent any errors from occurring. 

The default settings are set to territories as the lowest form of assignment, the districts as the second level and then regions as the third and final level of assignment. You can edit these default settings in the AlignMix options

 

Importing Third Party Data

If you are using the Full AlignMix Pro version you are able to access the third party data. If you are using the two week Pro trial then you will not be able to access this import. The third party data includes over 300 data sets that you can import into any sales territory map that you have created at the US 5 digit zip code level. To import this data you click on file, import, import third party data. A pop up will appear where you can select which data sets you want to import into your AlignMix file. You can perform this import at any time during your sales territory design process. 

Updating Data

 

You can  update any data set that has been imported into your AlignMix file. If you are updating zip code data then all you need is a column of zip codes and a column of the new data set. Then import this data as you normally would during the start of a mapping project. When you click the next button you will see a new window appear. This is where you can choose to create a new data set of overwrite data that has already been imported into this file. If you want to overwrite a data set then you click the cell and select the data set you want to overwrite. Then click next and the import process will continue as it usually does. This process can be used to import zip code data, account data, and also zip to territory assignments. 

Customizing Data Formats

Using the data definition tool you can customize the formatting of any data set that you have imported into AlignMix. This tool will allow you to put a currency symbol as a prefix in front of your sales territory sales data. In data definition you can also delete data sets that have been imported into your AlignMix file. 

Delete Imported Data

Using the data definition tool you can not only customize the appearance of your data sets but you can also delete data that has been imported into your AlignMix file. 

Error Report

If you import data and you see that there are some errors in your file… Do not worry. This is normal. This is especially normal if you haven’t used a mapping software in a few years or ever. The most common errors are caused by typos, blanks, or foreign post codes. If your error report shows 1000+ errors then there is a chance that something else went wrong during your import process. If this does happen make sure you are using the correct base geography for your import. Importing a zip code list into a Canadian FSA file will cause each row of data to appear as an error because they are two totally different data sets that are not compatible.