You are here

Home › Theatre Manager Online Help › Mail Lists - what are they? › Sample Mail List Queries

Mail Lists explained through a Venn Diagram

Subscribe to Syndicate
A Venn Diagram is a great way to illustrate criteria for more complex mail lists. Most people find building criteria for a simple mail list fairly straight forward. For example:
  • find all patrons who donated money this year
  • find all patrons who donated money last year
  • find all patrons who donated money any year before this year
  • find all Patrons who donated in a specific period to certain campaigns
The examples above need exactly one mail list - since the criteria needs only to be applied to each record when it is considered for inclusion in the result. If the record matches the criteria, it is selected.

The above concept also applies to any data in Theatre Manager such as ticket buyers last year, subscribers this year, gift certificate users in the past 3 years, volunteers since the beginning of time, etc.

There are 4 combinations that seem a little harder that might require either 'Add' and 'Remove' groups or more than one mail list as illustrated in the Venn Diagram above. These slightly more complex groups are:

  1. Blue group but not in Pink - or in the example above Patrons who did something last year but not this year. This type of group describes two very common data base search needs:
    • per the example above LYBUNT - last year but not this
    • SYBUNT - some year but not this
  2. Pink group but not in Blue - or using the example above Patrons who did something this year but not last year
  3. in both Blue and Pink groups - or per the example above Patrons who did something in both years
  4. in neither Blue and Pink groups - or Patrons who did not do anything in either year
The key difference in the above searches is that multiple data records must be considered before the patron is selected. In the simple example of gave last year but not this, Theatre Manager needs to consider two DISPARATE SETS of data records to find the difference.
Multiple criteria are represented by add/remove groups .. which you can think of as combining multiple Venn Diagrams together.
Individual criteria groups can use the having clause to create very powerful queries that find very targeted groups of patrons.

eg: you can find all donors that gave 3 or more times last year and didn't give 5 or more times the year before.

 

Last year but not this

(Blue group but not in Pink)

One way of thinking of this is people who are in one group, but not the other. There are two ways to accomplish this:

  • You can build your entire query in one mail list using:
    • an 'add group' with some inline criteria -and-
    • a 'remove group' with some inline criteria
    -or-
  • a mail list that refers to two other mail lists that have been previously built to identify patrons.

    -or-

  • A combination of inline criteria and/or mail lists to identify the different SETS of data

The above example shows:

  • An add group that finds tickets for patrons where the season is 2019
  • A remove group that then eliminates those patrons who have a ticket for the 2020 season
The criteria in each group can be multiple lines to be as restrictive as you wish. The important thing is that each group represents a different SET of patrons that must be found first before they can be compared.

This is similar to the above since it has both add and remove groups. The criteria within the group just happens to refer to previous mail lists that have been built to identify the SETS of patrons.

This year but not last

(Pink group but not in Blue)

This is similar to the case for 'last year but not this'. The criteria is simply reversed - since in both cases you are taking one SET (group) of patrons and removing the second SET (group) of patrons.

Again, you can:

  • build your entire query in one mail list using:
    • an 'add group' with some inline criteria -and-
    • a 'remove group' with some inline criteria
    -or-
  • a mail list that refers to two other mail lists that have been previously built to identify patrons.

    -or-

  • A combination of inline criteria and/or mail lists to identify the different SETS of data

This example shows the use of two mail lists to find the difference in the groups of people.

You could use the add and remove group approach as per the previous example.

In both groups

(in both Blue and Pink)

Something that must be in two groups is called the intersection set. To find patrons like this in theatre manager, you need to do it be creating two temporary lists. eg:

  • Build a mail list of people representing group A. In the example above, that would be the 'blue circle' or people who are in 'last year' mail list.
  • Build a mail list of people representing group B. In the example above, that would be the 'pink circle' or people who are in 'this year' mail list.
  • Build the the mail list that uses is all of to find people in all of last year and this year per the image to the right
Finding the intersection of two SETS (i.e. patrons in both groups) can only be done by pre-building two lists that represent each individual group and using a third list to find people in both lists.

In neither group

NOT in Blue and NOT in Pink)

Finding people in neither group is a little tricky. You need to find the group of everybody you are considering and then remove group 'A' and then remove group 'B'. You can do it in one of three ways:

  • You can build your entire query in one mail list using:
    • an 'add group' with some inline criteria -and-
    • a 'remove group' with some inline criteria -and-
    • a second 'remove group' with some different inline criteria
    -or-
    • An 'add' group with inline criteria or one that refers to a prebuilt mail list of all people.
    • A 'remove' group with a mail list that refers to two other mail lists that have been previously built to identify patrons.

      -or-

  • The use of NOT in an 'add' group that finds people in both mail lists (see previous example) in order to NEGATE the search. This approach is useful if the set of everybody really means everybody in the database.

    -or-

  • A combination of inline criteria and/or mail lists to identify the different SETS of data
Note: finding people not in a group starts with the premise that you need to define everybody and then remove patrons from that list. For example:
  • Everybody could literally mean everybody in the database -or-
  • all donors forever, -or-
  • all ticket buyers who paid money for tickets in the past two years (ignoring comps) -or-
  • all subscribers who bough their subscription using the promotion 'early bird renewal'
If your meaning of everybody is a limited set of patrons, you will need an 'add' group at the beginning of the criteria to specify the meaning of 'everybody'

In this example, everybody is explicitly defined as all patrons with a patron number greater than zero and the remove group says patrons belongs to mail lists 'is all of' last year or thins year.

They means remove patrons who are on both prebuilt mail lists (or all people who are not in last year or this year)

In this sample, everybody is patrons who are subscribers and part of the control house for dinner theatre. It then removes all donors and them removes all volunteers.

This results in subscribers who have never donated or volunteered.

In this example, we are looking for everybody who does not belong to any of the two prebuilt mail lists. Everybody is implied since Theatre Manager scans for all data, unless explicitly told not to.

In this case, Theatre Manager was told to only include people 'in none of' the mail lists.

Diataxis: 
Explanation