Analyzing Customer Behavior Data with CRM 2015 Rollup Fields

From a system customizer perspective, one of the most intriguing new features in Dynamics CRM 2015 is the addition of two new  field types. Alongside the traditional “simple” fields we can now define our custom attributes to be somewhat more “complex”, by choosing to create them as either calculated fields or rollup fields. These new field type options allow us to create point & click solutions to cover scenarios that would have previously required custom code.

Microsoft haven’t been advertising this feature much in their “what’s new” materials for the CRM 2015 version release, but great blog articles have already been written on the topic, like this one by Peter Majer on the Sonoma Partners blog. There are also articles on Technet for both rollup fields and calculated fields, which provide all the details you need for getting the new feature implemented in you CRM Online or CRM 2015 on-premises organization.

Rather than repeating this documentation, I’ll be exploring the different scenarios where the new complex field types can be leveraged, as well as the considerations and limitations to be aware of. In this first blog post I’ll demonstrate how the rollup fields can give us better insights on how our customers are reacting to email marketing campaigns sent to them.

Summarize Marketing Automation Data for Contacts

When our own CRM Online production organization received the 2015 update, the first use case I had on my mind for the shiny new features was to apply the rollup field functionality to the online marketing data that our ClickDimensions solution has been storing into the CRM database. If you’re not familiar with ClickDimensions, it’s a marketing automation service that allows you to send and track email campaigns, collect website visitor data, publish web forms integrated with CRM fields, configure automatic nurture programs for new leads, among other things (see this page for a detailed feature listing).

What makes ClickDimensions particularly interesting in this case is that all of the behavior data is available inside the CRM application, in the dedicated custom entities contained in the managed CD solution package. For example, a page view transaction from a website with the CD tracking script deployed will be stored as a record for the Page View entity and then linked to either an anonymous visitor or an existing lead or contact record in CRM. Since it’s an “XRM” solution that you install as an add-in to Dynamics CRM, rather than an external marketing database that would be just synchronized with CRM, we can leverage all of the CRM platform functionality for showing and analyzing the data, automating related business processes with workflows and so on.

Now with CRM 2015, the rollup and calculated fields have been added to our marketing automation toolkit. So, what do they allow us to do that would have previously been difficult/expensive to implement? As mentioned, all of the events tracked by ClickDimensions are linked to a CRM contact or lead record when the person performing the actions can be identified from browser cookies, unique id’s in links or submitted form data. If we would now like to view this data from the perspective of an individual contact, the rollup fields provide a way to summarize the data from related entities into fields available directly on the contact form.

Creating Rollup Fields for Contact’s Email Link Clicks

Let’s go ahead and add a new field for the contact entity. Yes, it must be a completely new field as you cannot convert existing standard or custom fields into calculated or rollup fields. Not a problem in our scenario, as we’re building a brand new feature into our CRM organization. The first field will be called Total Clicks and we’ll use it to count the number of related records found, so whole number is a suitable data type for us. By setting the field type as “Rollup” we’re given the option to edit the rollup formula.

In the rollup field definition editor we must now choose the related entity from which we wish to roll the data up to the contact entity. As we’re dealing with email link clicks, these are stored in the Email Event entity in the ClickDimensions solution, so that will be our choice. However, since the entity also contains data about events other than link clicks, we’ll need to add a filter into the related entity section and define that only email events where the field “type” equals “click” should be included in our rollup. As a final step, in the aggregation section we select “count” as the function that will provide the actual value for this new field on the contact record.

Let’s repeat the process for another new field: Latest Click. Instead of counting records, we’ll be looking at the date values on the related records, retrieving the highest date available and storing it into our rollup field. This means the data type should now be “date and time”. The rollup field definition is otherwise the same as with the first field, but in the aggregation section we’ll now have a different list of functions available for this data type. Let’s pick the Max function and apply it on the creation date of the email event records associated with the contact.

Alright, let’s save these fields and get them published! Oh, by the way, did you notice the yellow notification bar down in the rollup field definition editor window? That’s actually very useful information to be aware of once we move on to the next stage of our customization process.

Displaying the Rollup Field Data for Contacts

There are two main places where fields are presented in Dynamics CRM: forms and views. Let’s start from the contact form and include our new rollup fields there. In my scenario, we already have a suitable custom form available for the contact entity, which has been used for summarizing all the ClickDimensions data related to an individual contact record. Notice those fancy embedded charts and subgrids showing the email events for this test contact record of mine? That’s actually something you’ve been able to do already since the CRM 2011 version, so not exactly related to our agenda of the day, but a good reminder about how you can leverage the XRM capabilities of the Dynamics CRM platform to present data in a format that’s easier for end users to consume. (Check out my 10 tips for better CRM user experience for more discussion on that topic.)

I’ve added the Latest Clicks and Total Clicks on the contact form, below the existing chart and subgrid. But wait a minute: why are they empty? Did we do a mistake in the rollup field definitions? Don’t worry, this is exactly the expected result after creating a rollup field. If you took a moment to read that yellow notification bar I mentioned earlier, you’ll know that a mass calculation job was created by the CRM system after you added the rollup fields for the contact entity. Also, since it was by default scheduled to be run 12 hours after the field creation, we’re not going to see the whole magic until the next day in many cases.

If you’d rather validate that the new customizations you’ve created are working as planned, instead of just taking my word for it, open up a contact record form and hover over the rollup field. You’ll see a “recycle” icon that you can click on, after which CRM will perform the rollup calculation for that field, for only the currently selected record.

Once CRM has finished running he mass calculation job, there will be data in place for all the contact records. Analyzing the results of the rollup will require that we have a view available that can show this information. I’ve therefore proceeded with creating a new view for the contact entity, called “ClickDimensions Metrics”, which includes the new fields for Latest Click date and Total Clicks count, alongside other relevant contact profile information.

With this type of customizations we’re now able to show some quite interesting views for the CRM end users. For example, we can sort the views to bring up the contacts who have clicked on tracked email links most recently, or the ones who’ve accumulated the highest total count of clicks. The really interesting part is being able to combine these metrics with other parameters from the CRM database. We can show each account manager a view of their own contacts ranked by the behavioral data collected on email marketing campaigns. We can filter this down by customer lifecycle data and highlight the most active contacts from potential customers who have an open opportunity in CRM.

We could even build a view of a completely different entity instead and still leverage the rollup field data. How about showing a list of open opportunities where a contact from the potential customer account has clicked on a campaign email link during the past 2 weeks? No problem, here’s how you define such a query in Advanced Find:

One question that some of you might have at this point is “could I also do all of this with Microsoft Dynamics Marketing”? Well, as mentioned earlier on, the ability to leverage the rollup fields against marketing campaign results relies on the response data being physically inside the CRM database. Microsoft Dynamics Marketing (MDM) maintains its own separate marketing database and only a subset of the information is synchronized to the CRM database via the MDM Connector running in Azure. The current list of entities synchronized between MDM and CRM only includes website visit records stored into the msdyncrm_webbehavioraldata entity, so rolling up information about email campaign events onto CRM contact records doesn’t appear to be possible yet. Hopefully more data will become available for synchronization in future MDM versions, so that these latest Dynamics CRM platform features can also be put into use with it.