(Note: This material was originally presented by Chief Customer Officer Matt Wittemann during ClickDimensions' 2015 Idea Conference where you can find a recording of his session.)

As a marketer, it's important to understand the level of engagement your audience has with your email.

Engagement really means: how much does your audience interact with your emails? How often do they read them? How often do they click on your calls to action?

Measuring engagement provides a few key benefits:

  • It lets you know how you are doing. How relevant is your messaging? How effective are your calls to action?
  • It lets you see how different parts of your audience respond to your messaging. Are some people more likely to be engaged? Are there parts of your audience you are losing?
  • It provides a way to segment your audience for re-engagement efforts

Here's an example of looking at engagement data overlaid on a map:

With the powerful combination of Dynamics CRM and ClickDimensions, measuring audience engagement is easy. Let me show you how.

Where is Open and Click activity recorded?

First, let's take a look at where you can find open and click data for the recipients of your emails. If you are using ClickDimensions for your email marketing, this information is stored in a "Sent Email" record for each Contact you send an email to. There is a new "Sent Email" record created for every email, and we automatically summarize the Contact's interactions here for you.

Let's take a look at my Contact record.

Here I can see in list format how many emails I have received, how many times I have opened them, clicked a link in them, etc.

This is great information to have if I want to drill into each Contact. But let's take this data a step further in our analysis.

Rollup Fields

CRM 2015 introduced the concept of a "rollup field". This is a custom field you can add to the Contact record in CRM and use it to rollup the values from records related to the Contact.

I have added three rollup fields to the Contact entity:

  • Sent Emails
  • Opened Emails
  • Clicked Emails

Let's look at how the rollup field for Sent Emails is configured by clicking on the Edit button:

I did something similar to create rollup fields for the Opened Emails and Clicked Emails. However, in these fields, I am only interested in counting them if they have opened or clicked. I am not summing up the total number of opens or clicks – in other words, if the recipient opened the email 5 times, I am only counting it once. This is a decision I made on how I want to calculate engagement. My end goal is that I want to see the percentage of emails that I send that are getting opened or clicked at least once by a contact. I could sum the numbers if, for example, I wanted to measure and compare Contacts to see who is the most active "clicker", but in this case, I am just counting if they have at least opened the email once or clicked on one link. Here's how I did that:

I added a filter to only count the Sent Email records for a Contact if the Opens > 0. So the "Opened Emails" and "Clicked Emails" rollup fields use this filter:

Calculated Fields

CRM also has a great function to make calculated fields where I can take the values of two fields and perform math on them. I am using this function to show me the percentage of emails that the Contact has received and which they have opened or clicked. So I added two fields for this:

  • Open Rate (%)
  • Click Rate (%)

Here's the calculation I am making in this field:

Once I have added these rollup fields and calculated fields, CRM handles the process of populating them. I don't have to create any jobs or anything. CRM does that for me. The rollups will start within 12 hours of when I created the fields. (There is a way to get this to run sooner, but we won't go into that. I'll assume you're patient enough to wait for CRM to fill these fields in for you!)

Adding the fields to the form

After I created these fields, I figured this might be interesting information for our salespeople to see when they are talking to a customer, so I added them to the marketing area of the Contact form:

Adding the new fields to a view

Now here's where we really start to get interesting. Let's add our new columns to a view. In fact, I've created a new view called "Email Engagement". I've filtered this view to show only active contacts where the "Emails Sent" field is greater than 0. I also added columns from the Contact's Parent Account (Industry, City, Country, Relationship Type) as well as the Contact's Originating Lead (Lead Source and Source Campaign) to make it easier to analyze data about our engagement levels:

Once I have the view created, it's easy to create some charts so I can slice and dice my engagement rates and see if any patterns emerge. For example, I've created a chart called Open Rate by Industry:

From this chart, I can see that we've got our best readership engagement in the "Funeral Home" industry! Who knew! That's an interesting insight. Maybe we can do some more targeted marketing to that industry and develop some content that would be of interest to them.

I can also see our least engaged readership is in the "Distribution: Consumer Packaged Goods" industry. Sounds like a good opportunity to improve our messaging there and do some research and experimentation to see how we can become more relevant to that industry.

Excel: The Best BI Tool

Once you have the data in CRM, you can use the best BI tool to analyze the data: Excel. I say it's the best because it is often the most familiar tool to marketers. You can simply export your views to Excel, or you can get a little more advanced with tools like Power Query:


Getting your data into Excel is the first step, and from there, you can do all sorts of great things. I've dropped a few columns into a Pivot Table to group open and click rates by country:

Pivot Tables from Power Query Data

Bing Maps in Excel from Pivot Table Data

These are great because they are interactive. Zoom in/out, click, etc. They also provide interesting visuals that may shows correlations or outliers that you don't see by scanning the data in table format.

People Graphs in Excel from Pivot Table Data


This also opens up new possibilities for a re-engagement campaign. In addition to slicing and dicing our engagement by industry, region, or relationship type, we can plan to target individuals who have not opened or clicked at high levels. Maybe there is something we can do to re-capture their interest.

To start, I have created a new dynamic marketing list.

The criteria I've used are as follows:

This gives me a good list to start with. I know we've sent them at least 1 email, but so far they have clicked less than 10% of the time. Yet, they still have not unsubscribed (otherwise, we would have automatically set the "Do not allow Bulk Emails" to "Do Not Allow"!)

Tips for Re-Engagement

  1. Clean up your lists
    1. Measuring engagement is the first step. Some parts of your audience may no longer be appropriate targets of your marketing (aged out, not reachable, inactive for years, etc.).
    2. Don't re-engage with really old inactives – they'll drive up your rate of spam complaints.
  2. Experiment
    1. Which campaigns have the highest engagement rates and why?
    2. How can you replicate this success?
  3. Be Persistent
    1. Re-engagement often takes more than 1 email. Successful re-engagement campaigns are often a series of emails.
  4. Give your audience a reason to re-engage
    1. What's in it for them? (Special offers, new products/services)
    2. Give prominence to your call to action

Some questions about Measuring Engagement in this Manner

Q: We want to maintain calculated percentages on the Email Send record. Can I add rollup fields and calculated fields to ClickDimensions entities like the Email Send?
A: Yes, you can add fields like these to the Email Send. We already put the sum of opens, bounces, clicks, etc., on the Email Send record, though they are not displayed on the form, so you could add your own calculated field to calculate percentages.

Q: Are rollup fields and calculated fields available in CRM 2011 or CRM 2013?
A: No, Microsoft introduced these field types in CRM 2015.

Q: Isn't there a limit on the number of records you can bring into Excel?
A: Microsoft increased the limit on the number of rows that can be exported to Excel using the "Export to Excel" function in CRM 2015. It now allows up to 100,000 rows to be exported for analysis. PowerQuery uses CRM's OData endpoint to retrieve data. While the OData endpoint does not have a limit, it downloads 50 rows at a time, meaning it can take a long time to get a large number of rows. I recommend filtering your queries in order to return targeted sets of data, rather than, for example, "all contacts". Read more about filtering the OData queries here.