How to Calculate Account Engagement Score in Dynamics 365

In sales and CRM, measuring account engagement is crucial to identifying customer interactions and potential sales opportunities. In this guide, we will walk through how to calculate the engagement score for an account in Microsoft Dynamics 365 using activities such as emails, phone calls, and appointments.

What is an Engagement Score?

An engagement score represents the level of interaction between an account and the sales team. It can be calculated based on the number of completed activities, such as:

• Emails sent to contacts in the account
• Phone calls made to contacts in the account
• Appointments with contacts in the account

Steps to Calculate Engagement Score

We will implement a solution using a plugin in Dynamics 365 that:

1. Retrieves all contacts related to an account
2. Counts the number of relevant activities (emails, calls, appointments) for those contacts
3. Stores the calculated values in custom fields on the Account entity

Data Structure

We create the following custom fields in the Account table:

• hx_appointmentcount (Whole Number) - Counts completed or scheduled appointments.
• hx_outboundemailcount (Whole Number) - Counts emails sent by the sales team.
• hx_phonecallcount (Whole Number) - Counts completed phone calls.

Plugin Code: Calculate Engagement Score

using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
namespace HelloX.Account.Plugins
{
    /// <summary>
    /// Plugin to aggregate activity counts for all Contacts of a given Account.
    /// Triggered by the unbound action "hx_AccountCountContactActivities".
    /// 
    /// Input Parameters:
    ///   - AccountId (string): The GUID of the account as a string.
    ///   - StartTime (DateTime, optional): If not provided or less than CRM minimum, then no lower bound.
    ///   - EndTime (DateTime, optional): If not provided or less than CRM minimum, then no upper bound.
    ///     If only one is provided, the other is set accordingly.
    ///   - If both are empty (defaulted to DateTime.MinValue), then no date filter is applied.
    /// 
    /// Activity counts are determined by:
    ///   - Appointments: Count if the appointment's requiredattendees party list contains the contact
    ///                   and statecode is Completed (1) or Scheduled (3).
    ///   - Outbound Emails: Count if the email's "to" party list contains the contact and statuscode equals Sent (3).
    ///   - Phone Calls: Count if the phone call's "to" party list contains the contact,
    ///                  and statecode equals Completed (1) and statuscode equals Made (2).
    /// 
    /// The plugin aggregates these counts from all Contacts associated with the Account and updates the Account's
    /// hx_appointmentcount, hx_outboundemailcount, and hx_phonecallcount fields.
    /// </summary>
    public class CountContactActivities : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
            try
            {
                // Verify the action name.
                if (!context.MessageName.Equals("hx_AccountCountContactActivities", StringComparison.OrdinalIgnoreCase))
                {
                    tracingService.Trace("Not registered for hx_AccountCountContactActivities. Exiting.");
                    return;
                }
                // Retrieve AccountId from InputParameters as string, then convert to Guid.
                if (!context.InputParameters.Contains("AccountId") || !(context.InputParameters["AccountId"] is string))
                {
                    throw new InvalidPluginExecutionException("AccountId parameter is missing or invalid.");
                }
                string accountIdString = (string)context.InputParameters["AccountId"];
                Guid accountId;
                if (!Guid.TryParse(accountIdString, out accountId))
                {
                    throw new InvalidPluginExecutionException("AccountId parameter could not be parsed into a Guid.");
                }
                tracingService.Trace("Processing AccountId: {0}", accountId);
                // Define CRM minimum supported date.
                DateTime crmMinDate = new DateTime(1753, 1, 1);
                // Process StartTime and EndTime from InputParameters.
                DateTime? startTime = null;
                DateTime? endTime = null;
                if (context.InputParameters.Contains("StartTime") && context.InputParameters["StartTime"] is DateTime)
                {
                    DateTime tempStart = (DateTime)context.InputParameters["StartTime"];
                    if (tempStart >= crmMinDate)
                        startTime = tempStart;
                }
                if (context.InputParameters.Contains("EndTime") && context.InputParameters["EndTime"] is DateTime)
                {
                    DateTime tempEnd = (DateTime)context.InputParameters["EndTime"];
                    if (tempEnd >= crmMinDate)
                        endTime = tempEnd;
                }
                tracingService.Trace("StartTime: {0}, EndTime: {1}", startTime, endTime);
                // Retrieve all Contacts where parentcustomerid equals the AccountId.
                QueryExpression contactQuery = new QueryExpression("contact")
                {
                    ColumnSet = new ColumnSet("contactid")
                };
                contactQuery.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, accountId);
                EntityCollection contactCollection = service.RetrieveMultiple(contactQuery);
                tracingService.Trace("Found {0} contacts for Account {1}.", contactCollection.Entities.Count, accountId);
                if (contactCollection.Entities.Count == 0)
                {
                    tracingService.Trace("No contacts found. Exiting plugin.");
                    return;
                }
                // Build a HashSet of Contact IDs.
                HashSet<Guid> contactIds = new HashSet<Guid>();
                foreach (Entity contact in contactCollection.Entities)
                {
                    contactIds.Add(contact.Id);
                }
                int totalAppointments = 0;
                int totalOutboundEmails = 0;
                int totalPhoneCalls = 0;
                // ----------------- Retrieve Appointments in Bulk -----------------
                QueryExpression appointmentQuery = new QueryExpression("appointment")
                {
                    ColumnSet = new ColumnSet("activityid", "createdon", "requiredattendees")
                };
                // Apply date filter if provided.
                if (startTime.HasValue && endTime.HasValue)
                {
                    appointmentQuery.Criteria.AddCondition("createdon", ConditionOperator.Between, new object[] { startTime.Value, endTime.Value });
                }
                else if (startTime.HasValue)
                {
                    appointmentQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrAfter, startTime.Value);
                    appointmentQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, DateTime.UtcNow);
                }
                else if (endTime.HasValue)
                {
                    appointmentQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, endTime.Value);
                }
                // Add filter for appointments: statecode in [1 (Completed), 3 (Scheduled)]
                appointmentQuery.Criteria.AddCondition("statecode", ConditionOperator.In, new object[] { 1, 3 });
                EntityCollection appointments = service.RetrieveMultiple(appointmentQuery);
                tracingService.Trace("Retrieved {0} total appointments.", appointments.Entities.Count);
                foreach (Entity app in appointments.Entities)
                {
                    if (app.Contains("requiredattendees") && app["requiredattendees"] is EntityCollection attendees)
                    {
                        foreach (Entity attendee in attendees.Entities)
                        {
                            if (attendee.Contains("partyid") && ((EntityReference)attendee["partyid"]).Id != Guid.Empty)
                            {
                                Guid partyId = ((EntityReference)attendee["partyid"]).Id;
                                if (contactIds.Contains(partyId))
                                {
                                    totalAppointments++;
                                    break;
                                }
                            }
                        }
                    }
                }
                // ----------------- Retrieve Outbound Emails in Bulk -----------------
                QueryExpression emailQuery = new QueryExpression("email")
                {
                    ColumnSet = new ColumnSet("activityid", "createdon", "to")
                };
                if (startTime.HasValue && endTime.HasValue)
                {
                    emailQuery.Criteria.AddCondition("createdon", ConditionOperator.Between, new object[] { startTime.Value, endTime.Value });
                }
                else if (startTime.HasValue)
                {
                    emailQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrAfter, startTime.Value);
                    emailQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, DateTime.UtcNow);
                }
                else if (endTime.HasValue)
                {
                    emailQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, endTime.Value);
                }
                // Filter for sent emails - statuscode = Sent (3).
                emailQuery.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 3);
                EntityCollection emails = service.RetrieveMultiple(emailQuery);
                tracingService.Trace("Retrieved {0} total emails.", emails.Entities.Count);
                foreach (Entity email in emails.Entities)
                {
                    if (email.Contains("to") && email["to"] is EntityCollection toParty)
                    {
                        foreach (Entity party in toParty.Entities)
                        {
                            if (party.Contains("partyid") && ((EntityReference)party["partyid"]).Id != Guid.Empty)
                            {
                                Guid partyId = ((EntityReference)party["partyid"]).Id;
                                if (contactIds.Contains(partyId))
                                {
                                    totalOutboundEmails++;
                                    break;
                                }
                            }
                        }
                    }
                }
                // ----------------- Retrieve Phone Calls in Bulk -----------------
                QueryExpression phoneCallQuery = new QueryExpression("phonecall")
                {
                    ColumnSet = new ColumnSet("activityid", "createdon", "to")
                };
                if (startTime.HasValue && endTime.HasValue)
                {
                    phoneCallQuery.Criteria.AddCondition("createdon", ConditionOperator.Between, new object[] { startTime.Value, endTime.Value });
                }
                else if (startTime.HasValue)
                {
                    phoneCallQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrAfter, startTime.Value);
                    phoneCallQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, DateTime.UtcNow);
                }
                else if (endTime.HasValue)
                {
                    phoneCallQuery.Criteria.AddCondition("createdon", ConditionOperator.OnOrBefore, endTime.Value);
                }
                // Add filters for phone calls: statecode = Completed (1) and statuscode = Made (2)
                phoneCallQuery.Criteria.AddCondition("statecode", ConditionOperator.Equal, 1);
                phoneCallQuery.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 2);
                EntityCollection phoneCalls = service.RetrieveMultiple(phoneCallQuery);
                tracingService.Trace("Retrieved {0} total phone calls.", phoneCalls.Entities.Count);
                foreach (Entity phone in phoneCalls.Entities)
                {
                    if (phone.Contains("to") && phone["to"] is EntityCollection toParty)
                    {
                        foreach (Entity party in toParty.Entities)
                        {
                            if (party.Contains("partyid") && ((EntityReference)party["partyid"]).Id != Guid.Empty)
                            {
                                Guid partyId = ((EntityReference)party["partyid"]).Id;
                                if (contactIds.Contains(partyId))
                                {
                                    totalPhoneCalls++;
                                    break;
                                }
                            }
                        }
                    }
                }
                tracingService.Trace("Aggregated counts for Account {0} - Appointments: {1}, Outbound Emails: {2}, Phone Calls: {3}",
                    accountId, totalAppointments, totalOutboundEmails, totalPhoneCalls);
                // Update the Account with the aggregated counts.
                Entity accountToUpdate = new Entity("account", accountId);
                accountToUpdate["hx_appointmentcount"] = totalAppointments;
                accountToUpdate["hx_outboundemailcount"] = totalOutboundEmails;
                accountToUpdate["hx_phonecallcount"] = totalPhoneCalls;
                service.Update(accountToUpdate);
                tracingService.Trace("Account updated with aggregated activity counts.");
            }
            catch (Exception ex)
            {
                tracingService.Trace("Error in CountContactActivities: {0}", ex.ToString());
                throw new InvalidPluginExecutionException("An error occurred in CountContactActivities.", ex);
            }
        }
    }
}

Conclusion

This plugin helps calculate an engagement score for each account by tracking the number of relevant activities performed by sales representatives. By implementing this, sales teams can prioritize high-engagement accounts and take action accordingly.

Would you like to extend this functionality? Consider adding weights to different activity types to make the score more meaningful!