Tracking User Activity in Microsoft Dynamics CRM

Everybody agrees that user adoption is a good thing, and that high user adoption is a key measure of the overall success of a CRM implementation. But there’s less agreement about how to measure user adoption. One way is to use a dashboard to directly display measures of “what users are doing”. For example, suppose you’re interested in tracking how many records are created, over time and by user, for several key record types, such as accounts, contacts, and opportunities. Examine the following figure:

This dashboard displays a set of time series charts in the first row, of total records created over the last six months for accounts, contacts, and opportunities. In the second row, a corresponding chart for each record type shows the distribution of the same records across their owning users.

Dashboards like this can of course be created for any record type – activities, cases, leads, custom entities – and can provide a good high level view of “who’s doing what.” But although visualizations like this are a good start, they don’t go far enough in several ways. For example:

• They provide visualization, but they don’t really provide a measure.
• They don’t scale very well. For example, if you need to measure and compare usage for dozens or more users, they won’t be very effective.

However, you could score various user activities, assigning a certain number of points for different kinds of activities. For example, consider the following table:

Obviously the scheme you use to score users’ activities will differ for every business, but something like this one accords with common business requirements as well as with the way Microsoft Dynamics CRM is intended to be used:

• For activity records, it’s completed ones that matter. Canceled ones don’t count.
• Contacts and accounts – the customer records – are worth more than a completed activity.
• And while opportunities are worth something to create, they’re worth even more to close as won!

Tracking and scoring user activity can be accomplished by combining several customizations and workflows, and an example of this is presented next. The end-goal is a dashboard like the one in the following figure:

The key concept is that a single all-up score is tracked for each user. This is displayed in the Activity Score Leaders chart in the top row. The first two charts in the top row break out users’ scores by activity type, for different time periods: today and the last 30 days. The grid shown underneath the charts is a custom entity, here playing the role of a reminder to users, so they know how different activities are scored.

Setting Up

There are two main customizations required for the user activity scoring approach described here:

• A single custom field is added to the user entity to maintain an all-up “total score” value.
• And because it might be important to report on a user’s score over different time periods, I use a custom entity – Activity Counter Records – to store the detail on every activity contributing to the overall score.

How it Works

To focus on the essentials, let’s implement a simple version of user activity scoring according to these rules:

One workflow will be used for each of these activities, and it will perform the following actions:

• Create an activity counter record with the appropriate values, and associate it with the user performing the activity being scored.
• Update the aggregate score field on the user’s record.

Here are the four workflows as they appear in the process grid:

The following three figures illustrate the workflow that performs the scoring for the completed appointment activity. First, the following figure shows the workflow properties and overall structure:

It’s triggered by Record status changes, and the first If condition checks the status, only proceeding if the appointment is completed. I wrote this to also perform a check on whether the Regarding field contains data. This illustrates a possible requirement that only certain kinds of appointments get scored, such as those associated with account or contact records, etc.

The first action performed by the workflow is to create an activity counter record:

The most important point to note here is that the only way an activity counter record gets created is via these workflows. So in this approach, every (or almost every) field will get filled in automatically, behind the scenes. Records like these are not intended to be created interactively by a user! This accounts for why we can use simple text fields for things like Record Type and Event: since they’re always filled by the workflow, they’ll always be entered correctly.

Also, notice that the Score field is simply set to a fixed value, in this case 2.

The activity counter record is associated with the user with dynamic values on the custom User lookup field. In this example I filled both the Owner and custom User field, although that’s not really required.

The next step is to update the aggregate score field on the user record:

Here, the most important difference is the use of the Increment by operator: that’s how we get a convenient single all-up activity score stored on the user record.

The logic of the other three workflows is essentially the same. Here are a few things to think about and variations you might try:

• If all you need to do is track an aggregate score for each user, you don’t need the custom entity I used (activity counter). But remember, if you don’t have that you lose the time dimension along with activity details. For example, the following figure shows a specific user’s activity counter records from the context of the user form, one of which you’d have without the custom entity:

• Notice that the scoring logic in this approach is embedded in the workflow processes. In that sense it’s a little bit “hardwired”. With a little more work, you could create the ability for a user to configure settings for scoring, so that if you needed to change the score a user receives for an activity, you could update a record rather than the workflow process itself. One way to do this would be to create another custom entity, such as Activity Counter Settings, and have each activity counter record be a child record of a specific settings record. If you take another look at the way I created the activity counter record you’ll see a clue about how this approach can work:
• 

The Counter Setting field is a lookup, which if filled in will associate a counter record with a parent setting record. If you took this approach, then the Score field could be filled with dynamic values rather than directly as in this approach.

Make sure you are getting the most from your CRM system, contact Magenium Solutions today at [email protected]. Connect with us on Twitter.

By Richard Knudson, Magenium Solutions, Chicago Microsoft Dynamics CRM partner