View and evaluate the quality of your sales territories
Published: February 10, 2006
When managing your sales team, there may be times when you want to change the distribution of sales territories in a geographical region. You may want to compare sales forecasts with the actual sales figures for specific regions or distribute accounts more equally among your organization's salespeople. You could spend large amounts of time studying the maps of each territory to identify where changes need to be made. You would need to garner a cross-referenced list of your sales force and their associated territories before you can even begin the evaluation. Some possible points for consideration could include sales pipelines, current customer locations, sales history, associated product sales, and demographic information. Just gathering all of this information may seem daunting. Then, you would need to visualize the information within a geographic context. When you’re finished, you would want to be able to check the performance in your new territories over time to see if further adjustments are required. Seem too complicated?
Using Microsoft Dynamics CRM 3.0, Microsoft Office Excel, and Microsoft MapPoint, your job just got easier. Apply your situation to the following example to see a graphical representation of selected data. For demonstration purposes, let us assume that your company sells products that target the over 55 population segment. You have created territories in Microsoft CRM for most of the continental US with the exception of the Northeast. You plan to add new sales people nationally, but you want to be sure to put the new people where they are most needed. You may even want to expand by creating a new territory in the Northeast where none currently exists. You decide that you need to look at some demographic data for the territories (states), the number of new leads in each territory, and a list of current accounts existing in each territory for starters. You can use the data that you have collected in Microsoft CRM and United States demographic data available in Microsoft MapPoint to assist you. Follow these steps to see how it’s done.
If you have not previously created territories in Microsoft CRM, do so before proceeding. Use the Microsoft CRM Help topic “Create or edit a territory” to step you through the process if you need to create your territory data. You can create territories by postal code, area code, state or province, or other geographical region.
If you already have territories data in Microsoft CRM, begin by using the Look for or Advanced Find features in Microsoft CRM to help you gather all pertinent information. After you have collected your territories’ data, you will export the data to Excel worksheets. For our example, let us assume that the territories are a number of contiguous states named with the lead sales person’s name.
First, gather your territories’ geographic data:
1.
Open Microsoft CRM, and then in the Navigation Pane, click Sales, and then click Accounts.
2.
In the View list, select Active Accounts to get a complete list of accounts with which to work.
3.
On the Standard toolbar, click Advanced Find.
4.
On the Advanced Find page, click Edit Columns, select Primary Contact, and then in the Common Tasks area, click Remove.
5.
In the confirmation window, click OK.
6.
Repeat the previous two steps to remove the Main Phone and Address1: City columns.
7.
In the Common Tasks area, click Add Columns, and then select Address 1: State/Province and Territory from the list.
8.
Click OK, and then click OK again to return to Advanced Find.
9.
On the Find tab of the Advanced Find page, begin limiting your search to accounts with territories and the states located in each territory by leaving the default query Status equals Active remain as is.
Tip
For assistance using the Advanced Find feature, click Help, and then select Help on This Page from the list.
10.
Click Show Details to display Select, if it is not already displayed.
11.
First, limit your search to accounts with territories and the states they are located in:
•
Click Select, and then select Address 1: State/Province from the list.
•
Click Contains Data to indicate the limiting factor from the list.
12.
Next, you will want to locate the territories associated with those states by doing the following:
•
Click Select, and then select Territory from the list.
•
Click Contains Data to indicate the limiting factor from the list.
13.
Click Find to retrieve your data.
Note
Click Back to Query to make additional searches if needed.
Next, send the data that you gathered on accounts and territories to Excel by doing the following:
1.
On the Microsoft CRM Actions toolbar, click Export to an Excel worksheet.
2.
In the Export Data to Excel dialog box, click Static worksheet with records from all pages in the current view.
Tip
If you do not need to use the Advanced Find feature to limit your data, export your selected View list immediately to Excel by selecting the Dynamic worksheet option. After selecting this option, you can still delete and add additional columns before exporting the data to Excel.
3.
Click Export.
4.
Click Open.
Important
It is essential that you click Open and not Save here. You can make additional adjustments in Excel, such as deleting or rearranging columns, if necessary.
5.
From the File menu click Save as, change the file type to Microsoft Office Excel Workbook, and then name the file Accounts by Territory before clicking Save.
Next, export the leads’ data that you just gathered to Excel by repeating the process that you did with the territories’ data. Name your new file New Leads.
Now, create territories in MapPoint. After your territories are imported, MapPoint places your territories on a map based on the data provided. To create territories your data file must include at least a column of geographic data for your territories and one for the names of your territories. To create territories, do the following:
1.
Open MapPoint, and then clear the current map by clicking New on the Standard toolbar.
2.
On the Data menu, click Territories.
3.
On the Choose how you want to create your territories page, accept the default selection of Create from your own set of data, and then click Next.
Tip
You have already defined your territories in a worksheet imported from Microsoft CRM, but if you didn't have territories defined in source data, selecting Create manually allows you to create your territories by selecting places on the map using the Selection tool. You can also redraw your territories using the Selection tool.
4.
On the Import or link to your source file of territory data page, accept the default selection of Import your source file by clicking Next.
5.
Browse to the Accounts by Territory file that contains your territory information, select it, and then click Open.
6.
On the Choose the sheet or range you want to map page, select Territories, and then click Next.
7.
On the Import Territory dialog box, look at the headings in the row labeled Data type to confirm that MapPoint has correctly determined the types of data contained in your columns. Make changes, if necessary.
8.
Click Finish.
Tip
You may be prompted to indicate what a state or a geographic location that you entered in Microsoft CRM matches. For example, if you abbreviated Nebraska as NB instead of NE, the database won’t recognize it. You will be asked to select from a list of possibilities to indicate that NB means Nebraska in your file.
Because you now have a territories map, you can add account information to the map. To do so, import your Microsoft CRM source data. Then, import the data into MapPoint. MapPoint determines how the map displays based on the location information contained in your data.
1.
On the Data menu, click Import Data Wizard.
2.
Browse to your Excel data file, New Leads.xls, with the information about new leads in the territories that you want to include on your map, and click Open.
3.
On the Import Data Wizard page, look at the headings in the row labeled Data type to confirm that MapPoint has correctly determined the types of data contained in your columns and click Finish.
4.
On the Map Type page, click Sized Circle,and then click Next.
5.
On the Data Fields page, select Name as the data column to map and Territory to indicate how you want to show the data (this counts the number of leads in each territory) and click Next.
6.
On the Legend page, format the data by entering or selecting the following:
Because you need to evaluate the distribution of the target populations of those in the 55 or above age segment to help you make a decision about your territory organization, you will need to add US demographic information to your Territories map. To add US demographic information, do the following:
1.
From the MapPoint Data menu, select Data Mapping Wizard.
2.
On the Map Type page, select Shaded Circle, and then click Next.
3.
On the Data Set page, select Add demographics to the map, select United States demographic data from the list, and then click Next.
4.
On the Data Fields page in the Select the data field or column to map, select Population: Ages 55 or above.
5.
Leave the Divide the data you chose above by list at <None>.
6.
Select State from the Show the data by list, and then click Next.
7.
On the Legend page, format the data by entering or selecting the following:
•
Legend title: State Population Aged 55 or Above by State
•
Range type: Continuous logarithmic range
•
Order: High to low
•
Color: Green
•
Data range: (Leave automatic entry.)
•
Range label: (Leave automatic entry.)
8.
Click Finish.
9.
Click and drag the Selection tool diagonally across the area of the map that contains your data, and then release the mouse.
10.
Right-click your map, select Zoom, and then click In to get a larger, centered view of the territories and data.
Adding account data to your territories with clickable pushpins
In Microsoft MapPoint
Now, use MapPoint to display a list of current accounts that you imported from Microsoft CRM by adding clickable pushpins to your territories.
Note
When creating a map with multiple sets of data, it is best to include the pushpin data last so that the pushpins are visible and not hidden behind sized circles, graphs, or pie charts.
1.
On the Data menu, click Data Mapping Wizard.
2.
On the Map Type page, select Pushpin, and then click Next.
3.
On the Data Set page, select Import data to map it, and then select Next.
4.
Browse to your Accounts by Territory file again, select it, and click Open.
5.
Click Finish when the Import Data Wizard appears (you’ve already checked this data before when you created your territories).
6.
Keep the default settings making sure that Display selected fields name in balloons is selected.
7.
Click Finish.
8.
On the Standard toolbar, click Save, and name your map Territories.
To further enhance your experience, Microsoft has included many features that will make viewing and using your new map a snap:
•
Click a pushpin to view the list of accounts in each territory.
Caution
You must be exactly on the pushpin or your other data will be displayed instead.
•
See the number of leads either in the legend or by holding your cursor over the red circles.
•
To view the map without distracting detail, from the View menu, select Map Style, and then select Data Map.
•
To enlarge or reduce the map’s font, from the View menu, select Map Font, and then choose whether to make it larger or smaller.
•
Use Direction arrows and the Zoom feature to manipulate the map for a better view.
•
Print your map or save it as a Web page.
To view a typical display, see the following example map.
You might decide, based on the Territories map you created, that because there are large numbers of people in the 55 or above age category in states you do not currently serve that you should consider adding another territory there. For example, Sally has the most active accounts, but the demographic data isn’t as favorable in her territory. You can also see that Jan’s territory has the most new leads and large numbers of the 55 or above population, especially in California and Texas. You might consider splitting that territory. Depending on the data that you’ve created in Microsoft CRM, you could even add data about your competitors in the same territories, actual and potential revenue per territory, or further demographic information about income, and so on, to give you further data for decision-making.
By combining the capabilities of Microsoft CRM, Excel, and Microsoft MapPoint, you can create an environment that makes it much easier for you to have a graphical representation of the current state of your sales territories and make it easier for you to make decisions based the visual data. .
For other helpful Sales articles, see the Related Links section.
Note
Tip
. 
Important
Caution
