Territory Management in CRM Analytics


Data security has always been critical. And sometimes it becomes difficult to align business processes and the data security model. To be honest, I realized this was the case with some of our users who were struggling with implementing Territory Management in CRM Analytics. This was a cue for me to write this blog.

Now, I know it’s a long one. So let me tell you what I have planned to cover below:

  1. Brief on how security works in salesforce
  2. Important Objects of Territory Management used in CRMA solution
  3. Use cases, with a description of the solution and recipe JSON:
    1. At accounts level
    2. At opportunity level
  4. Key learnings

Also, this blog does not cover “Territory Management” implementation in Salesforce core. And the solution explained below is based on a specific use case and can serve as a building block for other use cases. Please test it out thoroughly before deploying it to end users to ensure it addresses your requirements.

Note: You can directly jump to ‘Step 1 – Basic Data Transformations’ for the data prep solution for the Territory Management solution in CRM Analytics.

How do records become visible in salesforce to any user?

Every record you see in Salesforce is a combination of two things – data and metadata. What’s the difference between the two you ask? When you create an account in Salesforce, you enter some details about this account. Like Account Name, Billing Address, Account Owner, etc. The details you entered constitute your data, and the fields against which you entered the data is metadata. Think of it as a table where the fields like Name, Owner, Address, etc are column headers, and values you enter against these columns is your data. Salesforce provides you the flexibility to restrict visibility at both column and row levels. 

The column-level security is driven by metadata security, and the row-level security is driven by data security.

Let’s understand what I mean by each of them. 

Metadata security

In the example above, you can control if someone will be able to access the object(Account table in our case), and the different fields it has(columns in the table). So we have object-level security and field-level security in play here. You configure these permissions at the profile or permission set level.

The “Read”, “Create”, “Edit”, and “Delete” object permissions determine which actions a user can perform on any of the object’s records to which they have access. Field-level security allows you to restrict certain users from seeing and/or editing sensitive or confidential information contained in records they can see.

Data Security

Metadata security controls access to objects and their fields. This means you have enforced layer 1 of data security already. 

The metadata security may not be enough though, for you may want to restrict the visibility of rows as well. This is where below mentioned features come into the picture that will help you control the visibility of records in salesforce:

  • Organization-wide defaults
  • Role hierarchy
  • Territory Management
  • Sharing rules
  • Teams and groups
  • Manual sharing
  • Programmatic sharing

Enter Territory Management

Territory management is a way of sharing your accounts and associated opportunities, cases, contacts and even leads with sales representatives that belong to respective territories.

As stated above, we won’t get into “How Salesforce does it?”. If you want to know more about it, you can go through the trailmix, help documentation, and this blog.

Truth be told, if you’re a data person like me, all you’d need to understand is the underlying data model – what is what of Territory management, and the transformations we need to implement the solution in CRMA. This is what we’re going to focus on. I’ll walk you through basic terminologies, and the objects concerned. Building the solution in CRMA would be rather easy. Let’s get started.

What’s what of Territory Management

I am pretty sure you must have come across multiple objects while dealing with territory management. While the help docs are pretty useful, I have compiled a table below with the key players for the CRMA solution. Let’s look at these key players and understand the role of each in the territory management implementation, in Salesforce and CRM Analytics.

Note: Here’s the salesforce help doc of the object model. 

Territory Management using Data Prep

Territory management can be a big mountain to climb, but we’ll take one step at a time. We’ll start with simple use cases and keep building on top of it. And remember, we’ll mostly use the power of flatten and joins to get the desired results. 

I have considered a simple use case – Implement a solution with ‘security predicate’ that shows me only those accounts that belong to my territories and their respective child territories.

Hypothetically, if I am able to create a dataset as shown below, my requirement would be fulfilled. And this is exactly what we’ll create in our solution.

table 1

Note: Before you begin this implementation in your org, I want you to check if sharing inheritance works for your use case. If yes, then you don’t have to do the additional work – it’ll take care of data security for you. If not, then use my recipes to create your own Territory Management solution in data prep. 

I have attached the recipe below in case you want to get started with it directly.

Golden Rule: Your security model in Data prep should reflect what you already have in salesforce. Any deviation from it can lead to confusion amongst end users.

I have broken down the recipe mentioned above into 2 major steps. Let’s get started with each.

Step 1 – Basic Data Transformations

Think of this step as the basic step you’d have to perform in order to address even the easiest use case. In this section, I will get all territories of the “Active” territory model, flatten and split the territory hierarchy, and get a list of all the users assigned to each territory in various hierarchies. And that’s it. Here, you can choose to use ‘joins’ instead of ‘flatten’ and ‘split’ to get to the various levels of hierarchy. 

Basically, this is the stage I want to reach by the end of step 1 :

table 2

I have summarised the data prep flow for this step in the image below:

Note: Yes, you’d need to know in advance how many levels of hierarchies exist in your Territory Model.

In the recipe JSON attached, I have assumed six hierarchies in total. Meaning, there can be 5 territories above the lowest territory. You can add/remove levels in the node “Transform: Flatten and split hierarchy” as per your setup.

Till the “Territories with flattened and split hierarchies” node in the diagram above, your data will look something like the table below – 

table 3

The next thing we need to do is get a list of all users who are assigned to a territory. This is where the “UserTerritory2Association” object comes into the picture. Each row in this object stores the association between a user and a territory. Say a territory T1 is shared with users U1, U2, and U3. And Territory T2 is shared with users U1, U2, U4, and so on… Then the data in this object will look something like the below.

table 4

Now get a list of all users who have access to a particular territory. This can be easily achieved by a multi-value lookup between Territory2 and UserTerritory2Association objects, for each territory in the hierarchy. After performing the lookups, your data will look something like in table 2.

And there you have it! Step 1 is done.

Step 2 – Security at the Object Level

In this step, I want to transform my data as shown in ‘table 1‘. Let’s see how we can do that.

As per our requirement, I should be able to access all those accounts which are shared with the territories I am assigned to, and their child territories. Let’s understand this with an example. Consider the table below:

table 5

If I am a user who has access to territory T2, and if T2 is a parent of territory T1, then I should be able to see accounts assigned to T1 and T2 both, regardless of whether I own the account. Looking at the table above, I should be able to see Acc1, Acc2, and Acc3.

How do I achieve this? Simple, First, get users per hierarchy against each account by using ‘TerritoryId’ as the key (column 2 from table 5 and column 1 from table 2).

The resulting table would look like below:

table 6

Let’s focus on the row highlighted in yellow in the above table. T1 is the lowest hierarchy, and T2, T3, T4, T5, and T6 should have access to it. In the yellow row, users assigned to territories T1 through T6 can access account Acc1. Pretty simple, right? All we have to now do is augment this info to the Account object and get columns User_n0 to User_n6 and viola. You have a functioning Territory management model for Accounts in CRM Analytics. 

Your security predicate will look like this: 

'Territory.users.N_0.UserId' == "$User.Id" || 'Territory.users.N_1.UserId' == "$User.Id" || 'Territory.users.N_2.UserId' == "$User.Id" || 'Territory.users.N_3.UserId' == "$User.Id" || 'Territory.users.N_4.UserId' == "$User.Id" ||  'Territory.users.N_5.UserId' == "$User.Id" ||  'Territory.users.N_6.UserId' == "$User.Id"

Note: You can use this recipe to create staging datasets instead, and join that to other datasets.

This is what your flow would look like – 

Note: For the sake of convenience, I am going to call our model “TM model” in the rest of the blog.

So, what next?

We have covered the basics so far. But chances are your security requirements may be more complex than this. Let’s discuss some more use cases I have come across in the past. 

Opportunities and Territories

In the case of opportunity visibility, there are three sub-use-cases: 

1. At least Read access on Opportunity records 

This scenario means if an account is shared with a territory, and I am assigned to that territory, I can at least view all the opportunities of this account, regardless of who owns it. Basically, get a list of all users who have access to this account, all the way up in the hierarchy. 

How do you do it? 
Use the above recipe and join it with opportunity records such that opportunities are the grain. Your predicate won’t change in this scenario and will be the same as mentioned above.

To summarise, this is what the flow would look like – 

2. No Access to Opportunity Records

This means that even if an account is shared with a territory, and I am assigned to that territory, I can view only the opportunities I own. The easiest way to handle this would be to use the TM predicates in conjunction with the opportunity ownership predicate. 

For example: (TM Predicate) && (‘OwnerId’ = “$User.Id”)

Note: This may also mean that territory management is not driving opportunity visibility, and you may not need to implement TM based predicates for it. A simple ‘OwnerId’ = “$User.Id” may suffice. Please implement the solution based on your use case.

3. Mix of both the worlds 

Here some territories allow at least read access to opportunities while others do not. Say T1 allows read access to opportunities regardless of who owns it, whereas T2 restricts this access to “View what you own” for opportunities. This may sound tricky to handle but it is actually rather straightforward. Let’s see how.

The ‘Territory2’ object has an attribute called “OpportunityAccessLevel” that stores the access a user has to a given opportunity shared with a territory. Now, if you look at our model above, right after we do “Hierarchy calculation and split”, instead of bringing in all the users from “UserTerritory2Association” object, we can bring in only those users where Territory’s OpportunityAccessLevel != “None” using a filter node. And that’s all the change you need to make. In the attached recipe json, I have brought this information down to the opportunity level. 

The predicate can potentially look like the below:

'Territory.users.N_0.UserId' == "$User.Id" || 'Territory.users.N_1.UserId' == "$User.Id" || 'Territory.users.N_2.UserId' == "$User.Id" || 'Territory.users.N_3.UserId' == "$User.Id" || 'Territory.users.N_4.UserId' == "$User.Id" || 'Territory.users.N_5.UserId' == "$User.Id" || 'Territory.users.N_6.UserId' == "$User.Id"

You can adopt a similar approach for Cases, Contacts, and Leads.

Account Team Members and Territory 

What if you want to add another layer of security to your data? Say along with the TM model, you also want to include Account Team Members in the predicate. You can join the team-based visibility model and TM model at the desired grain. At the predicate level, you can use appropriate logical operators and get the security model you need for your data. 

In case you want to understand how team-based security model can be implemented in CRMA, you can refer to this doc.

Some learnings along the way

I wrote this blog with basic use cases where the data is perfect and I did not encounter any issues. This may not be the case with your model. I have captured some learnings below, which I hope will help you.

  1. Check if sharing inheritance works for you, you may not need a custom solution after all.
  2. Try not to create very complex predicates as it affects the dashboard and lens runtime.
  3. Data Volume – association objects can be huge. The large data volumes will not only lead to high recipe runtimes but can also lead to replication failures at times. Try to limit their data by placing appropriate filters, or create a staging dataset for “step 1” and reuse it at the right grain.
  4. Trailhead has a good resource on Best Practices for implementing Territory Management in Salesforce. 
  5. Character limit exceeded – We’re creating multi-value fields for users who can access territory and by extension the account. If in any hierarchy, the total length of characters exceeds 32,000, you’ll get an error. If this happens, you can either split the column into more columns or you can pull in only those records from the ‘UserTerritory2Association’ object where the user has a CRMA license – but use the user fields only in the security predicate. You can also run the SOQL query below and figure out which territories have more than 1685 users assigned to them – then see if you can further split this data. I do not see this issue occurring for flattened_territories, but if it doesn’t, please use join approach to get the hierarchies instead of flatten and split.
SELECT Territory2Id, count(UserId) FROM UserTerritory2Association group by Territory2Id

Note: My solution focuses on getting UserIds for predicates. If you also want to use territory names for filters in dashboards, please tweak the recipe to bring in territory labels.

I hope you enjoyed reading this blog. If you have more questions on it, please do let me know via the comments.

How useful was this post?

Click on a star to rate useful the post is!

Written by

4 thoughts on “Territory Management in CRM Analytics”

  • 1
    Ajinkya Deshmukh on November 29, 2022 Reply

    Dear Darshana,

    Thanks for sharing this.
    It’s a intersting topic to explore and see how it can be implemented.

    Awaiting more details over this


  • 2
    Erik on November 29, 2022 Reply

    Thanks Darshna- we implemented this almost exactly as described earlier this year. It’s been a game changer for us and much easier to administer than the account/opportunity teams. Even with security inheritance- this is still helpful when applying security to external datasets. Thanks for the clear, descriptive examples- this is a must read for our team.

  • 3
    Avinash on January 24, 2023 Reply

    Thanks Darshna for this blog. I am implementing the same for my organization, but I’m confused about “Transform: Flatten and split hierarchy” . Why have you added substr(array_join(Id_flatten_multiField, ‘,’), 1, 18) to get N2 and for other nodes. How can I check my hierarchy as I have 7 Parent territories, and each has child territories within them.
    Please suggest me any idea to get N2,N3… for my hierarchy.

  • 4
    Freedom Software on May 27, 2023 Reply

    Thank you. That was such a detailed and good illustration of territory management. Keep sharing more helpful content.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.