In my previous post, I gave an overview of why I think the Campaign entity in Dynamics 365 is a really practical and versatile entity to use when keeping track of your marketing efforts. One way in which I recommend using the campaign entity is to track your spend vs. your revenue coming in and avoid being in the same position as Mr. Exotic pictured above. In this post, I’ll go through just one way in which 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 up 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 the “Estimated Revenue” and “Actual Revenue” fields too, 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 $1000:
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. Actually, this was the approach I took when calculating the Actual Revenue dollar amount:
Revenue is the fun side of a Campaign, but we also need a way to realise our spending also. To do this, 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 up the number of Sent Email records generated. If you’re using ClickDimensions, Sent Emails will automatically be associated to the campaign designated on the originating send.
Once you’ve 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 up costs for social and physical advertising – the campaign activities entity could be utilized for this functionality with some minor customisations similar to what I have shown here.
When we add all of these to our form we can begin to build a complete picture of our financials:
Throw in an Arc Knob for some visual context and you’re ready to plan out your marketing goals!