Query builder

The query builder is used when exporting data or selecting supporters to receive email broadcasts. The query builder consists of three criteria to define the groups to include in your query:

Build your universe

Use this section to identify groups of supporters that you would like to export (i.e. select an advocacy action name to export all users who took that action). Each filter you choose in this section is treated as an OR query. That means that the more filters you add to this, the bigger the amount of supporters that could be selected.

For example, adding two advocacy pages here would select supporters that had done either action, or both.

You need something in the Build Your Universe filter for the query to run.

Filter your universe

Filters added here will restrict the universe you created in the initial Build section. Use this section to retain records from the universe that match the criteria included in this box. For example: from the data (selected above) retain only records who selected an opt-in question checkbox (this is an important filter for retaining ONLY your opted-in supporters when setting up an email broadcast).

Each filter you add here is treated as an AND query, which means everything has to be obeyed within this section for a supporter to be chosen. This means the more filters you add, the more restricted the group.

For example, adding two advocacy pages here would select supporters that had done both (if a supporter had just done one, they would not be selected).

Exclude records

Use this section to exclude records that have been selected and filtered from the previous groups.

Participation Date

You can specify a date range when supporters participated in pages selected for this query. If you don’t do this, then no date restrictions will be placed on the exported supporters.

The Participation Date filter does not filter on questions or opt-ins.

Types of Data Filters

Within each section (build, filter and exclude) there are several tabs that identify types of data you can filter on. The first tab, for example, shows supporter fields so you could use this to select supporters within a certain Town, for example. Other tabs filter for different actions that the supporters might have done, for example opting-in or donating.

Supporter data filters

Filter based on a particular value stored in the static supporter data fields. Transactional tagged fields will not be included as options here.

When added, you will be offered a further filter option:

Tagged fields, such as Address 1, allow you to match (=) or not match (not =) your typed in value. For example City=London. City= (with no value) would pick up blank cities. You can also use contains, begins with or ends with. You can use ~ to separate multiple values, for example London~Manchester would pick up London OR Manchester. It is case sensitive unless “Match case” is left unticked.
Untagged fields offer the above comparison filters as well as date filters. These will only work on values in that field of the format YYYYMMDD, e.g. 20201225. The additional filters are between, greater than, less than, before and after. If a field is formatted differently, it will not be picked up by these date flters.

In addition to these fields you have three other special filters:

All Users will select everyone. This can be useful if you only want to filter or exclude, since the “Build your universe” section requires at least one filter
Supporter Created Date allows you to select for when the supporter record was first created
Is Suppressed allows you to filter for suppressed records

Question and Opt-in filters

Filter supporters’ data based on their answers to questions they have submitted on your campaign pages, i.e. you can export supporters who opted in to receive future emails from you by adding this filter with criteria = Y

Advocacy and Engagement filters

Filter supporters’ data based on the advocacy or engagement page they have participated in. The participation date filter will work on this type of filter.

“All Click to Call Campaigns”, “All Twitter Campaigns”, “All Email to Target Campaigns” will select all participations from those page types only.

“All Data Capture Campaigns” will select Data Capture pages, Petitions, Surveys, Sign-up forms and Email Subscription pages.

Email to Target filters allow you to select for registrations only, or both. Registrations are when a supporter completes the first page of an ETT, but does not go on to send the email.

Fundraising filters

Filter supporters’ data based on fundraising campaigns they have participated in. The participation date filter will work on this type of filter.

For all fundraising campaigns, choose “All Donation Pages”.

When adding the filter you have further optional filters:

Amount – filter by donation amount (any currency). Leave as NA if you do not wish to use this filter, or choose less than (less than but not equal to), greater than (greater than but not equal to), or between (use ~ to separate the range, e.g. 10~100 would export any donations of between 10 and 100 inclusive)
Payment type – filter by whether the donation was one-time or recurring. Leave as NA if you do not wish to use this filter
Payment status – filter by the payment status (success, reject or pending). Leave as NA to include all donations regardless of status
Currency – filter by currency. Leave as NA if you do not wish to use this filter

Supporter Hub filters

Filter supporters’ data based on participation on Supporter Hub page(s). This includes multi-select options based on who has:

Logged In
Updated Personal Details (such as name or address)
Updated Subscription Details (opt-in status)
Updated Recurring Gifts (such as amount or payment details)
Processed a Transaction (as an additional gift via a hub page)

Broadcast Email filters

Filter supporters’ data based on any previous emails you have sent to them, i.e. you can export supporters who opened or clicked on links in your newsletter.

Note that “Message sent” is only available for up to a year since the email was sent.

The names of the emails in this filter are taken from the message(s) within the email campaign.

Multi-select – click this if you want to add filters for several emails. This tool makes it easier to search for emails based on the type of email campaign (split test etc), tags, attributes, and also by ranges of created dates and sent dates.

Marketing Automation filters

Filter supporters’ data based Marketing Automations, e.g. if they currently are in an automation, or if they responded in a certain way.

This filter works by adding the automation in question, and then selecting criteria based on their response to any message (an email sent by the automation), or by individual messages. Clicking the latter will list all the messages within the automation and allow you to add conditions on that message (Sent, Opened, Clicked or Converted).

Note: when using the query builder for selecting email recipients, if your account preferences say that campaign emails cannot be sent to supporters in an automation, the query builder can select them using this filter but the email tool will reject them.

Tracking Value filters

Filter supporters’ data based on the tracking values that you have added to your action URLs. For example, you could export supporters who supported your campaign by clicking on a Facebook post.

Profile filters

Filter supporters’ data based on specific profiles you have created (you need to create and run the profile in “Data & Reports > Profiles” prior to using it as a filtering criteria).

Filter supporters’ data based on the attribute of the page or email campaign they interacted with.

CRM Segment filters

If you are using our import API to assign CRM segments, you can filter on them here. For more information on CRM segments, check out our import API documentation.

Peer-to-Peer Registration filters

Filter supporters’ data based on anyone that has registered for a specific ‘Peer-to-Peer’ campaign.

Peer-to-Peer Donation filters

Filter supporters’ data based on anyone that has donated to a specific ‘Peer-to-Peer’ campaign.

Event filters

Filter supporters’ data based on who have signed up for an event. This filter works by listing all available events, and then showing additional criteria for that event.

You can filter on Amount (the total cost paid for the event), Payment type (one-time or recurring), Payment status (reject, pending or success), or Currency. There are further filters which you can use to show mobile purchases only, and what to include in the transactional export.

ECS refers to an event purchase (credit/debit single), ECC to event purchase (free/cash tickets), ETK to event ticket purchase (the tickets bought with the event) and ETA to the attendees of the event.

Origin Source filters

Filter supporters based on Origin Source. Read more about Origin Source here.

Geo Location filters

Filter supporters’ data based on their longitude and latitude. Read more about Geo Location here.

Membership filters

Filter supporters based on their membership type. Read more about Membership Types here.

Adding filters

Click Add to add the filter to your selection. it will show in the summary at the top. Some will offer additional options as detailed above. You can remove the filters by clicking the X in the summary above.

Saving queries

If you would like to save the query, tick the box next “store your query” and supply a name for the query:

Once you go to the next step of the query builder, it will save it.

Saved queries can be organised into folders. See here for details on how you can start organising the saved queries.

Next steps

Once you have built your query, you can preview (showing the number of unique supporters selected), or go on to export the data or use it in your email campaign to send.

Transactional Data Reference File Download: Export transaction data template.xlsx

Troubleshooting

Failed to run query error

If you see this error, it could be because you do not have anything in the blue Build Your Universe filter. You need something here, even if it is just All Users