Use this link to share with your colleagues:
How to Build Tricky Reports with Cross Filters: https://help.pm.leapevent.tech/a/962107
Ever tried to build a report and needed a filter - e.g. "non-subscribers" - only to find you couldn't create the filter? This article is for you! Cross Filters allow you to create complex reports with filters from related data, like tickets and donations.
In this article, we'll look at examples of how you can use Cross Filters. First, we'll use a Cross Filter on a report to include active ticket buyers, then we'll narrow that Cross Filter to only include ticket buyers to a specific show. Next, we'll add a Cross Filter for folks who haven't donated since a certain date.
We'll also provide more resources to get you well acquainted with the beauty of this useful reporting tool.
PatronManager also comes with out-of-the-box reports called CRM Snapshots; we suggest you check out the CRM Snapshots tab to see if any of the reports there will give you the data you need before venturing into Cross Filters.
Remember! Before you dive into reporting, you always want to ask yourself the following two questions:
- What do I want to see?
- What do I want to do with the report?
1. Add Cross Filters to a report
Let's start with the "Collected or Confirmed Opt-in Contacts report. This is one of the broadest Contact reports in your PatronManager account; it includes every Contact in your system with a valid email address that hasn't opted out of receiving your emails.
For this example, let's say you want to narrow the scope of this report and only see patrons who have bought tickets from you.
1. Click "Edit"
2. Right away, click "Save As"
You should save a copy of the template under a new name right away. This way, you don't have to worry about saving over the template.
3. Fill in the details
- Report Name: Give the report a sensible name
- Report Unique Name: This should auto-populate based on the Report Name (if the old Report unique Name stays after you fill in the Report Name, just delete it and hit the tab bar to generate a new Report Unique Name)
- Report Description: Give the report a description so you and your colleagues know what it's for
- Folder: Put the report in a public folder so everyone can access it. This is an email list, so the Email Lists folder is a fine place for the report
- Click "Save"
Phew! Now we're safe.
4. Toggle to the "Filters" tab
5. Click on the down arrow and select "Add Cross Filter"
6. Set the criteria:
- Show me: Contacts with
- Secondary Object: Ticket Order Items
- Click "Apply"
7. Now let's add a sub-filter to our Cross Filter - click on the "Add Ticket Order Items" lookup, and search and select "status"
8. Set the criteria
We want to make sure we only include folks who have Active Ticket Order Items, meaning they haven't refunded their tickets:
- Operator: Equals
- Value: Active
- Click "Apply"
The non-ticket-buyers are now filtered out!
2. Let's narrow the report's scope further - to ticket buyers for a specific show
Our report looks pretty, pretty, pretty good so far.
How about this scenario? You want to send an email blast out to folks who have bought tickets to the mainstage production of the classic, Best in Show. We can filter out everyone who hasn't bought tickets to Best in Show with another condition to our Cross Filter.
1. Click "Edit"
2. Toggle to the Filters panel
3. Click on the "Add Ticket Order Items Filter" lookup on the Contacts with Ticket Order Items Cross Filter
4. Search and select "Ticketable Event Name" and fill in the following criteria
- Operator: equals
- Value: Best in Show
- Click "Apply"
When you fill in the Ticketable Event, make sure it's exactly as it is in Event Inventory. We suggest you copy the Ticketable Event from your Event Inventory and paste it in the value field to ensure accuracy.
You now have a list of Contacts who bought tickets to a particular show!
3. Let's add another Cross Filter - for donations!
Perhaps you want to send out something to people who have bought tickets to Best in Show but have never donated to your organization, asking them to become donors. You can do that with this same report - just add one more Cross Filter!
1. In the edit screen, click the down arrow and select Cross Filter
2. This time, we'll say "without" instead of "with" since we want to capture folks who have not made donations
- Show me: Contacts without
- Secondary Object: Donations
- Click "Apply"
3. Search and select "Won" in the Add Donations Filter lookup on the Cross Filter
4. Set the filter to "True" and click "Apply"
The donors are filtered out of the report!
Let's get more specific with this Cross Filter!
Let's add criteria to the Donations Cross Filter - we'll exclude folks who haven't donated since August 30, 2018.
1. Click on the "Add Donations Filter" lookup, search and select "Close Date"
2. Fill in the following criteria:
- Operator: greater than
- Date: 8/31/2018
- Click "Apply"
We now have a report that shows folks who bought tickets to Best in Show and haven't donated since 8/31/2018
Some things to keep in mind about Cross Filters
- If you want to see people who bought tickets to Romeo and Juliet but did not buy tickets to Twelfth Night, you need two separate Cross Filters:
- One for Contacts WITH ticket order items where the item detail contains "Romeo", and
- One for Contacts WITHOUT ticket order items where the item detail contains "Twelfth".
- This is the only way to filter out people who bought tickets to both shows.
- You can have up to three Cross Filters on one report, but no more.
- Cross Filters allow a report to filter on a field that may not even be on the report. However, this means you may not be able to pull in fields from the object you're using as a filter. You may be able to use Cross Filters to just see Contacts with donations, but that doesn't necessarily mean you'll be able to see their donations on the report.
Some other times to use Cross Filters
- If you're using the "Relationships" app, and you want to pull a report of Contacts with specific Relationships
- To see a Contact report of subscribers - pull a report of Contacts with Subscription Order Items
- To pull a Contacts report of just Contacts with recurring donations, where payment has been made 11 times (so it's time to renew)
- Use Contacts with Activities to see Contacts who you've been in contact with
- Use Contact with Campaign History to generate all kinds of reports about the success of your latest Campaign
- Any time you're trying to add a filter to a report and you can't find the field you need - try a Cross Filter!
Further Resources!
Cross Filters are a little complicated, but once you get the hang of them they're very useful. Here are a few resources directly from Salesforce that'll sharpen your Cross Filtering skills and help you become an expert in due time!