Microsoft CRM delivers a very powerful set of tools that enable users at both an individual and management level to track – and effectively manage goals. The purpose of this post isn’t to describe the architecture and out of the box functionality that exists with respect to goals in Microsoft CRM – my goal (please excuse the pun), is to focus on one specific element of functionality – Today’s Target.
The Today’s Target field is the mechanism that CRM uses to tell a user if they are on track to meet their goal on time – it serves as an indicator for where I should be as of today – and this can be a VERY important tool when managing a goal. In Microsoft CRM, the logic for calculating this number is relatively simple, because it treats every day in the goal as equal – it evaluates the:
- Total number of days in the goal period
- The current day number in the goal period
With these two pieces of information, Microsoft CRM will calculate the percentage of days within the goal period that have been completed – and then multiply it by the goal target.
For example – assume we create a goal to keep track of an individual’s sales for the month of March.
- We set the dates to start on 3/1/15 and end on 3/31/15
- We set the goal target to $1000
And let’s assume that on March 10th, we’d like to see how we’re doing with respect to our goal – are we on track? Given this scenario, CRM will determine Today’s Target by:
- Calculating that there are 31 days in the goal period
- Determine that March 10th is the 10th day of the goal period
- As of today, I should have completed 32% (10 divided by 31) of my goal
- And therefore, I should have $322 in sales (32% of $1000)
- So, if I have $320 in sales on March 10th, it looks like I’m doing great – and that I’m well on my way to meeting my goal.
And in certain scenarios, this calculation may be sufficient. But what happens when you introduce the concept of seasonality – a scenario that dictates that every day is NOT equal in terms of it’s contribution to goal completion. What if 80% of March’s sales happen in the first ten days?
Unfortunately, out of the box Goals in Microsoft CRM do not account for this. In this scenario (where 80% of the sales happen in the first ten days), CRM would show me that I’d only need to have $322 in sales to meet my goal, when in reality, I’d really need to have $800 in sales by March 10th to meet my goal! What’s a salesperson to do?! As a Microsoft CRM consultant, I ran into this exact problem in a recent project. Fortunately, with some creativity (and fun), we have addressed this issue using out of the box tools – and that’s exactly what we’re going to walk you through below.
How To Implement Seasonal Goal Target Weights in Microsoft CRM
Before you get started, you have to understand the type of goal you’re tracking – is it a currency, sum, or count? Then you have to understand the mechanism that you are going to use as a weight. And lastly, you have to be in a position to ascribe a value to each day in the goal period.
Determining Your Goal Weight
In my example, we are using company wide sales numbers as a proxy for determining the estimated weight that each goal within the period makes up. So, to start, we exported sales data from our ERP system into a spreadsheet, where each row contained a date and a sales amount. We then added a column to calculate the running total, % of sales that each date represented.
Create a Goal Date Entity
Next, I create a custom entity in Microsoft CRM named Goal Date to represent the relative weight of each day in the Goal – this record will have a two key fields on it:
- Percentage: This is taken from the Running Total % column in the spreadsheet – this represents the percentage of the goal that should be completed by the date that the record represents.
- Next Date: This is a self-referencing lookup – we use this in the workflow to sequence the following date (so the March 18th record would reference the March 19th record in this lookup). We stage this information on an Excel spreadsheet during import.
Then, we save this spreadsheet as a CSV file so that we can import it into CRM – and create the Date Weight records.
Create a Trigger Record
This involves a very handy trick I read about a few years back – while Microsoft CRM does NOT technically have out of the box functionality that allows for a non-programmatic way to schedule a workflow, we can use the Bulk Record Deletion, in combination with triggering a workflow to fire whenever a record gets Deleted, as a workaround. We will employ this trick to sequence our dates – and by this, I mean, ensuring we are associating the current date with the goal, and therefore, applying the correct weight.
The only important field that we add to the Trigger record is a lookup to the Goal entity. NOTE: We also have a lookup on the Goal that references the Trigger.
Customizing The Goal Entity
The last step is to create, in this case, three custom fields on the Goal entity:
- The first is a lookup to the Goal Date (Current Date Weight) record
- The second is a Decimal field named Today’s Weighted Percentage, which stores the Weight Percentage from the Goal Date record
- The third stores the Today’s Weighted Target – this is a calculated field that multiples the Target by Today’s Weighted Percentage (divided by 100, since we imported the percentage as a whole number, not a decimal).
So, let’s bring all of this together. Before we build any of the workflows, we first create a Bulk Record Deletion job against the Trigger entity, without any filters (meaning, we will delete EVERY trigger record) to run once a day, at 3am in the morning.
- The first workflow, which fires on the Creation of a Goal, first waits until the first Date Weight field is populated – it then Creates a Trigger record, and associates the lookup field on the Trigger record with the Goal that was just created.
- The second workflow, which fires whenever the Goal Date field on the Goal gets updated
- Sets the Today’s Weighted Percentage field from the Goal Date Record
- The final workflow, which fires on the deletion of a Trigger
- Updates the Goal referenced on the Trigger with the value of the NEXT Date field from the Goal Date record that was currently populated on the Goal. So, at midnight on March 10th, the Goal Date on the Goal will change to the March 11th record (NOTE: This will also trigger workflow number 2)
- If the new Goal Date contains data (meaning, it wasn’t the last date of the goal), then our workflow creates a NEW trigger record and associates it with the goal, thus repeating the process.
- The Today’s Weighted Target will calculate accordingly.
And there you have it – we have implemented a system for calculating an accurate value for Today’s Weighted Target. And with some CRM charting fun, you can replace the out of the box charts with our custom fields – and get some great visualizations, like the one below:
You’ll note that we are tracking three goals on this individual Goal record – we’ll save that for a future post, when we’ll covering managing tiered goals.
We’ll, that’s it for now – if you’d looking for some help with your Microsoft CRM Online or Microsoft CRM 2011/13/15 implementation, we’re here to help! Schedule your Microsoft Dynamics CRM consultation today!