Crowdskout’s Advanced Query Mode allows you to query off of Crowdskout Query Language. Crowdskout Query Language is based off of Structured Query Language (SQL) which is a programming language used to retrieve data from your database. You can segment on any data point or interaction that is found in your account.
The major difference between the regular segmenting interface and Advanced Query Mode is that Advanced Query Mode lets you choose groupings of data using the AND, OR, and NOT functions. This allows you to drill down on mixtures of data points from the same field at once. For instance, if you want to see all the people who clicked OR unsubscribed to an email, this is where you can create those segment commands.
Before explaining how to use Advanced Query Mode, let’s define some technical language:
Queries are commands that are used to retrieve the profiles in your account that meet a certain criteria set. These can have multiple lines of criteria in a query that filters your data in Advanced Query Mode. Boolean operators are used to string queries together to create a unique subset of your audience. These are created by putting together criterions and filters.
Queries are written in a specific language, Crowdskout Query Language, which is a simplified version of SQL. Crowdskout’s Advanced Query Mode interface allows you to use a drop-down checkbox option to populate queries. Users of Advanced Query Mode can also type their queries in the query box.
Criterions and Values
A criterion is the data point that you are focusing on in a query string. For instance, let’s say that you want to create a query to show all the people who are labeled as veterans in your account. Your Criterion would be “Veteran” and your Value would be “Yes.” The query in Advanced Query Mode would be:
Filters are things that further refine your criterion in a query string. These are found within the parentheses of the query string separated by commas. The filter is what you use to refine the criterion. Here’s an example:
This example shows the criterion: Phone Call, and its filters: PhoneCallName = "Test", CallResult = "Successful", PhoneCallUser = "Peter Test.” This query will show all the people who received the phone call named “Test” by Peter Test that was successful.
If there are multiple criterion and filters in a query string that are unique of one another, they will need to be separated by Boolean Operators. We call these the “AND,” “OR,” and the “ NOT” functions in Crowdskout. These function to combine multiple queries together to make one long query string to segment your data.
The AND Function
Choosing the “AND” function between two or more query strings will display all the profiles that match both of the criterions or filters you selected. For example, if you have the query:
The results will show only people who are veterans AND male AND who are also Hispanic. Additionally, the first query in your string auto starts with an “AND” function even though it is not visibly written in the string. For example, if you have the query:
The results will show all the people in your database who match the criterion of being a veteran. You can start out a query by using another boolean operator (AND/OR/AND NOT function) to put multiple queries together.
The OR Function
The “OR” function enables you to choose multiple data points without them being mutually exclusive. For example, you could create an Advanced Query that segments on all the people who have been sent an email OR have filled out a form on your website. Using the “OR” function makes the audience in a segment larger. f you create a segment based on emails sent and form fills in basic segmenting, the universe that would be selected would be all the people who have been sent both an email AND have filled out a form on your website.
The NOT Function
The NOT function refines your segments by excluding data that matches your criteria and filters. An example of this would be:
This query would show you all the profiles that ARE NOT male. This function is especially helpful for attributes (such as custom tags and custom radios) that only have one option. An example would be a custom radio attribute that is named “Favorite Color is Red” that only has the option “Yes.” By putting the NOT function in front of the criterion (Favorite Color is Red = “Yes”) this would show all the people in your database that have NOT been tagged as their favorite color being red.
Parentheses punctuation marks are used to separate individual query criterion and filters from one another. They make sure the criterion and filters are contained and are separated from the boolean operators (the AND, OR, and the AND NOT functions). Take this query for example:
The single parentheses keep the criterion of Veteran and Gender independent of one another. It also allows the boolean operator to join both criterions and filters together to make one query.
You can put another set of parenthesis around a query string to separate queries from one another. You can add multiple parentheses around a query string to separate queries from one another. Here’s an example:
The above query will show all the people who voted in both the 2012 AND 2008 Missouri General elections but did not vote in the 2014 OR 2008 Missouri primaries. As you can see the double parentheses keeps the queries separated so the criterion and filters from the first set does not affect the second set in double parentheses. The two sets are mutually exclusive. The double parentheses are required separate the 2012 and 2008 Missouri General Election voter with voters who did not vote in the 2014 or 2008 Missouri Primaries.
Run Query Button
The Run Query Button runs the query that you have typed or selected in the Advanced Query Mode box through the database. The query will show only the profiles that match your selected criteria.
For more information about creating Advanced Queries in Crowdskout, check out our FAQ on how to use Advanced Query Mode here.