In this post I’m going to run through a real world scenario of getting the sales goals of a sales force into CRM, utilising some of CRM’s cool new data import features…
Configure Your Goal
First step is to configure your goal. A colleague and friend of mine, Karl Iuel at Avanade, suggests Goal Management is better thought of as KPI Tracking and I really like that description. As you go through the process of configuring your Goals you will see CRM’s goal design is less about goals/targets and more about the tracking of actuals.
So, lets setup a KPI that we want to track and load a target against that KPI.
For this scenario we want to the track “the sales revenue booked each month by each rep”. We will determine this from the Actual Revenue Amount on Won Opportunities based on Actual Close Date and Opportunity Owner.
1. Navigate to Sales –> Goal Metrics, and click New:
2. Enter “Sales Revenue” as the Name, select “Amount” as the Metric Type, and then click Save:
3. The Rollup Fields sub-grid will now be enabled. Click in that grid to switch the Ribbon context and then click Add New Rollup Field:
4. Define the Rollup Field as shown below. What we’re doing here is asking CRM to keep a running total of the Actual Revenue field on Won Opportunities, using the Actual Close Date to sum by fiscal period.
5. Save and Close the Rollup Field form and you will be returned to the Goal Metric form. You’ll note that the Goal Metric record is really just a bucket for a collection of Rollup Fields. The KPI definition is really at the Rollup Field level.
At this point we could add a second Rollup Field to provide an “In Progress” measure – e.g. a KPI of the Est. Value sitting on Open Opportunities – which would obviously useful when comparing sold vs target as it provides visibility of what the rep has the pipeline to close that gap. For this scenario we will skip that step.
6. Ok, we’ve actually configured enough now to be able to start loading the individual Goal records. We’ve informed CRM that we want to track Sales Revenue as a KPI and we’ve indicated which Opportunity records should be included and which field the revenue should be derived from:
Note: In some scenarios you will need to take a few extra steps to further refine which records should be included. i.e. on the Goal record you can chose whether for each user only their own records should be counted (the default) or whether all records regardless of owner should be counted towards the user’s Goal (not sure when you would go with this option).
Sadly, if you want to filter based on the user in another way – e.g. based on who created or last modified the record – I think you’re out of luck.
Another option you have is to define a Rollup Query – essentially an Advanced Find set of criteria – to only count records that meet certain criteria – e.g. if you wanted to set regional goals for each user you could create a rollup query for each region, and as you enter the goal for the Southern region you would specify the Southern region rollup query should be applied, which instructs CRM to only count opportunities related to the Southern region.
For our scenario we want all Won Opportunities based on owner so we don’t need anything else.
Now, as much as we have defined the Goal Metric we want CRM is only going to keep track of the metric for those periods and reps that we have entered Goals for. As a consultant I’m used to saying “why bother setting a target if you’re not going to measure the actuals” – well, with CRM now the reverse is also true – CRM won’t bother tracking an actual unless you bother to set a target. And its it at this point that you’ll start to recognise the need for a bit of (ick!) data entry – or (even more ick!) data migration. How come? Well, lets say you have 200 reps and they each have quarterly sales targets, that means 800 Goal records need to be entered into CRM. I smell an ISV opportunity! But hey it’s easier than that, let’s make use of our good old friend Excel to help us out.
7. Navigate to Sales –> Goals, and select from the Import Data option on the Ribbon… Download Template for Import:
8. You will be prompted to download an XML file. Save this to your desktop and then open it in Excel.
Ok, now I think this is pretty cool, CRM has gone and dynamically created an XML file based on your meta data giving you a template data import file with a bunch of validation and comments built in.
9. Now we can go ahead and populate the fields we need to. I suggest you first go back to CRM and manually create one Goal record so you get a feel for the fields you need to populate. For our scenario we need to populate these fields /excel columns:
You’re likely to have this data in Excel somewhere or in your ERP system where you can easily get it out to Excel, so you should be able to easily populate this template with a bit of creative copy and pasting.
For something like the Name column I suggest you use Excel’s Concatenate function to come up with a unique value for each record. Not essential, but good practice. Here’s what I’ve used:
10. Save your Excel file (you can stick with the XML format).
11. Return to CRM and the Goals screen, and click again on the Import Data option on the Ribbon, but this time select … Import Data:
12. The Import Wizard will launch. At the prompt, browse to your Excel file and select it. Because you are using a CRM Excel template the file will auto-map and you will be taken straight to the Map Fields screen. Change the view option from the picklist in the top right corner to see just the unmapped fields:
In my case I missed a field. I didn’t keep the “Roll Up Only from Child Goals” field and it is a bit field so CRM needs to know how to translate my blank value. I was happy with the default but CRM doesn’t auto-map blank values to the default so I will do that manually, selecting the correct field from the dropdown…
… and then mapping the source value of (empty) to the value “No”.
13. Once you’ve addressed any mapping gaps click through the rest of the wizard until you get to the final confirmation page:
14. And you are done. Your quarterly targets are loaded for each Rep, and CRM will track sales revenue against those targets. Now go check out the dashboards, charts and reports now available to your users. Cool stuff.
GT // CRM 