Goal Metrics and Rollup Fields in Dynamics 365 Sales (CRM)
For any goal to have meaning, it must be clearly defined. Imagine if I determined one of my goals to be “I want my sales team to do stuff.” How would this be measured? Would the sales team know how to accomplish team or individual goals? Nope.
We need to bring clarity to this goal and probably break it down into several that could rollup into parent, or more organization-related, goals. A second version of the goal may be rewritten to “I want my sales team to each win a minimum of 12 Opportunities per quarter.” We could add another to fill out the “do stuff” by writing “I want my sales team to each have a minimum of two customer-communication activities per client per week.” Now we are getting clarity. Goal Metrics and Rollup Fields are the foundation of defining that clarity.
This blog is part of a series that will show you how to understand goals and goal management in Dynamics 365 Sales, part of Microsoft Dynamics 365 CRM solutions (otherwise known as CE). It does not emphasize how to set everything up in your system as that can be viewed here.
For this blog I have prepared some .csv files which you can import into a trial environment if desired to see how it all fits together. Unfortunately, we cannot import Rollup Fields which are children of Goal Metrics, so we will have to manually create Rollup Fields after the Goal Metrics are imported. I have provided an .xlsx file for Rollup Fields that coincide with Goal Metrics below.
- Import Goal Metrics (download csv file)
- Manually enter Rollup Fields for Goal Metrics (download csv file)
After step 1, the Goal Metrics will be available to enter Rollup Fields. Just open the Goal Metric, navigate to the Rollup Fields tab, and begin entering them.
For example, after the Number of Leads Goal Metric has been imported, you would enter the following Rollup Fields for it:
A Goal Metric is a parent to a Goal and must be created first in Sales Hub. A Sales Manager wants to create a Goal so navigates to Goals in the Sales Hub but quickly realizes that the action cannot be completed until a Goal Metric is created. A Goal record is dependent on or looks up to the Goal Metric record as demonstrated in the image below.
Imagine you are holding an average ruler in your hand. On one side it has 1/16th, 1/8th, 1/4th, ½ , and one-inch marks. On the other side, it has one millimeter, .5 centimeter, and one centimeter marks. This allows you to measure the same thing in different ways. Goal Metrics, combined with Rollup Fields, accomplish the same objective.
A Goal Metric is a unit of measure that quantifies a goal; it is the ruler by which an organization creates and uses to determine if a goal has been met. Most units of measure are already determined; we have kilometers, ounces, and 100 pennies to a dollar. But for Sales Hub Goals, the Goals administrator dictates what is and is not a Goal.
The Goal Metric fields set the stage for the type of Goal to be achieved; it defines the ruler. Will the units of measure be 1, 2, 3, and 4, or will they be $1.00, $2.00, $3.00, or $4.00 or maybe even 1.00, 2.00, 3.00, or 4.00?
Metric Type and Amount Data Type
There are two choices for Metric Type field: Amount or Count. If Amount is selected, Amount Data Type becomes required. Here are the possible combinations for these fields:
|Metric Type||Amount Data Type|
- Count is for literally counting the number of records like the number of Accounts, Contacts, Opportunities, or activities.
- Amount is for adding up money, whole numbers, or partial numbers.
- Money could be used to calculate the Estimated Revenue for Open Opportunities or the Amount of Products sold.
- Decimal could be used to calculate partial numbers like weight, number of products, or ratings.
- Integer could be used to calculate whole numbers like entered days, number of employees, or number of children.
Track Stretch Target
When you want to track goals beyond the main, check this box. If checked, you can view the results on the Targets tab on the Goal record:
What is a rollup anyway? It is a summary number, and it calculates a final number from a collection of records. For example, if a salesperson closed four Opportunities in the last month, the rollup or summary field would add up the Actual Revenue for those four records and “rollup” the total into the Goal.
Rollup Fields provide more clarity to the ruler (Goal Metric) created earlier. The Goal Metric determined 1 or $1.00 (the centimeters or inches) whereas the Rollup Field dictates 1 of what or $1.00 of what (measuring a picture frame or length of yarn), essentially defining the entity in Dynamics 365.
The combination of Goal Metric and Rollup Field defines the unit of measure like:
- Count the number of Phone Call records.
- Sum the total Actual Revenue of Won Opportunities.
- Sum the total weight of Products sold.
- Sum the total number of days record was in a pending status.
You will find the Rollup Fields on the Rollup Fields tab on the Goal Metric form:
There are three different types of Rollup Fields types: Actual, In-progress, and Custom, and for each, the goal manager of the organization determines the definition.
Actual means that it actually happened; it is not some future state. It is a won Opportunity, a completed Task or Phone Call, or an Order fulfilled.
In-progress means that it is not yet completed, that some work still needs to take place. It could be a new Lead that has not yet been converted and qualified.
Custom means it can be anything; it can be a secondary actual or maybe a negative target like lost Opportunities (that do not reduce the Actual Revenue).
Potential Example of a Custom Rollup Field
Custom Rollup Field Results
IMPORTANT: One Goal Metric may only have a total of three Rollup Fields and only one of each Rollup Field Type. For example, if one already exists, you will not be able to create a second Rollup Field of Rollup Field Type In-Progress (Money). If an In-Progress, Actual, and Custom Rollup Field already exist, the + New Rollup Field button disappears on the Rollup Attributes sub grid on the Rollup Fields tab on the Goal Metric form.
This determines if the Rollup will be an Actual, In-progress, or custom goal.
IMPORTANT: The system does not prevent anyone from selecting Actual as the Rollup Field and then defining In-Progress criteria like Open, Draft, etc., so it is critical to build Rollup Fields consistently that follow business logic and understanding.
Source Record Type
This determines the entity involved in the Rollup, and it is the first definition of anything specific. It is the item that is being measured.
Source Record Type State and Status
If desired, the Goals can be limited to only a certain set of records, based on State and Status. In many cases, these fields will narrow the results to the desired Goal Metric so Rollup Queries are not necessary. For example, if you wanted to set a goal for number of Phone Calls where State = Completed and Status = Made, you could. No Rollup Query necessary.
This directly reflects the selection of the Source Record Type field.
Since Goals are strictly set to dates, a date field needs to be selected to match up with the Goal Fiscal Period or To and From.
IMPORTANT: When considering Activities, not all Activities have the same date fields and not all fields are initially exposed on forms so would not be filled out as expected.
We cannot build Goals directly against Activities; we must build them against individual activity types like Email, Phone Call, or Appointment. Notice below that Activity is not represented on a Rollup Field Source Record Type:
Outside of having to build separate Goal Metrics for the individual activity types, we must understand there are differences in the date fields. Review this table below:
|Actual End||Actual End||Actual End|
|Actual Start||Actual Start||Actual Start|
|Created On||Created On||Date Created|
|Date Sent||Date Sent|
|Delay email processing until||Delay activity processing until|
|Due Date||Due||Due Date|
|Email Reminder Expiry Time|
|Last On Hold Time||Last On Hold Time||Last On Hold Time|
|Last Opened Time|
|Modified On||Modified On||Last Updated|
|Record Created On||Record Created On|
|Sort Date||Sort Date||Sort Date|
|Start Date||Start Date||Start Date|
|Date Delivery Last Attempted|
The main point is to have a clear understanding of all data fields that will affect the outcome of the measured goals.
We have learned quite a bit covering just two main elements of goal management. There are many essential points, but below I have listed some of the more important key learnings from this blog.
Goal Metric Dictates the Units of Measure
Remember, Goal Metric is the ruler that you build; it defines the tick marks on the ruler like count or amount (Money, Decimal, or Integer). All Rollup Fields and Goals that utilize the Goal Metric will follow suit.
Goal Metric Must Have at Least one Rollup Field
In theory, you can have a Goal Metric that does not contain a Rollup Field, but you would quickly discover that the Goal would measure nothing at all and provide no valuable data.
Goal Metric Can Only Have one Rollup Field of Each Rollup Field Type
Whether In-Progress, Actual, or Custom, the Goal Metric can only have one type of each.
Goal Metric Can Have a Max of Three Rollup Fields
It is not required to have all three Rollup Field types created for a Goal Metric, but we can only have a max of three.
A Goal That Has no Rollup Queries AND Has a Goal Metric That Has no Rollup Fields Will Not Provide Results
Goal Metric has no Rollup Fields:
Goal has no Rollup Queries:
Rollup Field Dictates What is Being Measured
Rollup Field is the item that is being measured by the ruler (Goal Metric). It is the number of open Leads or amount of estimated revenue for open Opportunities.
Rollup Field Date Field Coincides With Goal Period Type (Fiscal Period, Custom Period)
We are not there yet, but when a Goal is developed, a Goal Period Type must be set.
The Rollup Field Date Field must be set.
When it comes to tracking goals, if the record were closed on 4/1/2015, it would not be included in the Total Sales Team Goal Q1 results.
With Goal Metrics and Rollup Fields, the ruler has been created and defined, and we know what we are measuring. In the next blog, we will understand how to build a more precise measuring instrument (Rollup Queries), build out our actual Goals, and understand the goal data.
So far we have only covered foundational elements of Goals. In the next blog, we will tackle Rollup Queries, Goals, and actual Goal Management. If you have any questions about Dynamics 365 for Sales, please contact us.
41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 CRM apps. Includes interactions with Power Apps and Power Automate!