October 14, 2012 – Recently I’ve given presentations at Extreme CRM 2012 and the CRMUG Summit on various aspects of goal management. Judging by the questions I’m asked, the concept of goal criteria is one of the most confusing things about goals, so I thought I’d write a reasonably comprehensive summary on the topic. One interesting application of goal criteria is what I call “dynamic goals”: goals that don’t have a fixed time period, but rather are dynamically calculated using date functions such as today, this week, this month and so forth. So let’s start with a summary of how goal criteria work, and then examine how to use them to construct dynamic goals. In this part of the article I’ll illustrate the concepts with various permutations on a single basic example, using the out of the box Revenue goal metric, on which you can build sales goals based on the opportunity record type. The points I make are all general though, and you should be able to apply them to whatever goal scenarios you need to accommodate.
Let’s begin with a quick review. The general issue we’re discussing is which records “roll up” to determine the goal record’s calculated fields: the Actual and In-progress fields. On the default goal form, these are referred to as Participating Records, and you can see them by clicking the navigation links, as this figure illustrates:
Thanks to my fancy highlighting, you can eyeball this example to see that the sum of the Est. Revenue values on the four participating in-progress records sure enough equals the calculated In-progress (Money) field I’ve got displayed in the form header. (btw, this is an illustration of how you can customize the goal entity. Notice that this is a custom version of the goal form – I called it “Money Goals” as you can see in the form selector) So, what determines which records roll up to a goal? The first two factors are the Goal Metric and the Goal record itself. I’ll review each of these in turn and then drill down on Goal Criteria.
The Goal Metric
The Goal Metric determines the record type and the fields that roll up. For example, the following figure shows the out of the box Revenue goal metric, which specifies that the Opportunity entity’s Est. Revenue (schema name estimatedvalue) field rolls up to the In-progress (Money) field, and the Actual Revenue (actualvalue) field rolls up to Actual (Money) field on the goal record.
You can see from this figure how the goal metric’s Rollup Fields work: the Status (a.k.a State, in this context!), combined with the specified date field, instructs the goal engine which records to include. The first rollup field says, “open opportunities for which the estimated close date is within the goal’s date period should roll up to the In-progress (money) field”; the second says, “won opportunities for which the actual close date is within the goal’s date period should roll up to the actual field on the goal record”.
And another btw: if you’re going to use this goal metric for sales goals, either make the opportunity Est. Close Date field required, or be sure to tell your sales reps that opportunities without a value for it won’t be included in their In-progress values.
Every goal is based on a goal metric, and as we just discussed, the goal metric determines the record type and the fields that roll up, as well as how the status values of the records map to the actual and in-progress calculated fields on the goal record. On the goal record we add more specifics, and the most important determinants of the records that roll up are the Goal Owner and the Time Period fields. The goal in the following figure specifies a “custom period”; the From and To dates are what get compared to the date fields specified in the goal metric’s rollup fields (just discussed). So basically, this is an annual sales goal for 2012:
From a business standpoint, the Goal Owner field identifies the user who’s on the hook for the goal. And if you accept the default values for goal criteria, you’re instructing the goal engine to include all records owned by owner of the goal record. But as you’ll see next, the goal owner really needs to be discussed in conjunction with the options in the Goal Criteria section.
The Goal Criteria
Here’s where it gets interesting. The following figure shows the default – and easiest to understand – values in that section of the goal form:
The first option – Roll Up Only from Child Goals – should be set to Yes for parent goals where the child goals completely determine the values that should be rolled up. My favorite example here is for a sales manager’s goal: if a sales manager’s goal is entirely based on opportunities assigned to her sales reps, this should be set to Yes. But if the sales manager has her own portfolio of accounts and opportunities she owns should also be included (or if the goal is not a parent goal, as in this example), set this to No. But we’ve got enough on our plate without tackling parent and child goals, so for the rest of this article we’ll leave this set to No.
Apart from parent-child goals, there are four basic scenarios, so let’s review them next.
Scenario 1: Only Owner Matters
The Owned by goal owner option tells the goal engine to include all underlying records where the owner is the same as the owner of the goal. (Remember: in this example, the “underlying records” are all opportunities, because that’s specified by the goal metric.) So for this scenario, with the goal criteria configured as in the previous figure, and the goal owner=Richard Knudson, clicking the In-progress (Money) link in the participating records section shows the following (slightly customized) view of associated opportunities:
I’m the owner of the goal record, so the in-progress opportunity records are also all ones that are assigned to me.
Scenario 2: Owner Doesn’t Matter
Next, let’s suppose I want a goal for opportunities based on something other than the owner of the records. For example, suppose I want a goal for all opportunities for accounts in the West territory, regardless of their owner. For this we need to make two changes to the goal criteria: Select the All option for the Record Set for Rollup, and add a rollup query. Here’s what the goal criteria looks like in my example:
The rollup query here should be based on the opportunity entity, and is essentially a specialized advanced find view, filtering the records to include only ones where the potential customer (account) is in the West territory. Here’s what the rollup query looks like in my example:
This rollup query is typical in that it does not filter on date or status values. Why? Because those are already accounted for: the goal metric’s rollup fields specify the status values, and the time period fields on the goal record provide the time filter.
Anyway, as soon as you make changes like that and save the goal, you can see the impact because the participating records will reflect the new criteria (Did you know that? For a long time I thought that you had to recalculate the goal to refresh the participating records, but you don’t.) In my example, here are the in-progress participating records:
Whatever the criteria (product line, specific products, regions, opportunity type…) the key thing here is that the owner of the underlying record does not matter.
Scenario 3: Owner Matters, But So Does Something Else
Next, suppose you want to maintain a filter like the one in the previous scenario, but now you want to filter the rollup records further so that only opportunities owned by the goal owner will roll up. Simply switch the Record Set for Rollup option to Owned by goal owner, like this:
This will give us the subset of opportunities for accounts in the west, which are also owned by the goal owner:
And since the rollup query in this scenario is the most restrictive, the calculated In-progress and Actual values will always be the smallest compared to the other three scenarios.
Scenario 4: Nothing Matters
In some ways, this scenario is the most confusing. Suppose you don’t want to apply any filter: you want an all-up goal for all sales, no matter who owns the opportunity records, what territory they’re in or anything else. The Record Set for Rollup option should be set to All, and you will need an appropriate rollup query:
Here’s what a rollup query for opportunities will look like in these scenarios:
Again, the status values and dates are already accounted for in the goal metric and time period fields, so you don’t need to filter on those. In a scenario like this, the purpose of the rollup query is to broaden the record set, rather than to narrow it down as you normally do in an advanced find query.
And as you’d expect by now, the in-progress and actual values will always be the largest in this scenario, since its rollup query is the least restrictive of the four.
One apparent limitation of goals is the hardwired nature of the time period fields: whether you select the Custom Period or Fiscal Period option, you have to enter actual date values. This is fine for many requirements, but there are situations when you might want to have the actual and in-progress values calculated dynamically with respect to the date fields. To illustrate why dynamic goals might be useful, let’s switch examples and consider the following goal:
This goal is from my production CRM organization, and is based on a goal metric called Page Views. Here’s what the goal metric looks like:
This is an interesting goal metric for a couple of reasons:
- You can see that the single rollup field specifies Page View as the source record type. This custom entity is part of the ClickDimensions (CD) add-on solution for marketing automation and analytics. I’ve written several articles about this excellent application and won’t go into much detail on it here, other than to briefly describe the page view entity. This is one of the CD analytics entities (others are IP Organizations and Visits, for example) and, provided you can drop a little CD-provided tracking script onto your web site, it allows you to perform in-CRM web analytics. I know from experience that it’s easy to under-appreciate the importance of having web analytics inside your CRM, but the big win is that with this approach, analytics are not all anonymous, and there’s immense value in being able to tie web activities like page views, downloads, form submits and the like directly back to CRM contact records. (’nuff said on CD for now; Click here to see all of the articles I’ve ever written on CD.)
- Notice that it’s a count metric, and that the single rollup field – actualinteger – does not specify the status value. For this goal, status doesn’t matter: all I want to know is how many page views there were for a period of time, so I can leave status empty and just use created on as the date field.
So how can a goal with a name like “Daily Page Views, Q4 2012″ have a time period from 10/1/2012 to 12/31/2012? The problem here is that these dates are hardwired: you have to put actual date values in the From and To fields, and cannot use any date functions like Today, This Week and so forth. Daily page views are definitely goal-worthy…but are they worth creating 365 separate goal records? Doubtful!
Fortunately, we can use a Rollup Query to solve the problem. Here’s the Goal Criteria section of this goal’s form:
And here’s what the rollup query looks like:
Cool, right? In this case, the from and to dates don’t determine the page view records that roll up to the goal; these are determined by the more binding constraint in the rollup query, where we can use date functions such as Today. So this is another good example of how you can use a rollup query. Sometimes they narrow the rollup recordset, sometimes they broaden it – obviously, here the rollup query narrows it down to just what we want: today’s page views.
There are plenty of applications you might use dynamic goals for. In particular, they can address one of the limitations of goal management: how time-consuming it can be to create goal records. The page views example provides an extreme example: if you really had to use the hard-wired From and To date fields, it would be so tedious to create 365 individual daily goal records nobody would ever do it! But it’s easy to come up with other, more traditional examples. For example, suppose you want to track goal progress for appointments on a monthly basis. Here’s what a static goal record might look like:
This is the hardwired approach, and would require twelve separate goal records for 2012 (and most other years, also). Alternatively, a dynamic goal might look like this:
In this case, if I want to use this goal throughout 2012, the From date (1/1/2012) and the To date (12/31/2012) would be the way to go, but they aren’t the binding constraint determining which records roll up. In a dynamic goal, that’s the job of the rollup query:
And the rollup query could look like this:
Again, the key point is that using a rollup query gives you the advanced find functionality you need to avoid hard-wiring the date fields.
What are the pros and cons of dynamic goals as opposed to static ones? To shed some light on this, take a look at the Goals tab on the System Settings dialog (in the web client, click Settings, then Administration, then System Settings):
The first of these two options – the roll-up expiration time – tells CRM to stop recalculating goals 30 days after the date in the goal’s To field. So in one sense, you can think of the (hardwired) From and To date fields as simply specifying how long the goal should roll up for – not necessarily as filtering on the underlying record’s date fields! Again: you can use them to provide the date filter, but with a dynamic query you have an alternative.
The second – the roll-up frequency – specifies how often the goal engine will automatically recalculate the in-progress and actual fields on the goal record (or “roll up”, for short). 24 is the minimum value. You can manually recalculate as often as you want to, but the system will automatically recalculate only daily.
So with dynamic goals, what we gain in flexibility we lose in structure: effectively, they never stop rolling up. With 12 static monthly goals, you’d spend more time creating the goal records, but you’d have the historical values of each of those goal records provided by the default goal management structure. With one dynamic goal, you’d always have a snapshot referring to goal progress within the current month: easier, more flexible, but without the built in historical reporting the static approach naturally provides.
There are a couple of ways you could get some historical reporting even if you need to use dynamic queries:
- If you turn on auditing for the goal entity, you’ll have a record of every time the calculated fields change, and what the values were before and after. So if you stick with the default value of 24 for the roll-up recurrence frequency, you’d have at least daily data points for the actual and in-progress calculated fields. (At least, because you’ll probably do manual recalcs from time to time.) Unfortunately, there’s no out of the box way to perform reporting on audit history records (!) so you may have to be satisfied with gazing at the audit trail for now.
- If you need robust reporting, you could use a workflow or a plugin to write out records to a custom entity.
So besides out of the box historical reporting what do you lose with the dynamic goal approach? The only other thing I can think of off the top of my head is that the Today’s Target field isn’t well defined with a dynamic goal. But unless the Today’s Target value is an important concept for your goal scenarios, this might not be much of a loss.