How can I compile a report that shows all the sales leads that are NOT members of a particular marketing campaign?
Sales Leads that are not members of a marketing campaign
At first the simple answer to this question might appear to be to create a report based on Sales Leads that includes a column for Campaign Name and then simply apply a criteria to identify the rows that do not contain the Campaign Name you're looking for ... BUT ... because the report displays a separate row for each Campaign that the Lead belongs to, you'll only remove the row that contains the particular Campaign Name but there may well be other rows for the same Sales Lead. For example, the screenshot below shows a preview of Sales Lead report showing the same Lead as being a member of 3 different Campaigns. If a criterion were applied so that the report only shows rows that do not contain Test Campaign, the other 2 rows would still appear.
So, here's how to make sure that the report shows the correct results:
- Create a Sales Lead report with all your required columns, including one for the Object reference (which will be used to summarise the report later).
- Add a calculated column, called Campaigns, using the following formula:
- Add a calculated criterion of:
Campaigns does not contain [name of the campaign you want to exclude].
- Summarise by Object reference.
The screenshot below shows a report created by following the steps above, which only displays one row per Sales Lead, excluding any Leads which are members of a specific Campaign. (Click to enlarge.)