View and evaluate the quality of your sales territories

In Microsoft Dynamics CRM

Note

If you have not previously created territories in Microsoft Dynamics CRM, do so before proceeding. If you need to create your territory data, use the Microsoft Dynamics CRM Help topic “Create or edit a territory” to step you through the process. You can create territories by postal code, area code, state or province, or other geographical region.

If you already have territory data in Microsoft Dynamics CRM, begin by using the Look for or Advanced Find features in Microsoft Dynamics 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. 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. In Advanced Find, 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 Address 1: City columns.

7. In the Common Tasks area, click Add Columns, and then select Address 1: State/Province and Territory.

8. Click OK, and then click OK again to return to Advanced Find.

9. On the Find tab, begin limiting your search to accounts with territories and the states located in each territory by leaving the default query Status Equals Active.

   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, locate the territories associated with those states by doing the following:

    • Click Select, and under Fields, select Territory from the list.

    • Click Contains Data to indicate the limiting factor from the list.

13. Click Find to retrieve your data. Keep Advanced Find open.

    Note

    Click Back to Query to make changes to your search criteria, if needed.

From Microsoft Dynamics CRM to Excel

Next, send the data that you gathered on accounts and territories to Excel by doing the following:

1. In Advanced Find, on the Actions toolbar, click Export to an Excel worksheet .

2. In the Export Data to Excel dialog box, click Static worksheet with records from this page.

   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 clicking the Export Data to Excel button and then selecting the Dynamic worksheet option in the Export Data to Excel dialog box. 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. On the File menu, click Save as, change the file type to Microsoft Office Excel Workbook, name the file Accounts by Territory, and then click Save.

6. Click Save and Close.

In Microsoft Dynamics CRM

Next, gather data about the number of current leads in each territory.

Note

Because accounts and leads are in separate sections of Microsoft Dynamics CRM, you cannot include your leads' data with the territories' data.

1. On the Navigation Pane, click Sales, and then click Leads.

2. In the View list, select Open Leads to get a complete list of leads with which to work.

3. On the Standard toolbar, click Advanced Find.

4. Click Edit Columns, select Topic, 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 Status Reason and Created On fields.

7. In the Common Tasks area, click Add Columns, and select State/Province.

8. Click OK, and then click OK again to return to Advanced Find.

9. On the Find tab, begin limiting your search to open leads by leaving the default query Status equals Open.

10. Click Show Details to display Select, if it is not already displayed.

11. To search for the locations of your open leads:

    • In the Query pane, click Select, and then select State/Province from the list.

    • Click Contains Data to indicate the limiting factor from the list.

12. Click Find to retrieve your data. Keep Advanced Find open.

From Microsoft Dynamics CRM to Excel

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.

1. On the Actions toolbar, click Export to an Excel worksheet.

2. In the Export Data to Excel dialog box, click Static worksheet with records from this page.

3. Click Export.

4. Click Open.

5. On the File menu, click Save as, change the file type to Microsoft Office Excel Workbook, name the file New_Leads, and then click Save.

In Microsoft MapPoint

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.

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.

4. On the Import or link to your source file of territory data page, accept the default selection of Import your source file, and then click Next.

5. Browse to the Accounts by Territory file that contains your territory information, select it, and then click Open.

6. On the Import Territories Wizard 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.

7. Click Finish.

   Note

   • If the Finish button is disabled, you must set the data types for the records.

   • You may be prompted to clarify geographic information that you are importing from Microsoft Dynamics CRM. For example, if you abbreviated Nebraska as NB instead of NE, the database will not recognize it. You will be asked to select from a list of possibilities to indicate that NB means Nebraska in your file.

In Microsoft MapPoint

Because you now have a territory map, you can add account information to the map. To do so, import your Microsoft Dynamics CRM source data (New_Leads.xls). Then, import the data into MapPoint. MapPoint determines how the map is displayed 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, 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 then click Finish.

   Note

   • If the Finish button is disabled, you must set the data types for the records.

   • You may be prompted to clarify geographic information that you are importing from Microsoft Dynamics CRM. For example, if you abbreviated Nebraska as NB instead of NE, the database will not recognize it. You will be asked to select from a list of possibilities to indicate that NB means Nebraska in your file.

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 then click Next.

6. On the Legend page, format the data by entering or selecting the following:

   • Legend title: New Leads in Each Territory

   • No of ranges: 5 (one for each territory)

   • Range type: Unique values

   • Order: High to low

   • Color: Red

   • Data range: Specify a range.

   • Range label: (Leave default entry.)

7. Click Finish.

In Microsoft MapPoint

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 U.S. demographic information to your Territories map.

1. On the Data menu, click 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, 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 (2002).

5. Leave the Divide the data you choose 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: Population, Ages 55 or above (2002) by State

   • Range type: Continuous logarithmic range

   • Order: High to low

   • Data range: (Leave default entry.)

   • Range label: (Leave default 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 In and then click to get a larger, centered view of the territories and data.

In Microsoft MapPoint

Now, use MapPoint to display a list of current accounts that you imported from Microsoft Dynamics CRM by adding clickable pushpins to your territories.

Note

When you create 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 field names in balloons is selected.

7. Click Finish.

8. On the Standard toolbar, click Save, and name your map MP_Territories.

In Microsoft MapPoint

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.

  Note

  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 the Direction arrows and the Zoom In feature to manipulate the map for a better view.

• Print your map or save it as a Web page.

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 Dynamics 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 Dynamics CRM, Excel, and 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 on the visual data.