Do you use Salesforce Campaigns? If you do, you might use them for various purposes: marketing campaigns, tracking event invitations and attendance, or for something that is unique to your organization. Campaigns are a great out-of-the-box Salesforce tool that helps track the engagement between your Contacts and an event or initiative. Although Salesforce Campaigns are a great tool, have you ever found yourself asking, “Which Contacts were members of two Campaigns at the same time?” - whether it’s two simultaneous Campaigns or the same Campaign year-over-year.
If you are using Campaigns for marketing initiatives and your organization doesn’t leverage an integrated marketing tool (like MailChimp or Campaign Monitor), you may have a scenario where you did a mail-out and want to do a follow-up to an additional group of contacts but want to make sure you don’t include the original contacts in this new communication.
It’s possible you couldn’t figure out how to do this in Salesforce but instead found yourself exporting Campaign Member reports into Excel, where you could either rid the data of duplicates or use a pivot table to determine who is in two campaigns. There is a better way!
With Salesforce Reports and the use of Cross Filters, you can get the results you need without exporting to Excel.
Cross Filters? What are those? Here's Salesforce’s definition, “Use Cross Filters to include or exclude records in your report results based on related objects and their fields”. Still not entirely clear on what this means? Don’t worry, I will provide an example using Campaigns to show you how this works. But first a quick review of reports…
Again, in the Salesforce definition of Cross Filters there are three key terms: related objects, included, and excluded. In the case of Contacts and Campaigns you might want to know which Contacts are in a specific Campaign (or Campaigns), but you may also need to know which Contacts are not in a specific Campaign. The best starting point is a Contacts and Accounts report (not a Campaign Members report – I made this mistake too!).
Note: The screenshots below show how to build using the Salesforce Lightning Interface, but the process is very similar in Salesforce Classic.
To begin, create a Report from Contacts and Accounts. This will serve as the foundation for the rest of the Reports upon which you will use Cross Filters to help you get the results you are looking for.
Before adding any Cross Filters, run the report so you can see how many Contacts you have, so later you can verify if your Cross Filters are doing what you need. In my screenshot below, the report results show that this organization has seven Contacts:
Edit your report and click on the Filters tab. Click the dropdown menu beside the Filters label and you should now see Add Cross Filter (as below):
Now use a Cross Filter to show how many of the seven total Contacts were part of a specific Campaign. Below the Show Me label, select Contacts with Campaign History from the Secondary Object list. It should look like this:
Now click Apply. But we aren’t finished quite yet. If you run this report it will show you all Contacts with Campaign History, but not a specific Campaign History. For that we need to add a Campaign History Filter.
When you click on this box you will now see all the Campaign object fields that you can filter on. In this case we are going to use Campaign Name, but you could also use the Campaign Record ID. In this test case we have Contacts that are part of Test Campaign 1 and Test Campaign 2.
Here I want to see which of my seven Contacts are part of Test Campaign 1. So, I set the Filter field:
Click Apply and run the report, you can see that the results have gone from seven Contacts to just three:
With the use of a Cross Filter, we can now tell which Contacts from our full Contact database have been part of Test Campaign 1.
Next, I am going to show how to find out which Contacts have been part of a both of my test Campaigns. This is useful for comparing concurrent Campaigns or membership over multiple years.
To find out which Contacts are in two Campaigns at the same time, add a second cross filter to the same report. It is just that easy! Edit your existing report to do this.
In the screenshot below, in the Cross Filters section, there is now a second Cross Filter that has Campaign Name with the second Campaign.
You now have a report that shows Contacts that have been involved in both Test Campaign 1 and Test Campaign 2. Click Apply and run the report. You can see that now only two Contacts of the original seven are in both Campaigns at the same time!
As you have seen, Cross Filters are a powerful way of using reports to show information about the relationship of object to another. In this case, it is the relationship between Contacts and Campaign History that I care about, without needing any additional Campaign metadata. If you ever find yourself in this situation, using a Cross Filter in a Report may help.