Updated: 16th September 2024 for Release 201
Introduction to Query Builder
With Query Builder you can make queries on your database to create selections of visitors and use these in mailings, or export the results.
As we continue to work on this module we will add new filter options frequently. The current possibilities are therefore a subset of all the options that will be available in a later stage.
Getting started with Query Builder
You can access Query Builder in the Dashboard > Marketing > Query Builder.
The starting screen has an overview of all your queries. The Title and a description (if added) are shown to easily identify all queries in the overview. From here you can build a New Query or Edit an Existing one. You can also Archive a Query by selecting the archive icon in the left column. NOTE: If you add or remove filters this will automatically alter the results. You can also select Use query to get a preview of the results, export to csv and use in a mailing.
Creating and editing a query
To create a new Query select the New query button
To edit an existing query select Edit query. The detail page will open.
- Name = Name you Query. Recommend something related to query purpose e.g. 2324 Season Visitors
- Description = Optional description e.g. Visitors to 2324 season with Opt-in
- Mailing query = Set this to "Yes" if you want to use this query result for mailings later on. Only queries with this toggle switched on will be available in the mailing module, so you can create a manageable subset and be sure that mailings are never sent to the wrong query
- Archive after use = Set to “Yes” if you want the Query to be archived after use in Mailing. Note: This does not delete the query, it is hidden from the Query list on the previous page.
When using the query for mailings, a reminder will pop-up to notify you that you need to check if your query is GDPR compliant. This means adding the Opt-in filter to the query to make sure only visitors with the correct opt-in will receive your mailing.
Filters Explained
AND v OR
Some filters allow you to select multiple settings within the filter. If this is the case, at least one of the settings within the filter should be valid for the visitor to be included.
OR = If you add more than one option within a filter, the query result will use the ‘OR’ protocol.
For example, if you select multiple visitor tags in one filter, the result will be all visitors that have either the one visitor tag OR the other, or both:
.png?table=block&id=4aefa20f-262b-41b7-8f60-8008645aad76&cache=v2)
AND = If you want to make sure your result only has visitors that have both visitor tags, add the same filter twice. The query result will use the ‘AND’ protocol.
.png?table=block&id=f9ef5eeb-1866-4717-8a13-230a830fd351&cache=v2)
Available Filters:
Here is a list of all the Filters currently Available. We will add a lot more new filters in the future. For now this is the current set of filters that are released:
Filter | Description | Multi-select |
Has opt-in | Returns all visitors that have the selected opt-in | No |
Has visitor tag(s) | Returns visitors that have specific visitor tags selected. You can select multiple visitor tags here. Visitors will need to have at least one (OR) of these visitor tags to be included in the query. | Yes |
Does not have visitor tag | Returns all visitors that do not have the selected Visitor Tag. | No |
On mailing list(s) | Returns visitors that are on a specific mailing list.Visitors will need to be on at least one (OR) of these mailing lists to be included in the query. | Yes |
Not on mailing list | Returns visitors that are not on the selected mailing list. You can only select one mailing list here. | No |
Purchased in period | Returns all visitors that have purchased tickets for a certain amount of events in a given period. The period set applies to the date the purchase took place and not the event date. E.g. Purchased more than 4 events between 1st January 2023 and 31st December 2023. | N/A |
Visits in period | Returns all visitors that will attend/have attended an event in a given period a certain amount of times. The period set applies to the event dates in that period. E.g. Attended more than 4 events between 1st January 2023 and 31st December 2023 | N/A |
Purchased production(s) or event(s) | Returns all visitors with tickets for a specific event or if Production is selected, any event from that same production. | Yes |
Did not purchase production or event | Returns all visitors have not purchased tickets for a specific event or if Production is selected, any event from that same production.NOTE: If you want to include multiple events or productions, just add the same filter again while selecting another event or production. The result will be all visitors that have not bought tickets for any of the selected events/productions. | No |
Purchased genres/tags | Returns all visitors that have bought tickets for any Production with one or more of the selected genres/tags. You can select multiple genres/tags to the filter(OR). If you want to only find visitors that bought tickets in all genres, add the filter multiple times (AND). | Yes |
Has timeslot | Returns all visitors that have requested and received a time slot for your presale. | N/A |
Activity before | Returns all visitors who have not shown any activity since a certain date.NOTE: Activity includes any website visit while being logged in. If a user visits the site but does not login they could be excluded. | N/A |
Activity after | Returns all visitors who have shown activity since a certain date.NOTE: Activity includes any website visit while being logged in. If a user visits the site but does not login they could be excluded. | N/A |
Belongs to organisation(s) | Returns all visitors from one or more organisations. You can manage your organisations in Marketing> Organisations module or add visitors to organisations in the visitor module. | Yes |
Production(s) or event(s) on wish list | Returns all visitors who have one or more selected Productions or Events on their wish list. | Yes |
Adding filters to your query
All available filters are shown as big round buttons in the Query builder start screen.
The filters are set up in a way that they combine to form subsets of your visitors by using the "AND" protocol. The result of the query will only return visitors that are in filter X AND in filter Y. Meaning, the more filters you add, the “smaller” your list becomes.
Select a filter to add it to your query. It will show a new box where you can input your settings.
When you have added all your filters select ‘Recalculate’ at the bottom of the screen to run the query and calculate the resulting number of visitors:
.png?table=block&id=9cf3064f-7ecf-4daf-a7fb-6a96a9153cb6&cache=v2)
When you have finished building your Query, select Save. This will take you to the "Use query" screen, where you will get a preview of the result, export the list, or continue to the Mailings module:
Visitor accounts that have been anoyimised will not be included in Query Builder results.
Example Queries
Scenario 1: Mail all visitors who attended last season to tell them about the upcoming season
Filters:
- Has opt-in = Newsletter Opt-in
- Visits in period
- Visitor has tickets for [number of events] More than or Equal to 1 Events
- Event date(s) from… 01-09-2023
- … up to and including 31-03-2024
Scenario 2: Mail all customers who have not already not purchased for Christmas event, but attended during the Christmas period last year.
Filters:
- Visits in period* =
- Visitor has tickets for [number of events] More than or Equal to 1 Events
- Event date(s) from… 01-12-2023
- … up to and including 31-12-2023
- Did not purchase production or event = Select Production from dropdown
Alternatively, if you only had one Production in the previous festive period, in place of the you could use Purchased Production Filter and select that Production.
Archiving Queries
You can archive any queries no longer used, or you do not want to see in the main list. Each query also comes with a new “archive after use” option which will move the query to the archive after the mail has been sent.
You can also un-archive a query if you want to use it again.This is found on the Query Builder overview screen by selecting Archive at the top right.This new feature will enable you to keep your query collection tidy.
Feature launch party
You can replay the entire launch party, where we present this feature, explain the idea behind it and dive deeper into the technology behind the new mailing templates and Query Builder that come with this update.
Transitioning from Mailing Lists to Queries in your Mailings
All the data you previously used for your Mailings is still there for you to create your selections.
Creating these selections is now done within the Marketing>Query Builder Module, instead of while scheduling the mailing. This makes the process more secure, as you can re-use queries and scheduling a mailing has become far more easy by simply selecting the query you need.
For any Mailing Lists you used frequently in Mailings, you will now need to create a new query for them. Once that is done, you can reuse it as many times as you want.
Some examples:
- To mail all Newsletter subscribers, create a query with the filter “Newsletter opt-in”. That’s it, you are done! Give it a clear name and click Save.
- If you are using public Mailing Lists within your newsletter opt-in flow for different genres or other preferences, you can create a new query for each of those preferences individually. These preferences are stored in public mailing lists. For instance, if you want to mail only visitors with the preference “cabaret”, create a new query, add the filter “Newsletter opt-in” and then add a second filter “On mailing list” and select the “cabaret” mailing list. Now your Query contains everybody that has the newsletter opt-in (as required by GDPR) AND is also on the mailing list “Cabaret”.
Repeat this process for all different mailing groups you frequently use.
You can still upload mailing lists from your box-office system and use them in your queries. Make sure to always also add the “Newsletter opt-in” filter to that query:
- Create a new query, add the filter “Newsletter opt-in” and then add a second filter “On mailing list” and select the mailing list with the imported visitors.
Related article