This document will provide some tips on how to write queries in the query builder so that the data you expect is the data that you get. We’ll also explain how to create more efficient queries when large amounts of data are needed.
Exporting the data you need
Exporting question data on a page
If you export a petition, you might expect every field on the petition to be included in the export, such as a comment question, or an opt-in. This is not the case.
You need to include these additional fields if you want to export them. That’s because the query builder is just selecting supporters: if you add the petition in the “Build your universe” section, then it’s selecting supporters that answered the petition. That’s great for sending emails, but if you want details on what they answered, then add the fields as well into the filters and the system will include that data too.
In the above screenshot, it is selecting everyone that took the petition, or answered the comments, or the opt-in. That on its own would export a large amount of data: every petition signer as expected, but also everyone that commented on any page, and every opt-in. That’s why you then limit it to the petition only via the “Filter your universe” area.
The first, build you universe, part of the query is essentially telling it that it should also include the results of the comments and opt-in.
Understanding transactional data
It is important to understand that transactional data, such as opt-ins, creates a row of data each time the value is set. For example, if I opted-in and then out, two rows of data would be created with a status of Y and a status of N.
Let’s say I want to export a supporter’s opt-in status. If I create a query where I’m selecting an email address and filtering for the opt-in (with NA as the filter so all answers are included), like this:
Then the Preview Query will show 1 supporter, but a transactional export will show two rows, both for the same supporter.
More importantly, if they haven’t answered the opt-in yet, then this query would in fact retrieve 0 supporters. That’s because there are 0 transactional rows for the opt-in, and so you are filtering for transactional data that doesn’t exist. Therefore, they won’t be selected.
When you add a question filter, it is filtering for people that answered the question.
How do I export supporters that haven’t answered an opt-in then?
Therefore, if you wanted to export supporters that hadn’t answered the opt-in at all (that is, they haven’t answered the opt-in as Y or N ever), then you would simply exclude the opt-in question (with no criteria):
Email send data is not transactional
Note that we do not store the fact that someone was sent an email as a transactional piece of data. Therefore, if you choose that in your export, then it’ll return 0 transactions, even though it may select a group of supporters. Therefore, a User or Hybrid export would include all of them.
If the supporter interacts with the email, e.g. opens or clicks, then a transaction is created.
Also note that Email Send filters are only available for 6 months from the email send.
Creating efficient queries
Through the Engaging Networks platform you can query your data using a vast array of filters. Because there are so many options, it’s important to order your queries in a logically and to use some general guidelines to ensure that you are able to retrieve your data in a timely manner.
How the Queries Work
The query tool provides a way to search and filter the data collected on constituents as well any activity that takes place by those constituents. Each item that is dragged over from the left side boxes to the right side boxes creates a filter. These filters are then compiled together and grouped as universe filters (first filter selector), include filters (second filter selector), or exclude filters (third filter selector). Universe filters run first, then include filters, and finally the exclude filters. If a particular group of filters is not specified, then it’s skipped over.
The rest of the process is as follows:
- If universe filters are included, run these filters and get a distinct list of supporters.
- If include filters are included, run these filters and then retain from the universe only those specified in the include filters. If no filters were specified in the universe, then we simply proceed with the list of supporters from the include filters.
- If exclude filters are included, we then exclude all supporters returned from these filters from (2).
When more than one filter is included in any grouping, we include all the distinct supporters from all of the filters.
Order Matters
It’s important to plot out your queries in the most efficient order. The query below is inefficient because that software first has to pull ‘all users’ from the database and then retain only those that took the ‘Template Data Capture’ action.
This query would be much more efficient by simply querying on ‘Template Data Capture’.
General Tips
The following tips will generally allow the database to pull your data more efficiently and save you time retrieving your reports.
- Uncheck ‘match case’ – unless you are specifically trying to pull out case senstive data, it’s best to untick the ‘match case’ box
- Use “equal to” instead of “contains” – unless you don’t know the exact value you’re querying, it’s best to have the database search for a specific value
- Build the bulk of your query using the first filter selector – the database will do less filtering of the data if you don’t need to retain or exclude data
- Profiles are your friend – profiles already group your data, so using them in your queries will save the database work
- Save your queries – if you find yourself running the same query frequently, you can save that query and run it instead of dragging the boxes from left to right in the query builder