Budgeting and the Microsoft Dynamics Campaign Entity

The campaign entity in Dynamics 365 is a practical and versatile entity to use when keeping track of your marketing efforts. One great way to use the campaign entity is to track spending vs. revenue. Let’s look at just one way this can be achieved.

Calculating and Analyzing Revenue

In order to quantify the performance of our campaign from a dollar amount viewpoint, we need to determine what qualifies as revenue. In this example, I am going to say that every lead generated contributes to the Estimated Revenue figure and every related closed opportunity will calculate the Actual Revenue earned. Let’s set those two fields up as follows:

As seen above, the Leads Generated field is a simple whole number roll up field that will tally the number of leads that have the same source campaign value as the campaign we’re working with. The Opportunities Closed As Won roll up is a similar whole number field, just with an additional filter:

I also created Estimated Revenue and Actual Revenue fields, which will help us keep track of pipeline. In my case, I made the Estimated Revenue field really straightforward, assigning a dollar value of $1,000 for each lead I generate. I then wrote a very simple business rule that sets the value of the Estimated Revenue field to the number of leads generated multiplied by $1,000:

This could also be done as a calculated field. Another way of generating estimated revenue might be to do another roll up field that calculates the value of all open opportunities related to the campaign. This was the approach I took when calculating the Actual Revenue dollar amount:

Tracking Spending

To track our spending, we’re going to create some more roll up fields and calculated fields. For this particular example, I’m going to concentrate on emails, but in reality, there may be multiple different components to budget for.

First, I’m going to create a field where I can allocate a dollar amount to each email I send out for this campaign. It is just a basic currency field where the marketer can change the value if needed. Next, I’m creating yet another roll up field that will tally the number of sent email records generated. When using ClickDimensions, sent emails will automatically be associated to the campaign designated on the originating send.

Once you have built out all the fields for allocating cost, you can now apply these values to a Total Campaign Cost field. To mix things up, I created this as a calculated field that looks at the total number of sent emails and multiplies that by the email cost. If you’re taking multiple costs into account, you would build them into your calculated field:

You could also do a similar thing to tally costs for advertising – the campaign activities entity could be utilized for this functionality with some minor customizations like what we have shown here.

When we add all of these to our entity form, we can begin to build a complete picture of our financials: