Check out the Admin Console's Application Triggers for a simplified way to define criteria for a specific application-related event and the subsequent action that should take place after that event occurs. This includes assigning an application to the desired party after its been created, sending notifications (immediately or via a daily digest), and marking an application as ready for review after it has been submitted.
The Query Watch is a very powerful feature that leverages some of the software's core functionality and allows you to configure a robust system of notifications and data export. While the software contains many features that allow you to communicate and obtain critical data from your database, pulling these features together into an automated system is a necessary step in developing a workflow and integration that previously had to be managed manually or with custom built solutions.
While the interfaces and functions of the Query Watch tool are relatively simple, understanding the breadth on which they can be deployed is dependent on seeing how they are connected with other tools within the software. This document covers the functionality of the Query Watch interfaces and functions, as well as case examples for how a Query Watch can be used on a site.
These examples include:
- Daily lists of new applications that are emailed to advisors
- Hourly notifications about withdrawn applications
- Weekly emails for program reviewers with lists of complete applications for their programs
- Promotional emails being sent to applicants that have just become alumni of a program
- Daily notifications to department heads regarding their students applying to study abroad
This article covers the following features related to the Query Watch feature:
- Query Watch Actions
- Creating a Query Watch
- Editing a Saved Query
- Query Watch Examples
- Deleting Query Watches
- Query Watch Email Text Interface
- Frequently Asked Questions
A Query Watch is an event that automatically runs or "executes" a saved query at a pre-determined interval, records the differences between each execution of that query, and takes various actions based on that data.
When a Query Watch is created, the admin that creates it will identify one of their saved queries as the query to be executed. This can be something as simple as "All Pending Applications", or it can be as complex as, "All Accepted and Committed applicants that started their applications after 01/01/20 and have the application tag of 'Financial Aid' and have 'Business' as their value for major".
Any query, simple or advanced, can be used to create a Query Watch.
A query is automatically executed on a designated interval. This interval, or the time between a query watch execution, can be every 30 minutes, every day, every week, etc. The interval will depend on how often you would like to have actions taken based on the results of this query. In some cases, you will be looking to set up an email that is intended to be a daily report. In other cases, you may wish to have a notification sent out within an hour.
A history of the executions can be accessed by clicking on the Query Watch name. After each execution of the saved query, the results are stored in the "ALL" list on the history page, but they are also compared with the results of the previous execution of the saved query. Records that were not in the previous execution's results are flagged as "IN" records. In addition, any applications that were in the previous execution's results but are not in the current result list are flagged as "OUT" records. Administrators can access a history of each execution by clicking the Query Watch name.
On the Query Watch History page, the "IN", "OUT", "IN+OUT", and "ALL" categories save the list of applications at the time of each query. However, application information changes will be reflected if you go back to look at an older query. For example, if you set up a Query Watch for pending applications, it will save a list of all pending applications at the time of the query. If you go back and look at the list from five days ago, the same students will be included; however, their status may now show up as accepted.
After running the query and recording these results as "IN", "OUT", "IN+OUT", and "ALL" records, there are two standard actions that can be taken based on these records. One option is a saved export using a saved report as the template for the data to be included in the export. This export is then available for an administrator to open and view as a spreadsheet.
Important Note: Only the administrator who created the Query Watch and is logged into their account can see the report.
Another standard option is that an email is sent out to designated email addresses or to the email addresses of the applicants in the "IN", "OUT", "IN+OUT", and "ALL" records.
It is possible to have Terra Dotta create a custom action for a Query Watch. All custom actions are considered a professional service. Examples of custom actions for Query Watches include:
- System adds an application tag to the "IN" and/or "OUT" applications
- System changes the status of the "IN" and/or "OUT" applications
- System changes an applicant parameter value
For more information about the procedure/cost of creating a custom action, please open a Support request. Query Watches provide the basis for a reporting/notification system that can handle many tasks in your office's workflow. Utilizing Query Watches can alert the administrator when it is necessary to log in and take action on an applicant's application or provide additional information to the applicants at the appropriate time in the application life cycle.
Important Note: Before creating a Query Watch, it is important to note that no emails will be sent during the first run of that Query Watch. See the "Frequently Asked Questions" section at the end of this article for details.
If an administrator has the Query Watch permission, there will be a tab at the top of the admin home page for "Query Watches". Clicking on this tab will take the admin user to their current list of Query Watches. This page also displays the Query Watch usage for your site which shows how many query watches have been created by other administrators.
Each admin has their own distinct list of Query Watches, and this listing will display the configuration of each of them. It will also provide the report generated (if one was configured) from the last time the query was executed.
A new Query Watch can be created by clicking on the "New Query Watch" tab at the top of the page. This action directs you to the Query Watch configuration page.
Information on each configuration field is as follows:
Query Watch Name
The name should describe what this Query Watch is tracking, which might not be the same thing as the name of the saved query that is being executed. If you elect to have the Query Watch e-mailed to yourself or to others, the name of the Query Watch will appear in the subject line of the sent e-mail.
Frequency
The frequency is how often you want the saved query to be executed. Choose to have the Query Watch executed at an interval, daily, weekly or monthly at a specified time of the day.
- Interval: Select to execute the Query Watch more than once per day. After choosing "Interval", determine how often to run the query based on minutes or hours as desired.
- Daily: Select to execute the Query Watch once per day. After choosing "Daily", enter a desired execution time.
- Weekly: Select to execute the Query Watch once per week. After choosing "Weekly", select the desired day of the week on which to execute the query along with the desired execution time.
- Monthly: Select to execute the Query Watch once per month. After choosing "Monthly", select the desired day of the month on which to execute the query along with the desired execution time.
Important Note: Many Query Watches running simultaneously or repeatedly throughout the day can affect your sites performance as it is a drain on the server. It is not recommended to have Query Watches run more frequently than every hour.
Duration
Specify for what period of time you want the Query Watch to run. For example, if this is a Query Watch that is meant to alert a specific faculty member of new applications for the Fall 2020 Intensive Spanish Language program, then you can put an end date similar to the deadline for this program for Fall 2020. That way, once students are no longer permitted to create new applications, this Query Watch will stop being executed.
- First Date to Run: The Query Watch will make its first execution on this date.
- Last Date to Run: The Query Watch will make its last execution on this date.
Name of Saved Query
Select the query you would like to use from a drop-down menu listing of your saved and/or shared queries. You can only create Query Watches for queries that you have created.
Actions Taken on Update
These checkboxes indicate what should occur upon each execution of the saved query.
- Export: Produces a report each time the query is executed and is available to the admin who created the Query Watch. If you choose this option, you will need to check the Query Watch page to see the most recent report - or the Query Watch history page to see changes in the list over the past executions.
- Email: Sends an email each time the Query Watch is executed.
- Custom Process: When selected, the drop-down menu will only populate with options if your site has a custom process with Terra Dotta.
-
Workflow: When selected, this option will kick-off an existing workflow under Staff > Workflow.
- Single Workflow: One workflow will be triggered to start when there are any records reported in the "Records Included in Actions" that you have selected for this Query Watch.
- Records Included in Actions as Targets: One workflow will be triggered to start for each record reported in the "Records Included in Actions" that you have selected for this Query Watch.
Important Note: Actions taken will only occur if there are applicable applications included in the results of the saved query. For example, if you set up a "new application created" notification email that runs every 30 minutes, the email will only be sent if there is at least one new application that was created in that 30-minute period.
Records Included in Actions
These checkboxes are used to indicate which records you want the export, email, or workflow to include in the results of the query.
- IN: Include new records in the results of the query
- OUT: Include records that were in the previous execution of the query but not in the current results
- ALL: Include the complete list of records in the results of the query.
Report Format for Export
If you are saving an export, you can select one of your saved reports as the template for the output to be generated for the "Records Included in Actions". This saved report will define the data columns for the export.
If you have chosen to have "email" as an action taken upon update, you will see additional form options appear on the screen:
-
- Email Recipient List: A comma-delimited list of the email addresses that should receive the notification (i.e. yourself, a reviewer, an on-site director, etc.). If you mark the checkbox under this field, the applicants in the "Records Included in Actions" will be sent the notification. For example, if I only want my program reviewer, Marisol Valeska, to get the list of new applicants to her program, I would put "mvaleska@terradotta.com" into the "Email recipient list". However, if I want all the new applicants to a program to get an email sent to them within a day asking them to schedule an advising meeting, I would mark the "Send email to the addresses in..." checkbox.
- Email Message Body: This is the text of the email that is being sent to the recipients. A custom email template created in Admin Home > Admin Settings can be utilized here. This editor also includes the mail merge functionality, allowing you to personalize emails generated by query watches. Note: Merge fields do not pull in application data when an email is sent to a staff member because that person does not have an application from which to pull that data. The staff member will not see the applicants' data populated in the merge field since the email is delivered directly to them. This is important to remember when staff members are copied on emails to applicants or included directly in the query watch.
- Include list of records in email: This option determines whether or not the recipient gets the list of applications included in the email. This is helpful when an admin is receiving the email. This must be "No" if the Query Watch is going to send an email to the applicants.
After configuring the Query Watch, the "Add" button is clicked. When initially created, the query will be executed for an initial listing of the records to be watched, and it will make its first actionable execution after the time interval that was provided.
Important information for sites hosted with Terra Dotta:
A maintenance window is reserved every Friday from 11pm to 12am Eastern. You should not schedule query watches to run during this time as the task may be disrupted if site is undergoing maintenance.
When a query watch has been created and executed a number of times, the query watch can be edited to have a different saved query associated with it. When this is done, the query watch will continue to operate, but there may be unexpected behavior upon the first execution of the new saved query associated with the query watch.
While the ALL records for that initial execution will be the results of the new saved query, the IN and OUT records will reflect the changes between the first execution of the new saved query and the last results of the previously associated saved query.
For instance, if I have a query watch associated with PENDING applicants that has been running for a few days and the last time it ran there were 50 ALL records in the results, then I switch it to a saved query associated with COMMITTED applicants, the first time that query watch executes after the switch, there will be 50 OUT records - these OUT records are the 50 applications that were the 50 ALL records from the last time the query ran for PENDING applicants.
If you wish to fine-tune a query watch to involve different query criteria, it may be more effective to delete the old query watch and create a new one for cases in which you will be eliminating certain applicants from the results. This would be necessary for accurate actions taken as a part of the query watch.
While the ALL records for that initial execution will be the results of the new saved query, the IN and OUT records will reflect the changes between the first execution of the new saved query and the last results of the previously associated saved query.
For instance, if I have a query watch associated with PENDING applicants that has been running for a few days and the last time it ran there were 50 ALL records in the results, then I switch it to a saved query associated with COMMITTED applicants, the first time that query watch executes after the switch, there will be 50 OUT records - these OUT records are the 50 applications that were the 50 ALL records from the last time the query ran for PENDING applicants.
If you wish to fine-tune a query watch to involve different query criteria, it may be more effective to delete the old query watch and create a new one for cases in which you will be eliminating certain applicants from the results. This would be necessary for accurate actions taken as a part of the query watch.
The following examples are ways that you can use a Query Watch for notifications that your office can implement.
Daily Lists of New Applications that are Emailed to Advisors
Each day, an office advisor is expected to find any new applications in the system that have been created by the students and follow up accordingly. The advisor checks to make sure the student's GPA, major, and conduct standing are acceptable for the program. The advisor would do this by logging in each day and running a query of any applications with a 'created date' since the prior day. While this works fine during the majority of the application cycle, there are a number of weeks during which new applications are quite rare and the process of running this application query daily becomes an unnecessary chore.
Instead of running this query every day (which cannot be a saved query since the date is different every day), an administrator is going to set up a Query Watch that will email this advisor with a list of the new applications that were created in the previous 24 hours. If the advisor doesn't receive this email at the beginning of the day, the advisor knows that there are no new applications in the system. When the advisor does receive the email, the advisor gets a list of the applications that require reviewing.
Here is how the administrator sets this up:
- The administrator creates a saved query that is the base of all pending applications in the system. It is an advanced application search for all PENDING applicants. The admin saved this query as 'all PENDING applications'
- The administrator clicks on the 'Query Watches' tab on the admin homepage
- The administrator clicks on the 'New Query Watch' tab
- The administrator names the Query Watch "New Application Notification" and sets the frequency at 'daily' to occur at 8:00am
- The action taken on update 'email' checkbox is marked and only the 'IN' checkbox is marked for the 'records included in actions' options
- The email address of the advisor is entered into the 'Email recipient list' and the text 'New applications since yesterday:' is entered for the email message body
- The admin leaves the 'Include index of records in email?' set to 'Yes' and clicks the 'Add' button
Hourly Notifications on Withdrawn Applications
As the application deadline for the Summer 2020 application cycle approaches, we want to keep track of the applications that are getting withdrawn over the final two weeks that it is open. Many times the students are close to having the applications complete and only need a bit of assistance from an advisor to get it done. Instead of catching these on a daily basis, we want to have someone get notified ASAP when a student withdraws an application rather than having to repeatedly run queries (and these queries can only find the changes for a full prior day).
An administrator is going to set up a Query Watch email notification to be sent to the summer programs advisor. This notification will be sent hourly with a list of all the applicants that have withdrawn from a Summer 2020 program - if no applicants have withdrawn their applications, no email will be sent.
Here is how the administrator sets this up:
- The administrator creates a saved query for all the Summer 2020 applications in the withdrawn status. It is an advanced application search for all WITHDRAWN applicants for the Summer 2020 app cycle. The admin saved this query as 'Withdrawn Summer 2020 apps'
- The administrator clicks on the 'Query Watches' tab on the admin homepage
- The administrator clicks on the 'New Query Watch' tab
- The administrator names the Query Watch "Summer 2020 Withdraw Alert" and sets the frequency at Daily every 60 minutes
- The administrator selects the summer programs deadline as the end date for the Query Watch. The summer program advisor will not need the notifications after the deadline.
- The action taken on update 'email' checkbox is marked and only the 'IN' checkbox is marked for the 'records included in actions' options
- The email address of the advisor is entered into the 'Email recipient list' and the text 'Application withdraws in last hour:' is entered for the email message body
- The admin leaves the 'Include index of records in email?' set to 'Yes' and clicks the 'Add' button
The summer program administrator will immediately begin getting the notifications.
Weekly Emails for Program Reviewers with Lists of Complete Applications for Their Programs
There are a number of programs for which the faculty program reviewers would like to be notified when the applicants have completed all the pre-decision requirements. While they are setup in the system with reviewer access to see these applications, they need to login every day to find the complete applications. In addition, they can't tell which applications had been completed a week ago vs. just yesterday. These reviewers don't want to learn how to use the advanced query tool, and the administrator doesn't want to provide them with that permission anyway.
Instead, a Query Watch notification will be setup so that each week, the program reviewers receive an email listing all the applicants to their program that have completed all the pre-decision program requirements in the past week. They can use this list to determine which applications to look at on their Reviewer Home Page. In addition, if they don't receive the weekly email, they know that there are no new complete applications to be reviewed.
Here is how the administrator sets this up:
- The administrator creates a saved query for each faculty program reviewer's program. It is an advanced application search for all PENDING applicants who have a complete flag and are participating in a specific program(s). Each one of these saved queries is for PENDING applications that have been marked as complete since the first of the year. (The date is irrelevant so long as it is in the past). NOTE: An application is marked as complete when all elements for a specific programs and a specific phase have been completed and are checked off. The system will add a complete flag to applications that meet these criteria once a day, typically in the early morning.
- The administrator clicks on the 'Query Watches' tab on the admin homepage
- The administrator clicks on the 'New Query Watch' tab
- The administrator names the Query Watch (for example: "Complete Ireland Apps") and sets the frequency to weekly
- The action taken on update 'email' checkbox is marked and only the 'IN' checkbox is marked for the 'records included in actions' options
- The email address of the program reviewer of the Ireland program is entered into the 'Email recipient list' and the text 'Applications completed in the last week:' is entered for the email message body
- The admin leaves the 'Include index of records in email?' set to 'Yes' and clicks the 'Add' button
- The admin repeats 3-7 for each program/program reviewer
- The program reviewers will now get a weekly email alerting them to the new applications that need to be reviewed.
Daily Notifications to Department Heads Re: Students Applying to Study Abroad
Three departments at our institution require notification whenever they have students from their department applying to study abroad so that the department head can make sure their advisors are aware of the applicant's intent to spend a semester off-campus. The department heads would like to be notified of these applications immediately. This had been done in the past by having an advisor run a query each day to find out if there were any new applications for each of the three majors and sending out the lists to the department heads via email.
An administrator will be setting up a Query Watch email notification that will take care of this automatically and send the department heads a daily report listing any new applicants from their department.
Here is how the administrator sets this up:
- The administrator creates a saved query for each department. It is an advanced application and applicant search. Each one of these saved queries is for PENDING applications in which the applicant has that specific department for the applicant parameter 'Department'.
- The administrator clicks on the 'Query Watches' tab on the admin homepage
- The administrator clicks on the 'New Query Watch' tab
- The administrator names the Query Watch "Department notification: Business" and sets the frequency to daily
- The administrator does not add an end date because this is something that they want to happen indefinitely
- The action taken on update 'email' checkbox is marked and only the 'IN' checkbox is marked for the 'records included in actions' options
- The email address of the department head for the business school is entered into the 'Email recipient list' and the text 'New business school study abroad applicants:' is entered for the email message body
- The admin leaves the 'Include index of records in email?' set to 'Yes' and clicks the 'Add' button
- The admin repeats steps 3-7 for each department/department head
Deleting Query Watches
Ideally, one of the tasks a person should complete before leaving your office, is to review all query watches they may have created and either delete them or help another staff member re-create them. If a former staff member did not complete this task before leaving your office, you might find yourself and others receiving emails from this former staff member's query watches. Their query watches may or may not still be relevant to your office processes.
If the former staff member's query watches are no longer relevant or necessary, they can be simply deleted from your site:
- Hosted/SaaS: Open a case and request that all query watches created by that particular staff member be deleted.
- Installed: Ask a site administrator who has the 'Unrestricted Access' Maintenance permission to delete the query watches by going to Admin Home > Query Watches tab and click the red X next to the unwanted watches.
If you aren't sure which query watches the former staff member had, you won't want to delete them before reviewing the watches that are in the list. For Hosted/SaaS clients, Terra Dotta can provide a screenshot of all the query watch names. For Installed clients, any site administrator with the 'Unrestricted Access' permission will be able to see the query watches created by the former staff member.
If you need more transparency into the query watch configuration and saved query search criteria the former staff member had, please open a case and Terra Dotta can provide information about how you can accomplish this.
Please note: It is not possible to reassign a query watch from a former staff member to someone who is still in your office. If the Query Watch is still useful, it will be necessary to rebuild it under a different user record. Both the saved query and the query watch will need to be rebuilt by a new user for it to function as it did before.
It is also a good idea for all staff in your office who have created query watches, to look at their configuration pages when a staff member leaves and erase the ex-employee's email address if it appears in any 'Email recipient list' box. This will prevent annoying bounced email messages from cluttering up your Inbox.
Query Watch Email Text Interface
When Terra Dotta software sends a query watch email alert the subject line says "Query Watch Report:" and then lists the name of the query watch. If you are using query watch to email applicants it would make sense to edit the text 'Query Watch Report:" as they may not know what a Query Watch actually is.
You can do that by editing text interface field #12365. The name of the query watch will automatically be added to the subject line and can be edited by editing the name of the query watch.
By changing the text 'Query Watch Report' it will change the subject line of all query watch emails sent from your site.
Important Note: When removing all text from a text interface field, always replace the existing value with a single space to avoid any possible issues on your site. Never completely empty a text interface field.
Frequently Asked Questions
1. I have created a new Query Watch, yet no emails have been sent on the first run only. Why does this happen?
When a Query Watch is first created, it will execute the saved query during the first run to get an initial list of all records from which to begin. These applications do not generate email actions. If all of the applications being pulled in subsequent runs of the query were in the initial run of this Query Watch, then no emails will be sent.
To verify that all the records in the latest run of your query were in the initial run of the query, compare the number of ALL records in the first run (ID #1) in the Watch History with the number of ALL records in the latest run of the query. They should be the same number.
When a new application is created for this program, an email will be sent to the new applicant. If you wish to send this same email to the initial group of applicants, you can take those applications and use the batch action option to send an email to these applicants.
If you wish to have the applicants in the initial ALL records receive an email notification, you can view the ALL records for that initial execution and then use the batch email function to send it manually.