K9CRM

Hierarchical Sales Territory Structure for MS CRM

First of all I would like to wish all the readers a very Happy Sinhala & Tamil New Year, May all of your lives thrive with love and happiness.

Now starting off the discussion I wish to share my experience in the industries such as Trading, Distribution, where one can easily identify that their sales management is always tied up to a hierarchy of sales territories. This structures mainly based on a geography of a certain region or the entire world, depending on the reach of the company, where the main sales territory is divided in to countries, states/provinces, Districts/Cities etc. When CRM/PRM requirements arise in this arena and when the application is Microsoft Dynamics CRM, alas! a trouble arise. MS CRM Sales Territory Structure is as Flat as the earth.

As you can see from the above entity relationship diagram, MS CRM Sales Territory doesn’t have any self parenting relationships by default and it doesn’t allow you to create a custom Self Parenting Relationship as well. So how can we handle this?

You might say, Easy! Lets create a custom entity for sales territories, lets create a self parenting relationship and create one to many relationships with all relevant entities such as Account, Lead, User etc. Well “technically” yes, but making this the conclusion, you should see what are the client requirements related to “Sales Territory”? Can this simple solution address all of these? Well from my experience here are some of the requirements which may arise in similar scenarios. In order to understand this better lets take a small scenario as well. ABC Company has the following sales territory structure

1. Territory Based Security and Rights Assignment

In this case my client wanted to have Head of Sales at the Company Level, Country Managers, Regional Sales Managers, Area Sales Managers, Sales Coordinators and Sales Executives. They wanted Sales Executives to view & manage Sales Records (Leads, Opportunities, Quotes etc) and Customer Records (Accounts and Contacts) only within the areas assigned to them while Area Sales Managers should have the rights over all records within all Sales Territories assigned to their subordinates.

E.g.: City 1 can be managed by a single sales executive

        His Superior should be able to manage an owned sales territory which is Stated D (City 2 and Rest of State D except City 2) as well as his sales territory which is City 2.

        Country Manger will have all powers over all sales territories within the country.

2. Automatic, Lead, Customer Routing

In this organization, anyone can contribute to sales by entering any sales lead that they hear of. The lead will then be routed to the appropriate sales resource based on the sales territory of the lead after which it will be followed up and then if materialized an incentive will be available for the introducer of the lead.

Leads should tightly associate to geographies (geo addresses) in order for this to happen. Geography should be then coupled with the sales territories for routing.

3. Territory Based Sales Performance Management

Primary base of sales budgeting, forecasting, performance monitoring via KPIs for this company was sales territories. Sales Targets will be assigned at product sub-category level, sales territory level and time periods (fiscal) which will then be aggregated to form Company Targets. Marketing Budgets, Incentives were also derived from same. Primary measures were: Revenue, GP, No. of Units, Average Selling Price & GP Percentage.

In this context MS CRMs existing sales quota process was pretty useless and had to build a model from scratch.

As you can see a simple custom entity to represent “Sales Territories” will never address these requirements which are pretty common in trading/distribution organizations or retail (Mass marketing) organizations. So how shall we address same. Here’s my solution.

1. First of all there should be a self parenting hierarchy to represent sales territories. Can I create a simple custom entity? Well… With reference to Requirement #1, all of organizations sales record security is based on this sales territory hierarchy. So can we use business units for same? May be we can use it so easily we can have that security & rights assignment fulfilled.

Security Roles:

1. Sales Executive – Create, Read, Write, Append, Append To Rights to all customer and Sales Records (Own Records)

2. Sales Coordinator - Create, Read, Write, Append, Append To Rights to all customer and Sales Records (Business Unit Access)

3. Area Sales Manager & Above– Create, Read, Write, Append, Append To access to all customer and Sales Records (Parent, Child Business Unit Access.

 

2. Now that we have created sales territories we need to assign records to these territory sales people based on geography. So we need a geography entity structure. Self Parenting? Yes… Have to. And it should be related to BU Structure (Many to One)

Then in order to identify which records belong to which territory, all relevant records, such as account, lead etc should relate to geography and via geography to sales territory (Business Units)

Now when a particular lead or account is created in the system, it should be assigned with a geography. This can be done as a part of populating the contact address where the user will fill in the specifics such as PO BOX, Street etc and then pick the city, suburb from a list which in turn will populate the remaining fields of the address such as

• > District
• > State / Province
• > Country

In my part of the world, the addresses are not very well structured but in countries where it is more systematic, you may be able to enter a postal code or a zip code and have the most of the address populated.

This also increases the convenience for the end user and this increases the User Adaption Rate, which is Key for the success of the solution.

Here’s the lead address assignment tool which was created for the project in order to make this convenient for the end user.

Added the below button to Record Tool Bar to Bring up the Geography Selection Tool. This pops up at the creation (1st Save) of each record as a dialog box, making the process mandatory for the users to enter the Geography and Address.

This is the Tool Screen Cap :

Behind this interface is a simple model of code which

i. constructs the tree of geographies by retrieving multiple geography entity instances from CRM (Code for this tree construction will be given in the next article)

ii. populates the generic address fields based on the hierarchy level of the selected geography

iii. retrieve the Sales Territory of the selected geography, find out the CRM User who’s managing this territory and display to the user (Circled)

iv. Assign the record to that user up on confirmation (Clicking of OK Button).

Then for the System Administrators, managing these complex relationships with the traditional CRM UI can be very difficult. So a separate sales territory admin tool was built.

The tree view all the way towards the left side will show the Sales Territory Hierarchy in a easily browsable manner. Once a Sales Territory is selected in the tree, the middle tree view will list down the Users according to the user reporting hierarchy and the right tree view will show the portion of the geography hierarchy associated with the sales territory.

This allows the admins to easily add, remove territories, associate users and geographies with them and have a much more graphical representation of the Territory model which cannot be offered out of the box via CRM UI.

 

3. Then came the requirement to manage the budgets. Here’s the entity structure which was used for that:

Sales targets were entered at the granular level using the Sales Targets Entity by each responsible user. A separate workflow process was built to assign a task (to-do-item) for each user to enter their target values for each product sub-category (Product Line). These were then aggregated to form the divisional, Territory wise, and Product (Main Category) level targets.

Monitoring was entirely done, using KPI’s configured into a BI Data Cube or a UDM which consumed MS CRM Data. Target Values for the KPIs were given from the above Sales Targets and then the actual values were derived from Sales Order / Invoice Info.

So that’s it. As you can see it was so much more than creating just a custom entity to represent sales territories. Hope this article has given the basic idea for modeling Hierarchical Sales Territories  and other requirements which are commonly associated with it. And also how the flexibility of Microsoft Dynamics(TM) CRM 4.0 can be used to address complex customer requirements via careful entity relationship modeling, identification of processes and data flow.

Don’t forget to pass on your comments :)