Recently a customer asked how they could get a simplistic look at marketing ROI (return on investment) data within D365 Sales. They actively use the Lead table, created mainly by web forms tied to various campaigns. 


As part of the campaign records, the customer diligently tracks campaign costs. Additionally, they do a great job tying every lead to a source campaign.


With these two items in place, we’ve got a great start to setting up a simple cost-per-lead metric for them to review.


We suspect many other organizations would like to do the same, so we’re providing a quick overview of the logic we deployed to achieve this need.


It’s important to stress the items I previously mentioned:

• The customer diligently tracks campaign costs

• The customer does a great job ensuring all leads are tied to a Source Campaign


Without these practices, this would be difficult, if not impossible. We’ve long maintained that leveraging data from CRM/D365 is critical to a company, assuming the company makes the integrity of that data a top priority.


Without a focus on data integrity, the numbers would be skewed, and incorrect data is worse than no data.

Compiling the Data

The first step in this quest was to develop rollup fields on the Campaign table to count the Leads associated with the campaign. It was agreed that they wanted to count all Leads for the ROI calculation.


However, they also saw value in having rollup’s total Open Leads, Qualified Leads, and Disqualified Leads. They wanted these values to be visible on the form to see at a glance how the campaign was performing.


We developed the four rollup fields and added them to a section on the form.





It’s worth noting that there was some debate about using a sub-grid with the relevant views.

While this could have worked, the team wanted to see all the numbers at once. They were less concerned with the actual records, so individual rollups made the best sense for them.





Campaign Costs

The customer tracks their campaigns entirely on out-of-the-box records and fields. They create campaign activities with the amounts on them and the data automatically rolls up to the Campaign field into the Activity Cost field. 







Additionally, the customer uses the Misc. Cost field to enter anything not captured in the Campaign Activities. Together these fields are calculated into the Total Cost field.






Though very straightforward, this requires some attention to detail on the part of the team. As noted earlier, this customer is diligent about adding this information to ensure the campaigns reflect their actual cost.


If your company takes this action, make sure you have policies and standard operating procedures to inform users of where to enter which data.

These may sound like a lot of overhead in terms of work, but they are arguably the best way to ensure everyone is crystal clear on their data entry responsibilities.

Calculate the ROI

The ROI calculation is a straightforward division equation. To accomplish this, we use a calculated field (Cost Per Lead) that performs the following math:


Total Cost of Campaign / All Leads Rollup


As shown here, a campaign with a total cost of $150 has generated five leads equating to $30 / lead.



The Outcome

The customer loved this approach. Naturally, many variables could be included in the holistic calculation of any return on investment data. Still, this customer wanted to keep things simple and give them insight into their campaign performance, and this approach fit the bill perfectly.


This approach was easy to implement, leveraged out-of-the-box functionality, and provided a quick snapshot into the ROI of the campaign—precisely what they wanted.


Are you interested in implementing something similar in your company? Contact C5 Insight today to chat about how we can support you in your journey!