Commonly Used Person-Scoped Exports and Filters

The Slate Template Library (STL) offered a list of common exports and filters that many clients used to query on data. Those exports and filters, however, are not accessible in Configurable Joins. We've rebuilt many of these exports and filters in query libraries that you can bring into your instance using Suitcase to ease your use of Configurable Joins. To Suitcase the exports and filters enumerated below in:

  Ready-Made Examples

Use the Suitcase IDs below to import our ready-made examples directly into your database. 

Single Query Library Separate Query Libraries
This Suitcase ID includes two query libraries 62be3c84-4e5f-4673-aa42-4d6f628eb9ad:slate-examples

These query libraries also include commonly used application-scoped exports and filters, which you can learn more about here

Exports

Biographical

Name Intent
Number of Submitted Applications

Exports the number of applications per student. Additional filter criteria can be configured within the export, such as only counting the number of applications per round. 

Application History (Descending Order)

Exports values about the multiple applications someone may have. The applications returned can be reduced by adding filters within this export.

Max Score 1 by Test Type and Status

"Max Score" is the highest score per exam type per student. The test type and status will need to be configured for this export to function. 

Max Score 1 + Max Score 2 by Test Type and Status

"Max Score" is the highest score per exam type per student. The test type and status will need to be configured for this export to function. 

Max Score 1 Score and Test Date

Exports the highest test score and date per the test type and status filter criteria you configure.

All Tests Received

This export will concatenate all test scores received. By default, the export outputs status, name, and subtype.

Rank 1 Overall Address - ZIP Data by Data Point

US ZIP Data values from American Community Survey (ACS) can be exported using this export. This export will draw ACS values using the person record’s overall rank 1 address. A ZIP Data ‘Data Point’ will need to be specified from the export's join.

Rank 1 Mailing Address by Type - ZIP Data by Data Point

US ZIP Data values from American Community Survey (ACS) can be exported using this export. This export will draw ACS values using the person record’s rank 1 mailing address. A ZIP Data ‘Data Point’ will need to be specified from the export's join.

Rank 1 Permanent Address by Type - ZIP Data by Data Point

US ZIP Data values from American Community Survey (ACS) can be exported using this export. This export will draw ACS values using the person record’s rank 1 permanent address. A ZIP Data ‘Data Point’ will need to be specified from the export's join.

Parents Active Address

Exports the rank 1 parent’s address. If the student lives with the parent, the student’s address will be exported instead.

All Populations

Exports a list of all population names and timestamps that the person record is associated with.

Population Exists

Exports a ‘Yes’ or ‘No’ depending on whether the person's record exists in the selected population(s).

All Interests by Type

Exports a detailed concatenated list of all interests associated with a person’s record. Details can be adjusted, and additional filter criteria to limit which goods are returned can be added within the export’s configuration.

Person Status Change Dates by Status (Descending)

Exports the date, time, and status that a person status is changed on the record. An example of this would include a change from prospect to applicant person status.

 

Events & Forms

Name Intent
3 Most Recent Event Titles & Dates

Exports a concatenated list of the three most recent events sorted by descending Form Start Date.

All Event Registrations

Exports a concatenated list of events for which each person on the query provided a form response.

All Event Registrations by Folder

Exports a concatenated list of events on which each person in the query provided a form response for a specific folder you specify.

All Event Registrations by Template

Exports a concatenated list of events on which each person on the query provided a form response for a specific template you specify.

Form Field Value - Most Recent Submitted Form Response

Exports the most recent form response that a person submitted. Filter criteria can be modified to include Tentative, Declined, and Cancelled statuses.

All Forms Submitted Dates & Titles by Folder

Exports all form responses per person for the folder(s) specified.

All Forms Submitted Dates & Titles by Template

Exports all form responses per person for the template specified.

 

Interactions & Messages

Name Intent
Emails Opened in the Past 60 Days

Exports a count of the number of clicks and opens that a person has made on emails in the last 60 days. You can modify this export to search for people who have a specific number of clicks or opens. Use this export with caution as it is computationally expensive and involves large database tables. Using too many computationally expensive exports may result in the query timing out. 

Emails Clicked in the Past 60 Days

Exports a count of the number of clicks a person has made on emails in the last 60 days. Use this export with caution as it is computationally expensive and involves large database tables. Using too many computationally expensive exports may result in the query timing out. 

Most Recent Email Opened Subject

The subject line of the most recent mailing will be output if that mailing contains a click or open. 

Most Recent Email Opened Date

The most recent email opened date will be output for a mailing that contains a click or open.

First Email Opened Subject

The subject line of the oldest mailing opened (determined by ascending date value) will be output per person record.

First Email Opened Date

The email opened date of the oldest mailing opened (determined by ascending date value) will be output per person record.

Interaction Code Count between dates by Code

Exports a count of the number of interactions for the person record. Date ranges can be specified to specify further the count returned.

Mailings By Date

Exports a list of messages or mailing subjects and their delivery dates.

 

Schools

Name Intent
List of Schools by Level Of Study, Rank

Concatenated export of school names and types that are associated with a person’s record 

All Schools

Exports a list of all schools associated with the person record. By default, school names will be exported; however, that can be modified.

Total School Credit Hours by Level of Study

Exports the total credit hours for the student record. From within the export, the hours can be limited to a specific level of study.

Total School Credit Hours by Rank by Level of Study

Exports the total credit hours for the student record. Within the export, the hours can be limited to a specific level of study and a school rank.

 

Misc

Name Intent
List of All Tags by Most Recently Updated

Exports all tags associated with the person record by updated date.

Most Recent Ping Timestamp

Exports the most recent ping record’s date and time.

First Ping Timestamp

Exports the date and time of the oldest ping record associated with the person record.

Unique Ping URL Count

Exports the number of unique URLs for a person record's ping activity.

List of Tags by Created Date

Exports a list of tags associated with the person record and includes the created date for each tag.

List of Tags by Updated Date

Exports a list of tags associated with the person record and includes the most recent date each tag was updated.

All Origin Sources by Date

Exports a list of Origin sources and memos for the person record.

Number of Logins in the Past 60 Days

Exports a count of the number of logins for that person record. The date range can be adjusted.

Login History

Exports a login history list, including browser and platform information.

 

Filters 

Biographical

Name Intent
Has a Tag Set This filter will limit the query to return only person records that contain specific Tags. Tags can be selected from within the filter.
Has a Tag Set by Timestamp This filter will limit the query to return only person records containing specific Tags established during a specific date range. The Tags and date range need to be determined within the filter.
Not an Opt-Out / "Opt Out" Tag Not Set Excludes person records from being returned to the query if an “Opt Out” tag is set.
Not a Test Record / "Test Record" Tag Not Set Excludes person records from being returned to the query if an “Opt Out” tag is set. 
Has a Test Score by Type and Status This filter will limit the query to returning only person records associated with specific test types, configured within the filter criteria.
Has an Application This filter will check for the existence of any application records for the person record. The person record will not be returned to the query if no application(s) exist for that record.
Has a First Name This filter will check for the existence of a first name on the person record. Person records will not be returned to the query if no first name exists on the record.
First Name Contains Identifies records with a specific set of characters. For example, if your team tries to identify records with "test" in any part of the first name, this filter can be configured.
First Name Starts With Identifies records with a specific set of characters at the beginning of the first name.
First Name Ends With Identifies records with a specific set of characters at the end of the first name.
First Name Starts with Letters A-F Returns only person records to the query that have a first name whose first character is as you specify in the filter. To demonstrate, we set up the filter to return person records where the first name begins with any character between A and F.
First Name Ends with Letters A-F Returns only person records to the query that have a first name whose last character is as you specify in the filter. To demonstrate, we set up the filter to return person records where the first name ends with any character between A and F.
First Name Same as Last Name This is a comparison filter that returns person records with matching first and last name values.
Has a Middle Name This is an existence filter that returns person records with a middle name.
Has a Last Name This is an existence filter that returns person records with a last name.
Has a Sex This is an existence filter that returns person records that contain a value in the sex field.
In a Population/Populations This is an existence filter that returns person records where the person is assigned to a population. Within the filter, you can specify which population(s).
In a Population by Static Timestamp Returns person records assigned to a population during a specific date range.
In a Population by Dynamic Timestamp Returns person records that have been assigned specific population for a set amount of time.
In a Population by Timestamp Days Returns person records who entered a particular population a specific number of days ago.
Has a Birthdate Returns person records where a birthdate value exists.
Has an Email Address Returns person records where an email address value exists.
Has a Birthdate Month Returns person records where the birthdate month matches the specified filter criteria.
Email Address Ends With Returns email addresses that end as specified. For example, the filter can be configured to return all email addresses ending in a specific domain.
Has a Specific Birthdate  Year Returns person records where a specific birthdate year(s) exists.
Number of Applications Returns person records that have a specific number of application records.
Number of Submitted Applications Returns person records with a specific number of submitted application records.
Number of Submitted Applications by Round Returns person records with a specific number of submitted application records in a specific round.
Number of Submitted Application by Period Returns person records where a submitted application in a specified period exists.

 

Events & Forms

Name Intent
Has Event Registration by Event Folder and Event Start Date Range

Returns person records that have an event registration with the selected event folder(s) and event start date range.

Has Event Registration by Event Folder and Registration Status

Returns person records that have an event registration with the selected event folder(s) and registration status(es).

Has Event Registration by Event Folder and Submitted Date Range

Returns person records that have an event registration with the selected event folder(s) and the submitted date range.

Has Event Registration by Event Title and Registration Status Returns person records that have an event registration with the selected event title(s) and registration status(es).
Has Event Registration by Trip and Registration Status Returns person records that have an event registration with the selected trip(s) and registration status(es).
Has Event Registration by Folder, Registration Status, Form User 1

Returns person records that have an event registration with the selected folder(s), form user(s), and registration status(es).

Has Event Registration Count by Event Folder and Registration Status Returns person records that have the specified number of event registrations with the selected event folder(s) and registration status(es).
Has Event Registration Count by Event Folder, Registration Status, and Event Start Date Range Returns person records that have the specified number of event registrations with the selected event folder(s), registration status(es), and event start date range.
Has Event Registration Count by Event Template and Registration Status Returns person records that have the specified number of event registrations with the selected event template(s) and registration status(es).
Has Form/Event/Scheduler Registration Returns person records that have a form, event, or Scheduler registration.
Has Form Registration by Folder Returns person records that have a form registration with the selected folder(s).
Has Form Registration by Form and Registration Status Returns person records that have a form registration with the selected form(s) and registration status(es).
Has Form Registration by Form and Submitted Date Range Returns person records that have a form registration with the selected form(s) and submitted date range.
Has Form Registration by Form Submitted/Updated by User Returns person records that have a form registration with the selected form(s) and submitted/updated by the selected user(s).
Has Form Registration Count by Form and Registration Status Returns person records that have the specified number of form registrations with the selected form(s) and registration status(es).
Has Scheduler Registration by Folder and Registration Status Returns person records that have a Scheduler registration with the selected folder(s) and registration status(es).
Has Scheduler Registration by Template and Registration Status Returns person records that have a Scheduler registration with the selected template(s) and registration status(es).
Has Scheduler Registration by Template, Registration Status, and User 1/Interviewer Returns person records that have a Scheduler registration with the selected template(s), registration status(es), and user 1/interviewer.
Has Scheduler Registration Count by Folder and Registration Status Returns person records that have the specified number of Scheduler registrations with the selected folder(s) and registration status(es).
Has Scheduler Registration Count by Template and Registration Status Returns person records that have the specified number of Scheduler registrations with the selected template(s) and registration status(es).

 

Interactions & Messages

Name Intent
Contact in last 30 days Returns person records where a message delivery date exists in the last 30 days.
Has Interaction by Code Returns person records that have an interaction with the selected code(s).
Has Interaction by Code and Date Returns person records that have an interaction with the selected code(s) and date.
Has Interaction by Code, Date, and Subject Returns person records that have an interaction with the selected code(s), date, and subject.
Has Interaction by Code, Date, and User Returns person records that have an interaction with the selected code(s), date, and user(s).
Has Interaction Count by Code Returns person records that have the specified number of interactions with the selected code(s).
Has Message by Message Status Returns person records that have a message with the selected status(es).
Has Message by Mailing Name Returns person records that have a message with the selected mailing name(s).
Has Message by Mailing Name and Message Status Returns person records that have a message with the selected mailing name(s) and message status(es).
Has Message by Type Returns person records that have a message with the selected type(s).
Has Message by Message Status and Folder Returns person records that have a message with the selected message status(es) and mailing folder(s).
Has Message Count by Message Status Returns person records that have the specified number of messages with the selected status(es).

 

Schools

Name Intent
Has School by Level of Study Returns person records that have a school with the selected level of study/studies.
Has School by Level of Study and Conferred Date Returns person records that have a school with the selected level of study/studies and conferred date.
Has School Not in Organizations Dataset Returns person records that have a school with no corresponding organization record.
Has School by Level of Study Rank and Code Returns person records that have a school with the selected level of study rank and code(s).
Has School by Code Returns person records that have a school with the selected code(s).
Has School by Name Returns person records that have a school with the specified name.
Has School Returns person records that have a school.
Has School by State Returns person records that have a school with the selected US state(s).
Has Number of Schools by Country Returns person records that have the specified number of schools with the selected country/countries.
Has Number of Schools by Level of Study  Returns person records that have the specified number of schools with the selected level of study/studies.
Has Number of Schools by Level of Study and Degree Returns person records that have the specified number of schools with the selected level of study/studies and degree(s).

 

Misc

Name Intent
Has Field by Field ID Returns person records where a specific field ID value exists.
Has Field by Field ID and Timestamp Returns person records that meet the specified field ID and timestamp criteria.
Has Field by Prompt Active Status Returns person records with specific active prompts.
Has Fields by Number of Values Returns person records with a certain number of values in a specific field.
Has Field by Sum of Values Returns person records where the sum of a specified field value is of a specific value.
Was this article helpful?
2 out of 2 found this helpful