Now starting off the discussion on experiences
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
1. Territory Based Security and Rights
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
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.
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
> State / Province
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
This also increases the convenience for the end user and this
increases the User Adaption Rate, which is Key for the success of the
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
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
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
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.