5 Formula Fields to Add to Your Salesforce Campaign KPIs

Share this article...

Salesforce Campaigns and Campaign Members are a very powerful combination that some marketers don’t fully leverage. Look at a Salesforce Campaign record out-of-the-box, and you may notice that typical marketing metrics are missing.

Don’t worry, you can add these easily, with relatively little effort. In this post, I will show you how to add 5 campaign KPIs that have been requested by other organizations in the past, including Cost per Response, Net New Leads in Campaign, Cost per Acquisition, and others.

“Sonar”

1. Total Campaign Members

This first field is simply to show the number of Campaign Members in your Salesforce Campaign. Out-of-the-box, Salesforce comes with the fields ‘Num of sent in Campaign’ and ‘Num responded’ but no field that states the total. It can be a good field to have handy in reporting, in fact, some of the other formulas in this post will rely on it!

This is what is called a ‘Roll-Up Summary’ field – it calculates the number of Campaign Members (child records) associated with a Campaign (the parent record), ‘rolls’ the total up to then display on the Campaign.

When you create the new field, select Roll-Up Summary:

The summarized object is (obviously) Campaign Members, and select ‘COUNT’ to tell Salesforce that you want the field to count up the Campaign Members:

2. Cost per Campaign Member

Do you fill in the budget fields on your Campaigns? You have two budget fields available that you should start to use:

  • Budgeted Cost
  • Actual Cost

The formula to calculate how much each Lead/Contact in your campaign costs you relies on the actual cost field.

The calculation is:
Actual Cost in Campaign/Total Campaign Members

Create a formula field, output type currency. Set 2 decimal points, a setting at the bottom of the screen (which should already be the default). Then input the formula below:

ActualCost / Total_Campaign_Members

3. Cost per Response

You may be wondering how you can limit this formula to engaged leads/contacts only, who made a meaningful interaction with your brand. This is where the best practices around the ‘responded’ checkbox come into play*. Luckily this field comes out-of-the-box, called “Responses in Campaign”.

To reiterate the formula, you would simply divide the cost by the number of responded members using this formula:

ActualCost / NumberOfResponses

NOTE: a topic for another post, you should follow best practices around using the ‘responded’ checkbox, such as keeping your Campaign Members statuses consistent, updating a Campaign Member’s status as they engage (for Pardot users, this means adding completion actions to ‘Change CRM campaign status to –’). Your calculations here won’t be accurate if you are not disciplined.

4. Net New Leads in Campaign

While the thought of this formula will get any marketer interested, it’s my responsibility to cover the caveats first. Depending on your operations and org setup, this may not be the most sophisticated measurement, however, it ‘does the trick’ in the cases where I have used it.

NOTE: this is an advanced setup that requires Salesforce admin knowledge and the appropriate oversight of the whole org (as the solution involves automation, you should check whether it can leverage any existing processes on the Campaign Members object).

Here, I define a “Net New Lead” as a Campaign Members with a created date the same as the lead created date (ie. the lead did not exist before this date).

Campaign Member Created Date = Lead Created Date

First, create a formula field on the Campaign Members object, output type number (set 0 decimal points). Then input the formula below:

IF(CreatedDate = Lead.CreatedDate,1,0)

This means if the two dates are the same, show a ‘1’, and if not, show a ‘0’. The result will be a field on the Campaign Members that shows either a ‘1’ or a ‘0’ which I advise you keep hidden (ie. don’t add it to your page layout).

Next, we move on to “Net New Leads in Campaign”. You would think that it would be simple to now create a Roll-up Summary field just like we covered in point #1 (Total Campaign Members)…not so fast! There’s a quirk with Salesforce which means that we have to use a new field to base our Roll-up Summary field on*.

*The details: for those of you who are interested, the Net New Leads in Campaign field we just created is a cross-object formula field, ie. it includes fields from more than one object (Campaign Members and Leads, in this case). Cross-object formula fields cannot be used in Roll-up Summary fields.

Create a second field. Checkbox fields or picklists work well because they are both accepted as filter criteria in the Roll-up Summary field (which I will visit soon).

I chose a checkbox field for simplicity, creating the “Net New Lead Checkbox” field on the Campaign Members. We can use Salesforce automation (workflow rule, Process Builder, etc) to set the checkbox field to ‘true’ where the ‘Net New Lead’ formula field is 1.

Create a Roll-up Summary field on the Campaign object called ‘Net New Leads in Campaign’. The summarized object is (obviously) Campaign Members, and select ‘COUNT’ to tell Salesforce that you want the field to count up the Campaign Members.

Unlike Total Campaign Members (point #1), we want to only include the Campaign Members that have their ‘Net New’ checkbox checked. Put in the criteria by selecting “Only records meeting certain criteria should be included in the calculation”, then the “Net New Lead Checkbox” field from the dropdown, and specifying ‘True’.

A bit fiddly, I know, but we got there in the end!

5. Cost per Acquisition

Now we finally have ‘Net New Leads in Campaign’ at our disposal, we can calculate how much each new Lead the Campaign captured actually cost you.

To create the formula, you would simply divide the cost by the number of net new leads using this formula:

ActualCost / Net_New_Leads_in_Campaign

Glance back to Cost per Campaign Member (point #2) or Cost per Response (point #3) to refresh your memory on building formula fields.

Summary

You can now see how easy it is to add more campaign KPIs to your Salesforce Campaigns that tap into the Campaigns + Campaign Members power combo.

Any other campaign metrics you think are missing? What custom fields have you added to your Salesforce Campaigns? I would love to hear!

3 thoughts on “5 Formula Fields to Add to Your Salesforce Campaign KPIs

  1. Avatar

    Rebecca Sweetman

    Reply

    So, I’m curious if anyone got above working for Net New Leads in Campaign Member object 1 or 0? I tried the formula above and was not able to get it to work. I wonder if it is the date (campaign member) vs date/time (lead) meaning not ever equal? And can’t seem to pull in other fields such as member association date etc. Any tips to get it working is appreciated!

    1. Avatar

      Rebecca Sweetman

      Reply

      So, I worked out both Date/Time which will not ever be exactly equal. Better to use this formula:
      IF(DATEVALUE(CreatedDate) = DATEVALUE(Lead.CreatedDate),1,0) so they both are only date and can be compared. Hope that helps someone else. 😉

Leave a Reply