Patrons on at Least One of Multiple Mail Lists Top
By combining mail lists via criteria, you gain access to the data of many lists by forming a new list. Unlike merging mail lists, in which case the reference lists become one list, this allows you to keep the original lists intact and unchanged. This example uses the data file Mail Lists Associated with a Patron to pull patrons who are in either the Classic Plays Mail List or in the Musicals mail list. The criteria reads: Mail list # is one of Classic Plays or Musicals
. If the criteria is changed in either of these referenced mail lists, then this combined list must be executed again to update the information.
Patrons with Tickets to a Specific Event Top
Perhaps the most commonly used criteria, this criteria finds patrons with tickets to specified event(s). In this example, we use the data file Ticket Sales Data to find patrons with tickets to the Rolling Lego Concert. The criteria reads: Play # is one of (10-OZ) Rolling Lego Concert
. Theater Manager scans every ticket in the database to determine if its attached event matches the search. Since we are using this criteria within a Mail List (versus a report), the system automatically adds the second criteria line (and add the patron to whom the ticket was sold
) to then identify the patrons attached to tickets that match the search. Note that this criteria only searches ticket holders, not patrons who attended the event, which would require another search parameter based on the ticket scanned status of each record.
Lost Donors (LYBUNT) Top
This common criteria need puts venn diagram thinking into practice. LYBUNT is an acronym for last year but unfortunately not this year. You can apply it in many ways to ANY DATA in the database. For example, patrons who:
- volunteered the past 3 years, but not this year
- donated every even year, but not this year
- subscribed 4 years ago but not 3 years ago
- lapsed donors who donated last year but did not buy a ticket this year
- this version is interesting because it requires different data files for the second criteria (tickets) than for the first (donations)
The general process, regardless of what data file you use, is:
In Theatre Manager, to identify WHO falls into this category, we use a Mail List with two groups.
- Create a group to add people to the Mail List using criteria from the past
- Create a second group to remove people from the Mail List using criteria from the present
In this example, we use the data file Donation Data for a Patron with Each Gift to find patrons who donated in 2011 but not 2012. The add criteria reads: Donation Date is between Jan 1 2011 and Dec 31 2011
. The remove criteria reads: Donation Date is between Jan 1 2012 and Dec 31 2012
. You can make the add and remove criteria blocks as complicated as you wish. For example, you could further limit the search by date range, campaign type, giving level, address, etc.
Sometimes you may want to expand your LYBUNT search to search across household or to remove people if anybody in the household gave the second year. This means you must remove both primary and non-primary members of the household. This is possible, but involves a small nuance to the remove steps:
- Create an add step to add patron who donated (or volunteered, purchased tickets, etc)
- Create the first remove step to remove primary patrons only
- Create the second remove to remove non-primary patrons only
Patrons with Tickets to More than One Event Top
This criteria answers the question, "How do we find people who bought multiple events?". By changing the data file you search, you can use this technique (the having function) to find people who:
- donated multiple times
- volunteered multiple times
- received multiple mailings or eblasts
- bought multiple gift certificates
The general process, regardless of what data file you use, is:
- Enter the criteria that every individual item (e.g. ticket, pass, donation, letter) must match
- Add a having clause that looks for a unique count of item sequence numbers (e.g. event #, pass/g.c. type #, donation #, letter type #)
In this example, we use the data file Ticket Sales Data to find patrons with at least one ticket to a subset of events. The criteria reads: Play # is one of (12-001) Rainmaker or (11-002) Naked On the Info Highway or (12-01) Sweet Bird of Youth or (21-02) Rollin' Stones Concert
. Since this is a Mail List query, Theatre Manager automatically adds a second criteria line to identify the patrons associated with matching tickets. We used the having function to refine our results to only those patrons who have (HAVING:
) qualifying tickets to at least two (that is greater than or equal to 2
) different events (a unique count of Play #s
). You can make the first part of the criteria (above the Having line) as complicated as you wish. For example, you could further limit the search to only regular ticket types sold online between a specific date range.
Patrons With a Minimum Amount of Cumulative Giving in a Year Top
A common criteria request is "a list of patrons whose combined donations a year total at least X." In this example, we use the data file Donation Sales Data for a Patron with Each Gift to find patrons who have promised at least $500 towards a 2012 donation. The criteria reads: Donation Date is between Jan 1 2012 and Dec 31 2012
. We then used the having function to refine our results to only those patrons whose donations add up to (HAVING: a sum of Donation Amount-Actual
) at least $500 (that is greater than 500
).
Patrons with Subscription Tickets Within a Season Top
Since every ticket is sold with a sales promotion and sales promotions are tagged with a type (regular, season, education, group, other), we can easily scan ticket data to identify subscribers versus other patrons. In this example, we use the data file Ticket Sales Data to find patrons who have subscription tickets to a select list of events. The criteria reads: Play # is one of (11-D1) Fiddler on the Roof or (11-D2) You Say Tomato or (11-D3) Long Weekends are for Fun and Promotion Ticket Type is one of Season
. Since we are using this criteria within a Mail List (versus a report), the system automatically adds the second criteria line (and add the patron to whom the ticket was sold
) to then identify the patrons attached to tickets that match the search.
Patrons Without a Primary Email Address Top
You can build mail lists for people that have primary email, phone, fax or web addresses. Or conversely, you can build mail lists of patrons that do not have any email, phone, fax, or web addresses. If somebody has an email address, then the primary email address unique # will be non-zero. The same holds for phone, fax, address, etc. So to find patrons missing a primary contact type, we need to identify patrons whose primary unique # is zero. In this example, we use a field in the Marketing Patron Data table, which is accessible from all data files The criteria reads: Marketing Primary Email Unique # is equal to 0
and it can be combined with just about any other criteria.